enigma

SQL Server connector (not my job)

    A. First Edition

    First of all, I want to make it very clear that this is not programmed by me. I mean I just copy the code sample

    from MSDN and make a little a wrapping job for my personal usage in future. Therefore I kept all the original

    comment out there. Hope you understand that I do respect intellectual property from Microsoft.

    B.The problem

How to connect SQL server from C/C++? I don't know if this library is using OLE-DB or not. However, it works

and it suit my purpose: connecting to SQL server, sending SQL statement and making the basic displaying of

query. That is it.

C.The idea of program

    I want to clarify once again that I only wrapped a few functions in the example to be a class. I didn't code for anything.

    D.The major functions

    There are few points to mention.

    E.Further improvement

    F.File listing

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

file name: enigma.h

    #include "Rotor.h"

    const int RotorCount=3;
    const int SteckerIndex=8;
    const int ReflectorIndex=7;

    class Enigma
    {
    private:
    	Rotor rotors[RotorCount];
    	Rotor reflector;
    	Rotor stecker;
    	int advances[RotorCount];
    	int shifts[RotorCount];
    	void initialize();
    	void rotate();
    	int operation(int input);
    public:
    	void encode(char* text);
    	void decode(char* text);
    	void restoreSetting();
    	void run(char* text);
    	Enigma();
    };

file name: main.cpp (main)

    #include <stdio.h>
    #include "sqlserver.h"

    int main(int argc, char *argv[])
    {
    	SQLServer S;
    	S.login();
    	S.execSQL("select student_id, sname, gender, status from student;");
    	S.execSQL("select * from professor");

        return 0;

    }

    The result is like following:

    Message No.: 5701, Msg. State: 2, Msg. Severity: 0
    已将数据库上下文改为 'concordia'。
    Login succeed!
    student_id sname gender status
    =========== ========== ====== ======
    1234 nick name 1 1
    1235 nick nick 1 1
    (2 rows effected)
    prof_id pname address office email

    =========== ========== ================================================== ========== ===============
    =====
    123 David Concordia LB-915 David@hotmail.c
    om
    (1 row effected)
    Press any key to continue