Using MS SQL Server from C++ with wxWidgets



Using SQL Server with ODBC, C++, and wxWidgets

In this article, we will discuss about using MS SQL server with ODBC API from C++ program. [Eas18]. Thereafter, we will create a class module that connects to SQL server and use it with an example wxWidgets GUI application. For Unix and Linux machines, FreeTDS and ODBC need to be setup as discussed at the following link.

Using Microsoft SQL Server from Linux Machines with ODBC & FreeTDS
(http://coolemerald.blogspot.com/)

1. ODBC Handles
2. Connecting a Data Source
3. Connection Attributes
4. Result-set generating functions
5. Getting Result-sets
6. Return Statuses
7. ODBC C++ Class with wxWidgets
8. References

ODBC Handles

There are four main types of ODBC handles.

SQLHENV:

This is environment handle and it is the first handle to start. Only after an environment handle is allocated, other operations like defining ODBC version, allocating connection handle can be done. SQLSetEnvAttr function can be used to define its attributes. And SQLAllocHandle can be used for allocating other handles.

SQLHDBC:

This is connection handle. Connection handles are required to connect to data sources. To set and get its attributes, SQLSetConnectAttr and SQLGetConnectAttr can be used.

SQLHSTMT:

This is statement handle. After a data source has been successfully connected with a connection handle, a statement handle can be allocated and SQL executions can be performed. Similarly, SQLSetStmtAttr and SQLGetStmtAttrits can be used to set and get its attributes.

SQLHDESC:

This is descriptor handle. It is very useful and it is seldom used only to perform complex operations.

To allocate handles, SQLAllocHandle can be used as follows.

SQLRETURN SQLAllocHandle( SQLSMALLINT HandleType,
    SQLHANDLE InputHandle, SQLHANDLE *OutputHandlePtr)

Handle type can be one of the followings just as mentioned above.

1. SQLHENV
2. SQLHDBC
3. SQLHSTMT
4. SQLHDESC

To allocate environment handle, SQL_NULL_HANDLE can be used for the InputHandle argument. When allocating connection handle, the InputHandle argument should be its enclosing handle, environment handle. For statement handle and descriptor handle, connection handle should be the InputHandle. OutputHandlePtr is a pointer for the returned handle.

To free up the handles and their resources, SQLFreeHandle API can be used as follows.

SQLRETURN SQLFreeHandle( SQLSMALLINT HandleType, SQLHANDLE Handle)

The order of freeing up the handles should be the reverse of that of allocating them. Typical code found in an ODBC application is shown in Listing 1.

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
// code to connect to the data source

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
// do something with the statement handle 

SQLFreeHandle(SQL_HANDLE_STMT, stmt);
// disconnect

SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);

Listing 1. Typical code for allocating and freeing up handles in an ODBC applications.

For newer applications, ODBC version 3 should be used, and environment attribute for it can be set as follows.

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

Connecting a Data Source

To connect a data source using a connection handle, SQLDriverConnect can be used. The API for SQLDriverConnect is shown in the following listing.

SQLRETURN SQLDriverConnect(
  SQLHDBC ConnectionHandle,
  SQLHWND WindowHandle,
  SQLCHAR *InConnectionString,
  SQLSMALLINT StringLength1,
  SQLCHAR *OutConnectionString,
  SQLSMALLINT BufferLength,
  SQLSMALLINT *StringLength2Ptr,
  SQLUSMALLINT DriverCompletion)

Its arguments are as follows.

1. ConnectionHandle is a handle which is allocated for connection.
2. WindowHandle can be passed to the driver to show a dialog. If you do not want the driver to show a dialog, NULL can be used for it.
3. InConnectionString and StringLength1 is for input connection string where the connection attribute and value pairs can be put using semicolons as separators like "DSN = datasource; UID = username; PWD = password;". StringLength1 is the length of the InConnectionString. For null terminated string, SQL_NTS can be used instead of numbers.
4. OutConnectionString, BufferLength, and StringLength2Ptr are for full connection string. The full connection string can be reused for connection calls in their application. OutConnectionString is a pointer for the buffer to store output connection string . BufferLength is the size of the buffer to limit the length of the string. StringLength2Ptr is a pointer to integer where the resulting length of the string is returned. When the WindowHandle is NULL, the output connection string is same as input connection string. If not, the additional connection information obtained from the dialog will be included in the output connection string as a full connection string.
5. DriverCompletion dictates the driver behaviours when input connection string does not have enough information for the connection.

There are four types of DriverCompletion.

1. SQL_DRIVER_PROMPT shows a dialog and gets the additional information to add to InConnectionString . The resulting connection string is put in the buffer pointed by OutConnectionString
2. SQL_DRIVER_COMPLETE copies input connection string to output connection string when it does not need additional information for the connection. If the input connection string is imcomplete, it shows a dialog and behaves like SQL_DRIVER_PROMPT
3. SQL_DRIVER_COMPLETE_REQUIRED is same as SQL_DRIVER_COMPLETE , but it also disables the unnecessary information.
4. SQL_DRIVER_NOPROMPT copies input connection string to output connection string and connects to data source without showing the dialog. If the input connection string is wrong or imcomplete, it returns SQL_ERROR instead of showing the dialog.

If the dialog is cancelled, SQL_NO_DATA is returned.

Connection Attributes

When connecting a data source, the most important argument is InConnectionString. The attributes for InConnectionString are shown below.

1. DSN is data source name and it can be created and defined in advance. In Windows machines, ODBC Administrator can be used. In Linux machines, it can be defined in odbc.ini as in discussed in this article.
2. DRIVER is the name of the driver and it can be used in DSN-less connections.
3. FILEDSN is the name of a file that consists of connection attributes.
4. UID/PWD are username and password to authenticate the database.
5. SAVEFILE requests to save DSN attributes and it is the name of the file to save.

An example C++ program that connects a DSN is shown at the following link.

con.cpp (https://github.com/yan9a/odroid/blob/master/database-mssql/con.cpp)

When building the program, it is necessary to include -lodbc flag to indicate the use of ODBC library. Listing 1. con-bar.sh

g++ con.cpp -lodbc -std=c++11 -o con
./con

Listing 1. con-bar.sh - building and running C++ program using ODBC.

Result-set generating functions

Reselt-set generating functions are the main components of the ODBC API and they can perform operations such as SQL queries, retrieving metadata, etc. The obtaining result-sets are row and column data.

SQLExecDirect can be used to perform SQL queries. As an example, retrieving all the columns from a table, "mytable", is shown in the following listing.

SQLExecDirect(stmt_handle, "select * from mytable", SQL_NTS);

If you need to perform a same query several times, you can prepare the query using SQLPrepare and use it with SQLExecute repeatedly.

  SQLPrepare(stmt_handle,
    "select * from mytable where mycol = ?", SQL_NTS);
  // bind a parameter with SQLBindParam
  // set parameter's value
  loop {
    SQLExecute(stmt_handle);
    // get results
    // change parameter's value
  }

Other meta-data API includes SQLTables, SQLColumns, SQLTablePrivileges, SQLStatistics, SQLSpecialColumns, SQLProcedures, SQLProcedureColumns, SQLPrimaryKeys, SQLForeignKeys, SQLColumnPrivileges, and SQLGetTypeInfo.

Getting Result-sets

Reult-sets obtained from result-set generating functions are extracted using the three functions below.

1. SQLNumResultCols can be used to check the number of columns in the result-set.
2. SQLFetch can be used to extract the rows one by one. If there is no more row, SQL_NO_DATA will be returned.
3. SQLGetData is used to get column data typically using a loop structure.

A simple C++ example shown at the following link reads the values in a database table.

fetch.cpp (https://github.com/yan9a/odroid/blob/master/database-mssql/fetch.cpp)

Return Statuses

The return status from an ODBC API function can be checked whether the operation is successful or not. In C/C++, SQL_SUCCEEDED macro can be used. Its definition is shown below.

#define SQL_SUCCEEDED(rc) (((rc)&(~1))==0)

Almost all the ODBC functions return the following two values when success.

1. SQL_SUCCESS
2. SQL_SUCCESS_WITH_INFO

SQL_SUCCEEDED macro returns true for both of them. SQL_SUCCESS_WITH_INFO means the function is successful but there are some information that need attention. For example, if SQLGetData returns SQL_SUCCESS_WITH_INFO, that means the resulting column data is truncated because the buffer size is not enough. For some ODBC functions, even though SQL_SUCCEEDED macro is fail, it might not be error. For example, if SQLFetch returns SQL_NO_DATA, it means there is no further row to read.

If an ODBC function returns an error or SQL_SUCCESS_WITH_INFO, the diagnostic records are attached to its handle. To extract the information, SQLGetDiagRec can be used. More than one diagnostic record can be attached to the handle. The diagnostic records can be obtained by repeatedly calling SQLGetDiagRec starting with 1 until SQL_NO_DATA is returne. An example C++ function that extracts diagnostic records from a handle is shown in Listing 2.

vector < string > GetError(char const *fn,SQLHANDLE handle,SQLSMALLINT type)
{
    SQLINTEGER   i = 0;
    SQLINTEGER   native;
    SQLCHAR      state[7];
    SQLCHAR      text[256];
    SQLSMALLINT  len;
    SQLRETURN    ret;
    vector < string > emes;
    emes.push_back(fn);
    do { 
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,sizeof(text), &len);
        if (SQL_SUCCEEDED(ret)) { 
          printf("%s:%ld:%ld:%s\n", state, (long int)i, (long int)native, text); 
          emes.push_back((char*)text);
        }            
    } while( ret == SQL_SUCCESS );
    return emes;
}

Listing 2. Extracting diagnostic records.

ODBC C++ Class with wxWidgets

A simple C++ class example to use ODBC datasource is shown in

ceODBC.h (https://github.com/yan9a/odroid/blob/master/database-mssql/ceODBC.h) and

ceODBC.cpp (https://github.com/yan9a/odroid/blob/master/database-mssql/ceODBC.cpp)

. An example program using the class can be found at

simpleodbc.cpp (https://github.com/yan9a/odroid/blob/master/database-mssql/simpleodbc.cpp)

. At the start of the program, when an instance of ceODBC is initialized, environment handle and connection handle are allocated in the constructor. Thereafter, Connect method can be used to connect to a datasource. The connection can be checked with IsConnected method and The resulting row and column data for command like SELECT from a table are returned as a 2 dimensional string vector. For the statements like UPDATE, INSERT, DELETE that have no returned dataset, the number of affected row can be found at member variable, NumberOfRowsAffected. The connection with the datasource can be disconnected using Disconnect method. Freeing up environment handle and connection handle are done automatically in destructor. To build and run the program, commands as shown in Listing 3. simpleodbc-bar.sh can be used.

g++ simpleodbc.cpp ceODBC.cpp -lodbc -std=c++11 -o simpleodbc
./simpleodbc

Listing 3. simpleodbc-bar.sh - building and running simpleodbc.cpp.

An example of using that ceODBC class with wxWidgets is also shown at

wxodbc.cpp (https://github.com/yan9a/odroid/blob/master/database-mssql/wxodbc.cpp)

. When the button is clicked, a table is read and listed in the list box as shown in the GUI that is illustrated Figure 1.

Figure 1. GUI of wxodbc program.

The commands to build and run the example is shown in Listing 4. wxodbc-bar.sh. The implementation code for the class is in ceODBC.cpp, the reason is to prevent the driver from using SQLxxxW instead of SQLxxx APIs, in case wxWidgets defines UNICODE.

g++ wxodbc.cpp ceODBC.cpp -lodbc -std=c++11 `wx-config --cxxflags --libs` -o wxodbc
./wxodbc

Listing 4. wxodbc-bar.sh - building and running wxodbc.cpp.

References

[Eas18] Easysoft. Easysoft tutorials on using ODBC from C. 2018.
url: https://www.easysoft.com/developer/languages/c/odbc_tutorial.html.