Script Languages - Stonefield Query SDK

Stonefield Query supports scripting in the following languages: Visual FoxPro, VBScript, JavaScript, VB.NET, and C#.

VBScript and JavaScript

VBScript and JavaScript require that the Microsoft Script Control is installed, both on your system and on the user's system. That control is automatically installed on Windows 2000 or later and any Windows 9x version with Internet Explorer 4 or later installed so you likely won't have to install it manually.

To call a VBScript or JavaScript user-defined script, simply call it as you would any other function: 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)

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). You can reference properties and methods of the Application or other Stonefield Query object through this parameter. Here are examples of the GetCategory script in both VBScript and JavaScript:

VBScript

function Main(Application, CategoryID)
dim Database, XMLDOM, Node
set Database = Application.DataEngine.Databases.GetMainDatabase()
CharID = CStr(CategoryID)
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, CategoryID) {
var Database, CharID, SelectStmt, XMLResult, XMLDOM, Node ;
Database = Application.DataEngine.Databases.GetMainDatabase() ;
CharID = toString(CategoryID) ;
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 ;
}

VBScript and JavaScript 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 scripts, even if it takes a little while to figure out differences in syntax.

VB.NET and C#

VB.NET and C# require that version 2 of the .NET framework is installed, both on your system and on the user's system, before installing Stonefield Query.

To call a VB.NET or C# user-defined script, simply call it as you would any other function: 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)

VB.NET and C# 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. For example, use code like this in C# to get the ApplicationName property:

string caption = SQApplication.ApplicationName;

Use code like this in VB.NET:

Dim Message As String = SQApplication.ApplicationName

You can assign a new value to a property of the Application or other Stonefield Query object. For example, this C# code sets the Company property of a Settings object:

Settings.Company = "Test Company";

The VB.NET version is nearly identical:

Settings.Company = "Test Company"

To call a method of the Application or other Stonefield Query object, call that method directly. For example, use code like this in C# to call the ExecuteSQLStatement method of the Database object:

ResultSet = Database.ExecuteSQLStatement("select COMPANYNAME from CUSTOMERS");

Here's an example in VB.NET:

sfqApplication.ShowErrorMessage("This error message came from VB.NET")

You can get full IntelliSense in C# scripts edited in the zoomed code window if you add a LOCAL variable AS object comment in the code. For example, adding this to the start of a script:

/*
local sfqApplication as SQApplication, field as Field
*/

allows you to get IntelliSense of the sfqapplication and field objects. The LOCAL statement is needed for IntelliSense to work, but since you don't want that code actually executed, it's commented out. Unfortunately, VB.NET doesn't support multi-line comments, so this trick won't work in that language.

VB.NET and C# scripts may be fairly slow because Stonefield Query must call these scripts through a COM object (VFPDotNet.DLL), which means additional overhead for the COM calls, and because the DLL has to use reflection to call methods or access properties of the Stonefield Query objects. So, our recommendation is to use Visual FoxPro rather than VB.NET or C# for scripts, even if it takes a little while to figure out differences in syntax.

When using C# or VB.NET as your scripting language, the following .NET namespaces are available:

using System;
using System.Xml;
using System.Data;
using System.Windows.Forms;
using System.Reflection;
using System.Text;
using System.IO;
using SFQWrapper;

Special note on custom SFQApplication properties when using C# or VB.NET

When using C# or VB.NET as your scripting language, you can retrieve the values of custom SFQApplication properties that you added via SFQApplication.AddProperty() using the SFQApplication.GetProperty() method. You can set the value of a custom property using SFQApplication.SetProperty().

If your custom property is an object (COM or .NET), you can call methods of it using ReflectionService.CallMethod(), set properties using ReflectionService.SetProperty(), and get properties using ReflectionService.GetProperty().

Here's a C# example using these methods:

public static object COMScriptTest(string param1, bool param2)
{	
  // Retrieve custom SFQApplication Property
  object oCOMObject = SFQApplication.GetProperty("oCOMObject");

  // Call method of custom SFQApplication Property
  string result = (string)ReflectionService.CallMethod(oCOMObject, 
    "MethodName", param1, param2);
  MessageBox.Show(result);

  // Get property of custom SFQApplication Property
  bool boolProperty = (bool)ReflectionService.GetProperty(oCOMObject,
    "BoolProperty");

  // Set property of custom SFQApplication Property
  ReflectionService.SetProperty(oCOMObject,
    "BoolProperty", false);

  // Set value of custom SFQApplication Property
  SFQApplication.SetProperty("oCOMObject", null);

  return boolProperty;
}

The same script in VB.NET would be:

public shared function COMScriptTest(param1 as String, param2 as Boolean) as object

  ' Retrieve custom SFQApplication Property
  Dim oCOMObject as Object = SFQApplication.GetProperty("oCOMObject")

  ' Call method of custom SFQApplication Property
  Dim result as String = ReflectionService.CallMethod(oCOMObject, _
    "MethodName", param1, param2)
  MessageBox.Show(result)

  ' Get property of custom SFQApplication Property
  Dim boolProperty as Boolean = ReflectionService.GetProperty(oCOMObject, _
    "BoolProperty")

  ' Set property of custom SFQApplication Property
  ReflectionService.SetProperty(oCOMObject, _
    "BoolProperty", false)

  ' Set value of custom SFQApplication Property
  SFQApplication.SetProperty("oCOMObject", Nothing)

  return boolProperty

End Function

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 tiCategoryID
local loDatabase as Database, lcID, lcSelect, lcReturn
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
lcID = transform(tiCategoryID)
lcSelect = 'select CategoryName from Categories ' + ;
  'where CategoryID = ' + lcID
if not empty(loDatabase.ExecuteSQLStatement(lcSelect, , 'TEMP'))
  lcReturn = CategoryName
  use in TEMP
else
  lcReturn = tcCategoryID
endif
return lcReturn