As Stonefield Query starts up, each database defined in the data dictionary is given an opportunity to determine which sets of data (data sources) are available. You can customize the behavior of this step by creating a GetDataSources script for the database.

The general task to perform in GetDataSources is to add DataSource objects to the DataSources collection of the Database object.

This script is specific for a database, so its actual name is database.GetDataSources, where database is the name of the database. To create this script, click the Create "get data sources" script link when the database is selected in the TreeView.

Note that creating a GetDataSources script for a database turns off and disables the User Can Manage Data Sources configuration setting since those two are mutually exclusive.

Parameters
A reference to the Stonefield Query Application object and a reference to the Database object the script is for.

Return Value
True if the set of data sources was successfully created.

Example

Visual FoxPro
Suppose an accounting application allows the user to define different sets of data for different companies. A table called DATA that resides in the accounting application's directory contains a list of the company data sets the user has created. This table has two columns: NAME, which contains the descriptive name of the data set (such as the company name), and DIR, which contains the fully-qualified path to the database for the data set.

lparameters toApplication as SQApplication, toDatabase as Database
local lnSelect, loDataSource as VFPDataSource
lnSelect = select()
select 0
use (toApplication.TargetApplicationDirectory + 'DATA')
scan
  loDataSource = toDatabase.DataSources.AddItem('VFP', ;
    trim(NAME))
  loDataSource.Database = trim(DIR)
endscan
use
select (lnSelect)
return .T.

This code opens the DATA table in the accounting application's directory (which is stored in the TargetApplicationDirectory property of the passed Application object). It processes every record in that table, adding a new DataSource object to the DataSources collection of the Database object passed to the code. Since this is a Visual FoxPro data source, the first parameter passed to the AddItem method is "VFP," and the second parameter is the name to display to the user (the name of the company, in this case). The Database property of the new DataSource object is set to the path and name of the database for the data set.

Here's another example that sets up an ODBC data source.

lparameters toApplication as SQApplication, toDatabase as Database
local loDataSource as ODBCDataSource
loDataSource = toDatabase.DataSources.AddItem('ODBC', 'My Database')
loDataSource.Driver   = 'SQL Server'
loDataSource.Database = 'Northwind'
loDataSource.Server   = '(local)'
return .T.

VBScript
This example uses ADO to connect to SQL Server.

function Main(Application, Database)
dim DataSource
set DataSource = Database.DataSources.AddItem("ADO", _
  "My Database")
DataSource.ConnectionString = "Provider=SQLOLEDB.1;" + _
  "Integrated Security=SSPI;Persist Security Info=False;" + _
  "Initial Catalog=MyDatabase;Data Source=MyServer"
Main = True
End Function

This code adds a new DataSource object to the DataSources collection of the Database object passed to the code. Since this is an ADO data source, the first parameter passed to the AddItem method is "ADO," and the second parameter is the name to display to the user. The ConnectionString property of the new DataSource object is set to the connection string used to connect to the database.

C#
This example uses ODBC to connect to SQL Server.

Please note that the method in this script must be named database_GetDataSources where database is the name of the database item in your data dictionary. The sample below is a GetDataSources script for the northwind database.

public static bool northwind_GetDataSources(SFQApplication sfqApplication, 
  Database database)
{
  DataSource dataSource;
  dataSource = database.DataSources.AddItem("ODBC", "My Database");
  dataSource.Driver = "SQL Server";
  dataSource.Database = "Northwind";
  dataSource.Server = "(local)";
  return true;
}

VB.NET
This example uses ODBC to connect to SQL Server.

Please note that the method in this script must be named database_GetDataSources where database is the name of the database item in your data dictionary. The sample below is a GetDataSources script for the northwind database.

public shared function northwind_GetDataSources(sfqApplication as SFQApplication, _
  database as Database) as Boolean
  Dim dataSource as DataSource
  dataSource = database.DataSources.AddItem("ODBC", "My Database")
  dataSource.Driver = "SQL Server"
  dataSource.Database = "Northwind"
  dataSource.Server = "(local)"
  Return true
End Function

VBScript
Suppose a Web Service on a Web server somewhere has an ExecuteSQLStatement method that accepts a SQL statement and returns the results as XML, and you want to use that Web Service as a data source for Stonefield Query.

function Main(Application, Database)
dim DataSource
set DataSource = Database.DataSources.AddItem("WebService", _
  "Remote Data")
DataSource.WSDLURL = "http://www.myurl.com/dataserver.wsdl"
DataSource.Method = "ExecuteSQLStatement(<<select>>)"
Main = True
End Function

This code adds a new DataSource object to the DataSources collection of the Database object passed to the code. Since this is a Web Service data source, the first parameter passed to the AddItem method is "WebService," and the second parameter is the name to display to the user. The WSDLURL property of the new DataSource object is set to the URL of the WSDL file for the Web Service and the Method property is set to the name of the Web Service method to call.

Note the "<<select>>" placeholder in this code. This tells Stonefield Query to insert the SQL statement it has created for a particular table.

JavaScript
This example uses SQLXML, Microsoft's add-on for SQL Server that supports queries over HTTP.

function Main(Application, Database) {
var DataSource = Database.DataSources.AddItem('SQLXML', 
  'Remote Data') ;
DataSource.BaseURL = 'http://localhost/northwind' ;
return true;
}

This code adds a new DataSource object to the DataSources collection of the Database object passed to the code. Since this is a SQLXML data source, the first parameter passed to the AddItem method is "SQLXML," and the second parameter is the name to display to the user. The BaseURL property of the new DataSource object is set to the URL of the SQL virtual directory on the Web server.

See also

Allow Multiple Data Sources | Database Properties

© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic