GlobalSQLVariables section
The GlobalSQLVariables section is an optional section that you can use to define global SQL variables, which provide a means of exchanging data between different data sources.
Syntax
GlobalSQLVariables(<name> [, "<description>"])
ConnectionString = "<connection_string>"
SelectQuery = "<select_query>"
End GlobalSQLVariables
Parameters
name and description define a name and description for the section and should be of type Text.
ConnectionString
A connection string that defines the OLE DB connection properties, which define the OLE DB provider to be used to access the case data and all of the details about the physical data source, such as its name and location.
If you are using the UNICOM Intelligence Data Model, specify the UNICOM Intelligence OLE DB Provider by setting the
Provider connection property to
mrOleDB.Provider.n (where
n is the version number). The UNICOM Intelligence OLE DB Provider has some custom connection properties that define the CDSC that is to be used to access the case data, the Metadata Document (
.mdd) file or other metadata source and MDSC to be used, etc. For more information, see
Connection properties. The CDSC you are using must be read-enabled. This means that you cannot use a GlobalSQLVariables section with a Quantum data source.
You can specify file locations using a relative path (relative to the folder in which the DMS file is located when you run it). Generally, you do not need to specify a connection property for which the default value is being used.
An easy way to create the connection string is to use the UNICOM Intelligence Professional Connection String Builder. For more information, see
3. Transferring different types of data.
SelectQuery
An SQL query that defines the global variables. You define a global variable in the query using the AS clause and by prefixing the column name with the at sign (@). The query can specify a simple column or an expression based on one or more columns, using the SQL syntax supported by the OLE DB provider you are using. For more information, see
Basic SQL queries.
Example
The following example shows using a global SQL variable in a DMS file that is being used to clean batches of case data and write it to a different data source, which is being used to store the clean data. The example shows only the GlobalSQLVariables, InputDataSource, and OutputDataSource sections of the DMS file:
GlobalSQLVariables section
This specifies a connection string for the output (clean) database and a query that defines a global SQL variable called
@LastTransferred, which stores the maximum value of the
DataCollection.FinishTime variable. This is a system variable that stores the date and time that an interview is stopped or completed. This means that the global variable stores the date and time of the most recent (“newest”) respondent record in the output data source. For more information, see
System variables.
InputDataSource section
This specifies a connection string for the input (live) data source and a query that selects respondent records that have the Completed status and whose finish time is after that of the newest record in the output database.
OutputDataSource section
This specifies a connection string for the output (clean) database.
This example is provided as a sample DMS file (called
GlobalSQLVariable.dms) that is installed with the UNICOM Intelligence Developer Documentation Library. You need to run the
GlobalSQLVariableSetUp.dms sample first to set up the output data source and transfer the first records. For more information, see
Sample DMS files. You can use the
RunGlobalSQLVariableExample.bat sample batch file to run the two files in sequence (see
Sample batch files).
GlobalSQLVariables(myGlobals, "My globals section")
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.mdd"
SelectQuery = "SELECT MAX(DataCollection.FinishTime)
As @LastTransferred FROM VDATA"
End GlobalSQLVariables
InputDataSource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd"
SelectQuery = "SELECT * FROM VDATA WHERE
(DataCollection.FinishTime > '@LastTransferred')
AND (DataCollection.Status = {Completed})"
End InputDataSource
OutputDataSource(myOutputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.ddf"
MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.mdd"
End OutputDataSource
See