SQLEXAMP.C

/* SQLEXAMP.C - Copyright (c) 1990 - 1995 by Craig Henry.  This program may 
** be freely distributed and copied, without charge. However, any attempt to
** charge for this program will be considered a copyright infringement and
** will be prosecuted to the fullest extent.
**
** This program provides a simple example of logging onto a SQL Server,
** sending down commands, retrieving metadata, and result rows. Formatting
** and printing those results on the console.
**
*/

#if defined(DBNTWIN32)
#include <windows.h>
#endif

#include "stdio.h"// include standard header
#include "sqlfront.h"// include dblib macro/manifest defines
#include "sqldb.h"// include dblib datatype, prottypes, etc
#include "string.h"// include for string functions
#include "malloc.h"// include for malloc and free


// prototypes for internal functions
extern int DetermineRowSize(DBPROCESS *,int);
extern RETCODE PrintHeaders(DBPROCESS *);
extern RETCODE PrintRow(DBPROCESS *);

/*
** DetermineRowSize(DBPROCESS *,int)
**
** This function returns either the size of all columns in the row, converted
** to character data (SQLCHAR) with one space between each column, or
** if col is non-zero, the length of the input column converted to string.
** It is used to build the header strings, and each row of data, and is
** called to allocate the memory needed for each row, and determine how
** much of that space is to be used for each column
*/
int DetermineRowSize(dbproc,col)
DBPROCESS *dbproc;// The SQL Server connection structure
int col;// column size to get, 0 for all
{
int x,cols;// counters
int length=0;// total length of column(row).
int namelength;// length of name of column
int prlength;// printable length
char *name;// pointer to column name
if(!col) // get number of columns
cols = dbnumcols(dbproc);
// count from 1 to numcols if col is 0, else x will = col only
for(x=((col) ? col : 1);x<=((col) ? col : cols);x++)
{
switch(dbcoltype(dbproc,x))// get column type, determine SQLCHAR
{// converted length
case SQLNUMERIC:
case SQLDECIMAL:
{
DBCOL Col;
dbcolinfo(dbproc,CI_REGULAR,x,0,&Col);
prlength = Col.Precision + 2;
}
break;

case SQLBIT:// The PR... values are found in the
prlength = PRBIT;// SQLDB.H header file.
break;
case SQLINT1:
prlength = PRINT1;
break;
case SQLINT2:
prlength = PRINT2;
break;
case SQLINT4:
prlength = PRINT4;
break;
case SQLFLT8:
prlength = PRFLT8;
break;
case SQLDATETIME:
prlength = PRDATETIME;
break;
case SQLMONEY:
prlength = PRMONEY;
break;
case SQLVARBINARY :// VARBINARY IMAGE, and BINARY
case SQLBINARY:// convert to 2 times length
case SQLIMAGE:
prlength = dbcollen(dbproc,x)*2;
break;
default :
prlength = dbcollen(dbproc,x); // other types are maximum of
break; // actual column length
}
name = (char *)dbcolname(dbproc,x); // names may be longer than
namelength = (name) ? strlen(name) : 0; // column so use name len if
if(prlength<namelength) // longer of two.
length+=namelength+1; // add one for space between
else // columns
length+=prlength+1;
}
return length;// return the length of the
// field
}
/*
** RETCODE PrintHeaders(DBPROCESS *)
**
** This function builds the string that contains the names of each column,
** and a string containing '=' as a separator line. It does this by finding
** the print size of each column, allocating a buffer to hold all column names
** plus one space between each column name, then copying that name into the
** appropriate location into the buffer. Finally the two lines are
** printed.
*/
RETCODE PrintHeaders(dbproc)
DBPROCESS *dbproc;// The SQL Server connection structure pointer
{
int x,cols,size;// counters
char *header;// pointer for separator buffer
char *colnames;// pointer for column name buffer
char *colname;// scratch pointers
char *ptr,*hptr;
size = DetermineRowSize(dbproc,0);// get size of buffers
ptr = colnames = malloc(size+1); // get name buffer
hptr = header = malloc(size+1); // get separator buf
memset (header,' ',size);// set buffers to all spaces
memset (colnames,' ',size);
cols = dbnumcols(dbproc);// get number of columns
for(x=1;x<=cols;x++)// loop on all columns
{
size = DetermineRowSize(dbproc,x); // get size of this column
colname = (char *)dbcolname(dbproc,x);// get column name
strncpy(ptr,colname,strlen(colname));// copy name
memset(hptr,'=',size-1);// set ='s in separator line
hptr+=size;// move to next position
ptr+=size;// move to next position
}
*ptr = '\0';// null term both strings
*hptr = '\0';
printf("%s\n",colnames);// print both strings
printf("%s\n",header);
free(colnames);// free both buffers
free(header);
return SUCCEED;// done
}
/*
** RETCODE PrintRow(DBPROCESS *)
**
** This function prints out one row. dbnextrow() must be called to fetch the
** row to print. This routine could be used to print the current row as
** many times as wanted, as the current row data is always available until
** dbnextrow() is called to fetch the next row. This routine works like
** PrintHeaders above, but each column's data is obtained instead of a row
** name, and converted to a string. It is then set into the buffer.
*/
RETCODE PrintRow(dbproc)
DBPROCESS *dbproc;// SQL Server connection structure
{
int x,cols,size,datasize,colwidth,coltype;// counters
char *datavals;// data buffer pointer
char *data;// column data pointer
char *ptr;// scratch pointer
colwidth = DetermineRowSize(dbproc,0);
ptr = datavals = malloc(colwidth+1); // get buffer
cols = dbnumcols(dbproc);// get number of columns
for(x=1;x<=cols;x++)// do all columns
{
coltype = dbcoltype(dbproc,x);
size = DetermineRowSize(dbproc,x); // determine size of this column
memset(ptr,' ',size);// set it to spaces
data = (char *)dbdata(dbproc,x);// get pointer to column's data
if(data == (BYTE *)NULL)// if NULL, use "NULL"
{
strncpy(ptr,"NULL",4);// set NULL into buffer
ptr += size;// point past this column in output buf
}
else// else have data, so convert to char
{
datasize = dbconvert(dbproc,coltype,data,dbdatlen(dbproc,x),
SQLCHAR,ptr,(DBINT)size-1);
if (datasize < size && (coltype == SQLNUMERIC || coltype == SQLDECIMAL || coltype == SQLINT1 ||
coltype == SQLINT2 || coltype == SQLINT4 || coltype == SQLFLT8 || coltype == SQLFLT4))
{
memmove(ptr+size-1-datasize,ptr,datasize);
memset(ptr,' ',size-1-datasize);
}
ptr+=size;
}
}
*ptr = '\0';// null term string
printf("%s\n",datavals);// print row
free(datavals);// free buffer
return SUCCEED;// done
}

/*
**
** The below main is a mini isql interpreter and as such is only
** used for demonstration purposes. Command line args include the Server
** name as arg 1, User ID as arg 2, assumes the password is null.
** This routine requests opens the connection after obtaining the login record
** and filling it with the necessary info. Once the connection is established
** it accpets command input, set's it into the dbproc. On "go" it executes
** the command against the server, processes each results set and then returns
** to accepting command input. If "quit" or "exit" is input the program
** is terminated. This interpreter will not process COMPUTE statements,
** and will not work with commands that return text/image data.
*/
int main(argc, argv)
int argc;
char *argv[];
{
LOGINREC *login;// login rec pointer
DBPROCESS *dbproc;// SQL Server connection structure pointer

char cmd[150];// command buffer
char server[30];// server name buffer
int x=1;// command line counter
STATUS retc;// return code
const char * sqlversion;// pointer for version string
int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*);

*server = '\0';// null start these two buffers
*cmd = '\0';

if(argc == 1)// if no server name, request it
{
printf("Enter Server Name: ");
gets(server);
}
else// else it was input as first arg
strcpy(server,argv[1]);
if(argc < 2)// if no login id, request it
{
printf("Enter User Name: ");
gets(cmd);
}
else// otherwise it was input as second arg.
strcpy(cmd,argv[2]);

// check to see if communications layer was loaded (DOS ONLY)
if((sqlversion = dbinit()) == (BYTE *)NULL)
{
// DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther
printf("Error in DB-Library initialization, exiting\n");
return 1;
}
else
printf("DB-Library version: %s\n",sqlversion); // print dblib version

dbsettime(30);// set timeouts to 30 seconds

// set error/msg handlers for this program
dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
dberrhandle((DBERRHANDLE_PROC)err_handler);

login = dblogin();// get a login rec

DBSETLUSER(login,cmd);// set login id
DBSETLHOST(login,"SQL EXAMPLE");// set host name for sp_who
DBSETLVERSION(login, DBVER60);

// open connection to requested server. Pass null server name for local
// connection, if name not entered.
if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL)
{
// no one answered, so couldn't connect or error occurred
printf("Login failed\n");
return 1;
}
else
{
// loop on command input until quit or exit appears in first 4 bytes.
while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0))
{
printf("%d> ",x++);// print command prompt
gets(cmd);// get command
if(strnicmp(cmd,"go",2) == 0)// is it go
{
if(dbsqlexec(dbproc) == FAIL)// execute command
{
// problem occurred, just try another command
printf("Error in executing command batch!\n");
x = 1;
continue;
}
// command executed correctly, get results information
while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
{
if (retc == FAIL)// if error get out of loop
break;
// headers and data could be printed here with only two
// function calls, dbprhead(dbproc), and dbprrow(dbproc),
// which would output the headers, and all the data to
// standard output. However, that isn't very informative
// toward understanding how this data is obtained and
// processed, so I do it the hard way, one column at a time.

PrintHeaders(dbproc);// print header data

// loop on each row, until all read
while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS)
{
if(retc == FAIL)// if fail, then clear
{// connection completely, just
dbcancel(dbproc);// in case.
break;
}
else
PrintRow(dbproc);// else print the current row
}
if(DBCOUNT(dbproc) == 1L)// print the row count
printf("(1 row effected)\n");
else
printf("(%ld rows effected)\n",DBCOUNT(dbproc));

} // end while(dbresults())

x = 1;// reset command line counter
}
else
{
strcat(cmd," ");// go not detected, so put space
dbcmd(dbproc,cmd);// between each command and set in
}// dbproc.

} // end while()

dbclose(dbproc);// quit/exit input, close connection

// print adios and exit.
printf("SQL Server Connection to %s closed, bye bye.\n",server);
return 0;
}
}
/*
** msg_handler(char *buffer, long len);
**
** This routine is a local isql message handler call back function that
** is invoked whenever the SQL Server is sending a message back to
** the program.
**
*/
int msg_handler(dbproc, Msg, State, Severity, Message)
DBPROCESS *dbproc;// SQL Server connection structure
DBINT Msg;// SQL Server message number
int State;// State of the message
int Severity;// Severity of the message
char *Message;// The message itself (read only)
{
printf("Message No.: %ld, Msg. State: %d, Msg. Severity: %d\n",
Msg,State,Severity);
if(Message != NULL)
printf("%s\n",Message);
return (0);
}

/*
** err_handler(char *buffer, long len);
**
** This routine is a local error handler called by dblib if an internal
** error occurs, also to notify when a server message has been sent, which is
** obtained through the above message handler.
**
*/
int err_handler(dbproc, Severity,dberr, oserr, errstr, oserrstr)
DBPROCESS *dbproc;// SQL Server connection structure
int Severity;// Severity of Dblib error
int dberr;// dblib error, all dblib errors start at 10000
int oserr;// OS error from, C runtime
char *errstr;// dblib error string
char *oserrstr;// OS error string (if any)
{

printf("DB-LIBRARY Error - Severity: %d, Error No: %d, OS Error No: %d\n",
Severity, dberr, oserr);
if(errstr != NULL)
printf("%s\n",errstr);
if(oserrstr != NULL)
printf("%s\n",oserrstr);
return INT_CANCEL;
}

/*****************************************************************************/
/*======================== E N D - O F - F I L E ============================*/
/*****************************************************************************/