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