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.
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.
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.
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