Refresher

We said in Chapter 1, "Facilis Descensus Averni," that we assume you're already familiar with (among other things) programming with an SQL API such as ODBC, but here's a quick summary of ODBC basics to refresh your memory. All of our examples will work equally well with ODBC 3.x and standard SQL/CLI except where noted.

Primarily, ODBC is a specification of the format for calls and parameters that an application program can include, as well as a specification of the activities that the called routinesi.e., the ODBC drivermust perform when the call happens. Microsoft provides installation utilities and a driver manager for the Windows platform; however, one can dispense with the fripperies"ODBC" programs run on Unix too.

ODBC programs can be pure client-side, when they're front ends. ODBC programs can also be pure server-side, when they're CGI. Or ODBC programs can be between the extremesthe routines themselves are in a middling ODBC driver that can be middleware or might be completely inside the DBMS. (In this chapter, we'll use the term "DBMS" to mean everything that the function includes, no matter how indirectly.)

Every SQL call (at least when it's in a C/ODBC program) has these characteristics:

• It begins with the letters SQL.

• It refers to a handle of a resource. A resource is an env (environment), a dbc (database connection), a stmt (statement container), or a desc (descriptor for SQL statement parameters). These resources have a hierarchy, shown in Figure 12-1.

  Figure 12-1. Hierarchy of ODBC resources

  

Listing 12-1 shows the ODBC "hello-world" program. This simplest of all ODBC programs builds, and then tears down the minimum scaffolding that a program will always have: an env, a dbc, and a stmt. An ODBC program that passes any input parameters to SQL statements, or retrieves any result sets from SQL, will have descs too.

Listing 12-1 The simplest ODBC program

 #include "sqlcli.h"    /* contains headers and constants */ void main () { HENV henv;             /* fill this in with SQLAllocEnv */ HDBC hdbc;             /* fill this in with SQLAllocConnect */ HSTMT hstmt;           /* fill this in with SQLAllocStmt */ SQLAllocEnv(           /* Allocate environment handle; env */    &henv); SQLAllocConnect(       /* Allocate connect handle; dbc */    henv,    &hdbc); SQLConnect(            /* Connect */    hdbc,    "OCELOT",SQL_NTS,    "OCELOT",SQL_NTS,    "",SQL_NTS); SQLAllocStmt(          /* Allocate statement handle; stmt */    hdbc,    &hstmt); SQLExecDirect(         /* Do some SQL! */    hstmt,    "INSERT INTO Table1 VALUES (1)",SQL_NTS); SQLFreeStmt(           /* Free the stmt */    hstmt,    SQL_DROP); SQLDisconnect(         /* Disconnect; reverse Connect */    hdbc); SQLFreeConnect(        /* Free the dbc; reverse AllocConnect */    hdbc); SQLFreeEnv(            /* Free the env; reverse AllocEnv */    henv); } 

The important groups of ODBC calls are:

• The buildup/ teardown functions

  Usually you'll see SQLAllocEnv , SQLAllocConnect , SQLConnect , and SQLAllocStmt at program start, then various meat calls will occur, then you'll see SQLFreeStmt , SQLDisconnect , SQLFreeConnect , and SQLFreeEnv at program end. Lately there's been a trend to use busier function names :

   SQLAllocHandle(SQL_HANDLE_ENV,...) SQLAllocHandle(SQL_HANDLE_DBC,...) SQLAllocHandle(SQL_HANDLE_STMT,...) SQLAllocHandle(SQL_HANDLE_DESC,...) 

  etc. In nonstandard ODBC, you'll almost always find that, instead of SQLConnect , the function call used is SQLDriverConnect , which allows for passing more information than merely the data source name , the username, and the password.

• The diagnostic functions

  Anything can go wrong, so there's always been an SQLError function to return whatever diagnostic information the DBMS might have accumulated . To provide a much deeper level of detail, the latest versions of ODBC and SQL/CLI have two new functions: SQLGetDiagRec and SQLGetDiagField .

• The desc functions

  Only two desc functions are really necessary: SQLGetDescField and SQLSetDescField . All the others SQLGetDescRec , SQLSetDescRec , SQLBindCol , SQLBindParameter , SQLColAttribute , SQLDescribeCol , SQLDescribeParam , SQLNumParams , SQLNumResultCols , and SQLRowCount are just more or less complex combinations of the simple act of "getting a descriptor field" or "setting a descriptor field." For example, if you want to tell the DBMS about the C variables that are to be associated with the result of a SELECT statement, you'd either use SQLSetDescField multiple times or SQLBindCol once.

• The cursor functions

  A result set is what a SELECT returns. The cursor is the invisible marker that indicates a position within the result set. You can subdivide the result set into smaller sets (row sets), change the cursor position, or just fetch one or more values at the cursor position into the C variables that got described with a desc function. The principal cursor functions are SQLFetch , SQLFetchScroll , SQLSetCursorName , SQLGetCursorName , SQLSetPos (ODBC only), and SQLExtendedFetch (ODBC only, and obsolete). There's also SQLGetData and SQLPutData , which merely set values after a fetch is done.

• The catalog functions

  The catalog functions are mere containers for SELECTs that query the database catalog. Because they're containers for SELECTs, these functions return result sets. For example, SQLTables returns a list of tables (both base tables and views) in the database. Other catalog functions are SQLColumns , SQLColumnPrivileges , SQLSpecialColumns , SQL Procedures , and SQLPrimaryKeys .

• The attribute functions

  The attribute functions either set or get flags and parameters that affect the workings of an entire resource (env or dbc or stmt). Logically enough, there are three pairs of functions: ( SQLSetEnvAttr , SQLGet EnvAttr ), ( SQLSetConnectAttr , SQLGetConnectAttr ), and ( SQL Set StmtAttr , SQLGetStmtAttr ). Illogically, there is one more function that gets various unchangeable settings and constants: SQLGetInfo .

• The transaction end function

   SQLEndTran. 

Tracing MS Query

As part of our refresher, let's look at how a typical ODBC application communicates with the DBMS. We chose Microsoft Query for an example because it comes preinstalled with many Windows versions. We'll do our peeking with two simple tools: the MS-ODBC Administrator and the MS Notepad editor. If you have an ODBC data source and an MS Windows NT computer, you can follow along with this short exercise.

1. Get your Windows NT machine ready for using ODBC.

2. Turn ODBC tracing on: Click Settings/Control Panel (see Figure 12-2).

   Figure 12-2. Click Settings/Control panel

   

3. Start Microsoft's ODBC Data Source Administrator: Click Data Sources (ODBC) (see Figure 12-3).

   Figure 12-3. Click Data Sources (ODBC).

   

4. Click the Tracing tab (see Figure 12-4).

   Figure 12-4. Click the Tracing tab

   

5. Click the Start Tracing Now button; it will change to Stop Tracing Now. Click OK (see Figure 12-5). Illogically, as long as this button is set to "Stop Tracing Now," the tracing is on. If you're following along, come back here later to turn tracing back off because it slows down ODBC applications tremendously.

   Figure 12-5. Click the Start Tracing Now button

   

6. Now, start Microsoft Query: Click Start/Run and enter the following:

    C:\WINNT\MSAPPS\MSQUERY\MSQRY32.EXE 

   (The path may be different on your machine; see Figure 12-6.)

   Figure 12-6. Start Microsoft Query

   

7. Click New Query (see Figure 12-7).

   Figure 12-7. Click New Query

   

8. On the Select Data Source dialog box, double-click your choice of data source name (see Figure 12-8).

   Figure 12-8. Double-click your data source name

   

9. On the Add Tables dialog box, double-click your choice of table, then click Close (see Figure 12-9).

   Figure 12-9. Double-click your table, then click Close

   

10. On the Query1 dialog box, double-click your choice of column (see Figure 12-10).

    Figure 12-10. Double-click your column on the Query1 dialog box

    

11. Click File/Exit (see Figure 12-11).

    Figure 12-11. Click File/Exit

    

12. Now, start Notepad. Click Start/Programs/Accessories/Notepad (see Figure 12-12).

    Figure 12-12. Start Notepad

    

13. Click File/Open, then enter the following:

     C:\SQL.LOG 

    (See Figure 12-13.)

    Figure 12-13. Open SQL.LOG; click File/Open

    

Figure 12-14 shows SQL.LOG, the trace file that was on the Tracing dialog box set up at the beginning of this example.

Figure 12-14. A view of SQL.LOG

The SQL.LOG file contains an entry for every SQL call that MSQRY32.EXE performed, along with the parameter values and return codes. There's enough on display to allow you to analyze what MS Query did when you used it for your display. For example:

1. MS Query established the connection:

    SQLAllocEnv        /* allocate env */ SQLAllocConnect    /* allocate dbc */ SQLDriverConnect   /* connect */ ... SQLAllocStmt       /* allocate stmt */ 

2. MS Query got some information about the connection:

    SQLGetInfo <SQL_DATA_SOURCE_NAME>            /* get data source name */ SQLGetInfo <SQL_ACTIVE_STATEMENTS>            /* how many stmts can be active? */ SQLGetInfo <SQL_DATA_SOURCE_READ_ONLY>            /* is data source read-only? */ SQLGetInfo <SQL_DRIVER_NAME>            /* what is the ODBC driver name? */ SQLGetInfo <SQL_SEARCH_PATTERN_ESCAPE>            /* are % and _ used in SQLTables? */ SQLGetInfo <SQL_CORRELATION_NAME>            /* what is a correlation called? */ SQLGetInfo <SQL_NON_NULLABLE_COLUMNS>            /* can columns be NOT NULL? */ SQLGetInfo <SQL_CATALOG_NAME_SEPARATOR>            /* does "." follow the catalog qualifier? */ SQLGetInfo <SQL_FILE_USAGE>            /* how many tiers are there? */ SQLGetInfo <SQL_SEARCH_PATTERN_ESCAPE>            /* what's the LIKE escape character? */ SQLGetInfo <SQL_CATALOG_TERM> >            /* what is a catalog called? */ SQLGetInfo <SQL_DATABASE_NAME>            /* what is the database name? */ . . . SQLGetInfo <SQL_MAX_SCHEMA_NAME_LEN>            /* how long are schema names? */ 

   A series of SQLGetInfo calls is normal at this stage: DBMSs can have different syntaxes and DBMS vendors can use different vocabularies.

3. MS Query got some information about your table and its columns:

    SQLTables("%",0,0)           /* what catalogs exist? */ SQLTables("OCE","","","'TABLE','Vie'")           /* what tables exist? */ SQLGetTypeInfo <SQL_ALL_TYPES>                /* what data types are supported? */ SQLColumns("OCE","OCELOT","DEPT")            /* what columns exist? */ SQLSpecialColumns(SQL_BEST_ROWID,"EMPS",SQL_SCOPE_CURROW)                   /* what column is good for the search? */ 

   It's normal for an ODBC application to use catalog functions rather than to query the database catalog for this information. The dependence on SQLSpecialColumns is especially noteworthy. Without this call, MS Query would not know which of the table's columns to use for unique searches.

4. MS Query selected all columns in all rows:

    SQLExecDirect(    "SELECT EMPS.EMPNUM, ... FROM "OCELOT.OCELOT".EMPS EMPS") 

   MS Query requires quirky SQL syntax here; luckily all DBMSs accept double quotes in strange places. This is the only time in the session that an SQL statement actually appears.

5. MS Query got some information about the result set that was returned by the SELECT statement:

    SQLNumResultCols() /* how many columns in select list? */ loop   (for each column in select list) {   SQLDescribeCol()          /* what is column name etc. */   SQLColAttribute(          /* how wide is column? */      SQL_COLUMN_DISPLAY_SIZE)   } 

6. MS Query fetched each row, transferring data to bound columns:

    loop   (for each row till "NO DATA" return or screen is full) {   loop      (for each column in select list) {      SQLBindCol()      }   SQLFetch   } 

   Why is the SQLBindCol loop inside the main loop? That's something worth looking at later.

7. Lastly, MS Query performed a teardown by reversing each call that was performed when it connected:

    SQLFreeStmt        /* reverse SQLAllocStmt */ SQLDisconnect      /* reverse SQLDriverConnect */ SQLFreeConnect     /* reverse SQLAllocConnect */ SQLFreeEnv         /* reverse SQLAllocEnv */ 

Many developers have performed this same exercise with the intent of copying Microsoft's basic methods . The MS Query imitations all go through the same phases: a buildup with resource allocations , a series of SQLGetInfo and catalog-function calls, a point where an actual SQL statement goes down the line, some result processing, and teardown.