Testing Your SimbaEngine SDK ODBC Driver with ODBC Test

Microsoft’s ODBC Test is a great way to test an ODBC driver you are building with the SimbaEngine SDK.  It can also be useful to check the capabilities of an ODBC driver you haven’t used before.  ODBC Test provides simple, direct access to your driver.  You can check what metadata is being returned, different SQL capabilities and more.  This can be helpful for reproducing errors you’ve discovered in more complex applications, such as Tableau or Excel.

The first thing you’ll need to do is download ODBC Test from Microsoft.  It is shipped as part of the Microsoft Data Access Components.  Check for the latest version here.  You’ll need to make sure the bitness of ODBC Test you use matches the bitness of your driver.

There are many things you can do with ODBC Test.  This post will cover some useful, basic features.

Connecting to your DSN

The simplest way to connect is the Full Connect option.  Click Conn > Full Connect to see a list of DSN’s available.  If you don’t see your DSN, make sure the bitness of the driver and ODBC Test match.

connect your DSN for ODBC Test

Select your DSN from the list and hit OK to connect.

Select your DSN when developing ODBC Driver

A new pane should open with a success message.

connected

Basic Layout

There are many ways to do most things in ODBC Test.  Here is a very basic layout:

Basic layout for ODBC Test

You can type queries into the Query Pane.  Remember not to include a ; at the end of your query.

The Results Pane displays the results of your actions.  These can be query results, error information, etc…

Buttons

button Execute Execute Button.  It will execute a query you have typed into the execute pane.  It will not return results.

button Results Results Button.  It will return results from a query or other action that returns a result set.

button Clear Err Clear Error Button.  If an error has occurred, and you are unable to take  more actions, this will clear the error.

button Show Err Display Error Button.  If something fails, and the results pane shows “Return: SQL_ERROR=-1”, hitting the Display Error Button will provide more information.  Go to Tools > Options and check Automatic Error Checking if you would like errors to be returned automatically.

Executing a Query

To execute a query, type your query in the Query Pane.  Hit the Execute Button.  The Results Pane will display a message, SQL_SUCCESS=0 means your query was executed successfully.

To see the results of your query, hit the Results Button.  The result set will be displayed in the Results Pane.  The image below shows the results of a successful query against the ADDR table.  This table is included in the sample data for Quickstart with the SimbaEngine SDK.

Executing a query with ODBC Test

Type Metadata

Here are two ways of checking type metadata:

  1. SQLGetTypeInfo
    Click Tools > SQLGetTypeInfo
    This will call the ODBC SQLGetTypeInfo function.  You can select a specific type, such as SQL_DOUBLE, or return the information for all types by using SQL_ALL_TYPES.  All types will return all the SQL data types your driver supports.
    metadata SQL Get Type Info
    Hit OK, then hit the Results Button to see the result set.  This includes information such as Type Name, Data Type Number, Column Size, etc… For more information on data types, see here.
  2. Result Set Metadata
    After executing a query, you can see the metadata for the result set.
    Type a valid select statement in the Query Pane, then hit the Execute Button.  Don’t hit the Results Button.  Now, click Results > Describe Col All.
    This will display the metadata of all columns in the result set.
    display the metadata of all columns when using ODBC Test

Before completing another action, be sure to remember to hit the Results Button to clear the pending result set.

Supported Features

There are different options to see what a driver supports under the Conn menu.
You can look for individual features by clicking Conn > SQLGetInfo and then selecting the desired item.

supported features

To see all supported features, click Conn > Get Info All

Similarly, you can see supported ODBC functions by clicking Conn > SQLGetFunctions or Conn > Get Functions All

Catalog Functions

We looked at the catalog function SQLGetTypeInfo in the Type Metadata section above.  A couple other common catalog functions are SQLTables and SQLColumns.  These are often case sensitive.

SQLTables returns a result set of tables, based on your inputs.  If you use the defaults, all tables will be returned.  Depending on your driver and DSN, this may be limited by a default catalog value.

Click Catalog > SQLTables

SQL tables

You can also specify a catalog, schema or table.  You can use % as a wildcard.  The following example uses the wildcard for the schema, and looks for tables in the DBF Catalog.

SQL tables

After you hit OK on the dialog, click the Results Button to return the result set.

SQLColumns (Catalog > SQLColumns) is similar to SQLTables, except it returns information about columns.  Again, you can specify catalog, schema or table and you can also specify column name.  % still works as a wildcard.  The default will return all accessible columns in the database.

SQL columns

Debugging with Visual Studio

If you are developing a driver with the SimbaEngine SDK, ODBC Test is a great tool for debugging your code!  It can help you understand what happens in sample drivers like Quickstart that are included with the SimbaEngine SDK.  It’s also perfect for debugging your driver if a certain function or query is failing.

  1. Open your project in Visual Studio and place a breakpoint at an appropriate location.
  2. Build your project to make sure the codebase matches what ODBC Test is using.  Make sure you build the driver in Debug mode and that your bitness matches that of ODBC Test.  Also, make sure the driver  your DSN is using points to the DLL built by Visual Studio.
  3. Open ODBC Test.
  4. In Visual Studio, click Debug > Attach to Process
    debugging ODBC Test with Visual Studio
    Then select the process like odbcte32.exe from the list.
    If you are debugging something in the connection, make sure you attach before connecting in ODBC Test.
  5. Execute the query or other function you wish to test in ODBC Test.
  6. Step through the code.
    debug ODBC Test
    You can now do your desired debugging actions, like stepping through the code or seeing what values variables have!
    Hit the red stop button in Visual Studio to stop the debugging session.

Automatic Error Checking

You can configure ODBC Test to automatically display error messages when they occur instead of having to fetch them yourself.  To do this, click Tools > Options and check Automatic Error Checking:

auto_err