Most database API's work like this:
- Initialize
- Log into the database
- Loop
- Execute a query
- Get the result set column description
- Loop
- Fetch a row
- Manipulate the fields in the row
- Log out
- Clean up
The SQL Relay connection daemon class works this way too. Writing a connection daemon mainly consists of wrapping database API methods in corresponding connection class methods. More specifically, to write a new connection daemon, you create classes which inherit from the "connection" and "cursor" classes and implement their pure virtual methods.
Below is an example. On the left is pseudocode and on the right, the code for the MySQL connection.
Header File
// Copyright (c) 2000-2001 David Muse
// See the file COPYING for more information
#ifndef MYCONNECTION_H
#define MYCONNECTION_H
#define NUM_CONNECT_STRING_VARS however many connect string vars you have
#include <connection.h>
#include < whatever header files you need >
#include < your header file >
class myconnection;
class mycursor : public cursor {
friend class myconnection;
private:
mycursor(connection *conn);
~mycursor();
... override the cursor class's pure virtual methods ...
int executeQuery(char *query, long length);
char *getErrorMessage(int *liveconnection);
void returnRowCounts();
void returnColumnCount();
void returnColumnInfo();
int noRowsToReturn();
int skipRow();
int fetchRow();
void returnRow();
void cleanUpData();
... you'll need a result set descriptor
possibly some other descriptors ...
... you'll probably need variables for
the number of rows, columns,
affected rows and an error message ...
... and a pointer to the connection class ...
myconnection *myconn;
};
class myconnection : public connection {
friend class mycursor;
public:
myconnection();
~myconnection();
private:
... override the connection class's pure virtual methods ...
int getNumberOfConnectStringVars();
void handleConnectString();
int logIn();
cursor *initCursor();
void deleteCursor(cursor *curs);
void logOut();
int isTransactional();
int ping();
char *identify();
unsigned short *autoCommitOn();
unsigned short *autoCommitOff();
void commit();
void rollback();
... you'll need a connection descriptor here ...
... you'll also need variables for the
connect string arguments ...
};
#endif
|
// Copyright (c) 2000-2001 David Muse
// See the file COPYING for more information
#ifndef MYSQLCONNECTION_H
#define MYSQLCONNECTION_H
#define NUM_CONNECT_STRING_VARS 6
#include <connection.h>
#include <fstream.h>
#include <strstream.h>
#include <mysql.h>
class mysqlconnection;
class mysqlcursor : public cursor {
friend class mysqlconnection;
private:
mysqlcursor(connection *conn);
~mysqlcursor();
int executeQuery(char *query, long length);
char *getErrorMessage(int *liveconnection);
void returnRowCounts();
void returnColumnCount();
void returnColumnInfo();
int noRowsToReturn();
int skipRow();
int fetchRow();
void returnRow();
void cleanUpData();
MYSQL_RES *mysqlresult;
MYSQL_FIELD *mysqlfield;
MYSQL_ROW mysqlrow;
int ncols;
int nrows;
int affectedrows;
strstream *errmesg;
mysqlconnection *mysqlconn;
};
class mysqlconnection : public connection {
friend class mysqlcursor;
public:
mysqlconnection();
private:
int getNumberOfConnectStringVars();
void handleConnectString();
int logIn();
cursor *initCursor();
void deleteCursor(cursor *curs);
void logOut();
int isTransactional();
int ping();
char *identify();
unsigned short *autoCommitOn();
unsigned short *autoCommitOff();
void commit();
void rollback();
MYSQL mysql;
int connected;
char *user;
char *password;
char *db;
char *host;
char *port;
char *socket;
};
#endif
|
.C File
// Copyright (c) 1999-2001 Your Name Here // See the file COPYING for more information #include < your header file > ... you need to include this file too ... #include <datatypes.h> ... you may need to include some other headers ... |
// Copyright (c) 1999-2001 David Muse // See the file COPYING for more information #include <mysqlconnection.h> #include <datatypes.h> #include <stdlib.h> #include <string.h> |
|
myconnection::myconnection() {
connected=0;
... initialize whatever you need to here ...
}
|
mysqlconnection::mysqlconnection() {
connected=0;
}
|
|
myconnection::~myconnection() {
... deallocate whatever you initialized ...
}
|
... the mysqlconnection has no destructor ... |
|
int myconnection::getNumberOfConnectStringVars() {
return NUM_CONNECT_STRING_VARS;
}
|
int mysqlconnection::getNumberOfConnectStringVars() {
return NUM_CONNECT_STRING_VARS;
}
|
|
void myconnection::handleConnectString() {
... set your connect string variables
using the connectStringValue()
method ...
}
|
void mysqlconnection::handleConnectString() {
user=connectStringValue("user");
password=connectStringValue("password");
db=connectStringValue("db");
host=connectStringValue("host");
port=connectStringValue("port");
socket=connectStringValue("socket");
}
|
|
int mysqlconnection::logIn() {
... set some default values for any
missing connect string variables ...
... connect to the database ...
... return 1 for success, 0 for failure ...
}
|
int mysqlconnection::logIn() {
// handle host
char *hostval;
if (host && host[0]) {
hostval=host;
} else {
hostval="";
}
// handle port
int portval;
if (port && port[0]) {
portval=atoi(port);
} else {
portval=0;
}
// handle socket
char *socketval;
if (socket && socket[0]) {
socketval=socket;
} else {
socketval=NULL;
}
// handle db
char *dbval;
if (db && db[0]) {
dbval=db;
} else {
dbval="";
}
// initialize database connection structure
#if MYSQL_VERSION_ID>=32200
if (!mysql_init(&mysql)) {
return 0;
}
#endif
// log in
#if MYSQL_VERSION_ID>=32200
if (!mysql_real_connect(&mysql,hostval,user,password,dbval,
portval,socketval,0)) {
#else
if (!mysql_real_connect(&mysql,hostval,user,password,
portval,socketval,0)) {
#endif
logOut();
return 0;
} else {
#if MYSQL_VERSION_ID<32200
if (!mysql_select_db(&mysql,dbval)) {
logOut();
return 0;
}
#endif
connected=1;
return 1;
}
}
|
|
cursor *myconnection::initCursor() {
return (cursor *)new mycursor((connection *)this);
}
|
cursor *mysqlconnection::initCursor() {
return (cursor *)new mysqlcursor((connection *)this);
}
|
|
void myconnection::deleteCursor(cursor *curs) {
delete (mycursor *)curs;
}
|
void mysqlconnection::deleteCursor(cursor *curs) {
delete (mysqlcursor *)curs;
}
|
|
void myconnection::logOut() {
... disconnect from the database ...
}
|
void mysqlconnection::logOut() {
connected=0;
mysql_close(&mysql);
}
|
|
void myconnection::logOut() {
... disconnect from the database ...
}
|
void mysqlconnection::logOut() {
connected=0;
mysql_close(&mysql);
}
|
|
char *myconnection::identify() {
... return the name of your database ...
}
|
char *mysqlconnection::identify() {
return "mysql";
}
|
|
unsigned short myconnection::autoCommmitOn() {
... set autocommit on ...
... return 1 for success and 0 for failure ...
}
|
unsigned short mysqlconnection::autoCommitOn() {
// do nothing
return 1;
}
|
|
unsigned short myconnection::autoCommmitOff() {
... set autocommit off ...
... return 1 for success and 0 for failure ...
}
|
unsigned short mysqlconnection::autoCommitOff() {
// do nothing
return 1;
}
|
|
int myconnection::isTransactional() {
... you only need this method if your
database is not transactional ...
... return a 0 ...
}
|
int mysqlconnection::isTransactional() {
return 0;
}
|
|
int myconnection::ping() {
... test to see if the database connection
is still good, return 1 if it is, and
0 if it's not ...
}
|
int mysqlconnection::ping() {
#if MYSQL_VERSION_ID>=32200
if (!mysql_ping(&mysql)) {
return 1;
}
return 0;
#else
return 1;
#endif
}
|
|
void myconnection::commit() {
... if your database has a special commit
function, call it here ...
... if your database is non-transactional,
make this function do nothing ...
... if your database is transactional but
doesn't have a special commit function,
don't even implement this method at all,
let the base class provide it ...
}
|
void mysqlconnection::commit() {
// do nothing
}
|
|
void myconnection::rollback() {
... if your database has a special rollback
function, call it here ...
... if your database is non-transactional,
make this function do nothing ...
... if your database is transactional but
doesn't have a special rollback function,
don't even implement this method at all,
let the base class provide it ...
}
|
void mysqlconnection::rollback() {
// do nothing
}
|
|
mycursor::mycursor(connection *conn) : cursor(conn) {
mysqlconn=(mysqlconnection *)conn;
errmesg=NULL;
... initialize whatever you need to here ...
}
|
mysqlcursor::mysqlcursor(connection *conn) : cursor(conn) {
mysqlconn=(mysqlconnection *)conn;
errmesg=NULL;
}
|
|
mycursor::~mycursor() {
... deallocate whatever you initialized ...
}
|
mysqlcursor::~mysqlcursor() {
if (errmesg) {
delete errmesg;
}
}
|
|
int mycursor::executeQuery(char *query, long length) {
... set row and column counts to 0 ...
Note: Some databases have a 3 step query
execution process involving bind variables.
See the information at the end of this
document for how to handle those
databases.
... if your database doesn't support real
binds, then create a strstream buffer,
call fakeInputBinds() and pass the
result into your execute query command
(unless the result is NULL which means
there were no variables to bind in which
case you must execute the original query) ...
... get the result set ...
... get information about the result set
such as the row and column counts
and the number of affected rows
(affected rows are the number of rows
affected by an insert, update or delete) ...
... return 1 for success, 0 for failure ...
}
|
int mysqlcursor::executeQuery(char *query, long length) {
// initialize counts
ncols=0;
nrows=0;
// fake binds
strstream *newquery=fakeInputBinds(query);
// execute the query
if (newquery) {
if (mysql_real_query(&mysqlconn->mysql,
newquery->str(),
newquery->rdbuf()->pcount()-1)) {
delete newquery;
return 0;
}
delete newquery;
} else {
if (mysql_real_query(&mysqlconn->mysql,query,length)) {
return 0;
}
}
// store the result set
if ((mysqlresult=mysql_store_result(&mysqlconn->mysql))==
(MYSQL_RES *)NULL) {
// if there was an error then return failure, otherwise
// the query must have been some DML or DDL
char *err=mysql_error(&mysqlconn->mysql);
if (err && err[0]) {
return 0;
} else {
return 1;
}
}
// get the column count
ncols=mysql_num_fields(mysqlresult);
// get the row count
nrows=mysql_num_rows(mysqlresult);
// get the row count
affectedrows=mysql_affected_rows(&mysqlconn->mysql);
return 1;
}
|
|
char *mycursor::getErrorMessage(int *liveconnection) {
... call the database API to get an error message ...
... if that error is a down database, set the
"liveconnection" parameter to 1 ...
... return the error message string ...
}
|
char *mysqlcursor::getErrorMessage(int *liveconnection) {
// store the error message because mysql_ping will set it blank
if (errmesg) {
delete errmesg;
}
errmesg=new strstream();
*errmesg << mysql_error(&mysqlconn->mysql) << ends;
#if MYSQL_VERSION_ID>=32200
// only return an error if the database is up
if (mysqlconn->connected && !mysql_ping(&mysqlconn->mysql)) {
*liveconnection=1;
return errmesg->str();
} else {
*liveconnection=0;
return "";
}
#else
return errmesg->str();
#endif
}
|
|
void mycursor::returnColumnCount() {
... use the sendColumnCount() method to
return the number of columns in the result set...
}
|
void mysqlcursor::returnColumnCount() {
conn->sendColumnCount(ncols);
}
|
|
void mycursor::returnRowCounts() {
... use the sendRowCounts() method to return the
number of rows and/or affected rows in the result set...
... if your database api doesn't supply one or both
of these values, return -1 for whichever it doesn't
supply ...
}
|
void mysqlcursor::returnRowCounts() {
// send row counts
conn->sendRowCounts((long)nrows,(long)affectedrows);
}
|
|
void mycursor::returnColumnInfo() {
... First, send back the number of rows and
affected rows using the sendRowCounts()
method. For databases that don't return
these values, return -1 instead. ...
... if the query isn't a select, return immediately ...
... Position yourself at the first column.
This is important because returnColumnInfo()
may be called more than 1 time per result
set (if the user suspends the result set
for example) and you don't want to be
off the end of the column list from the
previous call. Some database API's provide
methods for accessing the columns by index
or require you to store the definitions
in your own array. For those databases,
this positioning is not necessary. ...
... Run through the columns of the result set,
for each, use the sendColumnDefinition()
method to return the name, type and size
of each. The column type should be one
of the types in the datatypes.h file, so
you'll have to establish a mapping between
one of them and the database API's data
types. If your database has a type that's
not in that file, add it and submit a
patch! ...
}
|
void mysqlcursor::returnColumnInfo() {
// for DML or DDL queries, return no column info
if (!mysqlresult) {
return;
}
// a useful variable
int type;
// position ourselves at the first field
mysql_field_seek(mysqlresult,0);
// for each column...
for (int i=0; i<ncols; i++) {
// fetch the field
mysqlfield=mysql_fetch_field(mysqlresult);
// append column type to the header
if (mysqlfield->type==FIELD_TYPE_STRING) {
type=STRING_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_VAR_STRING) {
type=VARSTRING_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_DECIMAL) {
type=DECIMAL_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_TINY) {
type=TINY_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_SHORT) {
type=SHORT_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_LONG) {
type=LONG_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_FLOAT) {
type=FLOAT_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_DOUBLE) {
type=DOUBLE_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_LONGLONG) {
type=LONGLONG_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_INT24) {
type=INT24_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_TIMESTAMP) {
type=TIMESTAMP_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_DATE) {
type=DATE_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_TIME) {
type=TIME_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_DATETIME) {
type=DATETIME_DATATYPE;
#if MYSQL_VERSION_ID>=32200
} else if (mysqlfield->type==FIELD_TYPE_YEAR) {
type=YEAR_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_NEWDATE) {
type=NEWDATE_DATATYPE;
#endif
} else if (mysqlfield->type==FIELD_TYPE_NULL) {
type=NULL_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_ENUM) {
type=ENUM_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_SET) {
type=SET_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_TINY_BLOB) {
type=TINY_BLOB_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_MEDIUM_BLOB) {
type=MEDIUM_BLOB_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_LONG_BLOB) {
type=LONG_BLOB_DATATYPE;
} else if (mysqlfield->type==FIELD_TYPE_BLOB) {
type=BLOB_DATATYPE;
} else {
type=UNKNOWN_DATATYPE;
}
// send column definition
conn->sendColumnDefinition(mysqlfield->name,
strlen(mysqlfield->name),
type,(int)mysqlfield->length);
}
}
|
|
int mycursor::noRowsToReturn() {
... test to see if you're at the end of the
result set, if so, return 1, otherwise
return 0 ...
... if there is no good way to know this
for your database, just return 0 ...
}
|
int mysqlcursor::noRowsToReturn() {
// for DML or DDL queries, return no data
if (!mysqlresult) {
return 1;
}
return 0;
}
|
|
int mycursor::skipRow() {
... If your API can skip rows in the result set without
actually fetching them, then do that here. If not
just call fetchRow() ...
}
|
int mysqlcursor::skipRow() {
return fetchRow();
}
|
|
int mycursor::fetchRow() {
... fetch a row, return a 1 for success and 0 for failure ...
}
|
int mysqlcursor::fetchRow() {
return ((mysqlrow=mysql_fetch_row(mysqlresult))!=NULL);
}
|
|
void mycursor::returnRow() {
... run through each column of the result
set and return it using the sendField()
method ...
}
|
void mysqlcursor::returnRow() {
for (int col=0; col<ncols; col++) {
if (mysqlrow[col]) {
conn->sendField(mysqlrow[col],strlen(mysqlrow[col]));
} else {
conn->sendNullField();
}
}
}
|
|
void mycursor::cleanUpData() {
... if you allocated any memory between
getting the query and returning the
result set, deallocate it here ...
}
|
void mysqlcursor::cleanUpData() {
if (mysqlresult!=(MYSQL_RES *)NULL) {
mysql_free_result(mysqlresult);
}
}
|
If your database has a lightweight facility for switching from one user to
another without just logging out and logging back in as a different user,
you should consider overriding the changeUser() method.
You may elect to override some more of the cursor class's virtual methods as well.
When a session is ended (either on purpose or accident) the connection daemon sends either a commit or rollback (depending on how it's configured) to the database if any DML or DDL queries were executed. The following methods are used to implement this functionality.
int queryIsNotSelect()int queryIsCommitOrRollback()
The queryIsNotSelect() method returns 0 if the query is a SELECT and 1 if it is not. Similarly, the queryIsCommitOrRollback() method returns 1 if the query is either a COMMIT or ROLLBACK and 0 if it is not. The connection class implements these methods by parsing the text of the query. Many database API's have their own means of identifying the query type. If yours does, then you should use it to override these methods.
Some databases have a 3 part process for executing queries or procedural code. In Part 1, the database figures out what steps it will take to run the query or code. In Part 2, local variables or values are "bound" to variables in those steps. In Part 3, the steps are executed. If your database has a process like this, you should override these methods.
int prepareQuery()int inputBindValue()int outputBindValue()int bindVariablePrefix()int nullBindValue()int nonNullBindValue()int bindValueIsNull()
The prepareQuery() method should execute Part 1 functions. The inputBindValue() and outputBindValue() methods should handle functions for Part 2. And the executeQuery() method should handle Part 3.
If your database uses a character other than a colon as a prefix for it's named bind variables, then you should override the bindVariablePrefix() method to return that character. Similarly, if your database uses a value other than 0 in it's bind functions to indicate that a value is NULL and a value other than -1 to indicate that a value is non-NULL then you should override the nullBindValue() and nonNullBindValue() methods to return the appropriate values. You should also override the bindValueIsNull() method to return 0 if the value passed into it corresponds to your non-NULL indicator and 1 if the value passed into it corresponds to your NULL indicator.