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
- Basic Layout
- Executing a Query
- Type Metadata
- Supported Features
- Catalog Functions
- Debugging with Visual Studio
- Automatic Error Checking
- Parameterized query
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. For more control over how the connection is made, see SQLDriverConnect.
Select your DSN from the list and hit OK to connect.
A new pane should open with a success message.
There are many ways to do most things in ODBC Test. Here is a very basic layout:
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…
Execute Button. It will execute a query you have typed into the execute pane. It will not return results.
Results Button. It will return results from a query or other action that returns a result set.
Clear Error Button. If an error has occurred, and you are unable to take more actions, this will clear the error.
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.
Here are two ways of checking type metadata:
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.
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.
- 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.
Before completing another action, be sure to remember to hit the Results Button to clear the pending result set.
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.
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
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
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.
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.
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.
- Open your project in Visual Studio and place a breakpoint at an appropriate location.
- 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.
- Open ODBC Test.
- In Visual Studio, click Debug > Attach to Process
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.
- Execute the query or other function you wish to test in ODBC Test.
- Step through the code.
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:
Follow these steps to connect:
- Allocate environment handle
- Click: Env > SQLAllocHandle
- Select Handle Type: SQL_HANDLE_ENV
- Set environment attribute to specify ODBC version
- Click: Attr > SQLSetEnvAttr
- If necessary, modify ODBC Version
- Allocate connection handle
- Click: Env > SQLAllocHandle
- Select Handle Type: SQL_HANDLE_DBC
- Click: Connect > SQLDriverConnect
- Set InConnection String
The connection string used will depend on the driver. First, you set the Driver name, like DRIVER=QuickstartDSIIDriver, then use a semicolon delimited string of any key value pairs necessary. A Sample string for the Simba Quickstart Driver is:
- Allocate statement handle
- Click: Env > SQLAllocHandle
- Select Handle Type: SQL_HANDLE_STMT
Now that you have a statement handle, you can do regular operations on the driver, such as: