Stonefield Query uses the concept of a "main" database for an application. The main database is the one the user can change data sources for. All other databases are assumed to be related to the main one in that selecting the data source for the main one somehow determines how the data source for the others is selected.

For example, imagine an accounting application that has two databases: a system database that contains application-specific data, and a company-specific database containing the accounting information for the company. The user may have different databases for different companies, so they can change data sources to select the appropriate database for the desired company. However, there is only one system database. In this case, the company-specific database is considered to be the main one, since that's the one the user can select different data sources for.

Here's another example: an application has multiple instances of two databases (such as for production and test data) on different servers. When the user chooses a data source for one of those databases, Stonefield Query uses the other database from the same server as the first one. Thus, either database can be the main database, and an OpenDataSource script for the second database can set the properties for the second database to match those of the selected data source for the main database.

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

Note that an OpenDataSource script must connect to the database. That is, somewhere in your script code, there should be a call to the Connect method of a DataSource object. If you don't do this, the user gets an error when they run Stonefield Query because no connection is opened to the database.

If the connection fails and you want to display your own error message in the OpenDataSource script and suppress Stonefield Query displaying a message, set the ErrorMessage property of the Database object to "*".

Note that after the script is done, Stonefield Query sets the CurrentDataSource property of the database object to the datasource object you've connected with. However, if you want to call ExecuteSQLStatement within the OpenDataSource script, you'll need to set that property manually using code similar to:

lparameters toApplication as SQApplication, toDatabase as Database, tcDataSource
local loDataSource as ODBCDataSource
loDataSource = toDatabase.DataSources.Item(tcDataSource)
llReturn = loDataSource.Connect()
if llReturn
  toDatabase.CurrentDataSource = loDataSource
  toDatabase.ExecuteSQLStatement('select * from customers')
endif
return llReturn

Parameters
A reference to the Stonefield Query Application object, a reference to the Database object the script is for, and the name of the data source being opened.

Return Value
True if the new data source was successfully opened.

Example
This example opens a non-main database on the same server as the main database.

Visual FoxPro

lparameters toApplication as SQApplication, toDatabase as Database, tcDataSource
local loDatabase as Database, loDataSource as ODBCDataSource, ;
  loNewDataSource as ODBCDataSource
loDatabase      = toApplication.DataEngine.Databases.GetMainDatabase()
loDataSource    = loDatabase.CurrentDataSource
loNewDataSource = toDatabase.DataSources.Item(1)
loNewDataSource.Server   = loDataSource.Server
loNewDataSource.UserName = loDataSource.UserName
loNewDataSource.Password = loDataSource.Password
loNewDataSource.Disconnect()
return loNewDataSource.Connect()

VBScript

function Main(Application, Database, DataSource)
dim MainDatabase, MainDataSource, NewDataSource
set MainDatabase   = Application.DataEngine.Databases.GetMainDatabase()
set MainDataSource = MainDatabase.CurrentDataSource
set NewDataSource  = Database.DataSources.Item(1)
NewDataSource.Server   = MainDataSource.Server
NewDataSource.UserName = MainDataSource.UserName
NewDataSource.Password = MainDataSource.Password
NewDataSource.Disconnect()
Main = NewDataSource.Connect()
end function

JavaScript

function Main(Application, Database, DataSource) {
var MainDatabase   = Application.DataEngine.Databases.GetMainDatabase() ;
var MainDataSource = MainDatabase.CurrentDataSource ;
var NewDataSource  = Database.DataSources.Item(1) ;
NewDataSource.Server   = MainDataSource.Server ;
NewDataSource.UserName = MainDataSource.UserName ;
NewDataSource.Password = MainDataSource.Password ;
NewDataSource.Disconnect() ;
return NewDataSource.Connect() ;
}

C#

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

public static bool northwind_OpenDataSource(SFQApplication sfqApplication, 
  Database database, string dataSource)
{
  Database mainDatabase   = sfqApplication.DataEngine.Databases.GetMainDatabase();
  DataSource mainDataSource = mainDatabase.CurrentDataSource;
  DataSource newDataSource  = database.DataSources.Item(0);
  newDataSource.Server   = mainDataSource.Server;
  newDataSource.UserName = mainDataSource.UserName;
  newDataSource.Password = mainDataSource.Password;
  newDataSource.Disconnect() ;
  return newDataSource.Connect() ;
}

VB.NET

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

public shared function northwind_OpenDataSource(sfqApplication as SFQApplication,
  database as Database, dataSource as string) as Boolean
  dim mainDatabase as Database = sfqApplication.DataEngine.Databases.GetMainDatabase()
  dim mainDataSource as DataSource  = mainDatabase.CurrentDataSource
  dim newDataSource as DataSource  = database.DataSources.Item(0)
  newDataSource.Server   = mainDataSource.Server
  newDataSource.UserName = mainDataSource.UserName
  newDataSource.Password = mainDataSource.Password
  newDataSource.Disconnect()
  return newDataSource.Connect()
End Function

This code calls the GetMainDatabase method of the Databases collection of the DataEngine object of the passed Application object to return a Database object containing properties for the main database. It then uses the CurrentDataSource property of the main database object to get a reference to the DataSource object for the current data source. The database this is the script for has only one data source object in its DataSources collection (Stonefield Query sets this up automatically), so a reference to that object is put into the NewDataSource variable. Then the Server, UserName, and Password properties of that DataSource object are set to the appropriate values from the main database's current DataSource object. The data source's Disconnect method is then called to ensure any former connection is closed and the Connect method is called to connect to the data source.