ToC path:

eXtremeDB ODBC DSN configuration on Windows systems

Please use these links to view an overview of the eXtremeSQL ODBC implementation and installation steps. Below are presented the steps to configure the McObject ODBC driver to connect to eXtremeSQL applications.

The McObject ODBC Setup dialog can be used to configure a DSN for local or remote connections.

The Setup Dialog looks like the image below:

Connecting to a local database

To connect to a database on the current computer, enter the name of the database as it is passed to the engine.open() or mco_db_open_dev() function calls in the server application, an optional description and specify that the server is on this computer (localhost) and the port (eg. 5001) that it is listening on. The example below specifies the tpcdb database used by the SDK samples samples\native\sql\tpc and odbc\samples\tpcodbc:

Pressing next brings up the following screen to specify whether this is a local or remote connection. Naturally, to create a local connection, the eXtremeDB database must be created in shared memory (not conventional memory) so that it can be accessed by more than one application (at a minimum, the eXtremeSQL application and the ODBC driver). So click the upper radio button and type the database name to connect to and click “Finish” to complete the DSN definition.

The screen below shows the specification for connecting to the TPC sample database.

To connect to the database from an eXtremeSQL ODBC application (see for example odbc\samples\tpcodbc.c), specify the database name as the second parameter of the ODBC function SQLConnect(). For example:
 
    SQLRETURN SQL_API SQLConnect( SQLHDBC hDbc,
                     SQLCHAR *dsnName,
                     SQLSMALLINT nameLength1,
                     SQLCHAR *userName,
                     SQLSMALLINT nameLength2,
                     SQLCHAR *authentication,
                     SQLSMALLINT nameLength3 )
                      
The nameLength1 parameter should be either the length of the dsnName argument, or SQL_NTS. The parameters after nameLength1 are ignored since eXtremeSQL uses an embedded database and doesn’t have a concept of “users”.

Note that the dsnName argument is the same name that was given to the eXtremeSQL API function engine.open() or the eXtremeDB API function mco_db_open_dev(). So, for the TPC sample, the database is opened as follows:
 
    engine.open("tpcdb", tpcdb_get_dictionary(),DATABASE_SIZE,PAGE_SIZE,MAP_ADDRESS);
     
Consequently the SQLConnect() call is
 
    SQLConnect( hDbc, “tpcdb”, SQL_NTS, 0, 0, 0, 0);
     
Now, to run the SDK sample programs tpc and tpcodbc first start tpc as follows:
 
    tpc -clients 1 -tpc 100000
     
The tpc program, acting as the database server, creates the in-memory database in shared memory and runs 100,000 iterations of the “TPC” test. When the test has completed wait (do not press the Enter key) to hold the tpc process (and its database) in memory. Now the ODBC driver can access the tpcdb database. Start the tpcodbc client program as follows:
 
    tpcodbc -tpc  100000 -db tpcdb
     
This command line instructs the ODBC client program to run 100,000 iterations of the “TPC” test and will display results similar to tpc because they both access the same database on the local computer. The difference is simply that tpc accesses the database directly via the eXtremeSQL API while tpcodbc accesses it through the ODBC driver.

Note that a subtle error can be introduced by copying and pasting the above commands into a console window. In some cases an unprintable ascii symbol is passed to the command line that causes the tpc sample to startup incorrectly. And consequently tpcodbc can’t connect to the database and throws an error. So we recommend typing the command lines manually.

Connecting to a remote database

To connect to a remote database, enter the name and description in the McObject ODBC Setup dialog as described above. Then type the IP address of the system that is hosting the database followed by a colon (“:”) and the port number on which the eXtremeSQL server process is listening.

The example below specifies the tpcdb database used by the SDK samples samples\native\sql\tpc and samples\native\sql\tpcodbc hosted on the computer with IP 207.2.78.232 that is listening on port 6100:

The connection to the remote database is done through the xSQL utility, which plays the role of the database server.

To demonstrate the remote connection, start the application on the host computer that will create the database. Assuming the IP address of the host computer is 207.2.78.232 and using again the SDK sample programs tpc and tpcodbc first start tpc as follows:
 
    tpc -clients 1 -tpc 100000
     
The tpc sample creates the database with the following function call:
 
    engine.open("tpcdb", tpcdb_get_dictionary(),DATABASE_SIZE,PAGE_SIZE,MAP_ADDRESS);
     
As in the local connection example above, the database named tpcdb will be placed in shared memory, and the program runs 100,000 iterations of the “TPC” test. When the test has completed wait (do not press the Enter key) to hold the tpc process (and its database) in memory.

Next, on the same computer, start xSQL which will provide remote access to the in-memory database:
 
    xsql tpcdb:6100
     
This command starts xSQL, instructs it to open the tpcdb database and to listen on port 6100 for remote processes wanting to connect to the database.

Finally, on another computer on the network, start the tpcodbc client program as follows:
 
    tpcodbc -tpc  100000 -db 207.2.78.232:6100
     
This command line instructs the program to run 100,000 iterations of the “TPC” test, opening the ODBC data source name 207.2.78.232:6100 which is, of course, the xSQL process running on the host computer and listening on port 6100. The C source code in tpcodbc establishes the ODBC connection as follows:
 
    database = argv[i]; // extract the data source name from the command line
    SQLAllocEnv(&hEnv); // allocate the environment handle
    SQLAllocConnect(hEnv, &hDbc); // allocate the connection handle
    // connect to the data source (McObject ODBC driver)
    SQLConnect(hDbc, database, SQL_NTS, NULL, 0, NULL, 0);
     
Now tpcodbc will display the results, although slower than tpc due to latency in the network subsystem processing the data transfer between the ODBC driver and the eXtremeSQL remote server. (If the application appears to hang it may be advisable to reduce the number of tests by the command line argument, e.g.: “-tpc 100”.)

Connection attributes

It is possible extract connection attributes from the connection string. The following attributes are supported:

DRIVER - the eXtremeDB driver name

DSN - DSN record

SERVER - the address or addresses divided by comma of the RSQL servers to connect to

SQL_N_REPLICAS - the number of replicas in the connection through the DistributedSqlEngine (corresponds to the nReplicas argument when calling the DistributedSqlEngine::open() C++ API)

SQL_CONNECT_ATTEMPTS - the number of attempts to connect (corresponds to the maxConnectAttempts argument when calling the RemoteSqlEngine::open()or DistributedSqlEngine::open() C++ APIs)

SQL_CONNECT_TIMEOUT - server connection timeout (corresponds to the connectTimeout argument when calling the RemoteSqlEngine::open()or DistributedSqlEngine::open() C++ APIs)

SQL_READ_TIMEOUT - server reply timeout (corresponds to the readTimeout argument when calling the RemoteSqlEngine::open()or DistributedSqlEngine::open() C++ APIs)

DATABASE - the database name in the connections to the shared memory database

When the DSN attribute is used, all connection data is taken from the DSN record. However if the SERVER, SQL_N_REPLICAS, SQL_CONNECT_ATTEMPTS and DATABASE attributes are specified separately, their values overwrite DSN values.

If the DSN attribute is not used, either the DRIVER or the SERVER must be specified for the remote (RSQL) connection and the DATABASE attribute must be specified to establish a shared memory database connection.

The driver name (DRIVER) for x32 systems is "McObject eXtremeDB driver" and for х64 systems it is "McObject eXtremeDB 64bit driver". The odbcad32 utility reflects these names.

Following is a C# code snippet showing how attributes can be specified:
 
    // Manually specify the driver and two servers addresses (distributed RSQL connection)
    String connectionString = "Driver={McObject eXtremeDB driver};SERVER=localhost:6001,localhost:6002;";
    // Get most of data source attributes from DSN 'xsql_dsn' but override the SERVER attribute
    //String connectionString = "DSN=xsql_dsn;SERVER=localhost:6001;";
    // Get all data source attributes from DSN 'xsql_dsn'
    //String connectionString = "DSN=xsql_dsn";
    System.Data.Odbc.OdbcConnection dbconn;
    dbconn = new System.Data.Odbc.OdbcConnection(connectionString);
     