|
|
|
|
|
|
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
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 |
|
|
|
|
|
|