You can create a user-defined script that performs any task you wish. These are often called from the output expression of a field or from another script.

How you call a user-defined script and what is passed to it depends on the script language you use.

VBScript and JavaScript

To call a VBScript and JavaScript user-defined script, use the built-in Stonefield Query RunScript function. The first parameter you pass to this function is the name of the script. Any parameters you wish to pass to the script are specified as additional parameters to RunScript. The following example calls the GetCategory script, passing it the current value of the CategoryID field:

RunScript('GetCategory', CategoryID)

VBScript and JavaScript user-defined scripts are automatically passed a reference to the Stonefield Query Application object, so the script code must accept this as the first parameter (the template code displayed when you create the script shows the typical syntax for this). Here are examples of the GetCategory script in both VBScript and JavaScript:

VBScript

function Main(Application, ID)
dim Database, XMLDOM, Node
set Database = Application.DataEngine.Databases.GetMainDatabase()
CharID = CStr(ID)
SelectStmt = "select CategoryName from Categories " & _
  "where CategoryID = " & CharID
XMLResult = Database.ExecuteSQLStatement(SelectStmt)
set XMLDOM = createobject("MSXML.DOMDocument")
XMLDOM.ASync = False
XMLDOM.LoadXML(XMLResult)
Main = XMLDOM.selectSingleNode("/DataSet/_resultset/categoryid").Text
end function

JavaScript

function Main(Application, ID) {
var Database, CharID, SelectStmt, XMLResult, XMLDOM, Node ;
Database = Application.DataEngine.Databases.GetMainDatabase() ;
CharID = toString(ID) ;
SelectStmt = 'select CategoryName from Categories ' +
  'where CategoryID = ' + CharID ;
XMLResult = Database.ExecuteSQLStatement(SelectStmt) ;
XMLDOM = new ActiveXObject('MSXML.DOMDocument') ;
XMLDOM.ASync = false ;
XMLDOM.LoadXML(XMLResult) ;
return XMLDOM.selectSingleNode('/DataSet/_resultset/categoryid').Text ;
}

Note that this code must hit the database for every field in the query. For a small result set, the performance is acceptable, but this script can be made significantly faster by pulling all of the records from the Categories table into memory the first time the script is called, and on subsequent calls, looking up the value in the in-memory result set. Here's the better script:

VBScript

function Main(Application, ID)
dim XMLDOM, Database, Node
XMLDOM = "Test"
on error resume next
XMLDOM = Application.Categories
if XMLDOM = "Test" then
  Application.AddProperty "Categories", null
end if
on error goto 0
if isnull(Application.Categories) then
  SelectStmt = "select CategoryID, CategoryName from Categories"
  set Database = Application.DataEngine.Databases.GetMainDatabase()
  XML = Database.ExecuteSQLStatement(SelectStmt)
  set XMLDOM = createobject("MSXML.DOMDocument")
  XMLDOM.ASync = False
  XMLDOM.LoadXML(XML)
  Application.Categories = XMLDOM
end if
CharID = CStr(ID)
set Node = XMLDOM.selectSingleNode("/DataSet/_resultset" & _
  "[categoryid=" & CharID & "]")
if isnull(Node) then
  Main = CharID
else
  Main = Node.childNodes(1).Text
end if
end function

JavaScript

function Main(Application, ID) {
var XMLDOM, e, SelectStmt, Database, XML, CharID, Node ;
try {
  XMLDOM = Application.Categories ;
}
catch(e) {
  Application.AddProperty('Categories', null) ;
}
if (Application.Categories = null) {
  SelectStmt = 'select CategoryID, CategoryName from Categories' ;
  Database = Application.DataEngine.Databases.GetMainDatabase() ;
  XML = Database.ExecuteSQLStatement(SelectStmt) ;
  XMLDOM = new ActiveXObject('MSXML.DOMDocument') ;
  XMLDOM.ASync = false ;
  XMLDOM.LoadXML(XML) ;
  Application.Categories = XMLDOM ;
}
CharID = toString(ID) ;
Node = XMLDOM.selectSingleNode('/DataSet/_resultset' + 
  '[categoryid=' + CharID + ']') ;
if (Node = null) {
  Result = CharID ;
  }
else {
  Result = Node.childNodes(1).Text ;
}
return Result ;
}

VBScript and JavaScript user-defined scripts may be fairly slow for a number of reasons. One is that these scripts are interpreted at runtime, so they must be executed line-by-line every time they are called. Another is that Stonefield Query must call these scripts through the Microsoft Script Control, which means additional overhead for the COM calls. As an example of the performance, in one test, Stonefield Query took 30 seconds to process 10,000 records using a VBScript script as the output expression for one of the fields in the query. That's not bad, but the same script rewritten in Visual FoxPro took less than one second. So, our recommendation is to use Visual FoxPro rather than VBScript or JavaScript for user-defined scripts, even if it takes a little while to figure out differences in syntax.

Visual FoxPro

To call a Visual FoxPro user-defined script, simply call it as you would any other function in Visual FoxPro: specify the script name and any parameters in parentheses. The following example calls the GetCategory script, passing it the current value of the CategoryID field:

GetCategory(CategoryID)

Visual FoxPro user-defined scripts are not passed a reference to the Stonefield Query Application object. If you need a reference to this object, use the built-in object named SQApplication. Here's an example of one script that returns the category name when passed the category ID.

lparameters tiID
local loDatabase as Database, lcID, lcSelect, lcReturn
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
lcID = transform(tiID)
lcSelect = 'select CategoryName from Categories ' + ;
  'where CategoryID = ' + lcID
if not empty(loDatabase.ExecuteSQLStatement(lcSelect, , 'TEMP'))
  lcReturn = CategoryName
  use in TEMP
else
  lcReturn = tcID
endif
return lcReturn

Note that this code must hit the database for every field in the query. For a small result set, the performance is acceptable, but this script can be made significantly faster by pulling all of the records from the Categories table into memory the first time the script is called, and on subsequent calls, looking up the value in the in-memory cursor. Here's the better script:

lparameters tiID
local loDatabase as Database, lcSelect, lcReturn
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
if not used('Categories')
  lcSelect = 'select CategoryID, CategoryName from Categories'
  loDatabase.ExecuteSQLStatement(lcSelect, '', 'Categories')
  index on CategoryID tag CategoryID
endif
if seek(tiID, 'Categories')
  lcReturn = Categories.CategoryName
else
  lcReturn = transform(tiID)
endif
return lcReturn