fbpx Skip to content

How to Build a Custom ODBC Driver (part 2)

insightsoftware -
November 10, 2022

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

22 12 Web Magnitude Dataconnectivity Blog

This is the second installment in a two part series of companion articles that demonstrate how to use the SimbaEngine SDK to create a custom ODBC driver. The first article in the series—found here—includes the first three steps for developing a custom driver, we pick up here at step 4.

Step 4. Create and return metadata sources

In this section we will make the code changes necessary so that an ODBC-enabled application can connect to the driver and retrieve type metadata from Bamboo.

Overview of SQL Catalog Functions

The custom ODBC driver uses metadata sources, provided by the SimbaEngine SDK, to handle SQL catalog functions.

ODBC applications need to understand the structure of a data store in order to execute SQL queries against it. This information is provided using catalog functions. For example, an application might request a result set containing information about all the tables in the data store, or all the columns in a particular table. Each catalog function returns data as a result set.

Most ODBC-enabled applications require a driver to implement the following catalog functions. It is possible to implement additional catalog functions in the custom driver if required.

CATALOG FUNCTION DESCRIPTION
SQLGetTypeInfo Returns information about data types supported by the data source.
SQLTables (CATALOG_ONLY) If CatalogName is SQL_ALL_CATALOGS and SchemaName and TableName are empty strings, the result set contains a list of valid catalogs for the data source. (All columns except the TABLE_CAT column contain NULLs.)
SQLTables (SCHEMA_ONLY) If SchemaName is SQL_ALL_SCHEMAS and CatalogName and TableName are empty strings, the result set contains a list of valid schemas for the data source. (All columns except the TABLE_SCHEM column contain NULLs.)
SQLTables (TABLE_TYPE_ONLY) If TableType is SQL_ALL_TABLE_TYPES and CatalogName, SchemaName, and TableName are empty strings, the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain NULLs.)
SQLTables Returns the list of table, catalog, or schema names, and table types, stored in a specific data source.
SQLColumns Returns a list of columns in one or more tables.

Example: Using Catalog Functions with the QuickStart driver

  1. In the ODBC Test application, connect to the QuickStart driver.
  2. To send the SQLTables (CATALOG_ONLY) catalog function, select Catalog > SQLTables.
  3. Enter SQL_ALL_CATALOGS for the CatalogName, then select the correct value for NameLength1. For example:Custom ODBC Driver | Catalog Name
  4. Click OK.
  5. Selectdisplay_view_iconto retrieve the results.

The following list of valid catalogs (containing only the “DBF” catalog) for the QuickStart data source is returned:

GetDataAll:
"TABLE_CAT",TABLE_SCHEM","TABLE_NAME","TABLE_TYPE","REMARKS"
"DBF",<Null>,<Null>,<Null>,<Null>
1 row fetched from 5 columns

For more information on SQL catalog functions, see: Catalog Functions

Implementing Metadata Sources to Handle Catalog Functions

SQL catalog functions are represented in the DSI by metadata sources; there is one metadata source for each of the catalog functions. QSDataEngine::MakeNewMetadataTable() is responsible for creating the metadata sources. Metadata sources are used return the catalog metadata about a data store to the ODBC application for the ODBC catalog functions.

Double click the TODO #7 Create and return your Metadata Sources message to jump to the relevant section of code.

There is one metadata source for each of the catalog functions. For example, when the application calls SQLColumns(), a DSI_COLUMNS_METADATA source is created to return the list of columns in one or more tables in the data store.

Each ODBC catalog function is mapped to a unique DSIMetadataTableId, which is then mapped to an underlying MetadataSource that the driver implements and returns. Each MetadataSource instance is responsible for the following:

  1. Creating a data structure that holds the data relevant for the custom data store, in the class constructor.
  2. Navigating the structure on a row-by-row basis, in the Move() method.
  3. Retrieving data in the GetData() method. (See Data Retrieval for a brief overview of data retrieval.)

Required Metadata Sources

All custom ODBC drivers must implement the following metadata sources, as they are required by ODBC applications:

