io Programmo 32/IOPROG_32.ISO/SOFT/SqlEval7/devtools/samples/DBLIB/c/sqlexamp/sqlexamp.c

C/C++ Source or Header | 1997-10-22 | 16.1 KB | 423 lines

/* 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.
int DetermineRowSize(DBPROCESS *,int);
RETCODE PrintHeaders(DBPROCESS *);
RETCODE PrintRow(DBPROCESS *);

// Prototypes for DB-Library error and message handling.
int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*);

/*
** 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(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

    *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;
        }
    }

/*
** 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
    (
    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;

                Col.SizeOfStruct = sizeof(DBCOL);

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

/*
** 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
    (
    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
    (
    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 ============================*/
/*****************************************************************************/