Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Initializing the ODBC Environment


The first task for any ODBC application is to initialize the ODBC environment by allocating an environment handle (SQL_HANDLE_ENV):

 /* Allocate environment handle */
 if ( SQLAllocHandle( SQL_HANDLE_ENV,
                      SQL_NULL_HANDLE,
                      &henv ) == SQL_ERROR )
 {
     printf( "Failed to allocate environment handle\n" );
     . . .
 }
 

Before an application allocates a connection, it should declare the version of ODBC that it has been written for (this mainly affects SQLSTATE values and datetime data types), and then allocate a connection handle:

 /* Set the ODBC version environment */
 SQLSetEnvAttr( henv,
                SQL_ATTR_ODBC_VERSION,
                (SQLPOINTER)SQL_OV_ODBC3,
                SQL_IS_INTEGER );
 
 /* Allocate connection handle */
 if ( SQLAllocHandle( SQL_HANDLE_DBC,
                      henv,
                      &hdbc ) == SQL_ERROR )
 {
     printf( "Failed to allocate connection handle\n" );
     . . .
 }

Making a Connection

If an ODBC data source has been defined, ODBC applications can connect to Mimer SQL by using the data source name. Alternatively SQLDriverConnect can be used.

There are a number of mechanisms to get the information required to make a connection; some applications supply the connection details, others use the ODBC dialog box to allow the user to complete the information.

The simplest form of connection uses SQLConnect, which requires a data source name, user ID and password, for example:

 SQLRETURN retcode;
 . . .
 
 /* Set connection timeout - 10 seconds */
 SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
 
 /* Connect - DSN, User ID, Password */
 retcode = SQLConnect(hdbc,
                      (SQLCHAR*) "EXAMPLEDB", SQL_NTS,
                      (SQLCHAR*) "MIMER_ADM", SQL_NTS,
                      (SQLCHAR*) "admin",
                      SQL_NTS);
 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
 {
     /* User connected */
 

SQLDriverConnect allows the driver to connect by supplying the connection information as a number of keyword-value pairs:

 "DSN=EXAMPLEDB;UID=MIMER_ADM;PWD=admin;"
 

There is an option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (the handle of the parent window needs to be supplied to use this facility).

In the following Windows example, the Driver Manager displays a window containing a combo box listing all the Mimer SQL database names and prompts for the user name and password:

 SQLCHAR     OutConnectString[256];
 SQLSMALLINT StringLength;
 SQLHWND     hwnd;
 
 hwnd = GetDesktopWindow();
 . . .
 
 retcode = SQLDriverConnect( hdbc,
                             hwnd,
                             (SQLCHAR*) "DRIVER=Mimer;", SQL_NTS,
                             (SQLCHAR*)OutConnectString,
                             sizeof(OutConnectString),
                             &StringLength,
                             SQL_DRIVER_COMPLETE );
 if (SQL_SUCCEEDED(retcode))
 {
     /* User connected */
     printf( "connection string used: %s\n, OutConnectString );
 
Note: The macro SQL_SUCCEEDED replaces the test against SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.

On other platforms, such as Linux, the driver does not implement a GUI popup box. Instead the user will be prompted for the required login attributes. In this case, referring to the previous example, the hwnd variable is set to null:

 hwnd = NULL;

Controlling Interaction with the User

You may wish to have more control over the interaction with the user, SQLDataSources provides a mechanism to get information about the data sources configured on the client:

 SQLCHAR   DSNname[SQL_MAX_DSN_LENGTH+1];
 SQLCHAR   driver[33];
 
 . . .
 
 /* Enumerate the system data source names */
 retcode = SQLDataSources( henv,
                           SQL_FETCH_FIRST_SYSTEM,
                          (SQLCHAR*)DSNname,
                           sizeof(DSNname),
                           NULL,
                           SQLCHAR*)driver,
                           sizeof(driver),
                           NULL );
 
 while (SQL_SUCCEEDED(retcode))
 {
     printf( "%-32s     %s\n", DSNname, driver );  // Display details
 
     /* Fetch next */
     retcode = SQLDataSources( henv,
                               SQL_FETCH_NEXT,
                              (SQLCHAR*)DSNname,
                               sizeof(DSNname),
                               NULL,
                               (SQLCHAR*)driver,
                               sizeof(driver),
                               NULL );		
 }

Connecting Using a File Data Source

Another way of making a connection is to create a file data source. The file contains keyword-value pairs to make the connection. On Windows, this file has a .dsn extension.

Although it is possible to include the password, this would make the system insecure and therefore is not recommended:

 [ODBC]
 DSN=EXAMPLEDB
 UID=MIMER_ADM
 

To make a connection using a file data source, use the option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (again, the handle of the parent window needs to be supplied to use this facility):

 retcode = SQLDriverConnect( hdbc,
                             hwnd,
                             (SQLCHAR*) "FILEDSN=example.dsn;",
                             SQL_NTS,
                             (SQLCHAR*)OutConnectString,
                             sizeof(OutConnectString),
                             &StringLength,
                             SQL_DRIVER_COMPLETE );
 if (SQL_SUCCEEDED(retcode))
 {
     /* User connected */
 

Mimer Specific Keywords to SQLDriverConnect

To allow an application to connect without specifying a data source in the connection string, the following driver-specific keywords have been added for the Mimer ODBC Driver:

The PROTOCOL keyword is mandatory for this option to be used. The regular keyword DATABASE must also be specified. Other driver-specific keywords should be used depending on the specified protocol. When PROTOCOL is specified, no data source lookup is done in the registry (Windows) or MIMER_SQLHOSTS (Linux and VMS).

Supported protocols are LOCAL (shared memory), TCP, NAMEDPIPES (only for Windows), RAPI (only for Windows), and DECNET (only for VMS).

The protocol TCP requires keyword NODE specifying the network node name. If keyword SERVICE is not specified, 1360 is used as default.

Win: The protocol NAMEDPIPES requires keyword NODE. If keyword SERVICE is not specified, the database name is used as default.
VMS: The protocol DECNET requires keyword NODE.
Note: SQLDriverConnect has a parameter that enables prompting for missing information. When PROTOCOL is specified, this is not possible.

Examples of connection strings that can be used:

 "DRIVER={Mimer};DATABASE=cartoons;UID=mickey;PWD=mouse;PROTOCOL=local"
 
 "DRIVER={Mimer};DATABASE=musix;UID=discux;PWD=records;PROTOCOL=local"
 
 "DRIVER={Mimer};DATABASE=strips;UID=winnie;PWD=thepooh;PROTOCOL=tcp; 
NODE=milne;SERVICE=1360"
 
 "DRIVER={Mimer};DATABASE=pip;UID=mickey;PWD=mouse;PROTOCOL=NamedPipes; 
NODE=winpix;SERVICE=pip"
 
 "DRIVER={Mimer};DATABASE=disney;UID=donald;PWD=duck;PROTOCOL=decnet; 
NODE=pictvms;INTERFACE=BG"

Determining Driver and Data Source Capabilities

After connection to the database, use SQLGetInfo to determine the capabilities of the driver and the data source associated with the connection:

 /* Display DBMS version details */
 SQLGetInfo( hdbc,
             SQL_DBMS_VER,
             (SQLPOINTER)&str_value,
             sizeof(str_value),
             &str_len );
 printf( "%s\n", str_value );
 
 /* Display SQL conformance level */
 SQLGetInfo( hdbc,
             SQL_SQL_CONFORMANCE,
             (SQLPOINTER)&int_value,
             sizeof(int_value),
             NULL );
 if (int_value & SQL_SC_SQL92_ENTRY)
    printf( "Entry level SQL-92\n" );
 if (int_value & SQL_SC_FIPS127_2_TRANSITIONAL)
    printf( "FIPS 127-2 transitional level\n" );
 if (int_value & SQL_SC_SQL92_INTERMEDIATE)
    printf( "Intermediate level SQL-92\n" );
 if (int_value & SQL_SC_SQL92_FULL)
    printf( "Full level SQL-92\n" );</pre>

Connecting on Linux and similar platforms

On Linux, it is possible to link an ODBC application directly to the Mimer ODBC library, libmimodbc.so. But, usually an ODBC Driver Manager is used, mainly to be able to handle several ODBC Data Sources. In that case the Driver Manager library is linked to the application and the Mimer ODBC library is pointed out as the Driver in the ODBC Data Source definition.

When a connection attempt is made using ODBC, a DSN (Data Source Name) is specified via one of the connection methods describe above. The ODBC Driver Manager looks up the given ODBC Data Source in an odbc.ini file. There can be a system wide odbc.ini file located in a known location for the platform, usually in /etc. Alternatively, the user can have a personal .odbc.ini located in the home directory. Or, the ODBCINI environment variable can be set to point out the data source definition file to be used.

A possible match between the given DSN and an entry in the odbc.ini file gives the connection information needed to load the relevant ODBC Driver dynamically and to proceed with the database access.

The following is an example of an odbc.ini file, describing two DSN specifications, with their names within straight brackets:

 [dsn_dbcust]
 Driver=/opt/MimerSQL-11.0.0A/lib/libmimodbc.so
 Database=customers
 Host=kixie
 Port=1360
 
 [dsn_dbext]
 Driver=/opt/MimerSQL-11.0.0A/lib/libmimodbc.so
 Database=external
 

In the first case above the information defined is enough to do a direct access to the database named `customers' on the network node `kixie', using the port number 1360. When reaching the database the user will have to provide a database user name (ident name) and a password.

In the second definition there is not enough information to do a direct access. Instead the given database is looked for in the Mimer SQL database registry file called /etc/sqlhosts, and if found there, that information will be used to proceed with the connection. In this case, if the DSN name is the same as the database name, the Database attribute is optional.

The following are valid DSN attributes in the odbc.ini file when read by the Mimer ODBC Driver:

Database
Mimer SQL database name
Driver
Mimer ODBC driver library path. Or, a driver name that should be defined in the odbcinst.ini file, usually the name is `mimersql'.
Host, Node, Server or Servername
Network node that the database resides on
Port or Service
TCP/IP port number
User, Username or Uid
Database user name (ident name)
Password or Pwd
Password string (not recommended to provide this way)

Disconnecting

When the application has finished using a data source, it calls SQLDisconnect.

After disconnecting, the application should call SQLFreeHandle to release the connection handle and, if appropriate, to release the environment handle.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX