Migrating SQL Server Embedded SQL for C/C++ (ESQL/C) to ODBC API

Embedded SQL (ESQL) is a SQL-92 standard application programming interface (API) for database access.

ESQL/C programs require preprocessing by a precompiler that converts Embedded SQL statements into function calls that can be accepted by a C compiler. Then the C compiler compiles the resulting source code into an executable program.

Microsoft SQL Server 2000 provides an Embedded SQL precompiler for C/C++ applications. The SQL Server precompiler translates Embedded SQL statements to the appropriate DB-Library API functions.

Although SQL Server 2008/2005 still supports connections from existing applications that use the DB-Library and Embedded SQL APIs, it does not include the files or documentation for these APIs.

Moreover future versions of SQL Server will not support connections from DB-Library or Embedded SQL applications.

Microsoft does not recommend using DB-Library or Embedded SQL for new applications, and recommends removing any dependencies on these APIs.

Instead of these APIs, Microsoft recommends using the SQLClient namespace or ODBC/OLE DB APIs.

ESQL/C syntax combines SQL-92 standard Embedded SQL and Transact-SQL syntax.

ESQL/C keywords and statements are case-insensitive, while cursor, prepared statement and connection names are case-sensitive.

The precompiler replaces embedded SQL statements by calls to the run-time library (sqlakw32.dll). The run-time library calls DB-Library (ntwdblib.dll) to access SQL Server.

Each Embedded SQL statement starts with EXEC SQL keyword. It tells the precompiler that the code entered between EXEC SQL and the semicolon (;) contains Embedded SQL statements.

 int getCount()
{
    EXEC SQL BEGIN DECLARE SECTION;
      int count;
    EXEC SQL END DECLARE SECTION;
 
    EXEC SQL CONNECT TO myserver.pubs USER $integrated;
    EXEC SQL SELECT count(*) INTO :count FROM authors;
 
    return count;
 }

You can see that the ESQL/C approach is more concise than the call-level methods such as ODBC, DB-Library, OLE DB etc.

Include files

• ESQL/C

ESQL/C does not require special include files, the precompiler adds them automatically during the precompilation process.

• ODBC

For an ODBC application, you typically need to include sqlext.h file in the source files where you use ODBC API functions and structures:

sqlext.h file contains ODBC 3 extensions and automatically includes sql.h that also includes sqltypes.h

windows.h is also required before including sqlext.h (if windows.h is not included yet in ESQL/C application):

Source C/C++ files

The precompiler nsqlprep.exe requires that the input C files with ESQL/C code have .SQC extension. As the result of precompilation, the precompiler generates .c files.

The SQL Server ESQL/C connection statement syntax:

When $integrated keyword is specified instead of login.password, Windows Authentication is used. If you omit the servername, the local server is assumed.

You can use character literals (quoted or unquoted) or host variables to specify connection parameters. For example:

   EXEC SQL CONNECT TO :svr USER :usr;
   EXEC SQL CONNECT TO "myserver.pubs" USER "usr.password";
   EXEC SQL CONNECT TO myserver.pubs USER usr.password;

Conversion to ODBC - ODBC Connection

To connect to a database in ODBC applications, you need to allocate environment and connection handles and execute a connection function:

• Allocating Environment Handle

Before you can call any ODBC function, you have to initialize the ODBC environment and allocate an environment handle:

  SQLHENV henv;
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

Note. Before an application allocates a connection, it must set the SQL_ATTR_ODBC_VERSION environment attribute that specifies whether ODBC 2.x or ODBC 3.x specification is used.

The environment handle is also used to allocate connection handles, and if the application uses multiple connections, and connection statements exist in different C/C++ files, it makes sense to define the environment handle as a global variable and initialize it in the C/C++ file containing main() function:

  // Global SQLHENV variable (for the example, in the file containing main() function )
  SQLHENV henv;
 
  void main()
  {
      // … 
      SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
      SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
      // …
  }

Then in other files that allocate connection handles, you can use extern variable:

   extern SQLHENV henv;
 
   void func()
   {
       // …   
       SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
       // …
   }

• Allocating Connection Handle

Before the application can connect to a database, it must allocate a connection handle:

   SQLHDBC hdbc;
   SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

The connection handle is also used to allocate statement handles, and if the same connection is used in multiple C/C++ files, it makes sense to define the connection handle as a global variable in the C/C++ file containing the connection function:

  // Global SQLHDBC variable (for the example, in the file containing connection function with this handle)
  SQLHDBC hdbc;
 
  void func()
  {
      // … 
      SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
      // …
  }

Then in other files that use this connection to allocate statement handles, you can use extern variable:

   extern SQLHDBC hdbc;
 
   void func2()
   {
       // …   
       SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 
       // …
   }

• Establishing a Connection

After allocating environment and connection handles, the application can connect to a database using SQLConnect(), SQLDriverConnect() and SQLBrowseConnect() functions.

Connecting with SQLConnect()

SQLConnect() is the simplest connection function that requires a data source name and optional user name and passwords.

The data source can be created using ODBC Administrator Tool, or you can provide a file containing driver, server and database name information.

    SQLConnect(hdbc, "datasource name", SQL_NTS, "user", SQL_NTS, "password", SQL_NTS);

Disadvantage of SQLConnect(): SQLConnect() does not use a connection string, you need to create a data source in the system before you can establish a connection. This can complicate maintenance and deployment of the application.

Although File DSN (connection information stored in a file) can be used to overcome this problem, it is more typical to use SQLDriverConnect() function.

Connecting with SQLDriverConnect()

SQLDriverConnect() allows you connecting to a database using a connection string. In the connection string you can specify driver, server, instance, database name, user and password.

With SQLDriverConnect() you do not need to create an ODBC data source in the system before you can establish a connection.

SQL Server connection string examples:

SQLDriverConnect() call example:

   SQLDriverConnect(hdbc, NULL, (SQLCHAR*)connection_string, connection_string_length, 
      NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

SQLCODE variable and SQLCA structure are used to handle errors in SQL Server ESQL/C applications.

SQLCODE Variable

sqlca.h file defines SQLCODE variable as:

   //#define SQLCODE sqlca->sqlcode
   extern long SQLCODE;

Each time SQL Server runs an Embedded SQL statement, it sets SQLCODE variable to indicate whether the last SQL statement completed successfully. 0 indicates successful execution, other values indicate warnings or errors.

SQL Server ESQL/C applications typically use the following code to check for an error or successful execution:

    EXEC SQL ... 
 
    if (SQLCODE == 0)
    {
       // ....
    }

Besides SQLCODE variable, you can also reference to the error message as SQLERRMC that defined in sqlca.h as:

   #define SQLERRMC sqlca->sqlerrmc

SQLCA Structure

The SQL communication area (SQLCA) is the main data structure for error handling in ESQL/C applications. Applications can check the error fields and status indicators of the SQLCA data structure to determine the success or failure of an Embedded SQL statement.

You can use EXEC SQL INCLUDE SQLCA statement to define SQLCA variable, but it is not required in SQL Server ESQL/C. The precompiler automatically includes declaration of extern variable and pointer to SQLCA variable as:

   extern struct tag_sqlca far sql_sqlca;
   extern struct tag_sqlca far *sqlca;

EXEC SQL INCLUDE SQLCA is supported for compatibility with legacy applications that use ESQL/C for other databases.

SQLCA structure is defined in sqlca.h as:

   typedef struct tag_sqlca {
	unsigned char	sqlcaid[8];          // Eyecatcher = 'SQLCA   '
	long                  sqlcabc;             // SQLCA size in bytes = 136
	long                  sqlcode;             // SQL return code
	short                 sqlerrml;            // Length for SQLERRMC
	unsigned char   sqlerrmc[70];      // Error message tokens
	unsigned char   sqlerrp[8];          // Diagnostic information
	long                  sqlerrd[6];	       // Diagnostic information
	unsigned char   sqlwarn[8];         // Warning flags
	unsigned char   sqlext[3];	       // Reserved
	unsigned char   sqlstate[5];         // SQLSTATE
//	unsigned char   sqlext[8];	          // Reserved
} SQLCA, *PSQLCA, far *LPSQLCA;

• sqlcode contains SQL Server error code. It is set to 0 if the last statement completed successfully. Values other than 0 indicate warnings or errors
• sqlwarn flags are set if certain exceptions, such as data truncation, occur
• sqlerrmc contains the error message text
• sqlerrd[0] contains the error number
• sqlerrd[2] array indicates the number of rows affected
• sqlstate contains run-time errors (SQL-92 standard SQLSTATE codes)

Multithreaded Applications

Since SQLCODE and sqlca are global variables, you cannot use ESQL/C statements in concurrent threads even if you use multiple connections. Each EXEC SQL statement will update the single error variable.

Conversion to ODBC: ODBC Error Handling

The ODBC API does not use the global SQLCODE variable and SQLCA structure. Instead, each ODBC API function returns the error status, for example:

   SQLCODE = SQLAllocHandle(...);
   // ...
   SQLCODE = SQLDriverConnect(...);
   // ...
   SQLCODE = SQLPrepare(...);
   // ...
   SQLCODE = SQLExecute(...);

Then you can use SQLGetDiagRec() function to get detailed information about the error - error message, native error code and SQLSTATE.

Conversion of ESQL/C error handling to ODBC mostly depends on whether the application uses SQLCA fields (besides sqlcode) or not

• SQLCODE, sqlca->sqlcode or sql_sqlca.sqlcode References only

If the application contains only references to SQLCODE, sqlca->sqlcode or sql_sqlca.sqlcode variables and does not use other SQLCA fields, you can declare local or global variable, and assign it to the return value of ODBC API functions:

   SQLRETURN SQLCODE;
   // ...
   SQLCODE = SQLExecute();
   //
   if(SQLCODE == 0)
   {
      //...
   }

The global variable can be required if a statement executed in one function but SQLCODE is referenced in another.

• SQLCA Field References

SQLCA fields are automatically filled with error information (error number, error message text, SQLSTATE i.e) in Embedded SQL applications. In ODBC you have to call SQLGetDiagRec() function to get this information.

Moreover, SQLGetDiagRec() function requires handle type and handle variable to be specified as input parameters that means it is not always possible to call SQLGetDiagRec() from any function.

For example, if a connection is failed, you call SQLGetDiagRec() as:

   SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, ...

While if a SQL statement failed, you call it as:

   SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, ...

So the problem is that you can reference SQLCA fields in any function in ESQL/C application while you have to call SQLGetDiagRec() after unsuccessful statement execution in ODBC.