METADATA SOURCE DESCRIPTION
DSI_TABLES_METADATA List of all tables defined in the data source. This source is constructed via the QSMetadataHelper and SQL Engine.
DSI_CATALOGONLY_METADATA List of all catalogs defined in the data source, if catalogs are supported. This source is constructed via the QSMetadataHelper and SQL Engine.
DSI_SCHEMA_METADATA List of all schemas defined in the data source. This source is constructed via the QSMetadataHelper and SQL Engine. QuickStartDSII does not support schemas.
DSI_TABLETYPEONLY_METADATA List of all table types (TABLE, VIEW, SYSTEM) defined within the data source. This source is constructed via the QSMetadataHelper and SQL Engine.
DSI_COLUMNS_METADATA List of all columns defined across all tables in the data source. This source is constructed via the QSMetadataHelper and SQL Engine.
DSI_TYPE_INFO_METADATA List of the supported types by the data source. This means the actual types that can be stored in the data source, not necessarily the types that can be returned by the driver. For instance, a conversion may result in a type being returned that is not stored in the data source.

Most catalog types are created using the metadata helper in conjunction with the SQL Engine.

Handling DSI_TYPE_INFO_METADATA

The underlying ODBC catalog function SQLGetTypeInfo is handled as follows:

  1. When called with DSI_TYPE_INFO_METADATA, QSDataEngine::MakeNewMetadataTable() will return an instance of QSTypeInfoMetadataSource().
  2. The QuickStart sample driver supports a large number of data types. For the Bamboo driver, we will start with a very simple list of data types and expose anything more complex as WVARCHAR:
    • SQL_BIGINT
    • SQL_FLOAT
    • SQL_BIT (Used for Boolean)
    • SQL_WVARCHAR
  3. For Bamboo, we modified QSTypeInfoMetadataSource::PrepareType() to only list support for these data types. You would make similar changes based on the data types in your underlying data source.

Handling the Other Metadata Sources

The other ODBC catalog functions, including SQLTables (CATALOG_ONLY), SQLTables (TABLE_TYPE_ONLY), SQLTables (SCHEMA_ONLY), SQLTables and SQLColumns, are handled automatically by the metadata helper class.

When these functions are called with any other DSIMetadataTableId, QSDataEngine::MakeNewMetadataTable() should return null. Returning null tells the SimbaEngine SDK to use the metadata helper class returned via QSDataEngine::CreateMetadataHelper() along with some default MetadataSources to create the data source metadata. You can also choose to return a DSIMetadataSource if you don’t want to use the metadata helper.

Change the following methods:

  • QSMetadataHelper::QSMetadataHelper() The example constructor retrieves a list of the tables in the data source. Modify this method to load the tables defined in your data store.
  • QSMetadataHelper::GetNextTable() In the QuickStart driver, this method returns the next table in the data source. Modify this method to retrieve the next table from your data store.

The DSIExtMetadataHelper class works by retrieving the identifying information for each table and then opening the table via QSDataEngine::OpenTable(). After QSTable is implemented, the correct metadata will be returned for all the tables and columns in our data source.

We can now retrieve type metadata from our data store.

Custom ODBC Driver | Type Metadata Example

Related Links: Fetching Metadata for Catalog Functions in Developing Drivers for Data Stores Without SQL


Step 5. Enable data retrieval

In this section, we will execute queries and retrieve data from the Bamboo data store.

QSDataEngine::OpenTable() is the entry point where the Simba SQL Engine requests that tables involved in the query be opened. It is called during the preparation of a SQL statement.

NOTE: The SQL Engine component of the SimbaEngine SDK allows applications to execute SQL commands on data stores that are not SQL-capable.

Click on TODO #8: Open A Table to jump to the relevant section of code.

QSTable is an implementation of DSIExtSimpleResultSet, an abstract class that provides basic forward-only result set traversal. The main role of QSTable is to translate the stored data from the native data format into SQL Data types.

The QuickStart sample driver is implemented for Tabbed Unicode Files. The sample driver translates the text from UTF16-LE strings into the SQL Data types defined for each column within the configuration dialog.

In the QuickStart driver, QSTable uses a TabbedUnicodeFileReader, which provides an interface to navigate between lines within a Unicode text file. This class preprocesses each row in the file to determine the starting file offset of each column in the row. Its GetData method takes a columnIndex and uses it to calculate the exact position in the file where the column’s data resides. The method repositions the file and retrieves the data as if from a byte-buffer. See Data Retrieval for a brief overview of data retrieval.

The following sections explain how we will implement data retrieval in the custom ODBC driver.

Modify the OpenTable Method

The QSDataEngine::OpenTable() method is called during the preparation of a SQL statement. We’ll modify this method to check that the supplied catalog, schema and table names are valid and correspond to a table defined in our data store. If the inputs are not valid, return null to indicate that the table does not exist. If the inputs are valid, return a new instance of QSTable.

Example: The DoesTableMetadataExist() and DoesTableDataExist() are the functions responsible for checking if the table and its metadata exist. For our project, we modified the DoesTableMetadataExist() to check for “.JSON” files. Originally, it searched for “.TXT” files.

We use these local JSON files to define the column names and types the driver will expose for each attribute in a specific endpoint. Depending on your data source, you might make a REST or other network call to get this kind of information from the server instead.

{
 "endPoint": "project",
 "columns":[
 {
 "colName": "KEY"
 "colType": 12
 },
 {
 "colName": "NAME"
 "colType": 12
 },
 {
 "colName": "DESCRIPTION"
 "colType": 12
 },
 {
 "colName": "LINK-HREF"
 "colType": 12
 },
 {
 "colName": "LINK-REL"
 "colType": 12
 }
 ]
}
Custom ODBC Driver | Open Table

Modify QSTable

We’ll now modify QSTable so that it can work with our data store.

Return the catalog, schema and table names for our table.

Make the following changes to your custom ODBC driver so that it can work with your data store:

QSTable::QSTable() : Modify the constructor to take in the catalog, schema and table names and save them in member variables.

QSTable::GetCatalogName() : Returns QS_CATALOG

QSTable::GetSchemaName() : Returns simba_wstring() (because it does not support schemas)

QSTable::GetTableName() : Returns m_tableName

EXAMPLE: In our case, we’ll change QS_CATALOG to BO_CATALOG, which is a constant string “BAMBOO” which the driver always returns as the catalog name. Many applications expect data sources to support catalogs, which is why we always return something here.

Custom ODBC Driver | QS_CATALO

Return the columns defined for your table

Modify QSTable::InitializeColumns() so that, for each column defined in the table, you define a DSIResultSetColumn in terms of SQL types.

EXAMPLE: Pseudo code for a custom QSTable::InitializeColumns()

For Each Defined Column

{
AutoPtr<DSIColumnMetadata> columnMetadata(
new DSIColumnMetadata());
columnMetadata->m_catalogName = m_catalogName;
columnMetadata->m_schemaName = m_schemaName;
columnMetadata->m_tableName = m_tableName;
columnMetadata->m_name = //column name
columnMetadata->m_label = //localized column name
columnMetadata->m_unnamed = false;
columnMetadata->m_charOrBinarySize = //the length in bytes
columnMetadata->m_nullable = DSI_NULLABLE;

// Change the first parameter of this method to the SQL
// type that maps to your data store type.
SqlTypeMetadata* sqlTypeMetadata =
SqlTypeMetadataFactory::MakeNewSqlTypeMetadata(
SQL_WVARCHAR, TDW_BUFFER_OWNED);
columns->AddColumn(
new DSIResultSetColumn(
sqlypeMetadata,
columnMetadata.Detach()));
}
m_columns.Attach(columns.Detach());

Implement Navigation

The methods listed below are responsible for navigating a data structure containing information about one table in the data store, and retrieving data from the table. We’ll modify these methods for our data store:

  • QSTable::MoveToBeforeFirstRow()
  • QSTable::MoveToNextRow()
  • QSTable::RetrieveData

In a custom implementation:

  • It is best to implement a streaming interface for the data in the table within the data store.
  • Provide the ability to navigate forward from one table row to the next.
  • Provide the ability to navigate across columns within the row.
  • Provide the ability to read the data associated with the current row and column combination.

EXAMPLE:

MoveToBeforeFirstRow – indicates that the driver has not started fetching data yet.

MoveToNextRow – If the driver hasn’t started fetching yet, it moves to the first row. Otherwise, it will move to the next row from where it currently is. You will need to decide when to fetch more data from your server in these operations. Typically, you would fetch a certain number of rows from the server at a time, say 100, and repeat this as those rows have been moved through.

Custom ODBC Driver | MoveToNextRow

Modify the DoCloseCursor Method

QSTable::DoCloseCursor() is a callback method called from Simba SQL Engine to indicate that data retrieval has completed, and that we may now do any tasks related to closing the connection to our data store.

This completes the core functionality of the driver! You can now do a full test in Tableau or Power BI and list tables as well as return data from tables! Now we need to do a bit of clean up and modify our connection dialog.


Step 6. Rebrand and rename the custom OBDC driver

In this final section, we will conclude our custom build by renaming and rebranding our driver.

Rebrand Error Messages

Error messages sent by the driver are visible to applications and customers. In the QuickStart sample driver, error messages are branded with QuickStart, QS, and Simba. In this section, we will explain how to rebrand the error messages to reflect the custom driver name and the company name.

All the error messages used within the DSI implementation are stored in a file called QSMessages.xml.

To configure error messages, we will change QuickStart, QS, and Simba to Bamboo, BO, and Simba respectively using the following steps:

  1. Rename the xml file to reflect the name of the company or the custom ODBC driver.
  2. Double click the TODO #9 Register the QSMessages.xml file for handling by DSIMessageSource message to jump to the relevant section of code.
  3. Update the line associated with the TODO to match the new name of the xml file.
  4. Open the xml file and change all instances of the following items:
    • Change the letters QS to an appropriate two-letter abbreviation.
    • Change the word QuickStart to an appropriate name for the custom driver.
  5. For each exception thrown within the custom DSI implementation, change the parameters to match the custom driver name. This rebrands the error messages to reflect the name of the driver.
  6. Double click the T to go to the relevant section of code.
  7. The vendor name is prepended to all error messages that are visible to applications. As explained in the code comments, change the vendor name from Simba to an appropriate name for your company. In our example, we are keeping the Simba name.

Rebrand the Custom ODBC Driver

All the TODOs in the QuickStart sample driver project are finished, and the custom driver is rebranded and retrieving data from our data store. To complete the custom driver, we’ll add the following functionality:

  1. Rename all files and classes in the project to have the two-letter abbreviation chosen as part of TODO #9—i.e., BO.
  2. Create a driver configuration dialog. This dialog is presented to the user when the ODBC Data Source Administrator is used to create a new ODBC DSN or configure an existing one. The QuickStart driver project contains an example ODBC configuration dialog under the Setup folder in the QuickStart driver project. EXAMPLE: The “Setup” folder contains the files needed to be modified for the dialog windows.Custom ODBC Driver | Setup DirectoryThis is the base dialog box.Custom ODBC Driver | Dialog boxAfter modification, it has text boxes for username, password, server, and port.Custom ODBC Driver | Dialog box after modification
  3. To see the driver configuration dialog that was created in the previous step, run the ODBC Data Source Administrator, open the Control Panel, select Administrative Tools, and then select Data Sources (ODBC). If the Control Panel is set to view by category, then Administrative Tools is located under System and Security.

IMPORTANT: If you are using 64-bit Windows with 32-bit applications, you must use the 32-bit ODBC Data Source Administrator. You cannot access the 32-bit ODBC Data Source Administrator from the Start menu or control panel in 64-bit Windows. To access a 32-bit ODBC Data Source Administrator from a 64-bit machine, run C:\WINDOWS\SysWOW64\odbcad32.exe.

For more information, see: Bitness and the Windows Registry and 32-bit vs 64-bit ODBC Data Source Administrator.

Conclusion

The Bamboo custom driver project demonstrates how the SimbaEngine SDK can provide a complete set of components to quickly, easily and cost-effectively implement a driver that meets your data connectivity requirements. It includes all the components needed to deploy an ODBC, JDBC, ADO.NET, or OLE DB driver, for SQL-enabled and non-SQL data stores, as a single tier or client/server, standards-based solution. Using our comprehensive documentation, an array of pre-defined functionality can be included to easily deploy a fully optimized driver suited to specific requirements.

Simba Eval

Interested? If so, we welcome you to take advantage of our free, 20-day evaluation. It comes complete with the SimbaEngine SDK Evaluation Software and a dedicated support team to assist you throughout the evaluation. Start your evaluation