enigma

1. SQLServer.h

2. SQLServer.cpp

3. main.cpp (main)

file name: SQLServer.h

#include <windows.h>
#include "windef.h"
#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

const int MaxCMDBuffer=150;
const int MaxServerName=30;
const int MaxPassword=30;
const int MaxUserName=30;

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

class SQLServer
{
private:
	LOGINREC*   plogin;      // login rec pointer
    DBPROCESS*  dbproc;     // SQL Server connection structure pointer
	char        password[MaxPassword];
	char		user[MaxUserName];
    char        cmd[MaxCMDBuffer];   // command buffer
    char        server[MaxServerName]; // server name buffer
    int         x;      // command line counter
    STATUS      retc;       // return code
    char* sqlversion; // pointer for version string
	void setServer(const char* serverName="nick");
	void setPassword(const char* pword="202409");
	void setUser(const char* userName="sa");
public:
	void serverSetup(const char* userName, const char* pword, const char* serverName);
	bool login();
	bool execSQL(const char* sql);
	bool showResult();
	SQLServer();
};

file name: SQLServer.cpp

#include "sqlserver.h"

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
/* 
** 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.
**
*/
// This sample uses mixed mode security, other than Windows NT Authentication, 
// to establish connections. To use Windows NT Authentication, please use 
// DBSETLSECURE to set the secure connection flag.

/*
** 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.
*/
// 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*);

SQLServer::SQLServer()
{
	setServer();
	setUser();
	setPassword();
	cmd[0]='\0';
}



bool SQLServer::login()
{
	dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
    dberrhandle((DBERRHANDLE_PROC)err_handler);

	if ((plogin=dblogin())==NULL)
	{
		printf("Login failed\n");
		return false;
	}
	DBSETLVERSION(plogin, DBVER60);
	if (DBSETLHOST(plogin, server)==FAIL)
	{
		printf("Login failed\n");
		return false;
	}

	if (DBSETLUSER(plogin, user)==FAIL)
	{
		printf("Login failed\n");
		return false;
	}
	if (DBSETLPWD(plogin, password)==FAIL)
	{
		printf("Login failed\n");
		return false;
	}


	if((dbproc=dbopen(plogin, server))==NULL)
    {
		// no one answered, so couldn't connect or error occurred
		printf("Login failed\n");
		return false;
    }
	printf("Login succeed!\n");
	return true;
}

void SQLServer::setUser(const char* userName)
{
	strcpy(user, userName);
}

void SQLServer::setPassword(const char* pword)
{
	if (pword==NULL)
	{
		password[0]='\0';
	}
	else
	{
		strcpy(password, pword);
	}
}

void SQLServer::setServer(const char* serverName)
{
	if (serverName==NULL)
	{
		server[0]='\0';
	}
	else
	{
		strcpy(server, serverName);
	}
}

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

bool SQLServer::showResult()
{
	while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
	{
		if (retc == FAIL)           // if error get out of loop
		{
			return false;
		}

		// 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.
				return false;
			}
			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())
	}
	return true;
}

void SQLServer::serverSetup(const char* userName, const char* pword, const char* serverName)
{
	setServer(serverName);
	setUser(userName);
	setPassword(pword);
}


bool SQLServer::execSQL(const char* sql)
{
	strcpy(cmd, sql);           // go not detected, so put space
	dbcmd(dbproc,cmd);          // between each command and set in
	if(dbsqlexec(dbproc) == FAIL)   // execute command
    {
		// problem occurred, just try another command
		printf("Error in executing command batch!\n");
		return false;
	}
	showResult();
	return true;
}

/*
** 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 =(char*) malloc(size+1);    // get name buffer
    hptr = header = (char*)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 =(char*) 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 == 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,(LPCBYTE)data,dbdatlen(dbproc,x),
                SQLCHAR,(LPCBYTE)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
    }


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