ODBC - SQL database connection strings¶
SQL database connection strings
The name = clause in an openfile command can contain a SQL database connection string as an alternative to a pathname. Examples:
openfile FILE1 name = "DBID=db1; DRIVER=SQL Server; SERVER=myserver; DATABASE=master;" create
openfile FILE2 name = "DBID=db1; DRIVER=; ... ; UID=username; PWD=password;" update
The first field=value pair must be “DBID=*database_id*;” where database_id is a database declared in the Sculptor program. This informs Sculptor that the table is to be attached to that database. All other field=value pairs are standard. If a value parameter contains non-alphanumeric characters, it should be enclosed in {} brackets. Example:
openfile FILEID name = "DBID=db1; ...; SERVER={myserver-001}; ... ;" update
The table name is taken from the !file declaration. Sculptor ignores any path information and extracts only the base filename for use with ODBC.
For a complete definition of an ODBC connection string, see the MSDN documentation for the function SQLDriverConnect().
UID and PWD are not necessary if Windows Authentication and a trusted connection are being used. They are necessary for use with SQL Server authentication.
There is no need to define an ODBC data source. The following database definition is sufficient:
-database db1 {
type = DBT_ODBC
flags = DBFL_CASE_SENSITIVE
!file FILE1 "mydata/file1"
!file FILE2 "mydata/file1"
}
In this case –database must be used because the database can’t be opened until the first openfile command has supplied the connection information (when the database defines a correct connection string in the source property a +database can be used).
When this type of openfile command is used, the files can be declared inside the database structure, as above, or outside, as below:
-database db1 {
type = DBT_ODBC
flags = DBFL_CASE_SENSITIVE
}
!file FILE1 "mydata/file1"
!file FILE2 "mydata/file1"
Since Sculptor version 6.0.0 it’s possible to connect to a SQL database in the Sculptor server by adding a “SCULPTOR_SERVER=” clause to the start of the source string in an ODBC database declaration or using the name clause on the openfile command (in this case SCULPTOR_SERVER must go after DBID). Example:
+database mydb {
type = DBT_ODBC
source = "SCULPTOR_SERVER=myserver;DSN=mydsn"
riu_timeout = 3
}
Example of an alternative form of source string:
source = "SCULPTOR_SERVER=myserver;DRIVER=SQL Server;SERVER=MYSQLSERVER;DATABASE=master"
The SCULPTOR_SERVER clause must be the first clause in the source string. Do not use {} to enclose the Sculptor server name as this is not supported. Connection to the ODBC driver is made on the server and all network traffic is handled by Sculptor, making it unnecessary to install the ODBC driver on the client.
RELATED TOPICS |