This chapter describes:
- How to add, modify and delete FairCom DB SQL ODBC data sources on Windows client systems using the ODBC Data Source Administrator utility
- The format of the driver and data source information maintained by the ODBC Data Source Administrator
Configuring Data Sources with the ODBC Data Source Administrator
The ODBC Data Source Administrator is a Microsoft utility to configure ODBC data sources and drivers.
ODBC uses the term "data source" to refer all the information an application needs to connect to a particular database. This information includes the driver name and location, network address, network software, and database name. Depending on the version of Windows, the ODBC Data Source Administrator stores data source information in text files or in the system registry.
The ODBC Data Source Administrator lets you enter the connection details for accessing a FairCom DB database and associate it with a data source name that users refer to when they need to access the data through an ODBC application.
Invoking the ODBC Data Source Administrator Utility
The FairCom DB ODBC driver installation procedure installs the ODBC Driver Manager software and the associated ODBC Data Source Administrator utility if it was not already installed. To configure the ODBC data source, invoke the ODBC Data Source Administrator Utility:
- On Windows 8 and newer, press the Windows key on your keyboard, type in "ODBC Data Sources," and press Enter. This will load the ODBC Data Sources Control Panel.
- On other versions of Windows, select Start > Settings > Control Panel from the Windows menu and double-click the ODBC Data Sources option in the Control Panel.
FairCom DB ODBC Data Sources
Once you invoke the ODBC Data Source Administrator:
- In the dialog box for the type of data source you choose, choose the Add button. The Add Data Source dialog box appears.
- Select FairCom DB SQL from the list of installed drivers and choose Finish. The FairCom DB ODBC Setup dialog box appears.
- Fill in the dialog box fields as shown in the following figure and choose OK. The ODBC Data Source Administrator writes the values you supply to ODBC.INI or to the DSN file you indicated.
Data Source Name - A local name for the FairCom DB SQL data source for use in connect calls and by the ODBC Administrator.
Description - Optional descriptive text.
Host - Specify the machine name on which the FairCom DB SQL Server is running.
Database - The name of the database where the FairCom DB SQL data source resides.
User ID / Password - User name and password for connecting to the database. The driver uses those values if the application does not supply them in the call. You can leave these fields blank if you want the driver to use the defaults on the server. If no defaults are defined and you leave these fields blank, the user will be prompted when the application connects.
Service - The name of the Service FairCom DB SQL listens to. If empty, sqlnw is used.
Default Fetch Size - This value is the size (in bytes) used by the driver to fetch multiple rows from the server. It reduces network requests resulting in performance gains. If not set, the internal buffer size is 5000 bytes.
- In your connection string, set the attribute "FETCH_SIZE=[number of bytes]"
- In your ODBC.INI file, set the attribute "Default Fetch Size=[number of bytes]"
- In the connection string, the attribute is: QUERY_TIMEOUT=[number of seconds]
- In the ODBC.INI file, the attribute is: Default Query Timeout=[number of seconds]
- The Data Source Dialog box reappears, and now includes the newly-added data source.
Configuring SSL in Your Connection String
In the ODBC connection string, it is possible to add "SSL=xyz" where "xyz" is one of the options 2 or 3 from the SSL parameters listed above in the FairCom DB ODBC Setup dialog box.
You can add one of the following to your existing ODBC connection strings to enable TLS/SSL:
"SSL=BASIC" - Encryption with default server certificate.
"SSL=ctsrvr.pem" - Use Peer authentication with explicitly named cert located in the local directory.
"SSL=C*\certs\ctsrvr.pem" - Use Peer authentication with an explicitly named cert with a full path.
Modifying and Deleting FairCom DB SQL Data Sources
You can modify or delete a FairCom DB SQL data source after you add it. Invoke the ODBC Data Source Administrator and select the data source you want to modify or delete. Then choose the Configure or Remove button:
- Choosing the Configure button displays the FairCom DB ODBC Setup dialog box (see the previous figure) with the current values for that data source. Change the values of any fields, then choose OK.
When you modify a data source, the ODBC Data Source Administrator modifies the entry for the data source name you specify. For example, you could modify a FairCom DB SQL data source to change the user name and password that connections use.
- Choosing the Remove button displays a confirmation box. Choose OK if you want to delete the data source.
When you delete a data source, the ODBC Data Source Administrator deletes the entry for that data source. (Deleting a data source has no effect on any database, only on ODBC’s information for accessing the database.)
ODBC Data Source and Driver Information FORMAT
Windows Registry
ODBC driver information is stored in subkeys of the Windows registry.
User data sources are stored under the HKEY_CURRENT_USER key and are available only to the current user. System data sources are stored under the HKEY_LOCAL_MACHINE key and can be used by more than one user.
For user data sources, the complete registry key is HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.
The ODBCINST.INI subkey is a subkey of HKEY_LOCAL_MACHINE, which describes the number and types of ODBC drivers installed on the system. The ODBC Data Source Administrator reads the subkey when it invokes the driver’s setup DLL to add or modify a data source for the driver.
Query Timeout Options
FairCom DB SQL supports a timeout option for an executing query. This feature can ensure that an unintended query statement does not consume excessive processing time.
With FairCom DB ODBC you can set the query timeout value for the statement with the SQLSetStmtAttr() FairCom DB ODBC API function and the SQL_ATTR_QUERY_TIMEOUT parameter set to the number of seconds to wait for the query to execute before returning to the application. A value of 0 indicates no timeout value, which is also the default. The following example code will set a query timeout value of five seconds for the referenced statement handle.
ODBC Example
/* Set the Query timeout to 5 seconds */
SQLSetStmtAttr(hstmt, (void*)SQL_ATTR_QUERY_TIMEOUT, 5, 0);
Using ODBC through ADO.NET, you can specify the OdbcConnection.CommandTimeout property to set a query timeout value on an ODBC statement as demonstrated with the following syntax.
ODBC via ADO.NET Example
OdbcConnection myConnection = new OdbcConnection();
myConnection.ConnectionString = "DSN=c-treeSQL ODBC Database";
myConnection.Open();
OdbcCommand oc = new OdbcCommand("SELECT TOP 50000 FROM my_big_table WHERE this < that AND this_string = 'that_string' ORDER BY foo”, myConnection);
// Set a query timeout of 5 seconds.
oc.CommandTimeout = 5;
try
{
oc.ExecuteReader();
}
catch (Exception ex)
{
// Log some error
}
ODBC Driver Socket SEND/RECV Timeout
A send/recv timeout option is available such that a FairCom DB ODBC client can request a timeout for a connection socket. If the client experiences a lengthy wait for the server to reply, the client can continue to work after closing the connection.
A FairCom DB ODBC driver can set the timeout with a call to the SQLSetConnectAttr() ODBC API function and the SQL_ATTR_CONNECTION_TIMEOUT parameter with the time value in seconds.
Example
/* Set socket timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, (void*)SQL_ATTR_CONNECTION_TIMEOUT, 5, 0);
Note: SQLSetConnectAttr(SQL_ATTR_CONNECTION_TIMEOUT) sets the timeout value for the entire ODBC driver, not just for the current connection. A default value of 0 indicates no timeout.
Driver Login Timeout
The FairCom DB ODBC Driver for Windows supports a configurable timeout on driver connection login. An application can set the login timeout by calling the SQLSetConnectAttr() ODBC API function with the SQL_ATTR_LOGIN_TIMEOUT attribute and a timeout value.
/* Set the Login timeout to 5 seconds */
SQLSetConnectAttr(hdbc, (void*)SQL_ATTR_LOGIN_TIMEOUT, 5, 0);
This timeout sets the maximum time for which the ODBC driver waits for the connection attempt to FairCom DB SQL to complete. If the connection attempt does not complete in the specified time period, the ODBC driver fails the connection attempt with the following error:
20212, "Error in Network Daemon".
The default login timeout value is 15 seconds.
Using Reserved Keywords with Microsoft Excel
A feature was added to allow a query from Microsoft Excel and ODBC on tables containing fields identified with FairCom DB SQL reserved keywords. The FairCom DB ODBC Driver now considers a qualified identifier (i.e., a field name preceded by the table name in the form: tablename.fieldname) as a field even if it is a reserved keyword. This is done by automatically wrapping the qualified fieldname in double quotes, which has the effect of making it case-sensitive. Because this behavior only applies to qualified identifiers it can be avoided by specifying only the fieldname without the tablename.
To use this particular feature with Microsoft Excel and the FairCom DB ODBC Driver, a data source should specify the string “DHQQI” in the Options field of the Data Source Configuration window. This option enables the extended processing for only this connection.
Nodename + Process ID used to identify ODBC applications
The ODBC API does not have an interface to specify extra identifying information that our server can display to identify a specific application using the ODBC driver. By default, c-tree Server set the "nodename" to "SQL:<dbname>" when a new connection was made.
It is now possible to display a client process ID connected to the server. On the server side, when SQL_OPTION PID_IN_NODEID is specified in ctsrvr.cfg, the client process ID is appended to the default nodename as "SQL: PID <client pid>". If the client application does not pass the PID information, the original default nodemane is still used. The default node name can be overwritten at any time after connection by a client calling the fc_set_nodename built-in stored procedure.
This nodename string is displayed for all SQL client APIs and tools except for JDBC-based APIs/tools.