|
|
|
|
|
|
DSQLSAMP Program Using Dynamic SQL
The DSQLSAMP program demonstrates how a C program can be constructed using dynamic SQL syntax according to international standards.
The source code file for the DSQLSAMP program is dsql.ec which contains a collection of routines that define a simple but convenient API for dynamic SQL, which can be called from other C programs.
The dsqlsamp.c file contains a program that calls the routines defined in dsql.ec. The DSQLSAMP program allows the user to enter an SQL statement that will be executed directly, in a manner similar to the BSQL program, see the Mimer SQL User's Manual for details about BSQL.
Building the DSQLSAMP Program
Source Code for the DSQLSAMP Program in C
/********************************************************************/ /* dsqlsamp.c - sample program using dynamic SQL driver */ /********************************************************************/ /* */ /* Simple command line oriented SQL executor */ /* */ /********************************************************************/ /* */ /* 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. */ /* */ /********************************************************************/ #include <stdlib.h> #include <stdio.h> #include <string.h> #include "dsql.h" /* function prototype definitions */ static char sqlbuf [4096]; static void error (); static void getid (char* prompt,char* text); static char* savres (char* result,char* sqlbuf); static int* savlen (int* collen,int colcount,char* sqlbuf); static void show (char* result,int rowcount,int* collen,int colcount); /********************************************************************/ int main() /********************************************************************/ { char database [129]; char username [129]; char password [19]; char* p; char* result; int* collen; int rc; int cursor; int colcount; int rowcount; printf("\n *** Welcome to Mimer Dynamic SQL Sample Program ***\n\n"); /* * Connect to database */ for (;;) { getid("Database:",database); getid("Username:",username); getid("Password:",password); /* NOTE: password is not hidden!*/ printf(" \n"); if ((rc = DsqlConnect(database,username,password)) > 0) break; if (rc < 0) error(), exit(0); printf("You have entered wrong username or password.\n"); printf("Please, try again.\n\n"); } printf(" - End an SQL statement with ';'\n"); printf(" - Exit the program by giving an empty command\n\n"); /* * SQL command loop */ for (;;) { /* * Read an SQL statement ending with ';' */ printf("SQL>"); /* Display command prompter */ for (p = sqlbuf;; p++, *p++ = ' ') { *p = '\0'; fgets(p,255,stdin); p += strlen(p); while ((*p == '\0' || *p == ' ') && p > sqlbuf) p--; if (*sqlbuf == '\0' || *p == ';') break; printf(" >"); } printf(" \n"); if (*p == '\0') /* Empty command, ask if exit */ { printf("Quit? (y/n) "); fgets(p,255,stdin); printf(" \n"); if (*p == 'y' || *p == 'Y') break; continue; } *p = '\0'; /* Eliminate the ending ';' */ /* * Execute SQL-statement */ cursor = DsqlExecute(sqlbuf); if (cursor > 0) { /* * It was a select statement, get column names */ if ((colcount = DsqlColNames(cursor,sqlbuf,sizeof(sqlbuf))) > 0) { result = savres(NULL,sqlbuf); collen = savlen(NULL,colcount,sqlbuf); /* * Fetch the resulting rows into memory and * calculate the minimum possible width of the columns */ rowcount = 0; while ((rc = DsqlFetch(cursor,sqlbuf,sizeof(sqlbuf))) > 0) { result = savres(result,sqlbuf); collen = savlen(collen,colcount,sqlbuf); rowcount++; } if (rc < 0 || DsqlClose(cursor) < 0) error(); /* * Show the result set nicely formatted */ show(result,rowcount,collen,colcount); } else { error(); } } else if (cursor == 0) { printf("*** SQL statement executed successfully! ***\n\n"); } else { error(); } } /* * Disconnect and exit */ if (DsqlDisconnect() < 0) error(); printf(" *** Exit from Mimer Dynamic SQL Sample Program ***\n\n"); exit(0); return 0; } /********************************************************************/ /* */ /* Print SQL error message */ /* */ /********************************************************************/ static void error() /********************************************************************/ { DsqlError(sqlbuf,sizeof(sqlbuf)); printf("%s\n",sqlbuf); } /********************************************************************/ /* */ /* Prompt for connect identifiers */ /* */ /********************************************************************/ static void getid(char* prompt,char* text) / *******************************************************************/ { printf("%s",prompt); sqlbuf[0] = '\0'; fgets(sqlbuf,255,stdin); sqlbuf[128] = '\0'; strcpy(text,sqlbuf); } /********************************************************************/ /* */ /* Save a result row in memory */ /* */ /********************************************************************/ static char* savres(char* result,char* sqlbuf) /********************************************************************/ { static int reslen; int buflen = strlen(sqlbuf) + 1; if (result == NULL) { result = malloc(buflen + buflen - 1); memcpy(result,sqlbuf,buflen); reslen = buflen - 1; } else if((result = realloc(result,reslen + buflen)) == NULL) { printf("** Fatal: not enough memory, exiting... **\n\n"); exit(0); } memcpy(&result[reslen],sqlbuf,buflen); reslen += buflen - 1; return result; } /********************************************************************/ /* */ /* Keep track of the minimum possible width of the columns */ /* */ /********************************************************************/ static int* savlen(int* collen,int colcount,char* sqlbuf) /********************************************************************/ { char* p = strtok(sqlbuf,"\t\n"); int i,n; if (collen == NULL) collen = calloc(sizeof(int),colcount); for (i = 0; i < colcount; i++) { n = p ? strlen(p) : 0; if (collen[i] < n) collen[i] = n; p = strtok(NULL,"\t\n"); } return collen; } /********************************************************************/ /* */ /* Print the formatted result set and release memory */ /* */ /********************************************************************/ static void show(char* result, int rowcount, int* collen, int colcount) /********************************************************************/ { char* p = strtok(result,"\t\n"); int i,n,pos; printf("*** %d row%s selected ***\n\n", rowcount, rowcount == 1 ? "" : "s"); for (n = 0; n < rowcount + 2; n++) { pos = 0; memset(sqlbuf,' ',sizeof(sqlbuf)); for (i = 0; i < colcount; i++) { if (p) memcpy(&sqlbuf[pos],p,strlen(p)); if (n == 1) memset(&sqlbuf[pos],'=',collen[i]); p = strtok(NULL,"\t\n"); pos += collen[i] + 1; } sqlbuf[pos] = '\0'; printf("%s\n",sqlbuf); } printf("\n"); free(result); free(collen); } /********************************************************************/ /* dsql.h */ /********************************************************************/ #ifndef DSQL_H #define DSQL_H int DsqlConnect (char* database,char* userid,char* passwd); int DsqlDisconnect (void); int DsqlExecute (char* sql); int DsqlColNames (int stmt,char* buffer,int len); int DsqlFetch (int stmt,char* buffer,int len); int DsqlClose (int stmt); int DsqlError (char* message,int len); #endif /********************************************************************/ /* dsql.ec - dynamic SQL driver */ /********************************************************************/ /* */ /* This package is a DSQL sample with the following functions: */ /* */ /* DsqlConnect - connect to the database */ /* DsqlDisconnect - disconnect from the database */ /* 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. */ /* */ /********************************************************************/ #include <stdlib.h> #include <stdio.h> #include <string.h> #include <ctype.h> #include "dsql.h" /* function prototype definitions */ #define TRUE 1 #define FALSE 0 /* * VARCHAR data type definition */ #define SQL_VARCHAR 12 /* * SQLSTATE return code definitions */ #define SQL_SUCCESS "00000" #define SQL_INSUFFICIENT_ITEM_DESCRIPTOR_AREAS "01005" #define SQL_NO_DATA "02000" #define SQL_CONNECTION_REJECTED "28000" /* * Shared data areas */ exec sql begin declare section; static char statement [129]; /* Extended statement name */ static char cursor [129]; /* Extended cursor name */ static char descriptor [129]; /* SQL descriptor name */ static char sqlstate [6]; /* SQLSTATE */ static char buffer [8192]; /* Data buffer */ exec sql end declare section; static int seqno = 0; /*Sequence number for generating unique names */ /********************************************************************/ /* */ /* Connect to the database. */ /* Parameters: */ /* */ /* Return codes: */ /* */ /* 1 : successful connection */ /* 0 : wrong username or password */ /* -1 : error (call DsqlError for message text) */ /********************************************************************/ int DsqlConnect() /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql begin declare section; char database [129] = ""; char username [129] = ""; char password [19] = ""; exec sql end declare section; char* p; getid("Database:",database); getid("Username:",username); getid("Password:",password); /* NOTE: password is not hidden! */ /* * Connect to database. */ for (p = username; *p; p++) *p = (char)toupper((int)*p); exec sql connect to :database user :username using :password; return 1; exception: if (strcmp(sqlstate, SQL_CONNECTION_REJECTED) == 0) { return 0; } return -1; } /********************************************************************/ /* */ /* Disconnect from the database. */ /* */ /* Return codes: */ /* */ /* 0 : OK */ /* -1 : error (call DsqlError for message text) */ /* */ /********************************************************************/ int DsqlDisconnect() /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql disconnect; return 0; exception: return -1; } /********************************************************************/ /* */ /* 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) /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql begin declare section; int count; int n; int type; int length; exec sql end declare section; /* * Check argument. */ if (sqlstmt == NULL) return -1; /* * Make unique statement and descriptor names. */ seqno++; sprintf(statement, "%d",seqno); sprintf(descriptor,"%d",seqno); /* * Copy SQL statement to buffer. */ strncpy(buffer,sqlstmt,sizeof(buffer)); buffer[sizeof(buffer) - 1] = '\0'; /* * Prepare SQL statement. */ exec sql prepare :statement from :buffer; /* * Allocate descriptor. */ exec sql allocate descriptor :descriptor; /* * Describe output. */ exec sql describe output :statement using sql descriptor :descriptor; if (strcmp(sqlstate, SQL_INSUFFICIENT_ITEM_DESCRIPTOR_AREAS) == 0) { /* * The descriptor area was insufficient. * Allocate a new one with appropriate size. */ exec sql get descriptor :descriptor :count = count; exec sql deallocate descriptor :descriptor; exec sql allocate descriptor :descriptor with max :count; exec sql describe output :statement using sql descriptor :descriptor; } /* * Get descriptor count to see if this was a select statement or not. */ exec sql get descriptor :descriptor :count = count; if (count == 0) { /* * Non-select statement. Execute. */ exec sql execute :statement; /* * Deallocate statement and descriptor. */ exec sql deallocate prepare :statement; exec sql deallocate descriptor :descriptor; /* * Return successful completion of non-select statement. */ return 0; } else { /* * Select statement. Set all columns to VARCHAR(512). * Mimer SQL automatic type conversion will * handle numeric data. */ type = SQL_VARCHAR; length = 512; for (n = 1; n <= count; n++) { exec sql set descriptor :descriptor value :n type = :type, length = :length; } /* * Make a unique cursor name. */ sprintf(cursor,"%d",seqno); /* * Allocate and open cursor. */ exec sql allocate :cursor cursor for :statement; exec sql open :cursor; /* * Return cursor identifier. */ return seqno; } exception: return -1; } /********************************************************************/ /* */ /* 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) /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql begin declare section; int count; int n; char name [129]; exec sql end declare section; int length; /* * Check arguments. */ if (record == NULL || len < 32) return -1; /* * Build descriptor name. */ sprintf(descriptor,"%d",c); /* * Get number of columns. */ exec sql get descriptor :descriptor :count = count; for (n = 1; n <= count; n++) { /* * Get one column name. */ exec sql get descriptor :descriptor value :n :name = name; /* * Trim spaces and append a tab. */ length = strlen(name); 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; exception: return -1; } /********************************************************************/ /* */ /* 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) /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql begin declare section; int count; int length; int isnull; int n; exec sql end declare section; /* * Check arguments. */ if (record == NULL || len < 32) return -1; /* * Build cursor and descriptor names. */ sprintf(cursor, "%d",c); sprintf(descriptor,"%d",c); /* * Fetch next row from result table. */ exec sql fetch next from :cursor into sql descriptor :descriptor; if (strcmp(sqlstate,SQL_SUCCESS) == 0) { /* * Get number of columns. */ exec sql get descriptor :descriptor :count = count; for (n = 1; n <= count; n++) { /* * Get one column value. */ exec sql get descriptor :descriptor value :n :buffer = data, :length = returned_length, :isnull = indicator; /* * Check NULL indicator, trim spaces, append tab. */ if (isnull == -1) { *record++ = '?'; len--; } else { 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 (strcmp(sqlstate,SQL_NO_DATA) == 0) { /* * No more data. */ return 0; } exception: 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) /********************************************************************/ { exec sql whenever sqlerror goto exception; /* * Build cursor, statement and descriptor names. */ sprintf(cursor, "%d",c); sprintf(statement, "%d",c); sprintf(descriptor,"%d",c); /* * Close cursor. */ exec sql close :cursor; /* * Deallocate statement and descriptor. */ exec sql deallocate prepare :statement; exec sql deallocate descriptor :descriptor; return 0; exception: return -1; } /********************************************************************/ /* */ /* 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) /********************************************************************/ { exec sql whenever sqlerror goto exception; exec sql begin declare section; int count; int length; char state [6]; /* returned_sqlstate may not be stored sqlstate */ int n; exec sql end declare section; char* p; /* * Check arguments. */ if (message == NULL || len < 32) return -1; /* * Get number of errors. */ exec sql get diagnostics :count = number; for (n = 1; n <= count; n++) { /* * Get diagnostics of an error. */ exec sql get diagnostics exception :n :buffer = message_text, :length = message_length, :state = returned_sqlstate; /* * Prepare message area. */ if (length > len - 18) length = len - 18; memcpy(message,buffer,length); p = strchr(message,':'); if (p) *p = '\n'; if (length > 64) { p = strchr(&message[64],' '); if (p) *p = '\n'; } message += length; len -= length; *message++ = '\n'; /* * Add SQLSTATE code. */ strcpy(message,"SQLSTATE:"); message += 9; strcpy(message,state); message += 5; *message++ = '\n'; *message++ = '\n'; len -= 17; if (len < 32) break; } *message = '\0'; return 0; exception: strcpy(message,"cannot get diagnostics\n\n"); return -1; }
 Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se |
|
|
|
|
|
|