DatabaseInstalling a Custom ODBC Driver on Windows for SQL Anywhere

Installing a Custom ODBC Driver on Windows for SQL Anywhere

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Problems can arise when deploying an application if it uses the same Open Database Connectivity (ODBC) drivers as other applications already installed on the same device. This article will discuss how developers can install custom ODBC drivers to ensure that your application’s drivers are not being overwritten. When an application is deployed with ODBC on Windows, the ODBC driver must be installed into the Windows operating system. A DSN then can be created for the application to use to connect to the database. The alternative is to create a connection string without using a DSN but instead using the installed driver name.

As an example, consider an application that uses a SQL Anywhere database. A developer must consider the fact that other applications may also be using their own installations of SQL Anywhere on the same machine, without the end user knowing about it. As such, you have to consider how to prevent your SQL Anywhere components from being updated or replaced by another third-party application, outside of your control.

If another application that embeds SQL Anywhere is installed, it can potentially overwrite the ODBC driver your application installed with a different maintenance release or EBF build number, possibly incompatible with your application. The next time your application runs, the user could encounter problems that will be difficult to track down. Similarly, the installation of your program may overwrite an existing ODBC driver, potentially causing problems with other applications already installed on the machine. Worse, uninstalling another application may remove the ODBC driver that your application relies on, stopping your application before it can even start.

To prevent this, you can create your own custom driver name in the ODBC Registry on Windows, and make sure it always points to the SQL Anywhere ODBC driver that you install with your application. This way, you can ensure the location and specifics of the ODBC driver libraries that you want your application to use during normal operation, and you can ensure you will not affect the ODBC drivers of any other applications on the system if your application is updated or uninstalled.

Manually Creating the Custom ODBC Driver

The following is a walkthrough of how to create your own custom ODBC driver entry by setting specific values in the Registry by using the regedit utility. Once you understand how this works, you can use the windows API functions to open and write the specific keys you require to automate the process.

The first thing you need to do is tell the ODBC Administrator that there is a new driver installed. You do this by adding a new string value to the “ODBC Drivers” key of the Registry. Open “HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INIODBC Drivers” and add a new string value to this key. The name of the string value should be the name of the new driver you are creating. In your case, use “CustomDriver.” The value for this key should be “Installed.”

Figure 1: Adding the new ODBC driver, called “CustomDriver,” to the Registry

Next, you need to tell the ODBC Administrator where to find the ODBC driver files for your new driver when it performs two different activities. The first tells the ODBC Administrator where to find the setup information used when a new DSN is created; the second tells the ODBC Administrator where to find the ODBC driver when an application refers to it in its connect string. To do this, add a new key to “HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INI” with the same name as the previous key you created—in your case, “CustomDriver.” This key will need two string values created for it, one called “Driver,” and the other called “Setup.” The values for these strings should be the name and location of the ODBC driver you are planning to use for your application.

Figure 2: Telling the ODBC administrator where to find the ODBC driver files

At this point, your custom driver is installed and you can begin creating data sources using it, or using the driver name in a connection where you don’t want to specify a DSN (for example, “Driver=CustomDriver;UID=DBA;PWD=SQL;ENG=demo”). If you run the ODBC Data Source Administrator application (odbcad32.exe), you will see your new driver included in the list of drivers when you add a new data source. It will also appear on the “Drivers” tab in the application.

Figure 3: The new custom driver now appears when adding new data sources

Automatically Creating the ODBC Driver Entry

The Windows API provides methods for accessing and modifying the Registry to add the new ODBC driver as part of an application installation. The following function demonstrates how a new Registry key can be created and set for your ODBC driver programmatically.

STDAPI RegisterCustomODBCDriverName( char * DriverName,
                                     char * InstallPath )
{
   char     key[1024];
   char     basekey[1024];
   int           path_len;
   HKEY          hKey;
   int           ret;

   strcpy( basekey, "SOFTWAREODBCODBCINST.INI" );

   // Make Registry entries for the ODBC driver.
   // odbcodbcinst.iniodbc driversDriverName=installed
   strcpy( key, basekey );
   strcat( key, "ODBC Drivers" );
   ret = RegCreateKeyEx( HKEY_LOCAL_MACHINE,
                         key,
                         0,
                         NULL,
                         REG_OPTION_NON_VOLATILE,
                         KEY_ALL_ACCESS,
                         NULL,
                         &hKey,
                         NULL   );

   ret = RegSetValueEx( hKey,
                        DriverName,
                        0,
                        REG_SZ,
                        (unsigned char *)"Installed",
                        10 );    // length of 'Installed' + NULLCHAR

   // odbcodbcinst.iniDriverNamedriver=<path>dbodbc?.dll
   // odbcodbcinst.iniDriverNamesetup=<path>dbodbc?.dll
   strcpy( key, basekey );
   strcat( key, DriverName );
   ret = RegCreateKeyEx( HKEY_LOCAL_MACHINE,
                         key,
                         0,
                         NULL,
                         REG_OPTION_NON_VOLATILE,
                         KEY_ALL_ACCESS,
                         NULL,
                         &hKey,
                         NULL   );

   path_len = _strlen( InstallPath ) + 1;    // include NULLCHAR
   ret = RegSetValueEx( hKey,
                        "Driver",
                        0,
                        REG_SZ,
                        (unsigned char *)InstallPath,
                        path_len );

   ret = RegSetValueEx( hKey,
                        "Setup",
                        0,
                        REG_SZ,
                        (unsigned char *)InstallPath,
                        path_len );

   return S_OK;
}

Now that you have created the new driver, you also must be able to remove it as part of a good uninstall process. The following function will remove the driver entries from the Registry:

UnRegisterCustomODBCDriverName( char * DriverName )
{
   char     key[1024];
   char     basekey[1024];
   HKEY          hKey;

   //Construct the Registry key to open
   strcpy( basekey, "SOFTWAREODBCODBCINST.INI" );
   strcpy( key, basekey );
   strcat( key, "ODBC Drivers" );

   //Open the Registry key
   if( RegOpenKeyEx( HKEY_LOCAL_MACHINE,
                     key,
                     0,
                     KEY_ALL_ACCESS,
                     &hKey      ) != ERROR_SUCCESS ) {

      return E_UNEXPECTED;
   }

   strcpy( key, DriverName );

   //Remove the driver if it already exists
   if( RegDeleteValue( hKey,
                       key      ) {

      return E_UNEXPECTED;
   }

   strcpy( key, basekey );
   strcat( key, DriverName );

   if( RegDeleteKey( HKEY_LOCAL_MACHINE,
                     key     ) != ERROR_SUCCESS ) {

      return E_UNEXPECTED;
   }

   return S_OK;
}

Final Remarks

By creating your own custom ODBC driver entry, you effectively can isolate your application database connectivity components from other applications running on the same system. This decreases the chance for deployment problems, and problems related to applying maintenance or other upgrades. In turn, this decreases the amount of installation and configuration support that will be required during deployment, and improves the overall end user experience with your application.

About the Author

Jason Hinsperger has been working with SQL Anywhere for over 13 years. In his current role as a Senior Product Manager with Sybase iAnywhere, Hinsperger regularly shares his in-depth knowledge about SQL Anywhere through articles and talks. When he’s not working with SQL Anywhere or learning new technologies, Hinsperger spends his time outdoors running a cattle farm in southern Ontario, Canada.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories