Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


OSQLSAMP Program Using Dynamic SQL


To be able to compile the ODBC example program, an ODBC SDK is needed. This package should include the necessary header files, such as sqlext.h and sql.h, or corresponding.

The osql.c file contains a collection of routines that define a simple but convenient API for dynamic SQL, using ODBC according to international standards. These routines can be called from other C programs, in this case osqlsamp.c.

The osqlsamp.c (same as dsqlsamp.c - see DSQLSAMP Program Using Dynamic SQL) file contains a program that calls the routines in osql.c. The osqlsamp program allows the user to enter a SQL statement that will be executed directly, similar to the BSQL program, see the Mimer SQL User's Manual.

Building the OSQLSAMP program

UNIX: The following example assumes MIMER is installed in the directory /opt/mimer821A and illustrates how the OSQLSAMP program is compiled and built by using the distributed example makefile:
 $ mkdir osql # Do everything in a sub directory
 $ cd osql
 $ cp /opt/mimer821A/examples/ex_makefile ./makefile
 $ cp /opt/mimer821A/examples/osql.c .
 $ cp /opt/mimer821A/examples/dsql.h .
 $ cp /opt/mimer821A/examples/dsqlsamp.c osqlsamp.c
 
In the following example the ODBC Driver Manager is presumed to be installed under /usr/local.
Update the following macros in the makefile:
The INCLUDE symbol should be updated to include the search path for the ODBC Driver Manager include files.
The MYPROG symbol should be set to the main program name, in this case "osqlsamp". The MYFUNCS symbol should hold the name of the object file(s) for the subroutines, in this case "osql.o" (avoid trailing spaces).
The ODBCLIB symbol should be updated to include the search path for the ODBC Driver Manager library and the name of that library.
 INCLUDE = -I$(MIMINC) -I. $(EXTEND) $(EXTEND_SHL)-I/usr/local/include
 MYPROG = osqlsamp
 MYFUNCS = osql.o
 ODBCLIB = -L/usr/local/lib -liodbc $(CLIB) $(LIBC)
 
Note: The names of ODBC Driver Manager libraries and include files may vary between different products. These names must match the makefile and the C-source header include statements, respectively.

UNIX: (continued)
Then make the osqlsamp program:
 $ export MIMER_HOME=/opt/mimer821A
 $ make
 
To be able to execute the osqlsamp program the .odbc.ini file must be installed in the user HOME directory and updated according to the following example (for details, see documentation for the ODBC Driver Manager in use). The .odbc.ini file should include lines as in the following example in order to access a Mimer SQL database:
 [ODBC Data Sources]
 hotel=MIMER database
 
 [hotel]
 Driver = /usr/lib/libmimer.so
 
 [default]
 Driver = /usr/lib/libmimer.so
 Database = hotel
 
Note: If the ODBC Data Source name is not equal to the Mimer SQL database name the "Database" parameter should be used in the .odbc.ini file, as shown for the "default" ODBC Data Source above.

VMS: The OSQLSAMP program is not currently distributed on VMS.

Win: On Windows platforms, the examples files are installed in the dev directory which is located below the installation directory. The dev directory contains a makefile called makefile.mak, which will build all the example programs at the same time. The examples have been tested using Microsoft Visual C.
These instructions assume that Microsoft Visual C is used and that the make command is being executed from the dev directory, appropriate adjustments must be made if this is not the case.
$ nmake -f makefile.mak

Source Code for the OSQLSAMP Program in C

The main program source is the same as for DSQLSAMP and is in dsqlsamp.ec, the following code, in osql.c replaces the code in dsql.c.

 /********************************************************************/
 /*  osql.c - ODBC sql driver                                        */
 /********************************************************************/
 /*                                                                  */
 /* This package is an ODBC SQL sample with the following functions: */
 /*                                                                  */
 /*      DsqlConnect    - connect to data source                     */
 /*      DsqlDisconnect - disconnect from the data source            */
 /*      DsqlExecute    - execute an SQL statement                   */
 /*      DsqlColNames   - get the column names of a result table     */
 /*      DsqlFetch      - fetch the next row from a result table     */
 /*      DsqlClose      - close a cursor                             */
 /*      DsqlError      - get error message text                     */
 /*                                                                  */
 /********************************************************************/
 /*                                                                  */
 /*  Created by Upright Database Technology AB.                      */
 /*                                                                  */
 /* You have a free right to use, modify, reproduce, and distribute  */
 /* the sample files (and/or any modified version) in any way you    */
 /* find useful, provided that you agree that Upright Database       */
 /* Technology AB has no warranty obligations or liability for any   */
 /* sample files which are modified.                                 */
 /*                                                                  */
 /********************************************************************/
 /********************************************************************/
 /*                                                                  */
 /*  For Windows, include the following:                             */
 /*                                                                  */
 /*   #include <windows.h>                                           */
 /*                                                                  */
 /********************************************************************/
 #include <stdlib.h>
 #include <stdio.h>
 #include <string.h>
 #include <ctype.h>
 #include "sqlext.h"
 #include "dsql.h"       /* function prototype definitions */
 
 #define SQL_BADARG  -3
 #define SQL_HANDLES -4
 
 #define MAXHSTMTS   32
 
 #ifndef TRUE
 #define TRUE        1
 #define FALSE       0
 #endif
 /*
  *  Local functions
  */
 static int    check     (RETCODE);
 static int    sethstmt  (HSTMT);
 static HSTMT  gethstmt  (int);
 static void   drophstmt (int);
 /*
  *  Shared data areas
  */
 static HENV   henv;
 static HDBC   hdbc;
 static HSTMT  hstmt;
 static HSTMT  hstmts [MAXHSTMTS];
 static SDWORD errcode;
 static char   state  [6];
 static char   sqlmsg [SQL_MAX_MESSAGE_LENGTH];
 static char   buffer [8192];
 static char*  format = "[DSQL Driver]%s";
 
 /********************************************************************/
 /*                                                                  */
 /*  Connect to the data source.                                     */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      1 : successful connection                                   */
 /*      0 : wrong username or password                              */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /********************************************************************/
 int DsqlConnect()
 /********************************************************************/
 {
     RETCODE rc;
     UCHAR   strout[256];
     SWORD   length;
     SQLHWND hwnd;
 
 /********************************************************************/
 /*                                                                  */
 /*  For Unix, include the following:                                */
 /*                                                                  */
 /*   hwnd = 0;                                                      */
 /*                                                                  */
 /********************************************************************/
 /********************************************************************/
 /*                                                                  */
 /*  For Windows, include the following:                             */
 /*                                                                  */
 /*   hwnd = GetDesktopWindow();                                     */
 /*                                                                  */
 /********************************************************************/
     /*
      *  Allocate environment.
      */
     if (henv == NULL)
     {
         SQLAllocEnv(&henv);
         if (henv == NULL)
         {
             strcpy(sqlmsg,"cannot allocate ODBC environment\n");
             return -1;
         }
     }
     /*
      *  Allocate a connection handle.
      */
     if (check(SQLAllocConnect(henv,&hdbc)) < 0) return -1;
     /*
      *  Connect to data source.
      */
 /********************************************************************/
 /*                                                                    */
 /*  For Unix, include the following:                                  */
 /*                                                                    */
 /*   rc = check(SQLDriverConnect(hdbc,hwnd,(UCHAR*)"",0,              */
 /*             strout,255,&length,SQL_DRIVER_PROMPT));                */
 /*   if (rc != 0)                                                     */
 /*   {                                                                */
 /*       if (check(SQLAllocConnect(henv,&hdbc)) < 0) return -1;       */
 /*                                                                    */
 /*       rc = check(SQLDriverConnect(hdbc,0,(UCHAR*)"DSN=default",    */
 /*                  SQL_NTS,strout,255,&length,SQL_DRIVER_COMPLETE)); */
 /*       if (rc != 0)                                                 */
 /*       {                                                            */
 /*           if (rc == -2) return 0;                                  */
 /*           if (*sqlmsg == '\0')                                     */
 /*                strcpy(sqlmsg,"SQLDriverConnect failed...\n");      */
 /*           return -1;                                               */
 /*        }                                                           */
 /*    }                                                               */
 /*                                                                    */
 /********************************************************************/
 /*  For Windows, include the following:                               */
 /*                                                                    */
 /*   rc = check(SQLDriverConnect(hdbc,hwnd,(UCHAR*)"",0,              */
 /*            strout,255,&length,SQL_DRIVER_PROMPT));                 */
 /*        if (rc != 0)                                                */
 /*        {                                                           */
 /*            if (rc == -2) return 0;                                 */
 /*            return -1;                                              */
 /*        }                                                           */
 /*                                                                    */
 /********************************************************************/
     return 1;
 }
 
 /********************************************************************/
 /*                                                                  */
 /*  Disconnect from the data source.                                */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /********************************************************************/
 int DsqlDisconnect()
 /********************************************************************/
 {
     /*
      *  Disconnect.
      */
     if (check(SQLDisconnect(hdbc)) < 0) return -1;
     /*
      *  Free connection handle.
      */
     if (check(SQLFreeConnect(hdbc)) < 0) return -1;
     /*
      *  Free environment.
      */
     SQLFreeEnv(henv);
     hdbc = SQL_NULL_HDBC;
     henv = SQL_NULL_HENV;
     return 0;
 }
 /********************************************************************/
 /*                                                                  */
 /*  Execute an SQL statement.                                       */
 /*                                                                  */
 /*  Parameter:                                                      */
 /*                                                                  */
 /*      char* sqlstmt : SQL statement to be executed                */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*     +n : a select statement successfully opened,                 */
 /*          returning a cursor identifier                           */
 /*      0 : a non-select statement executed successfully            */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /*  Note:                                                           */
 /*                                                                  */
 /*   It is assumed that the SQL statement does not contain any      */
 /*   parameter markers. Unique statement, descriptor, and cursor    */
 /*   names are used, which make it possible to have several cursors */
 /*   open at the same time.                                         */
 /*                                                                  */
 /********************************************************************/
 int DsqlExecute(char* sqlstmt)
 /********************************************************************/
 {
     SWORD count;
     /*
      *  Check argument.
      */
     if (sqlstmt == NULL) return check(SQL_BADARG);
     /*
      *  Allocate a statement handle.
      */
     if (check(SQLAllocStmt(hdbc,&hstmt)) < 0) return -1;
     /*
      *  Execute SQL statement.
      */
      if (check(SQLExecDirect(hstmt,(UCHAR*)sqlstmt,SQL_NTS)) < 0) return -1;
     /*
       *  Get number of columns to see if this was a select statement or not.
      */
     if (check(SQLNumResultCols(hstmt,&count)) < 0) return -1;
     if (count == 0)
     {
         /*
          *  Non-select statement. Free statement handle.
          */
         if (check(SQLFreeStmt(hstmt,SQL_DROP)) < 0) return -1;
         hstmt = SQL_NULL_HSTMT;
         return 0;
     }
     else
     {
         /*
          *  Select statement. Return index for handle.
          */
         return sethstmt(hstmt);
     }
 }
 
 /********************************************************************/
 /*                                                                  */
 /*  Get the column names of a result table.                         */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      int   c      : cursor identifier returned from DsqlExecute  */
 /*      char* record : buffer for the result string                 */
 /*      int   len    : length of the buffer (must be >= 32)         */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*     +n : OK - number of columns                                  */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /*  Note:                                                           */
 /*                                                                  */
 /*  The column names are returned in a tab ('\t') separated string  */
 /*  ending with a newline ('\n'). If the string is too large, it is */
 /*  truncated.                                                      */
 /*                                                                  */
 /********************************************************************/
 int DsqlColNames(int c,char* record,int len)
 / *******************************************************************/
 {
     char   name [19];
     SWORD  count;
     SWORD  n,cb,ct,cs,cn;
     UDWORD cp;
     int    length;
     /*
      *  Check arguments.
      */
     if (record == NULL || len < 32) return check(SQL_BADARG);
     /*
      *  Get statement handle.
      */
     hstmt = gethstmt(c);
     /*
      *  Get number of columns.
      */
     if (check(SQLNumResultCols(hstmt,&count)) < 0) return -1;
     for (n = 1; n <= count; n++)
     {
         /*
          *  Get one column name.
          */
         if (check(SQLDescribeCol(hstmt,(UWORD)n,
                                  (UCHAR*)name,(SWORD)sizeof(name),
                                  &cb,&ct,&cp,&cs,&cn)) < 0) return -1;
         /*
          *  Trim spaces and append a tab.
          */
         length = (int)cb;
         if (length == 0) *name = ' ', length = 1;
         while (length > 1 && name[length - 1] == ' ') length--;
         if (length > len - 2) length = len - 2;
         memcpy(record,name,length);
         record += length;
         len    -= length;
         if (len == 2) break;
         if (n < count)
         {
             *record++ = '\t';
             len--;
         }
     }
     *record++ = '\n';
     *record   = '\0';
     return count;
 }
 
 /********************************************************************/
 /*                                                                  */
 /*  Fetch the next row from a result table.                         */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      int   c      : cursor identifier returned from DsqlExecute  */
 /*      char* record : buffer for the result string                 */
 /*      int   len    : length of the buffer (must be >= 32)         */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*     +n : OK - number of columns                                  */
 /*      0 : No more data (End of File)                              */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /*  Note:                                                           */
 /*                                                                  */
 /* The column values are returned in a tab ('\t') separated string  */
 /* ending with a newline ('\n'). It is assumed that the data does   */
 /* not contain tab, newline or null characters. A column value of   */
 /* NULL will be return as '?'. If the string is too large, it is    */
 /* truncated.                                                       */
 /*                                                                  */
 /********************************************************************/
 int DsqlFetch(int c,char* record,int len)
 /********************************************************************/
 {
     RETCODE rc;
     SWORD   n;
     SWORD   count;
     SDWORD  cb;
     int     length;
     /*
      *  Check arguments.
      */
     if (record == NULL || len < 32) return check(SQL_BADARG);
     /*
      *  Get statement handle.
      */
     hstmt = gethstmt(c);
     /*
      *  Fetch next row from result table.
      */
     rc = check(SQLFetch(hstmt));
     if (rc == 0)
     {
         /*
          *  Get number of columns.
          */
         if (check(SQLNumResultCols(hstmt,&count)) < 0) return -1;
         for (n = 1; n <= count; n++)
         {
             /*
              *  Get one column value.
              */
             if (check(SQLGetData(hstmt,n,SQL_C_CHAR,
                                  (PTR)buffer,(SDWORD)len,&cb)) < 0) return -1;
             /*
              *  Check NULL indicator, trim spaces and append a tab.
              */
             if ((length = (int)cb) == SQL_NULL_DATA)
             {
                 *record++ = '?';
                  len--;
             }
             else
             {
                 if (length == 0) *buffer = ' ', length = 1;
                 while (length > 1 && buffer[length - 1] == ' ') length--;
                 if (length > len - 2) length = len - 2;
                 memcpy(record,buffer,length);
                 record += length;
                 len    -= length;
             }
             if (len == 2) break;
             if (n < count)
             {
                 *record++ = '\t';
                 len--;
             }
         }
         *record++ = '\n';
         *record   = '\0';
         return count;
     }
     else if (rc > 0)
     {
         /*
          *  No more data.
          */
         return 0;
     }
     return -1;
 }
 
 /********************************************************************/
 /*                                                                  */
 /*  Close a cursor.                                                 */
 /*                                                                  */
 /*  Parameter:                                                      */
 /*                                                                  */
 /*      int c : cursor identifier returned from DsqlExecute         */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error (call DsqlError for message text)                 */
 /*                                                                  */
 /********************************************************************/
 int DsqlClose(int c)
 /********************************************************************/
 {
     drophstmt(c);
     return check(SQLFreeStmt(hstmt,SQL_DROP));
 }
 /********************************************************************/
 /*                                                                  */
 /*  Get error message text.                                         */
 /*                                                                  */
 /*  Parameters:                                                     */
 /*                                                                  */
 /*      char* message : buffer for the result string                */
 /*      int   len     : length of the buffer (must be >= 32)        */
 /*                                                                  */
 /*  Return codes:                                                   */
 /*                                                                  */
 /*      0 : OK                                                      */
 /*     -1 : error (cannot get message text)                         */
 /*                                                                  */
 /*  Note:                                                           */
 /*                                                                  */
 /*   The message text is returned in a newline ('\n') separated     */
 /*   string. If the string is too large, it is truncated.           */
 /*                                                                  */
 /********************************************************************/
 int DsqlError(char* message,int len)
 /********************************************************************/
 {
     if (message == NULL || len < 32) return -1;
     strncpy(message,sqlmsg,len);
     message[len] = '\0';
     return 0;
 }
 
 /********************************************************************/
 static int check(RETCODE rc)
 /********************************************************************/
 {
     char* msg;
     SWORD max;
     SWORD len;
     int   login;
 
     if (rc == SQL_ERROR || rc == SQL_SUCCESS_WITH_INFO)
     {
         msg   = sqlmsg;
         max   = (SWORD)sizeof(sqlmsg);
         login = FALSE;
         while (SQLError(henv,hdbc,hstmt,
                         (UCHAR*)state,&errcode,
                         (UCHAR*)msg,max,&len) == 0)
         {
             msg += len; max -= len;
             len  = (SWORD)sprintf(msg,"\nSQLSTATE:%s\n\n",state);
             msg += len; max -= len;
             if (strcmp(state,"28000") == 0) login = TRUE;
         }
         if (login) return -2;
         if (hstmt && rc == SQL_ERROR)
         {
             SQLFreeStmt(hstmt,SQL_DROP);
             hstmt = SQL_NULL_HSTMT;
         }
         else if (hstmt == NULL && hdbc && rc == SQL_ERROR)
         {
             SQLFreeConnect(hdbc);
             hdbc = SQL_NULL_HDBC;
         }
     }
     switch (rc)
     {
     case SQL_SUCCESS_WITH_INFO:
     case SQL_SUCCESS:
         return 0;
 
     case SQL_NO_DATA_FOUND:
         return 1;
 
     case SQL_STILL_EXECUTING:
         sprintf(sqlmsg,format,"Still executing");
         return -1;
 
     case SQL_NEED_DATA:
         sprintf(sqlmsg,format,"Need data");
         return -1;
 
     case SQL_HANDLES:
         sprintf(sqlmsg,format,"Too many handles");
         return -1;
 
     case SQL_BADARG:
         sprintf(sqlmsg,format,"Bad argument");
         return -1;
 
     case SQL_INVALID_HANDLE:
         sprintf(sqlmsg,format,"Invalid handle");
         return -1;
 
     case SQL_ERROR:
     default:
         return -1;
     }
 }
 
 /********************************************************************/
 static int sethstmt(HSTMT hstmt)
 /********************************************************************/
 {
     int id;
 
     for (id = 0; id < MAXHSTMTS; id++)
     {
         if (hstmts[id] == 0)
         {
             hstmts[id] = hstmt;
             return id + 1;
         }
     }
     return check(SQL_HANDLES);
 }
 
 /********************************************************************/
 static HSTMT gethstmt(int id)
 /********************************************************************/
 {
     if (id > 0 && id < MAXHSTMTS + 1)
     {
         return hstmts[id - 1];
     }
     return NULL;
 }
 
 /********************************************************************/
 static void drophstmt(int id)
 /********************************************************************/
 {
     if (id > 0 && id < MAXHSTMTS + 1)
     {
         hstmts[id - 1] = NULL;
     }
 }


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX