Database access from C and C++ are less standardized and often a bit more cumbersome than for many newer languages, but C and C++ are still important languages for database applications.
This article will show some of the possibilities for writing database applications in C and C++.
The examples will be shown in simple - possible a bit oldfashioned - C and C++. I will expect examples to work with C 89 and C++ 98 compliant compilers.
The examples will not be explained in great detail. The reader is expected to have a basic knowledge about C/C++, SQL and relational databases. With such a background the examples should be easy to read. Both SQL and code are very simple.
The examples may seem a bit long, but database code in C/C++ often requires many lines of code and I want to show complete examples not just code snippets.
All the examples below will use the following database table:
CREATE TABLE T1 (
F1 INTEGER NOT NULL,
F2 VARCHAR(50),
PRIMARY KEY(F1)
);
I will also show some build commands. But please note that build commands are very dependent on platform, software versions, setup etc., so you may need to modify to suit your environment.
Please note that I do not code much C/C++ today, so the code examples may contain errors or non optimal code.
CLI (Call Level Interface) is a standard API for database access. CLI was designed 1988-1992 and was added to the ISO/ANSI standard SQL-92. Today CLI is managed by X/Open.
CLI is implemented in ODBC and IBM DB2.
ODBC (Open Database Connectivity) is a standard API and driver architecture for database access. The ODBC API is a super-set of CLI API - just adding some functions for driver managemanet and datasource management. First version of ODBC came out in 1992 and several new versions has been released since then.
ODBC is implemented in all recent Windows versions and available via third party software on various *nix platforms. Most traditional database vendors provide an ODBC driver.
Architecture for non-ODBC CLI:
Architecture for ODBC:
The ODBC architecture may look like a lot of overhead, but note that extra function calls are insignificant compared to network IO and disk IO involved.
| Names |
CLI
ODBC |
| History |
CLI definition 1988-1992
CLI part of SQL-92 ODBC 1.0 1992 ODBC 3.0 1993 |
| Supported databases |
CLI: DB2
ODBC: most traditional relational databases (Oracle DB, IBM DB2, Sybase, MS SQLServer, MySQL, PostgreSQL, MS Access etc.) |
| Supported platforms |
CLI: Windows, Linux, AIX, Mainframe
ODBC: primarily Windows but also Unix and Linux |
| Status | Once it was the standard database API for everything, but today its usage is more rare and C is the main languages still using it |
The API is available in two flavors:
The difference is relative minor - mostly the functions for data structure allocation and free plus the function for error message retrieval.
Even though the API is a C API, then it can of course be used in C++ and the usage can be wrapped in OO style access.
There are two ways to establish a connection:
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* platform specific header files */
#include <windows.h>
/* ODBC/CLI headers */
#ifdef DB2CLI
#include <sqlcli1.h>
#endif
#ifdef ODBC
#include <sql.h>
#include <sqlext.h>
#endif
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void env_exit(SQLHENV env)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(env, SQL_NULL_HDBC, SQL_NULL_HSTMT, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void con_exit(SQLHDBC con)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(NULL, con, SQL_NULL_HSTMT, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void stmt_exit(SQLHSTMT stmt)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(NULL, NULL, stmt, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get connection */
SQLHDBC get_connection_dsn(SQLHENV env, char *dsn, char *un, char *pw)
{
SQLHDBC con;
int stat;
stat = SQLAllocConnect(env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
return con;
}
SQLHDBC get_connection_dsnless(SQLHENV env, char *constr)
{
SQLHDBC con;
char resconstr[1024];
short int resconstrlen;
int stat;
stat = SQLAllocConnect(env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLDriverConnect(con, NULL, (SQLCHAR *)constr, (SQLSMALLINT)strlen(constr),
(SQLCHAR *)resconstr, (SQLSMALLINT)sizeof(resconstr), (SQLSMALLINT *)&resconstrlen,
SQL_DRIVER_NOPROMPT);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
return con;
}
/* get f1 given f2 */
int t1_get_one(SQLHDBC con, char *f2)
{
SQLHSTMT stmt;
char *sqlstr;
long int f1;
int stat;
/* get statement */
sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParam(stmt, 1, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, f2, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Row not found");
return 0; /* will never be reached */
}
/* close statement */
SQLCloseCursor(stmt);
SQLFreeStmt(stmt, 0);
return f1;
}
/* get all records */
int t1_get_all(SQLHDBC con, struct t1 *buf, int bufsiz)
{
SQLHSTMT stmt;
char *sqlstr, f2[F2_MAX_LEN+1];
SQLLEN lenf2;
long int f1;
int stat, count;
/* get statement */
sqlstr = "SELECT f1,f2 FROM t1";
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* execute */
stat = SQLExecDirect(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
stat = SQLBindCol(stmt, 2, SQL_C_CHAR, f2, sizeof(f2), &lenf2);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
count = 0;
for(;;)
{
if(count >= bufsiz) break;
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
f2[lenf2] = 0;
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
/* close statement */
SQLCloseCursor(stmt);
SQLFreeStmt(stmt, 0);
return count;
}
/* add record */
void t1_put(SQLHDBC con, int f1, char *f2)
{
SQLHSTMT stmt;
char *sqlstr;
SQLLEN n;
int stat;
/* get statement */
sqlstr = "INSERT INTO t1 VALUES(?, ?)";
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParam(stmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
stat = SQLBindParam(stmt, 2, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, f2,NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* check if OK */
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
if(n != 1)
{
other_exit("INSERT did not insert 1 row");
}
/* close statement */
SQLFreeStmt(stmt, 0);
}
/* delete record */
void t1_remove(SQLHDBC con, int f1)
{
SQLHSTMT stmt;
char *sqlstr;
SQLLEN n;
int stat;
/* get statement */
sqlstr = "DELETE FROM t1 WHERE f1 = ?";
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParam(stmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* check if OK */
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
if(n != 1)
{
other_exit("DELETE did not delete 1 row");
}
/* close statement */
SQLFreeStmt(stmt, 0);
}
/* print all records */
void t1_dump(SQLHDBC con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(SQLHDBC con)
{
int f1;
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
}
void test_dsn(char *dsn, char *un, char *pw)
{
SQLHENV env;
SQLHDBC con;
int stat;
printf("DSN=%s\n", dsn);
/* get connection */
stat = SQLAllocEnv(&env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
con = get_connection_dsn(env, dsn, un, pw);
/* test */
test(con);
/* close connection */
SQLDisconnect(con);
SQLFreeConnect(con);
SQLFreeEnv(env);
}
void test_dsnless(char *constr)
{
SQLHENV env;
SQLHDBC con;
int stat;
printf("Connection string=%s\n", constr);
/* get connection */
stat = SQLAllocEnv(&env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
con = get_connection_dsnless(env, constr);
/* test */
test(con);
/* close connection */
SQLDisconnect(con);
SQLFreeConnect(con);
SQLFreeEnv(env);
}
int main()
{
#ifdef SQLSRVODBC
test_dsn("ARNEPC4_SQLSRV", "", "");
test_dsnless("Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;");
#endif
#ifdef MYSQLODBC
test_dsn("ARNEPC4_MYSQL", "root", "");
test_dsnless("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;");
#endif
#ifdef DB2ODBC
test_dsn("ARNEPC4_DB2", "", "");
test_dsnless("Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef ORACLEODBC
test_dsn("ARNEPC4_ORACLE", "arne", "xxxxxx");
test_dsnless("Driver={Oracle in instantclient_11_2};dbq=localhost/XE;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef PGSQLODBC
test_dsn("ARNEPC4_PGSQL", "", "");
test_dsnless("Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;");
#endif
#ifdef DB2CLI
test_dsn("Test", "", "");
#endif
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// platform specific header files
#include <windows.h>
/* ODBC/CLI headers */
#ifdef DB2CLI
#include <sqlcli1.h>
#endif
#ifdef ODBC
#include <sql.h>
#include <sqlext.h>
#endif
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class OldOdbc : public DB
{
private:
SQLHENV env;
SQLHDBC con;
void EnvExit();
void ConExit();
void StmtExit(SQLHSTMT stmt);
void OtherExit(const char *msg);
public:
// construct instance
OldOdbc(const char *dsn, const char *un, const char *pw);
OldOdbc(const char *constr);
// destruct instance
virtual ~OldOdbc();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void OldOdbc::EnvExit()
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(env, SQL_NULL_HDBC, SQL_NULL_HSTMT, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void OldOdbc::ConExit()
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(NULL, con, SQL_NULL_HSTMT, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void OldOdbc::StmtExit(SQLHSTMT stmt)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLError(NULL, NULL, stmt, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void OldOdbc::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
OldOdbc::OldOdbc(const char *dsn, const char *un, const char *pw)
{
int stat;
stat = SQLAllocEnv(&env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Error allocating environment");
}
stat = SQLAllocConnect(env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
}
OldOdbc::OldOdbc(const char *constr)
{
int stat;
stat = SQLAllocEnv(&env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Error allocating environment");
}
stat = SQLAllocConnect(env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
char resconstr[1024];
short int resconstrlen;
stat = SQLDriverConnect(con, NULL, (SQLCHAR *)constr, (SQLSMALLINT)strlen(constr),
(SQLCHAR *)resconstr, (SQLSMALLINT)sizeof(resconstr), (SQLSMALLINT *)&resconstrlen,
SQL_DRIVER_NOPROMPT);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
}
// destruct instance
OldOdbc::~OldOdbc()
{
SQLDisconnect(con);
SQLFreeConnect(con);
SQLFreeEnv(env);
}
// get f1 given f2
int OldOdbc::T1GetOne(const char *f2)
{
int stat;
// get statement
const char *sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
SQLHSTMT stmt;
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParam(stmt, 1, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, (SQLPOINTER)f2, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind results
long int f1;
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// process result
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Row not found");
return 0; // will never be reached
}
// close statement
SQLCloseCursor(stmt);
SQLFreeStmt(stmt, 0);
return f1;
}
// get all records
int OldOdbc::T1GetAll(struct t1 *buf, int bufsiz)
{
int stat;
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1";
SQLHSTMT stmt;
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// execute
stat = SQLExecDirect(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind results
long int f1;
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
char f2[F2_MAX_LEN+1];
SQLLEN lenf2;
stat = SQLBindCol(stmt, 2, SQL_C_CHAR, f2, sizeof(f2), &lenf2);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// process result
int count = 0;
for(;;)
{
if(count >= bufsiz) break;
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
f2[lenf2] = 0;
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
// close statement
SQLCloseCursor(stmt);
SQLFreeStmt(stmt, 0);
return count;
}
// add record
void OldOdbc::T1Put(int f1, const char *f2)
{
int stat;
// get statement
const char *sqlstr = "INSERT INTO t1 VALUES(?, ?)";
SQLHSTMT stmt;
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParam(stmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
stat = SQLBindParam(stmt, 2, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, (SQLPOINTER)f2, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// check if OK
SQLLEN n;
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
if(n != 1)
{
OtherExit("INSERT did not insert 1 row");
}
// close statement
SQLFreeStmt(stmt, 0);
}
// delete record
void OldOdbc::T1Remove(int f1)
{
int stat;
// get statement
const char *sqlstr = "DELETE FROM t1 WHERE f1 = ?";
SQLHSTMT stmt;
stat = SQLAllocStmt(con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParam(stmt, 1, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// check if OK
SQLLEN n;
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
if(n != 1)
{
OtherExit("DELETE did not delete 1 row");
}
// close statement
SQLFreeStmt(stmt, 0);
}
void test(DB *db)
{
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
}
void test_dsn(const char *dsn, const char *un, const char *pw)
{
cout << "DSN=" << dsn << endl;;
// get database connection
DB *db = new OldOdbc(dsn, un, pw);
// test
test(db);
// release database connection
delete db;
}
void test_dsnless(const char *constr)
{
cout << "Connection string=" << constr << endl;;
// get database connection
DB *db = new OldOdbc(constr);
// test
test(db);
// release database connection
delete db;
}
int main()
{
#ifdef SQLSRVODBC
test_dsn("ARNEPC4_SQLSRV", "", "");
test_dsnless("Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;");
#endif
#ifdef MYSQLODBC
test_dsn("ARNEPC4_MYSQL", "root", "");
test_dsnless("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;");
#endif
#ifdef DB2ODBC
test_dsn("ARNEPC4_DB2", "", "");
test_dsnless("Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef ORACLEODBC
test_dsn("ARNEPC4_ORACLE", "arne", "xxxxxx");
test_dsnless("Driver={Oracle in instantclient_11_2};dbq=localhost/XE;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef PGSQLODBC
test_dsn("ARNEPC4_PGSQL", "", "");
test_dsnless("Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;");
#endif
#ifdef DB2CLI
test_dsn("Test", "", "");
#endif
return 0;
}
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* platform specific header files */
#include <windows.h>
/* ODBC/CLI headers */
#ifdef DB2CLI
#include <sqlcli1.h>
#endif
#ifdef ODBC
#include <sql.h>
#include <sqlext.h>
#endif
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void env_exit(SQLHENV env)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_ENV, env, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void con_exit(SQLHDBC con)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_DBC, con, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void stmt_exit(SQLHSTMT stmt)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get connection */
SQLHDBC get_connection_dsn(SQLHENV env, char *dsn, char *un, char *pw)
{
SQLHDBC con;
int stat;
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
return con;
}
SQLHDBC get_connection_dsnless(SQLHENV env, char *constr)
{
SQLHDBC con;
char resconstr[1024];
short int resconstrlen;
int stat;
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLDriverConnect(con, NULL, (SQLCHAR *)constr, (SQLSMALLINT)strlen(constr),
(SQLCHAR *)resconstr, (SQLSMALLINT)sizeof(resconstr), (SQLSMALLINT *)&resconstrlen,
SQL_DRIVER_NOPROMPT);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
return con;
}
/* get f1 given f2 */
int t1_get_one(SQLHDBC con, char *f2)
{
SQLHSTMT stmt;
char *sqlstr;
long int f1;
int stat;
/* get statement */
sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, f2, strlen(f2), NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Row not found");
return 0; /* will never be reached */
}
/* close statement */
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
return f1;
}
/* get all records */
int t1_get_all(SQLHDBC con, struct t1 *buf, int bufsiz)
{
SQLHSTMT stmt;
char *sqlstr, f2[F2_MAX_LEN+1];
SQLLEN lenf2;
long int f1;
int stat, count;
/* get statement */
sqlstr = "SELECT f1,f2 FROM t1";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* execute */
stat = SQLExecDirect(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
stat = SQLBindCol(stmt, 2, SQL_C_CHAR, f2, sizeof(f2), &lenf2);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
count = 0;
for(;;)
{
if(count >= bufsiz) break;
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
f2[lenf2] = 0;
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
/* close statement */
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
return count;
}
/* add record */
void t1_put(SQLHDBC con, int f1, char *f2)
{
SQLHSTMT stmt;
char *sqlstr;
SQLLEN n;
int stat;
/* get statement */
sqlstr = "INSERT INTO t1 VALUES(?, ?)";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
stat = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, f2, strlen(f2), NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* check if OK */
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
if(n != 1)
{
other_exit("INSERT did not insert 1 row");
}
/* close statement */
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
/* delete record */
void t1_remove(SQLHDBC con, int f1)
{
SQLHSTMT stmt;
char *sqlstr;
SQLLEN n;
int stat;
/* get statement */
sqlstr = "DELETE FROM t1 WHERE f1 = ?";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* check if OK */
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
if(n != 1)
{
other_exit("DELETE did not delete 1 row");
}
/* close statement */
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
/* print all records */
void t1_dump(SQLHDBC con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(SQLHDBC con)
{
int f1;
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
}
void test_dsn(char *dsn, char *un, char *pw)
{
SQLHENV env;
SQLHDBC con;
int stat;
printf("DSN=%s\n", dsn);
/* get connection */
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
con = get_connection_dsn(env, dsn, un, pw);
/* test */
test(con);
/* close connection */
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
void test_dsnless(char *constr)
{
SQLHENV env;
SQLHDBC con;
int stat;
printf("Connection string=%s\n", constr);
/* get connection */
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
con = get_connection_dsnless(env, constr);
/* test */
test(con);
/* close connection */
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
int main()
{
#ifdef SQLSRVODBC
test_dsn("ARNEPC4_SQLSRV", "", "");
test_dsnless("Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;");
#endif
#ifdef MYSQLODBC
test_dsn("ARNEPC4_MYSQL", "root", "");
test_dsnless("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;");
#endif
#ifdef DB2ODBC
test_dsn("ARNEPC4_DB2", "", "");
test_dsnless("Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef ORACLEODBC
test_dsn("ARNEPC4_ORACLE", "arne", "xxxxxx");
test_dsnless("Driver={Oracle in instantclient_11_2};dbq=localhost/XE;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef PGSQLODBC
test_dsn("ARNEPC4_PGSQL", "", "");
test_dsnless("Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;");
#endif
#ifdef DB2CLI
test_dsn("Test", "", "");
#endif
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// platform specific header files
#include <windows.h>
/* ODBC/CLI headers */
#ifdef DB2CLI
#include <sqlcli1.h>
#endif
#ifdef ODBC
#include <sql.h>
#include <sqlext.h>
#endif
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class NewOdbc : public DB
{
private:
SQLHENV env;
SQLHDBC con;
void EnvExit();
void ConExit();
void StmtExit(SQLHSTMT stmt);
void OtherExit(const char *msg);
public:
// construct instance
NewOdbc(const char *dsn, const char *un, const char *pw);
NewOdbc(const char *constr);
// destruct instance
virtual ~NewOdbc();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void NewOdbc::EnvExit()
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_ENV, env, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void NewOdbc::ConExit()
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_DBC, con, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void NewOdbc::StmtExit(SQLHSTMT stmt)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
cout << "SQL error: " << state << " - " << code << " - " << buf << endl;
exit(1);
}
void NewOdbc::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
NewOdbc::NewOdbc(const char *dsn, const char *un, const char *pw)
{
int stat;
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Error allocating environment");
}
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
}
NewOdbc::NewOdbc(const char *constr)
{
int stat;
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Error allocating environment");
}
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
char resconstr[1024];
short int resconstrlen;
stat = SQLDriverConnect(con, NULL, (SQLCHAR *)constr, (SQLSMALLINT)strlen(constr),
(SQLCHAR *)resconstr, (SQLSMALLINT)sizeof(resconstr), (SQLSMALLINT *)&resconstrlen,
SQL_DRIVER_NOPROMPT);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
EnvExit();
}
}
// destruct instance
NewOdbc::~NewOdbc()
{
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
// get f1 given f2
int NewOdbc::T1GetOne(const char *f2)
{
int stat;
// get statement
const char *sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
SQLHSTMT stmt;
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, (SQLPOINTER)f2, strlen(f2), NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind results
long int f1;
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// process result
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
OtherExit("Row not found");
return 0; // will never be reached
}
// close statement
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
return f1;
}
// get all records
int NewOdbc::T1GetAll(struct t1 *buf, int bufsiz)
{
int stat;
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1";
SQLHSTMT stmt;
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// execute
stat = SQLExecDirect(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind results
long int f1;
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
char f2[F2_MAX_LEN+1];
SQLLEN lenf2;
stat = SQLBindCol(stmt, 2, SQL_C_CHAR, f2, sizeof(f2), &lenf2);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// process result
int count = 0;
for(;;)
{
if(count >= bufsiz) break;
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
f2[lenf2] = 0;
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
// close statement
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
return count;
}
// add record
void NewOdbc::T1Put(int f1, const char *f2)
{
int stat;
// get statement
const char *sqlstr = "INSERT INTO t1 VALUES(?, ?)";
SQLHSTMT stmt;
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
stat = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, strlen(f2), 0, (SQLPOINTER)f2, strlen(f2), NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// check if OK
SQLLEN n;
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
if(n != 1)
{
OtherExit("INSERT did not insert 1 row");
}
// close statement
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
// delete record
void NewOdbc::T1Remove(int f1)
{
int stat;
// get statement
const char *sqlstr = "DELETE FROM t1 WHERE f1 = ?";
SQLHSTMT stmt;
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
ConExit();
}
// prepare
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// bind parameters
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &f1, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// execute
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
// check if OK
SQLLEN n;
stat = SQLRowCount(stmt, &n);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
StmtExit(stmt);
}
if(n != 1)
{
OtherExit("DELETE did not delete 1 row");
}
// close statement
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
void test(DB *db)
{
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
}
void test_dsn(const char *dsn, const char *un, const char *pw)
{
cout << "DSN=" << dsn << endl;;
// get database connection
DB *db = new NewOdbc(dsn, un, pw);
// test
test(db);
// release database connection
delete db;
}
void test_dsnless(const char *constr)
{
cout << "Connection string=" << constr << endl;;
// get database connection
DB *db = new NewOdbc(constr);
// test
test(db);
// release database connection
delete db;
}
int main()
{
#ifdef SQLSRVODBC
test_dsn("ARNEPC4_SQLSRV", "", "");
test_dsnless("Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;");
#endif
#ifdef MYSQLODBC
test_dsn("ARNEPC4_MYSQL", "root", "");
test_dsnless("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;");
#endif
#ifdef DB2ODBC
test_dsn("ARNEPC4_DB2", "", "");
test_dsnless("Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef ORACLEODBC
test_dsn("ARNEPC4_ORACLE", "arne", "xxxxxx");
test_dsnless("Driver={Oracle in instantclient_11_2};dbq=localhost/XE;Uid=arne;Pwd=xxxxxx;");
#endif
#ifdef PGSQLODBC
test_dsn("ARNEPC4_PGSQL", "", "");
test_dsnless("Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;");
#endif
#ifdef DB2CLI
test_dsn("Test", "", "");
#endif
return 0;
}
Windows GCC C ODBC build:
gcc -DODBC -D%2ODBC -Wall %1.c -lodbc32 -o %1.exe
Windows GCC C++ ODBC build:
g++ -DODBC -D%2ODBC -Wall %1.cpp -lodbc32 -o %1.exe
Windows MSVC++ C ODBC build:
cl /DODBC /D%2ODBC %1.c odbc32.lib
Windows MSVC++ C++ ODBC build:
cl /EHsc /DODBC /D%2ODBC %1.cpp odbc32.lib
ODBC code is actual portable if using only standard SQL .
Windows MSVC++ C DB2 CLI build:
cl /W2 /DWIN32 /DDB2CLI %1.c db2api.lib
Windows MSVC++ C++ DB2 CLI build:
cl /EHsc /W2 /DWIN32 /DDB2CLI %1.cpp db2api.lib
ADO (ActiveX Data Objects) is a COM based database API on top of OLE DB. It can be used by any language supporting COM, but the two most popular languages are ASP/VBS and C++. Because it is COM and OLE DB based it is tied to Microsoft Windows.
It uses a provider model where the database vendor provides an OLE DB provider for the database.
To ease the migration from ODBC to ADO then Microsoft supplied an ODBC OLE DB Provider so ADO works with any ODBC driver as well as real OLE DB providers.
Architecture of ADO:
| Names |
ADO
ActiveX Data Object OLE DB |
| History |
ADO was first released in 1996.
ADO.NET became the new database technology in 2002. Microsoft announced in 2012 that OLE DB provider for SQLServer would go away in 2019, but that decision was reversed in 2017. |
| Supported databases | Many of the major databases on Windows (MS SQLServer, Oracle DB, IBM DB2, MS Access etc.). |
| Supported platforms | Windows |
| Status | Back in the late 90's ADO was intended to replace ODBC and ADO was practically the only choice for database access in the then very popular ASP. But since then Microsoft has switched to .NET and now it is ADO.NET providers not ADO providers that are in demand. There is little interest in ADO today, so it looks like ODBC will live longer than ADO. |
// standard C++ headers
#include <iostream>
#include <cstdlib>
using namespace std;
// ADO headers
#include <windows.h>
#import "msado15.dll" rename("EOF", "EndOfFile")
using namespace ADODB;
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
// utility function
static void bstr2char(char *out, BSTR in)
{
int len = SysStringLen(in);
for(int i = 0; i < len; i++)
{
out[i] = (char)in[i];
}
out[len] = 0;
}
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class ADO : public DB
{
private:
_ConnectionPtr con;
void ExceptionExit(_com_error ex);
void OtherExit(const char *msg);
public:
// construct instance
ADO(const char *constr, const char *un, const char *pw);
// destruct instance
virtual ~ADO();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void ADO::ExceptionExit(_com_error ex)
{
cout << "COM error: " << ex.ErrorMessage() << ", ADO error: " << ex.Description() << endl;
exit(1);
}
void ADO::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
ADO::ADO(const char *constr, const char *un, const char *pw)
{
try
{
con.CreateInstance(__uuidof(Connection));
con->Open(constr, un, pw, adConnectUnspecified);
}
catch(_com_error &ex)
{
ExceptionExit(ex);
}
}
// destruct instance
ADO::~ADO()
{
con->Close();
con = NULL;
}
// get f1 given f2
int ADO::T1GetOne(const char *f2)
{
try
{
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "SELECT f1 FROM t1 WHERE f2 = ?";
cmd->NamedParameters = false;
// add parameter
_ParameterPtr p = cmd->CreateParameter("", adVarChar, adParamInput, -1);
p->Value = f2;
cmd->Parameters->Append(p);
// open record set
_RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdText);
// process result
int f1;
if(!rs->EndOfFile)
{
f1 = rs->GetCollect("f1").intVal;
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
// close record set
rs->Close();
rs = NULL;
cmd = NULL;
return f1;
}
catch(_com_error &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// get all records
int ADO::T1GetAll(struct t1 *buf, int bufsiz)
{
try
{
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "SELECT * FROM t1";
// open record set
_RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdText);
// process result
int count = 0;
while(!rs->EndOfFile && (count < bufsiz))
{
buf[count].f1 = rs->GetCollect("f1").intVal;
bstr2char(buf[count].f2, rs->GetCollect("f2").bstrVal);
count++;
rs->MoveNext();
}
// close record set
rs->Close();
rs = NULL;
cmd = NULL;
return count;
}
catch(_com_error &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// add record
void ADO::T1Put(int f1, const char *f2)
{
try
{
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "INSERT INTO t1 VALUES(?, ?)";
cmd->NamedParameters = false;
// add parameters
_ParameterPtr p1 = cmd->CreateParameter("", adInteger, adParamInput, -1);
p1->Value = f1;
cmd->Parameters->Append(p1);
_ParameterPtr p2 = cmd->CreateParameter("", adVarChar, adParamInput, -1);
p2->Value = f2;
cmd->Parameters->Append(p2);
// execute
_variant_t n;
cmd->Execute(&n, NULL, adCmdText);
// check if OK
if(n.intVal != 1)
{
OtherExit("INSERT did not insert 1 row");
}
}
catch(_com_error &ex)
{
ExceptionExit(ex);
}
}
// delete record
void ADO::T1Remove(int f1)
{
try
{
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "DELETE FROM t1 WHERE f1 = ?";
cmd->NamedParameters = false;
// add parameters
_ParameterPtr p = cmd->CreateParameter("", adInteger, adParamInput, -1);
p->Value = f1;
cmd->Parameters->Append(p);
// execute
_variant_t n;
cmd->Execute(&n, NULL, adCmdText);
// check if OK
if(n.intVal != 1)
{
OtherExit("DELETE did not delete 1 row");
}
}
catch(_com_error &ex)
{
ExceptionExit(ex);
}
}
void test(const char *constr, const char *un, const char *pw)
{
cout << "Connection string=" << constr << endl;
// get database connection
DB *db = new ADO(constr, un, pw);
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
// release database connection
delete db;
}
int main()
{
CoInitialize(NULL);
#ifdef SQLSRV
test("Provider=SQLNCLI11;Server=localhost;Database=Test;Trusted_Connection=yes;", "", "");
test("Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;", "", "");
#endif
#ifdef DB2
test("Provider=IBMDADB2;Protocol=TCPIP;Hostname=localhost;Database=Test;Port=50000;Uid=arne;Pwd=xxxxxx;", "", "");
test("Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;", "", "");
#endif
#ifdef ORACLE
test("Provider=OraOLEDB.Oracle;Data Source=localhost:1521/XE;", "arne", "xxxxxx");
test("Driver={Oracle in instantclient_11_2};Dbq=localhost/XE;;Uid=arne;Pwd=xxxxxx;", "", "");
#endif
CoUninitialize();
return 0;
}
Windows MSVC++ C++ build:
cl /EHsc /D%2 %1.cpp
The Qt C++ framework provides a database independent database API called Qt SQL.
| Names | Qt SQL |
| History | |
| Supported databases | Most traditional relational databases (ODBC, MySQL, PostgeSQL, Oracle, DB2) |
| Supported platforms | All where Qt is available (Windows, Linux, MacOS X) |
| Status | Actively maintained |
Qt SQL is only available within the Qt framework.
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// Qt headers
#include <QtSql/QtSql>
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class QtDB : public DB
{
private:
char connam[256];
QSqlDatabase db;
void ConExit();
void QueryExit(QSqlQuery q);
void OtherExit(const char *msg);
public:
// construct instance
QtDB(const char *driver, const char * host, const char *usr, const char *pw, const char *dbnam);
// destruct instance
virtual ~QtDB();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void QtDB::ConExit()
{
cout << "SQL error: " << db.lastError().text().toStdString() << endl;
exit(1);
}
void QtDB::QueryExit(QSqlQuery q)
{
cout << "SQL error: " << q.lastError().text().toStdString() << endl;
exit(1);
}
void QtDB::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
QtDB::QtDB(const char *driver, const char * host, const char *usr, const char *pw, const char *dbnam)
{
sprintf(connam, "%s - %s", driver, dbnam);
db = QSqlDatabase::addDatabase(driver, connam);
db.setHostName(host);
db.setUserName(usr);
db.setPassword(pw);
db.setDatabaseName(dbnam);
if(!db.open())
{
ConExit();
}
}
// destruct instance
QtDB::~QtDB()
{
db.close();
//QSqlDatabase::removeDatabase(connam);
}
// get f1 given f2
int QtDB::T1GetOne(const char *f2)
{
// get query
const char *sqlstr = "SELECT f1 FROM t1 WHERE f2 = :f2";
QSqlQuery q(db);
// prepare
q.prepare(sqlstr);
// bind parameters
q.bindValue(":f2", f2);
// execute
if(q.exec())
{
// process result
if(q.next())
{
int f1 = q.value(0).toInt();
return f1;
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
}
else
{
QueryExit(q);
return 0; // will never be reached
}
}
// get all records
int QtDB::T1GetAll(struct t1 *buf, int bufsiz)
{
// get query
const char *sqlstr = "SELECT f1,f2 FROM t1";
QSqlQuery q(db);
// execute
if(q.exec(sqlstr))
{
// process result
int count = 0;
while(q.next() && count < bufsiz)
{
buf[count].f1 = q.value(0).toInt();
strcpy(buf[count].f2, q.value(1).toString().toStdString().c_str());
count++;
}
return count;
}
else
{
QueryExit(q);
return 0; // will never be reached
}
}
// add record
void QtDB::T1Put(int f1, const char *f2)
{
// get query
const char *sqlstr = "INSERT INTO t1 VALUES(:f1, :f2)";
QSqlQuery q(db);
// prepare
q.prepare(sqlstr);
// bind parameters
q.bindValue(":f1", f1);
q.bindValue(":f2", f2);
// execute
if(q.exec())
{
// check if OK
if(q.numRowsAffected() != 1)
{
OtherExit("INSERT did not insert 1 row");
}
}
else
{
QueryExit(q);
}
}
// delete record
void QtDB::T1Remove(int f1)
{
// get query
const char *sqlstr = "DELETE FROM t1 WHERE f1 = :f1";
QSqlQuery q(db);
// prepare
q.prepare(sqlstr);
// bind parameters
q.bindValue(":f1", f1);
// execute
if(q.exec())
{
// check if OK
if(q.numRowsAffected() != 1)
{
OtherExit("DELETE did not delete 1 row");
}
}
else
{
QueryExit(q);
}
}
void test(DB *db)
{
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
}
void test(const char *lbl, const char *driver, const char * host, const char *usr, const char *pw, const char *dbnam)
{
cout << lbl << ":" << endl;
DB *db = new QtDB(driver, host, usr, pw, dbnam);
test(db);
delete db;
}
void realmain()
{
test("MySQL", "QMYSQL", "localhost", "root", "","Test");
test("PostgreSQL", "QPSQL", "localhost", "postgres", "xxxxxx", "Test");
test("ODBC - SQLServer", "QODBC3", "", "", "", "Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;");
test("ODBC - MySQL", "QODBC3", "", "", "", "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;");
test("ODBC - DB2", "QODBC3", "", "", "", "Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx;");
test("ODBC - PostgreSQL", "QODBC3", "", "", "", "Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;");
}
To actually get a Qt program running some setup is necessary. I don't know Qt and the code below is copied from the net.
#include <QtCore/QObject>
class Main : public QObject
{
Q_OBJECT
public:
Main(QObject *parent = 0) : QObject(parent) { };
public slots:
void run();
signals:
void finished();
};
#include <QtCore/QCoreApplication>
#include <QtCore/QSharedPointer>
#include <QtCore/QObject>
#include <QtCore/QTimer>
#include "Main.h"
void realmain();
void Main::run()
{
realmain();
emit finished();
}
int main(int argc, char *argv[])
{
QCoreApplication app(argc, argv);
QSharedPointer<Main> mp(new Main(&app));
QObject::connect(mp.data(), SIGNAL(finished()), &app, SLOT(quit()));
QTimer::singleShot(0, mp.data(), SLOT(run()));
return app.exec();
}
Build with MSVC++ on Windows:
%QTDIR%\bin\moc Main.h -o Main_moc.cpp
cl /EHsc /I%QTDIR%\include QtDB.cpp Main.cpp Main_moc.cpp %QTDIR%\lib\Qt5Sql.lib %QTDIR%\lib\Qt5Core.lib
Apache has native library APR (Apache Portable Runtime) provinding some core functionality needed by many applications.
One APR module is DBD which provide a general database API using a driver model.
The API is not widely used outside Apache product, but it does provide a decent database API.
| Names | DBD |
| History | DBD was added to APR sometime in early 2000's |
| Supported databases | Most (ODBC, PostgreSQL, MySQL, Oracle DB, MS SQLServer, Sybase / Sybase ASE) |
| Supported platforms | Most (Linux, Windows, macOS, some Unix etc.) |
| Status | Maintained |
Not all drivers are good. ODBC worked fine for me. But PostgreSQL and MySQL did not work for me.
/* standard C headers */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/* APR headers */
#include "apr.h"
#include "apr_pools.h"
#include "apr_dbd.h"
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void driverdb_exit(apr_dbd_driver_t *driver, apr_dbd_t *db, apr_status_t stat)
{
printf("APR DBD error:%s\n", apr_dbd_error(driver, db, stat));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get f1 given f2 */
int t1_get_one(apr_pool_t *pool, apr_dbd_driver_t *driver, apr_dbd_t *db, char *f2)
{
apr_status_t stat;
apr_dbd_prepared_t *stmt;
apr_dbd_results_t *res;
apr_dbd_row_t *row;
int f1;
/* prepare SQL */
stat = apr_dbd_prepare(driver, pool, db, "SELECT f1 FROM t1 WHERE f2 = %s", NULL, &stmt);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* execute */
stat = apr_dbd_pvselect(driver, pool, db, &res, stmt, 0, f2);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* process result */
if(!apr_dbd_get_row(driver, pool, res, &row, -1))
{
f1 = atoi(apr_dbd_get_entry(driver, row, 0));
return f1;
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
return 0;
}
/* get all records */
int t1_get_all(apr_pool_t *pool, apr_dbd_driver_t *driver, apr_dbd_t *db, struct t1 *buf, int bufsiz)
{
apr_status_t stat;
apr_dbd_results_t *res;
apr_dbd_row_t *row;
int count;
/* execute */
stat = apr_dbd_select(driver, pool, db, &res, "SELECT f1,f2 FROM t1", 0);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* process result */
count = 0;
while(!apr_dbd_get_row(driver, pool, res, &row, -1))
{
buf[count].f1 = atoi(apr_dbd_get_entry(driver, row, 0));
strcpy(buf[count].f2, apr_dbd_get_entry(driver, row, 1));
count++;
}
return count;
}
/* add record */
void t1_put(apr_pool_t *pool, apr_dbd_driver_t *driver, apr_dbd_t *db, int f1, char *f2)
{
apr_status_t stat;
apr_dbd_prepared_t *stmt;
char f1s[20];
int nrows;
/* prepare SQL */
stat = apr_dbd_prepare(driver, pool, db, "INSERT INTO t1 VALUES(%d, %s)", NULL, &stmt);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* execute */
sprintf(f1s, "%d", f1);
stat = apr_dbd_pvquery(driver, pool, db, &nrows, stmt, f1s, f2);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* check if OK */
if(nrows != 1)
{
other_exit("INSERT did not insert 1 row");
}
}
/* delete record */
void t1_remove(apr_pool_t *pool, apr_dbd_driver_t *driver, apr_dbd_t *db, int f1)
{
apr_status_t stat;
apr_dbd_prepared_t *stmt;
char f1s[20];
int nrows;
/* prepare SQL */
stat = apr_dbd_prepare(driver, pool, db, "DELETE FROM t1 WHERE f1 = %d", NULL, &stmt);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* execute */
sprintf(f1s, "%d", f1);
stat = apr_dbd_pvquery(driver, pool, db, &nrows, stmt, f1s);
if(stat != APR_SUCCESS)
{
driverdb_exit(driver, db, stat);
}
/* check if OK */
if(nrows != 1)
{
other_exit("DELETE did not delete 1 row");
}
}
/* print all records */
void t1_dump(apr_pool_t *pool, apr_dbd_driver_t *driver, apr_dbd_t *db)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(pool, driver, db, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(char *drivername, char *params)
{
printf("%s %s:\n", drivername, params);
apr_status_t stat;
apr_pool_t *pool;
apr_dbd_driver_t *driver;
apr_dbd_t *db;
int f1;
/* initialize */
stat = apr_pool_initialize();
if(stat != APR_SUCCESS)
{
other_exit("pool_initialize failed");
}
stat = apr_pool_create_core(&pool);
if(stat != APR_SUCCESS)
{
other_exit("pool_create_core failed");
}
stat = apr_dbd_init(pool);
if(stat != APR_SUCCESS)
{
other_exit("dbd_init failed");
}
/* get connection */
stat = apr_dbd_get_driver(pool, drivername, &driver);
if(stat != APR_SUCCESS)
{
other_exit("dbd_get_driver failed");
}
stat = apr_dbd_open(driver, pool, params, &db);
if(stat != APR_SUCCESS)
{
other_exit("dbd_get_open failed");
}
/* test t1_get_one */
f1 = t1_get_one(pool, driver, db, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(pool, driver, db);
/* test t1_put */
t1_put(pool, driver, db, 999, "XXX");
t1_dump(pool, driver, db);
/* test t1_remove */
t1_remove(pool, driver, db, 999);
t1_dump(pool, driver, db);
/* close */
apr_dbd_close(driver, db);
apr_pool_clear(pool);
apr_pool_destroy(pool);
apr_pool_terminate();
}
int main()
{
test("odbc", "DataSource=ARNEPC4_SQLSRV");
test("odbc", "DataSource=ARNEPC4_MYSQL,User=root");
test("odbc", "DataSource=ARNEPC4_DB2");
test("odbc", "DataSource=ARNEPC4_ORACLE,User=arne,Password=xxxxxx");
test("odbc", "DataSource=ARNEPC4_PGSQL");
test("odbc", "Connect='Driver={SQL Server Native Client 11.0};Server=localhost;Database=Test;Trusted_Connection=yes;'");
test("odbc", "Connect='Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Test;User=root;Password=;'");
test("odbc", "Connect='Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=Test;Uid=arne;Pwd=xxxxxx'");
test("odbc", "Connect='Driver={Oracle in instantclient_11_2};dbq=localhost/XE;Uid=arne;Pwd=xxxxxx;'");
test("odbc", "Connect='Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Test;Uid=postgres;Pwd=xxxxxx;'");
test("mysql", "Host=localhost,Dbname=Test,User=root,Pass=");
test("pgsql", "host=localhost dbname=Test user=postgres password=xxxxxx");
return 0;
}
Windows MSVC++ build:
cl /MD /I%APR_DIR%\include /I%APRUTIL_DIR%\include test.c %APR_DIR%\lib\libapr-1.lib %APRUTIL_DIR%\lib\libaprutil-1.lib
Linux GCC build:
gcc -Wall -I/usr/include/apr-1 test.c -o test -lapr-1 -laprutil-1
SQLRelay is an open source project available at SourceForge.
Strictly speaking SQL Relay is not an API to database access - SQL Relay is an API to a database proxy that can access databases.
Such a database proxy provide several benefits, including but not limited to:
| Names | SQL Relay native API |
| History | First release in 2000 |
| Supported databases | Most (Oracle, DB2, MySQL, PostgreSQL, ODBC etc.) |
| Supported platforms | Most (Linux, Windows, Unix, macOD, VMS etc.) |
| Status | Actively maintained |
The API is available in two forms:
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* SQLRelay headers */
#include <sqlrelay/sqlrclientwrapper.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
typedef char *(*ss2s)(char *s1, char *s2);
typedef char *(*sss2s)(char *s1, char *s2, char *s3);
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void con_exit(sqlrcon con)
{
printf("SQLR connection error: %s\n", sqlrcon_errorMessage(con));
exit(1);
}
void curs_exit(sqlrcur curs)
{
printf("SQLR cursor error: %s\n", sqlrcur_errorMessage(curs));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get f1 given f2 */
int t1_get_one(sqlrcon con, char *f2, sss2s placeholder, ss2s bindname)
{
sqlrcur curs;
int stat, f1;
char sqlstr[40 + 2 * F2_MAX_LEN];
/* get cursor */
curs = sqlrcur_alloc(con);
/* prepare */
sprintf(sqlstr, "SELECT f1 FROM t1 WHERE f2 = %s", placeholder("1", "f2", "text"));
sqlrcur_prepareQuery(curs, sqlstr);
/* bind parameters */
sqlrcur_inputBindString(curs, bindname("1", "f2"), f2);
/* execute */
stat = sqlrcur_executeQuery(curs);
if(!stat) curs_exit(curs);
/* process result */
if(sqlrcur_getFieldByIndex(curs, 0, 0) == NULL)
{
other_exit("Row not found");
return 0; /* will never be reached */
}
f1 = sqlrcur_getFieldAsIntegerByIndex(curs, 0, 0);
sqlrcon_endSession(con);
/* close cursor */
sqlrcur_free(curs);
return f1;
}
/* get all records */
int t1_get_all(sqlrcon con, struct t1 *buf, int bufsiz)
{
sqlrcur curs;
int stat, row, count;
/* get cursor */
curs = sqlrcur_alloc(con);
/* execute */
stat = sqlrcur_sendQuery(curs, "SELECT f1,f2 FROM t1");
if(!stat) curs_exit(curs);
/* process result */
sqlrcur_setResultSetBufferSize(curs, 100);
row = 0;
count = 0;
for(;;)
{
if(count >= bufsiz) break;
if(sqlrcur_getFieldByIndex(curs, row, 0) == NULL) break;
buf[count].f1 = sqlrcur_getFieldAsIntegerByIndex(curs, row, 0);
strcpy(buf[count].f2, sqlrcur_getFieldByIndex(curs, row, 1));
count++;
row++;
}
sqlrcon_endSession(con);
/* close cursor */
sqlrcur_free(curs);
return count;
}
/* add record */
void t1_put(sqlrcon con, int f1, char *f2, sss2s placeholder, ss2s bindname)
{
sqlrcur curs;
int stat, n;
char sqlstr[40 + 2 * F2_MAX_LEN];
/* get cursor */
curs = sqlrcur_alloc(con);
/* prepare */
sprintf(sqlstr, "INSERT INTO t1 VALUES(%s, %s)", placeholder("1", "f1", "int"), placeholder("2", "f2", "text"));
sqlrcur_prepareQuery(curs, sqlstr);
/* bind parameters */
sqlrcur_inputBindLong(curs, bindname("1", "f1"), f1);
sqlrcur_inputBindString(curs, bindname("2", "f2"), f2);
/* execute */
stat = sqlrcur_executeQuery(curs);
if(!stat) curs_exit(curs);
n = sqlrcur_affectedRows(curs);
if(n != 1)
{
other_exit("INSERT did not insert 1 row");
}
sqlrcon_endSession(con);
/* close cursor */
sqlrcur_free(curs);
}
/* delete record */
void t1_remove(sqlrcon con, int f1, sss2s placeholder, ss2s bindname)
{
sqlrcur curs;
int stat, n;
char sqlstr[40 + 2 * F2_MAX_LEN];
/* get cursor */
curs = sqlrcur_alloc(con);
/* prepare */
sprintf(sqlstr, "DELETE FROM t1 WHERE f1 = %s", placeholder("1", "f1", "int"));
sqlrcur_prepareQuery(curs, sqlstr);
/* bind parameters */
sqlrcur_inputBindLong(curs, bindname("1", "f1"), f1);
/* execute */
stat = sqlrcur_executeQuery(curs);
if(!stat) curs_exit(curs);
n = sqlrcur_affectedRows(curs);
if(n != 1)
{
other_exit("DELETE did not delete 1 row");
}
sqlrcon_endSession(con);
/* close cursor */
sqlrcur_free(curs);
}
/* print all records */
void t1_dump(sqlrcon con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(char *host, int port, char *un, char *pw, sss2s placeholder, ss2s bindname)
{
sqlrcon con;
int f1;
printf("Connect host=%s port=%d:\n", host, port);
/* open */
con = sqlrcon_alloc(host, port, NULL, un, pw, 0, 1);
sqlrcon_autoCommitOn(con);
/* test t1_get_one */
f1 = t1_get_one(con, "BB", placeholder, bindname);
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX", placeholder, bindname);
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999, placeholder, bindname);
t1_dump(con);
/* close */
sqlrcon_free(con);
}
#define MAXIX 100
static int phix = 0;
static char phbuf[MAXIX][16];
static int bnix = 0;
static char bnbuf[MAXIX][16];
/* standard (ODBC, DB2, MySQL) */
static char *std_ph(char *num, char *nam, char *typ)
{
return "?";
}
static char *std_bn(char *num, char *nam)
{
return num;
}
/* PostgreSQL */
static char *pgsql_ph(char *num, char *nam, char *typ)
{
char *buf = phbuf[phix];
sprintf(buf, "$%s::%s", num, typ);
phix = (phix + 1) % MAXIX;
return buf;
}
static char *pgsql_bn(char *num, char *nam)
{
return num;
}
/* Oracle */
static char *ora_ph(char *num, char *nam, char *typ)
{
char *buf = phbuf[phix];
sprintf(buf, ":%s", nam);
phix = (phix + 1) % MAXIX;
return buf;
}
static char *ora_bn(char *num, char *nam)
{
return nam;
}
int main()
{
test("localhost", 9001, "arne", "topsecret", std_ph, std_bn); /* 9001 = localmysql */
test("localhost", 9002, "arne", "topsecret", pgsql_ph, pgsql_bn); /* 9002 = localpgsql */
test("localhost", 9003, "arne", "topsecret", std_ph, std_bn); /* 9003 = localdb2 */
test("localhost", 9004, "arne", "topsecret", ora_ph, ora_bn); /* 9004 = localora */
test("localhost", 9005, "arne", "topsecret", std_ph, std_bn); /* 9005 = odbcsqlsrv */
test("localhost", 9006, "arne", "topsecret", std_ph, std_bn); /* 9006 = odbcmysql */
test("localhost", 9007, "arne", "topsecret", std_ph, std_bn); /* 9007 = odbcpgsql */
test("localhost", 9008, "arne", "topsecret", std_ph, std_bn); /* 9008 = odbcdb2 */
//test("localhost", 9009, "arne", "topsecret", std_ph, std_bn); /* 9009 = odbcora */ /* NOTE: not working - INSERT and UPDATE fail */
return 0;
}
Build on Windows with GCC:
gcc -c -I%SQLR%\include sqlr.c -o sqlr.obj
g++ sqlr.obj -o sqlr.exe %SQLR%\lib\libsqlrclientwrapper.lib %SQLR%\lib\libsqlrclient.lib %SQLR%\lib\librudiments.lib
// standard C++ headers
#include <iostream>
#include <cstdio>
#include <cstdlib>
using namespace std;
/* SQLRelay headers */
#ifdef WIN32
#define RUDIMENTS_HAVE_CHAR16_T // hack to solve some rudiments vs VS2019 problem
#endif
#include <sqlrelay/sqlrclient.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
typedef const char *(*ss2s)(const char *s1, const char *s2);
typedef const char *(*sss2s)(const char *s1, const char *s2, const char *s3);
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class OOSQLR : public DB
{
private:
sqlrconnection *con;
sss2s placeholder;
ss2s bindname;
void ConnectionExit(sqlrconnection *con);
void CursorExit(sqlrcursor *curs);
void OtherExit(const char *msg);
public:
// construct instance
OOSQLR(const char *host, int port, const char *un, const char *pw, sss2s placeholder, ss2s bindname);
// destruct instance
virtual ~OOSQLR();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void OOSQLR::ConnectionExit(sqlrconnection *con)
{
cout << "SQLR connection error: " << con->errorMessage() << endl;
exit(1);
}
void OOSQLR::CursorExit(sqlrcursor *curs)
{
cout << "SQLR cursor error: " << curs->errorMessage() << endl;
exit(1);
}
void OOSQLR::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
OOSQLR::OOSQLR(const char *host, int port, const char *un, const char *pw, sss2s placeholder, ss2s bindname)
{
con = new sqlrconnection(host, port, NULL, un, pw, 0, 1);
con->autoCommitOn();
this->placeholder = placeholder;
this->bindname = bindname;
}
// destruct instance
OOSQLR::~OOSQLR()
{
delete con;
}
// get f1 given f2
int OOSQLR::T1GetOne(const char *f2)
{
// get cursor
sqlrcursor *curs = new sqlrcursor(con);
// prepare
char sqlstr[40 + 2 * F2_MAX_LEN];
sprintf(sqlstr, "SELECT f1 FROM t1 WHERE f2 = %s", placeholder("1", "f2", "text"));
curs->prepareQuery(sqlstr);
// bind parameters
curs->inputBind(bindname("1", "f2"), f2);
// execute
int stat = curs->executeQuery();
if(!stat) CursorExit(curs);
// process result
if(curs->getField((uint64_t)0, (uint32_t)0) == NULL)
{
OtherExit("Row not found");
return 0; /* will never be reached */
}
int f1 = curs->getFieldAsInteger((uint64_t)0, (uint32_t)0);
con->endSession();
// close cursor
delete curs;
return f1;
}
// get all records
int OOSQLR::T1GetAll(struct t1 *buf, int bufsiz)
{
// get cursor
sqlrcursor *curs = new sqlrcursor(con);
// execute
int stat = curs->sendQuery("SELECT f1,f2 FROM t1");
if(!stat) CursorExit(curs);
// process result
curs->setResultSetBufferSize(100);
int row = 0;
int count = 0;
for(;;)
{
if(count >= bufsiz) break;
if(curs->getField((uint64_t)row, (uint32_t)0) == NULL) break;
buf[count].f1 = curs->getFieldAsInteger((uint64_t)row, (uint32_t)0);
strcpy(buf[count].f2, curs->getField((uint64_t)row, (uint32_t)1));
count++;
row++;
}
con->endSession();
// close cursor
delete curs;
return count;
}
// add record
void OOSQLR::T1Put(int f1, const char *f2)
{
// get cursor
sqlrcursor *curs = new sqlrcursor(con);
// prepare
char sqlstr[40 + 2 * F2_MAX_LEN];
sprintf(sqlstr, "INSERT INTO t1 VALUES(%s, %s)", placeholder("1", "f1", "int"), placeholder("2", "f2", "text"));
curs->prepareQuery(sqlstr);
// bind parameters
curs->inputBind(bindname("1", "f1"), f1);
curs->inputBind(bindname("2", "f2"), f2);
// execute
int stat = curs->executeQuery();
if(!stat) CursorExit(curs);
int n = curs->affectedRows();
if(n != 1)
{
OtherExit("INSERT did not insert 1 row");
}
con->endSession();
// close cursor
delete curs;
}
// delete record
void OOSQLR::T1Remove(int f1)
{
// get cursor
sqlrcursor *curs = new sqlrcursor(con);
// prepare
char sqlstr[40 + 2 * F2_MAX_LEN];
sprintf(sqlstr, "DELETE FROM t1 WHERE f1 = %s", placeholder("1", "f1", "int"));
curs->prepareQuery(sqlstr);
// bind parameters
curs->inputBind(bindname("1", "f1"), f1);
// execute
int stat = curs->executeQuery();
if(!stat) CursorExit(curs);
int n = curs->affectedRows();
if(n != 1)
{
OtherExit("DELETE did not delete 1 row");
}
con->endSession();
// close cursor
delete curs;
}
void test(const char *host, int port, const char *un, const char *pw, sss2s placeholder, ss2s bindname)
{
cout << "Connect host=" << host << " port=" << port << endl;
/* open */
DB *db = new OOSQLR(host, port, un, pw, placeholder, bindname);
/* test T1GetOne */
int f1 = db->T1GetOne("BB");
printf("%d\n", f1);
/* test T1GetAll */
db->T1Dump();
/* test T1Put */
db->T1Put(999, "XXX");
db->T1Dump();
/* test T1Remove */
db->T1Remove(999);
db->T1Dump();
}
#define MAXIX 100
static int phix = 0;
static char phbuf[MAXIX][16];
static int bnix = 0;
static char bnbuf[MAXIX][16];
/* standard (ODBC, DB2, MySQL) */
static const char *std_ph(const char *num, const char *nam, const char *typ)
{
return "?";
}
static const char *std_bn(const char *num, const char *nam)
{
return num;
}
/* PostgreSQL */
static const char *pgsql_ph(const char *num, const char *nam, const char *typ)
{
char *buf = phbuf[phix];
sprintf(buf, "$%s::%s", num, typ);
phix = (phix + 1) % MAXIX;
return buf;
}
static const char *pgsql_bn(const char *num, const char *nam)
{
return num;
}
/* Oracle */
static const char *ora_ph(const char *num, const char *nam, const char *typ)
{
char *buf = phbuf[phix];
sprintf(buf, ":%s", nam);
phix = (phix + 1) % MAXIX;
return buf;
}
static const char *ora_bn(const char *num, const char *nam)
{
return nam;
}
int main()
{
test("localhost", 9001, "arne", "topsecret", std_ph, std_bn); /* 9001 = localmysql */
test("localhost", 9002, "arne", "topsecret", pgsql_ph, pgsql_bn); /* 9002 = localpgsql */
test("localhost", 9003, "arne", "topsecret", std_ph, std_bn); /* 9003 = localdb2 */
test("localhost", 9004, "arne", "topsecret", ora_ph, ora_bn); /* 9004 = localora */
test("localhost", 9005, "arne", "topsecret", std_ph, std_bn); /* 9005 = odbcsqlsrv */
test("localhost", 9006, "arne", "topsecret", std_ph, std_bn); /* 9006 = odbcmysql */
test("localhost", 9007, "arne", "topsecret", std_ph, std_bn); /* 9007 = odbcpgsql */
test("localhost", 9008, "arne", "topsecret", std_ph, std_bn); /* 9008 = odbcdb2 */
//test("localhost", 9009, "arne", "topsecret", std_ph, std_bn); /* 9009 = odbcora */ /* NOTE: not working - INSERT and UPDATE fail */
return 0;
}
Build on Windows with MSVC++:
cl /EHsc /DWIN32 /I%SQLR%\include sqlr.cpp %SQLR%\lib\libsqlrclientwrapper.lib %SQLR%\lib\libsqlrclient.lib %SQLR%\lib\librudiments.lib
One note about the code. The SQL Relay API is database independent except for parameter placeholders and bindnames. Those use the convention of the database specific client library. The code above use some weird functions to generalize that. But if your code will only use one database or at least databases using the same style, then you can just hardcode placeholders and bindnames.
MySQL provide a native C API. The official name is MySQL Connector/C, but it is usually referred to as libmysql.
There are probbaly not that many C/C++ database applications using this API, but this API is the foundation for PHP's mysql and mysqli extensions, so a huge part of all PHP application indirectly use libmysql. And in fact the PHP API is very similar to the C API.
For PHP API see here.
| Names |
MySQL C API
MySQL Connector/C libmysql |
| History |
Part of MySQL from the beginning (1995)
Got statements in 2005 |
| Supported databases | MySQL |
| Supported platforms | All where MySQL is available (Linux, Windows, MacOS X, some Unix etc.) |
| Status | Actively maintained and critical for many (including PHP) |
The API is available in two flavors:
The difference is huge - it is really two completely different API's. The old API uses an escape function while the new API uses prepared statements and parameters.
Do not use the old API. Always use the new API. It is non-trivial to make code using the old API robust and secure.
Even though the API is a C API, then it can of course be used in C++ and the usage can be wrapped in OO style access.
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* required (can also be done by including OS specific header) */
#define SOCKET int
/* MySQL headers */
#include <mysql.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void con_exit(MYSQL *con)
{
printf("MySQL error: %s\n", mysql_error(con));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get connection */
MYSQL *get_connection(char *host, int port, char *un, char *pw, char *db)
{
MYSQL *con;
con = mysql_init(NULL);
if(con == NULL) con_exit(con);
con = mysql_real_connect(con, host, un, pw, db, port, NULL, 0);
if(con == NULL) con_exit(con);
return con;
}
/* get f1 given f2 */
int t1_get_one(MYSQL *con, char *f2)
{
MYSQL_RES *result;
MYSQL_ROW row;
char f2esc[2 * F2_MAX_LEN], sqlstr[40 + 2 * F2_MAX_LEN];
int stat, f1;
/* construct SQL */
mysql_real_escape_string(con, f2esc, f2, strlen(f2));
sprintf(sqlstr, "SELECT f1 FROM t1 WHERE f2 = '%s'", f2esc);
/* execute */
stat = mysql_query(con, sqlstr);
if(stat != 0) con_exit(con);
/* process result */
result = mysql_store_result(con);
if(result == NULL) con_exit(con);
if((row = mysql_fetch_row(result)))
{
f1 = atoi(row[0]);
mysql_free_result(result);
return f1;
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
}
/* get all records */
int t1_get_all(MYSQL *con, struct t1 *buf, int bufsiz)
{
MYSQL_RES *result;
MYSQL_ROW row;
int stat, count;
/* execute */
stat = mysql_query(con, "SELECT f1,f2 FROM t1");
if(stat != 0) con_exit(con);
/* process result */
result = mysql_store_result(con);
if(result == NULL) con_exit(con);
count = 0;
while((row = mysql_fetch_row(result)) && (count < bufsiz))
{
buf[count].f1 = atoi(row[0]);
strcpy(buf[count].f2, row[1]);
count++;
}
mysql_free_result(result);
return count;
}
/* add record */
void t1_put(MYSQL *con, int f1, char *f2)
{
char f2esc[2 * F2_MAX_LEN + 1], sqlstr[40 + 2 * F2_MAX_LEN];
int stat;
/* construct SQL */
mysql_real_escape_string(con, f2esc, f2, strlen(f2));
sprintf(sqlstr, "INSERT INTO t1 VALUES(%d, '%s')", f1, f2esc);
/* execute */
stat = mysql_query(con, sqlstr);
if(stat != 0) con_exit(con);
/* check if OK */
if(mysql_affected_rows(con) != 1)
{
other_exit("INSERT did not insert 1 row");
}
}
/* delete record */
void t1_remove(MYSQL *con, int f1)
{
char sqlstr[40 + 2 * F2_MAX_LEN];
int stat;
/* construct SQL */
sprintf(sqlstr, "DELETE FROM t1 WHERE f1 = %d", f1);
/* execute */
stat = mysql_query(con, sqlstr);
if(stat != 0) con_exit(con);
/* check if OK */
if(mysql_affected_rows(con) != 1)
{
other_exit("DELETE did not delete 1 row");
}
}
/* print all records */
void t1_dump(MYSQL *con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
int main()
{
MYSQL *con;
int f1;
/* get connection */
con = get_connection("localhost", 3306, "root", "", "Test");
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
/* close connection */
mysql_close(con);
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// required (can also be done by including OS specific header)
#define SOCKET int
// MySQL headers
#include <mysql.h>
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class OldMysql : public DB
{
private:
MYSQL *con;
void ConExit();
void OtherExit(const char *msg);
public:
// construct instance
OldMysql(const char *host, int port, const char *un, const char *pw, const char *db);
// destruct instance
virtual ~OldMysql();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void OldMysql::ConExit()
{
cout << "MySQL error: " << mysql_error(con) << endl;
exit(1);
}
void OldMysql::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
OldMysql::OldMysql(const char *host, int port, const char *un, const char *pw, const char *db)
{
con = mysql_init(NULL);
if(con == NULL) ConExit();
con = mysql_real_connect(con, host, un, pw, db, port, NULL, 0);
if(con == NULL) ConExit();
}
// destruct instance
OldMysql::~OldMysql()
{
mysql_close(con);
}
// get f1 given f2
int OldMysql::T1GetOne(const char *f2)
{
int stat;
// construct SQL
char f2esc[2 * F2_MAX_LEN], sqlstr[40 + 2 * F2_MAX_LEN];
mysql_real_escape_string(con, f2esc, f2, strlen(f2));
sprintf(sqlstr, "SELECT f1 FROM t1 WHERE f2 = '%s'", f2esc);
// execute
stat = mysql_query(con, sqlstr);
if(stat != 0) ConExit();
// process result
MYSQL_RES *result = mysql_store_result(con);
if(result == NULL) ConExit();
MYSQL_ROW row;
if((row = mysql_fetch_row(result)))
{
int f1 = atoi(row[0]);
mysql_free_result(result);
return f1;
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
}
// get all records
int OldMysql::T1GetAll(struct t1 *buf, int bufsiz)
{
int stat;
// execute
stat = mysql_query(con, "SELECT f1,f2 FROM t1");
if(stat != 0) ConExit();
// process result
MYSQL_RES *result = mysql_store_result(con);
if(result == NULL) ConExit();
int count = 0;
MYSQL_ROW row;
while((row = mysql_fetch_row(result)) && (count < bufsiz))
{
buf[count].f1 = atoi(row[0]);
strcpy(buf[count].f2, row[1]);
count++;
}
mysql_free_result(result);
return count;
}
// add record
void OldMysql::T1Put(int f1, const char *f2)
{
int stat;
// construct SQL
char f2esc[2 * F2_MAX_LEN + 1], sqlstr[40 + 2 * F2_MAX_LEN];
mysql_real_escape_string(con, f2esc, f2, strlen(f2));
sprintf(sqlstr, "INSERT INTO t1 VALUES(%d, '%s')", f1, f2esc);
// execute
stat = mysql_query(con, sqlstr);
if(stat != 0) ConExit();
// check if OK
if(mysql_affected_rows(con) != 1)
{
OtherExit("INSERT did not insert 1 row");
}
}
// delete record
void OldMysql::T1Remove(int f1)
{
int stat;
// construct SQL
char sqlstr[40 + 2 * F2_MAX_LEN];
sprintf(sqlstr, "DELETE FROM t1 WHERE f1 = %d", f1);
// execute
stat = mysql_query(con, sqlstr);
if(stat != 0) ConExit();
// check if OK
if(mysql_affected_rows(con) != 1)
{
OtherExit("DELETE did not delete 1 row");
}
}
int main()
{
// get database connection
DB *db = new OldMysql("localhost", 3306, "root", "", "Test");
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
// release database connection
delete db;
return 0;
}
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* required (can also be done by including OS specific header) */
#define SOCKET int
/* MySQL headers */
#include <mysql.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void con_exit(MYSQL *con)
{
printf("MySQL error: %s\n", mysql_error(con));
exit(1);
}
void stmt_exit(MYSQL_STMT *stmt)
{
printf("MySQL Statement error: %s\n", mysql_stmt_error(stmt));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get connection */
MYSQL *get_connection(char *host, int port, char *un, char *pw, char *db)
{
MYSQL *con;
con = mysql_init(NULL);
if(con == NULL) con_exit(con);
con = mysql_real_connect(con, host, un, pw, db, port, NULL, 0);
if(con == NULL) con_exit(con);
return con;
}
/* get f1 given f2 */
int t1_get_one(MYSQL *con, char *f2)
{
MYSQL_STMT *stmt;
MYSQL_BIND in[1];
MYSQL_BIND out[1];
char *sqlstr;
int stat, f1;
/* prepare statement */
stmt = mysql_stmt_init(con);
if(stmt == NULL) con_exit(con);
sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) stmt_exit(stmt);
/* bind parameters */
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_STRING;
in[0].buffer = f2;
in[0].buffer_length = strlen(f2);
in[0].is_null= 0;
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) stmt_exit(stmt);
/* execute */
stat = mysql_stmt_execute(stmt);
if(stat != 0) stmt_exit(stmt);
/* bind result */
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &f1;
out[0].buffer_length = sizeof(f1);
stat = mysql_stmt_bind_result(stmt, out);
if(stat != 0) stmt_exit(stmt);
/* process result */
stat = mysql_stmt_store_result(stmt);
if(stat != 0) stmt_exit(stmt);
if(!mysql_stmt_fetch(stmt))
{
mysql_stmt_free_result(stmt);
return f1;
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
}
/* get all records */
int t1_get_all(MYSQL *con, struct t1 *buf, int bufsiz)
{
MYSQL_STMT *stmt;
MYSQL_BIND out[2];
char *sqlstr, f2[F2_MAX_LEN + 1];
unsigned long int lenf2;
int stat, f1, count;
/* prepare statement */
stmt = mysql_stmt_init(con);
if(stmt == NULL) con_exit(con);
sqlstr = "SELECT f1,f2 FROM t1";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) stmt_exit(stmt);
/* execute */
stat = mysql_stmt_execute(stmt);
if(stat != 0) stmt_exit(stmt);
/* bind result */
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &f1;
out[0].buffer_length = sizeof(f1);
out[1].buffer_type = MYSQL_TYPE_STRING;
out[1].buffer = f2;
out[1].buffer_length = sizeof(f2);
out[1].length = &lenf2;
stat = mysql_stmt_bind_result(stmt, out);
if(stat != 0) stmt_exit(stmt);
/* process result */
stat = mysql_stmt_store_result(stmt);
if(stat != 0) stmt_exit(stmt);
count = 0;
while(!mysql_stmt_fetch(stmt) && (count < bufsiz))
{
buf[count].f1 = f1;
f2[lenf2] = 0;
strcpy(buf[count].f2, f2);
count++;
}
mysql_stmt_free_result(stmt);
return count;
}
/* add record */
void t1_put(MYSQL *con, int f1, char *f2)
{
MYSQL_STMT *stmt;
MYSQL_BIND in[2];
char *sqlstr;
int stat;
/* prepare statement */
stmt = mysql_stmt_init(con);
if(stmt == NULL) con_exit(con);
sqlstr = "INSERT INTO t1 VALUES(?, ?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) stmt_exit(stmt);
/* bind parameters */
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &f1;
in[0].buffer_length = sizeof(f1);
in[0].is_null= 0;
in[1].buffer_type = MYSQL_TYPE_STRING;
in[1].buffer = f2;
in[1].buffer_length = sizeof(f2);
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) stmt_exit(stmt);
/* execute */
stat = mysql_stmt_execute(stmt);
if(stat != 0) stmt_exit(stmt);
/* check if OK */
if(mysql_stmt_affected_rows(stmt) != 1)
{
other_exit("INSERT did not insert 1 row");
}
}
/* delete record */
void t1_remove(MYSQL *con, int f1)
{
MYSQL_STMT *stmt;
MYSQL_BIND in[1];
char *sqlstr;
int stat;
/* prepare statement */
stmt = mysql_stmt_init(con);
if(stmt == NULL) con_exit(con);
sqlstr = "DELETE FROM t1 WHERE f1 = ?";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) stmt_exit(stmt);
/* bind parameters */
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &f1;
in[0].buffer_length = sizeof(f1);
in[0].is_null= 0;
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) stmt_exit(stmt);
/* execute */
stat = mysql_stmt_execute(stmt);
if(stat != 0) stmt_exit(stmt);
/* check if OK */
if(mysql_stmt_affected_rows(stmt) != 1)
{
other_exit("DELETE did not delete 1 row");
}
}
/* print all records */
void t1_dump(MYSQL *con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
int main()
{
MYSQL *con;
int f1;
/* get connection */
con = get_connection("localhost", 3306, "root", "", "Test");
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
/* close connection */
mysql_close(con);
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// required (can also be done by including OS specific header)
#define SOCKET int
// MySQL headers
#include <mysql.h>
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class NewMysql : public DB
{
private:
MYSQL *con;
void ConExit();
void StmtExit(MYSQL_STMT *stmt);
void OtherExit(const char *msg);
public:
// construct instance
NewMysql(const char *host, int port, const char *un, const char *pw, const char *db);
// destruct instance
virtual ~NewMysql();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void NewMysql::ConExit()
{
cout << "MySQL error: " << mysql_error(con) << endl;
exit(1);
}
void NewMysql::StmtExit(MYSQL_STMT *stmt)
{
cout << "MySQL Statement error: " << mysql_stmt_error(stmt) << endl;
exit(1);
}
void NewMysql::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
NewMysql::NewMysql(const char *host, int port, const char *un, const char *pw, const char *db)
{
con = mysql_init(NULL);
if(con == NULL) ConExit();
con = mysql_real_connect(con, host, un, pw, db, port, NULL, 0);
if(con == NULL) ConExit();
}
// destruct instance
NewMysql::~NewMysql()
{
mysql_close(con);
}
// get f1 given f2
int NewMysql::T1GetOne(const char *f2)
{
int stat;
// prepare statement
MYSQL_STMT *stmt = mysql_stmt_init(con);
if(stmt == NULL) ConExit();
const char *sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) StmtExit(stmt);
// bind parameters
MYSQL_BIND in[1];
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_STRING;
in[0].buffer = (char *)f2;
in[0].buffer_length = strlen(f2);
in[0].is_null= 0;
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) StmtExit(stmt);
// execute
stat = mysql_stmt_execute(stmt);
if(stat != 0) StmtExit(stmt);
// bind result
int f1;
MYSQL_BIND out[1];
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &f1;
out[0].buffer_length = sizeof(f1);
stat = mysql_stmt_bind_result(stmt, out);
if(stat != 0) StmtExit(stmt);
// process result
stat = mysql_stmt_store_result(stmt);
if(stat != 0) StmtExit(stmt);
if(!mysql_stmt_fetch(stmt))
{
mysql_stmt_free_result(stmt);
return f1;
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
}
// get all records
int NewMysql::T1GetAll(struct t1 *buf, int bufsiz)
{
int stat;
// prepare statement
MYSQL_STMT *stmt = mysql_stmt_init(con);
if(stmt == NULL) ConExit();
const char *sqlstr = "SELECT f1,f2 FROM t1";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) StmtExit(stmt);
// execute
stat = mysql_stmt_execute(stmt);
if(stat != 0) StmtExit(stmt);
// bind result
char f2[F2_MAX_LEN + 1];
unsigned long int lenf2;
int f1;
MYSQL_BIND out[2];
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &f1;
out[0].buffer_length = sizeof(f1);
out[1].buffer_type = MYSQL_TYPE_STRING;
out[1].buffer = f2;
out[1].buffer_length = sizeof(f2);
out[1].length = &lenf2;
stat = mysql_stmt_bind_result(stmt, out);
if(stat != 0) StmtExit(stmt);
// process result
stat = mysql_stmt_store_result(stmt);
if(stat != 0) StmtExit(stmt);
int count = 0;
while(!mysql_stmt_fetch(stmt) && (count < bufsiz))
{
buf[count].f1 = f1;
f2[lenf2] = 0;
strcpy(buf[count].f2, f2);
count++;
}
mysql_stmt_free_result(stmt);
return count;
}
// add record
void NewMysql::T1Put(int f1, const char *f2)
{
int stat;
// prepare statement
MYSQL_STMT *stmt = mysql_stmt_init(con);
if(stmt == NULL) ConExit();
const char *sqlstr = "INSERT INTO t1 VALUES(?, ?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) StmtExit(stmt);
// bind parameters
MYSQL_BIND in[2];
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &f1;
in[0].buffer_length = sizeof(f1);
in[0].is_null= 0;
in[1].buffer_type = MYSQL_TYPE_STRING;
in[1].buffer = (char *)f2;
in[1].buffer_length = sizeof(f2);
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) StmtExit(stmt);
// execute
stat = mysql_stmt_execute(stmt);
if(stat != 0) StmtExit(stmt);
// check if OK
if(mysql_stmt_affected_rows(stmt) != 1)
{
OtherExit("INSERT did not insert 1 row");
}
}
// delete record
void NewMysql::T1Remove(int f1)
{
int stat;
// prepare statement
MYSQL_STMT *stmt = mysql_stmt_init(con);
if(stmt == NULL) ConExit();
const char *sqlstr = "DELETE FROM t1 WHERE f1 = ?";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
if(stat != 0) StmtExit(stmt);
// bind parameters
MYSQL_BIND in[1];
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &f1;
in[0].buffer_length = sizeof(f1);
in[0].is_null= 0;
stat = mysql_stmt_bind_param(stmt, in);
if(stat != 0) StmtExit(stmt);
// execute
stat = mysql_stmt_execute(stmt);
if(stat != 0) StmtExit(stmt);
// check if OK
if(mysql_stmt_affected_rows(stmt) != 1)
{
OtherExit("DELETE did not delete 1 row");
}
}
int main()
{
// get database connection
DB *db = new NewMysql("localhost", 3306, "root", "", "Test");
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
// release database connection
delete db;
return 0;
}
Windows GCC C build:
gcc -Wall -I%MYSQL%\include -L%MYSQL%\lib %1.c -lmysql -o %1.exe
Windows GCC C++ build:
g++ -Wall -I%MYSQL%\include -L%MYSQL%\lib %1.cpp -lmysql -o %1.exe
Windows MSVC++ C build:
cl /I%MYSQL%\include %1.c %MYSQL%\lib\libmysql.lib
Windows MSVC++ C++ build:
cl /EHsc /I%MYSQL%\include %1.cpp %MYSQL%\lib\libmysql.lib
Linux GCC C build:
gcc -Wall -I/usr/include/mysql -L/usr/lib64/mysql $1.c -lmysqlclient -o $1
Linux GCC C++ build:
g++ -Wall -I/usr/include/mysql -L/usr/lib64/mysql $1.cpp -lmysqlclient -o $1
OpenVMS C build:
$ cc /include=mysql051_root:[include]/names=as_is 'p1'
$ link 'p1'+mysql051_root:[vms.lib]mysqlclient_mixed/lib+mysql051_root:[vms.lib]libssl_mixed/lib+mysql051_root:[vms.lib]libcrypto_mixed/lib+libz:libz64_ev56/lib
MySQL has also made several attempts to create a true C++ API. The latest attempt is called MySQL Connector/C++ and is heavily inspired by Java JDBC API.
For Java JDBC API see here.
| Names |
MySQL C++ API
MySQL Connector/C++ |
| History | Relative new |
| Supported databases | MySQL |
| Supported platforms | All where MySQL is available (Linux, Windows, some Unix etc.) |
| Status | Not widely used |
// standard C++ headers
#include <iostream>
#include <cstdlib>
using namespace std;
// MySQL headers
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
using namespace sql;
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class OOMysql : public DB
{
private:
Connection *con;
void ExceptionExit(SQLException ex);
void OtherExit(const char *msg);
public:
// construct instance
OOMysql(const char *conurl, const char *un, const char *pw);
// destruct instance
virtual ~OOMysql();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void OOMysql::ExceptionExit(SQLException ex)
{
cout << "MySQL exception: " << ex.what() << endl;
exit(1);
}
void OOMysql::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
OOMysql::OOMysql(const char *conurl, const char *un, const char *pw)
{
try
{
Driver *driver = get_driver_instance();
con = driver->connect(conurl, un, pw);
}
catch(SQLException &ex)
{
ExceptionExit(ex);
}
}
// destruct instance
OOMysql::~OOMysql()
{
delete con;
}
// get f1 given f2
int OOMysql::T1GetOne(const char *f2)
{
try
{
// prepare statement
const char *sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
PreparedStatement *pstmt = con->prepareStatement(sqlstr);
// bind parameters and execute query
pstmt->setString(1, f2);
ResultSet *rs = pstmt->executeQuery();
// process result
int f1;
if(rs->next())
{
f1 = rs->getInt(1);
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
delete rs;
delete pstmt;
return f1;
}
catch(SQLException &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// get all records
int OOMysql::T1GetAll(struct t1 *buf, int bufsiz)
{
try
{
// get statement and execute query
const char *sqlstr = "SELECT f1,f2 FROM t1";
Statement *stmt = con->createStatement();
ResultSet *rs = stmt->executeQuery(sqlstr);
// process result
int count = 0;
while(rs->next() && (count < bufsiz))
{
buf[count].f1 = rs->getInt(1);
strcpy(buf[count].f2, rs->getString(2).c_str());
count++;
}
delete rs;
delete stmt;
return count;
}
catch(SQLException &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// add record
void OOMysql::T1Put(int f1, const char *f2)
{
try
{
// prepare statement
const char *sqlstr = "INSERT INTO t1 VALUES(?, ?)";
PreparedStatement *pstmt = con->prepareStatement(sqlstr);
// bind parameters and execute query
pstmt->setInt(1, f1);
pstmt->setString(2, f2);
int n = pstmt->executeUpdate();
// check if OK
if(n != 1)
{
OtherExit("INSERT did not insert 1 row");
}
delete pstmt;
}
catch(SQLException &ex)
{
ExceptionExit(ex);
}
}
// delete record
void OOMysql::T1Remove(int f1)
{
try
{
// prepare statement
const char *sqlstr = "DELETE FROM t1 WHERE f1 = ?";
PreparedStatement *pstmt = con->prepareStatement(sqlstr);
// bind parameters and execute query
pstmt->setInt(1, f1);
int n = pstmt->executeUpdate();
// check if OK
if(n != 1)
{
OtherExit("DELETE did not delete 1 row");
}
delete pstmt;
}
catch(SQLException &ex)
{
ExceptionExit(ex);
}
}
int main()
{
// get database connection
DB *db = new OOMysql("tcp://localhost:3306/Test", "root", "");
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
// release database connection
delete db;
return 0;
}
Windows MSVC++ C++ build:
cl /EHsc /I%MYSQLPLUS%\include /I%BOOST% %1.cpp %MYSQLPLUS%\lib\mysqlcppconn.lib
Note that MySQL Connector/C++ requires Boost library.
PostgreSQL provide a native C API called LIBPQ.
| Names |
PostgreSQL C API
libpq |
| History | Part of PostgreSQL from the beginning (1996) |
| Supported databases | PostgreSQL |
| Supported platforms | All where PostgreSQL is available (Linux, Windows, macOS, some Unix etc.) |
| Status | Actively maintained |
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* OS specific headers */
#ifdef _WIN32
#include <winsock.h>
#endif
#ifdef _unix
#include <arpa/inet.h>
#endif
/* PgSQL headers */
#include <libpq-fe.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void con_exit(PGconn *con)
{
printf("PostgreSQL error: %s\n", PQerrorMessage(con));
exit(1);
}
void res_exit(PGresult *res)
{
printf("PostgreSQL error: %s\n", PQresultErrorMessage(res));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get connection */
PGconn *get_connection(const char *constr)
{
PGconn *con;
con = PQconnectdb(constr);
if (PQstatus(con) != CONNECTION_OK) con_exit(con);
return con;
}
#define TXTFMT 0
#define BINFMT 1
/* get f1 given f2 */
int t1_get_one(PGconn *con, char *f2)
{
PGresult *res;
char *sqlstr;
int f1;
const char *paramval[1];
int paramlen[1];
int paramfmt[1];
/* prepare */
sqlstr = "SELECT f1 FROM t1 WHERE f2 = $1::text";
res = PQprepare(con, "stmt_get_one", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
PQclear(res);
/* execute and process result */
paramval[0] = f2;
paramlen[0] = strlen(f2);
paramfmt[0] = BINFMT;
res = PQexecPrepared(con, "stmt_get_one", 1, paramval, paramlen, paramfmt, BINFMT);
if(PQresultStatus(res) != PGRES_TUPLES_OK) res_exit(res);
if(PQntuples(res) > 0)
{
f1 = ntohl(*((int *)PQgetvalue(res, 0, 0)));
PQclear(res);
PQexec(con, "DEALLOCATE stmt_get_one");
return f1;
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
}
/* get all records */
int t1_get_all(PGconn *con, struct t1 *buf, int bufsiz)
{
PGresult *res;
char *sqlstr;
int count, i;
/* prepare */
sqlstr = "SELECT f1,f2 FROM t1";
res = PQprepare(con, "stmt_get_all", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
PQclear(res);
/* execute and process result */
res = PQexecPrepared(con, "stmt_get_all", 0, NULL, NULL, NULL, BINFMT);
if(PQresultStatus(res) != PGRES_TUPLES_OK) res_exit(res);
count = PQntuples(res);
for(i = 0; i < count && i < bufsiz; i++)
{
buf[i].f1 = ntohl(*((int *)PQgetvalue(res, i, 0)));
strcpy(buf[i].f2, PQgetvalue(res, i, 1));
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_get_all");
/* */
return count;
}
/* add record */
void t1_put(PGconn *con, int f1, char *f2)
{
PGresult *res;
char *sqlstr;
const char *paramval[2];
int paramlen[2];
int paramfmt[2];
int temp;
/* prepare */
sqlstr = "INSERT INTO t1 VALUES($1::int, $2::text)";
res = PQprepare(con, "stmt_put", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
PQclear(res);
/* execute and check if OK */
temp = htonl(f1);
paramval[0] = (char *)&temp;
paramlen[0] = sizeof(temp);
paramfmt[0] = BINFMT;
paramval[1] = f2;
paramlen[1] = strlen(f2);
paramfmt[1] = BINFMT;
res = PQexecPrepared(con, "stmt_put", 2, paramval, paramlen, paramfmt, 0);
if(PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
if(strcmp(PQcmdTuples(res), "1") != 0)
{
other_exit("INSERT did not insert 1 row");
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_put");
}
/* delete record */
void t1_remove(PGconn *con, int f1)
{
PGresult *res;
char *sqlstr;
const char *paramval[2];
int paramlen[2];
int paramfmt[2];
int temp;
/* prepare */
sqlstr = "DELETE FROM t1 WHERE f1 = $1::int";
res = PQprepare(con, "stmt_remove", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
PQclear(res);
/* execute and check if OK */
temp = htonl(f1);
paramval[0] = (char *)&temp;
paramlen[0] = sizeof(temp);
paramfmt[0] = BINFMT;
res = PQexecPrepared(con, "stmt_remove", 1, paramval, paramlen, paramfmt, 0);
if(PQresultStatus(res) != PGRES_COMMAND_OK) res_exit(res);
if(strcmp(PQcmdTuples(res), "1") != 0)
{
other_exit("DELETE did not delete 1 row");
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_remove");
}
/* print all records */
void t1_dump(PGconn *con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
int main()
{
PGconn *con;
int f1;
/* get connection */
con = get_connection("host=localhost port=5432 dbname=Test user=postgres password=xxxxxx");
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
/* close connection */
PQfinish(con);
return 0;
}
Windows GCC build:
gcc -Wall -I"%PGSQL%\include" pgsql.c -o pgsql.exe "%PGSQL%\lib\libpq.lib" -lws2_32
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
/* OS specific headers */
#ifdef _WIN32
#include <winsock.h>
#endif
#ifdef _unix
#include <arpa/inet.h>
#endif
/* PgSQL headers */
#include <libpq-fe.h>
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
const int TXTFMT = 0;
const int BINFMT = 1;
class Pgsql : public DB
{
private:
PGconn *con;
void ConExit();
void ResExit(PGresult *res);
void OtherExit(const char *msg);
public:
// construct instance
Pgsql(const char *constr);
// destruct instance
virtual ~Pgsql();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void Pgsql::ConExit()
{
cout << "PostgreSQL error: " << PQerrorMessage(con) << endl;;
exit(1);
}
void Pgsql::ResExit(PGresult *res)
{
cout << "PostgreSQL error: " << PQresultErrorMessage(res) << endl;
exit(1);
}
void Pgsql::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
Pgsql::Pgsql(const char *constr)
{
con = PQconnectdb(constr);
if (PQstatus(con) != CONNECTION_OK) ConExit();
}
// destruct instance
Pgsql::~Pgsql()
{
PQfinish(con);
}
// get f1 given f2
int Pgsql::T1GetOne(const char *f2)
{
PGresult *res;
const char *sqlstr;
int f1;
const char *paramval[1];
int paramlen[1];
int paramfmt[1];
/* prepare */
sqlstr = "SELECT f1 FROM t1 WHERE f2 = $1::text";
res = PQprepare(con, "stmt_get_one", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
PQclear(res);
/* execute and process result */
paramval[0] = f2;
paramlen[0] = strlen(f2);
paramfmt[0] = BINFMT;
res = PQexecPrepared(con, "stmt_get_one", 1, paramval, paramlen, paramfmt, BINFMT);
if(PQresultStatus(res) != PGRES_TUPLES_OK) ResExit(res);
if(PQntuples(res) > 0)
{
f1 = ntohl(*((int *)PQgetvalue(res, 0, 0)));
PQclear(res);
PQexec(con, "DEALLOCATE stmt_get_one");
return f1;
}
else
{
OtherExit("Row not found");
return 0; /* will never be reached */
}
}
// get all records
int Pgsql::T1GetAll(struct t1 *buf, int bufsiz)
{
PGresult *res;
const char *sqlstr;
int count, i;
/* prepare */
sqlstr = "SELECT f1,f2 FROM t1";
res = PQprepare(con, "stmt_get_all", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
PQclear(res);
/* execute and process result */
res = PQexecPrepared(con, "stmt_get_all", 0, NULL, NULL, NULL, BINFMT);
if(PQresultStatus(res) != PGRES_TUPLES_OK) ResExit(res);
count = PQntuples(res);
for(i = 0; i < count && i < bufsiz; i++)
{
buf[i].f1 = ntohl(*((int *)PQgetvalue(res, i, 0)));
strcpy(buf[i].f2, PQgetvalue(res, i, 1));
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_get_all");
/* */
return count;
}
// add record
void Pgsql::T1Put(int f1, const char *f2)
{
PGresult *res;
const char *sqlstr;
const char *paramval[2];
int paramlen[2];
int paramfmt[2];
int temp;
/* prepare */
sqlstr = "INSERT INTO t1 VALUES($1::int, $2::text)";
res = PQprepare(con, "stmt_put", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
PQclear(res);
/* execute and check if OK */
temp = htonl(f1);
paramval[0] = (char *)&temp;
paramlen[0] = sizeof(temp);
paramfmt[0] = BINFMT;
paramval[1] = f2;
paramlen[1] = strlen(f2);
paramfmt[1] = BINFMT;
res = PQexecPrepared(con, "stmt_put", 2, paramval, paramlen, paramfmt, 0);
if(PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
if(strcmp(PQcmdTuples(res), "1") != 0)
{
OtherExit("INSERT did not insert 1 row");
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_put");
}
// delete record
void Pgsql::T1Remove(int f1)
{
PGresult *res;
const char *sqlstr;
const char *paramval[2];
int paramlen[2];
int paramfmt[2];
int temp;
/* prepare */
sqlstr = "DELETE FROM t1 WHERE f1 = $1::int";
res = PQprepare(con, "stmt_remove", sqlstr, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
PQclear(res);
/* execute and check if OK */
temp = htonl(f1);
paramval[0] = (char *)&temp;
paramlen[0] = sizeof(temp);
paramfmt[0] = BINFMT;
res = PQexecPrepared(con, "stmt_remove", 1, paramval, paramlen, paramfmt, 0);
if(PQresultStatus(res) != PGRES_COMMAND_OK) ResExit(res);
if(strcmp(PQcmdTuples(res), "1") != 0)
{
OtherExit("DELETE did not delete 1 row");
}
PQclear(res);
PQexec(con, "DEALLOCATE stmt_remove");
}
int main()
{
// get database connection
DB *db = new Pgsql("host=localhost port=5432 dbname=Test user=postgres password=xxxxxx");
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
// release database connection
delete db;
return 0;
}
Windows GCC build:
g++ -Wall -I"%PGSQL%\include" pgsql.cpp -o pgsql.exe "%PGSQL%\lib\libpq.lib" -lws2_32
Oracle provides OCI (Oracle Call Interface) C API and OCCI (Oracle C++ Call Interface) C++ API.
An open source librray LIBOCI with both C and C++ API on top of OCI is available here.
Oracle introduced HLI (Host Language Interface) C API in 1988 and it was later renamed to OCI. Oracle added OCCI on top of of OCI in 2001.
Vincent Rogier started open source LIBOCI project in 2007.
| Names |
OCI
OCCI LIBOCI |
| History |
HLI / OCI 1988
OCCI 2001 LIBOCI 2007 |
| Supported databases |
Oracle DB
Oracle Rdb |
| Supported platforms | All where Oracle DB is available (mainframe, Unix, Linux, Windows) |
| Status | Still widely used in Oracle world |
I will show examples in LIBOCI. LIBOCI API is easier to use than Oracle OCI API.
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* LIBOCI headers */
#define OCI_API __stdcall
#include <ocilib.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void lib_exit(OCI_Error *err)
{
printf("SQL error: %d - %s (%s)\n", OCI_ErrorGetOCICode(err), OCI_ErrorGetString(err), OCI_GetSql(OCI_ErrorGetStatement(err)));
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get f1 given f2 */
int t1_get_one(OCI_Connection *con, char *f2)
{
OCI_Statement *stmt;
OCI_Resultset *rs;
char *sqlstr;
int f1;
/* get statement */
sqlstr = "SELECT f1,f2 FROM t1 WHERE f2 = :f2";
stmt = OCI_StatementCreate(con);
/* prepare */
OCI_Prepare(stmt, sqlstr);
/* bind parameters */
OCI_BindString(stmt, ":f2", f2, strlen(f2));
/* execute */
OCI_Execute(stmt);
/* process result */
rs = OCI_GetResultset(stmt);
if (OCI_FetchNext(rs))
{
f1 = OCI_GetInt(rs, 1);
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
/* close statement */
OCI_StatementFree(stmt);
return f1;
}
/* get all records */
int t1_get_all(OCI_Connection *con, struct t1 *buf, int bufsiz)
{
OCI_Statement *stmt;
OCI_Resultset *rs;
char *sqlstr;
int count;
/* get statement */
sqlstr = "SELECT f1,f2 FROM t1";
stmt = OCI_StatementCreate(con);
/* execute */
OCI_ExecuteStmt(stmt, sqlstr);
/* process result */
rs = OCI_GetResultset(stmt);
count = 0;
while (OCI_FetchNext(rs) && count < bufsiz)
{
buf[count].f1 = OCI_GetInt(rs, 1);
strcpy(buf[count].f2, OCI_GetString(rs, 2));
count++;
}
/* close statement */
OCI_StatementFree(stmt);
return count;
}
/* add record */
void t1_put(OCI_Connection *con, int f1, char *f2)
{
OCI_Statement *stmt;
char *sqlstr;
/* get statement */
sqlstr = "INSERT INTO t1 VALUES(:f1, :f2)";
stmt = OCI_StatementCreate(con);
/* prepare */
OCI_Prepare(stmt, sqlstr);
/* bind parameters */
OCI_BindInt(stmt, ":f1", &f1);
OCI_BindString(stmt, ":f2", f2, strlen(f2));
/* execute */
OCI_Execute(stmt);
/* check if OK */
if(OCI_GetAffectedRows(stmt) != 1)
{
other_exit("INSERT did not insert 1 row");
}
/* close statement */
OCI_StatementFree(stmt);
}
/* delete record */
void t1_remove(OCI_Connection *con, int f1)
{
OCI_Statement *stmt;
char *sqlstr;
/* get statement */
sqlstr = "DELETE FROM t1 WHERE f1 = :f1";
stmt = OCI_StatementCreate(con);
/* prepare */
OCI_Prepare(stmt, sqlstr);
/* bind parameters */
OCI_BindInt(stmt, ":f1", &f1);
/* execute */
OCI_Execute(stmt);
/* check if OK */
if(OCI_GetAffectedRows(stmt) != 1)
{
other_exit("DELETE did not delete 1 row");
}
/* close statement */
OCI_StatementFree(stmt);
}
/* print all records */
void t1_dump(OCI_Connection *con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(OCI_Connection *con)
{
int f1;
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
}
void test_oci(char *dbnam, char *usr, char *pw)
{
OCI_Connection *con;
/* get connection */
con = OCI_ConnectionCreate(dbnam, usr, pw, OCI_SESSION_DEFAULT);
/* test */
test(con);
/* close connection */
OCI_ConnectionFree(con);
}
int main()
{
OCI_Initialize(lib_exit, NULL, OCI_ENV_THREADED);
test_oci("XE", "arne", "xxxxxx");
OCI_Cleanup();
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// LIBOCI headers
#define OCI_API __stdcall
#include <ocilib.h>
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class LibOci : public DB
{
private:
OCI_Connection *con;
public:
// construct instance
LibOci(const char *db, const char *usr, const char *pw);
// destruct instance
virtual ~LibOci();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void LibExit(OCI_Error *err)
{
cout << "SQL error: " << OCI_ErrorGetOCICode(err) << " - " << OCI_ErrorGetString(err) << " (" << OCI_GetSql(OCI_ErrorGetStatement(err)) << ")" << endl;
exit(1);
}
void OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
LibOci::LibOci(const char *db, const char *usr, const char *pw)
{
con = OCI_ConnectionCreate(db, usr, pw, OCI_SESSION_DEFAULT);
}
// destruct instance
LibOci::~LibOci()
{
OCI_ConnectionFree(con);
}
// get f1 given f2
int LibOci::T1GetOne(const char *f2)
{
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1 WHERE f2 = :f2";
OCI_Statement *stmt = OCI_StatementCreate(con);
// prepare
OCI_Prepare(stmt, sqlstr);
// bind parameters
OCI_BindString(stmt, ":f2", (char *)f2, strlen(f2));
// execute
OCI_Execute(stmt);
// process result
int f1;
OCI_Resultset *rs = OCI_GetResultset(stmt);
if (OCI_FetchNext(rs))
{
f1 = OCI_GetInt(rs, 1);
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
// close statement
OCI_StatementFree(stmt);
return f1;
}
// get all records
int LibOci::T1GetAll(struct t1 *buf, int bufsiz)
{
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1";
OCI_Statement *stmt = OCI_StatementCreate(con);
// execute
OCI_ExecuteStmt(stmt, sqlstr);
// process result
OCI_Resultset *rs = OCI_GetResultset(stmt);
int count = 0;
while (OCI_FetchNext(rs) && count < bufsiz)
{
buf[count].f1 = OCI_GetInt(rs, 1);
strcpy(buf[count].f2, OCI_GetString(rs, 2));
count++;
}
// close statement
OCI_StatementFree(stmt);
return count;
}
// add record
void LibOci::T1Put(int f1, const char *f2)
{
// get statement
const char *sqlstr = "INSERT INTO t1 VALUES(:f1, :f2)";
OCI_Statement *stmt = OCI_StatementCreate(con);
// prepare
OCI_Prepare(stmt, sqlstr);
// bind parameters
OCI_BindInt(stmt, ":f1", &f1);
OCI_BindString(stmt, ":f2", (char *)f2, strlen(f2));
// execute
OCI_Execute(stmt);
// check if OK
if(OCI_GetAffectedRows(stmt) != 1)
{
OtherExit("INSERT did not insert 1 row");
}
// close statement
OCI_StatementFree(stmt);
}
// delete record
void LibOci::T1Remove(int f1)
{
// get statement
const char *sqlstr = "DELETE FROM t1 WHERE f1 = :f1";
OCI_Statement *stmt = OCI_StatementCreate(con);
// prepare
OCI_Prepare(stmt, sqlstr);
// bind parameters
OCI_BindInt(stmt, ":f1", &f1);
// execute
OCI_Execute(stmt);
// check if OK
if(OCI_GetAffectedRows(stmt) != 1)
{
OtherExit("DELETE did not delete 1 row");
}
// close statement
OCI_StatementFree(stmt);
}
void test(DB *db)
{
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
}
void test_dsnless(const char *constr)
{
cout << "Connection string=" << constr << endl;;
}
void test_oci(const char *dbnam, const char *usr, const char *pw)
{
// get database connection
DB *db = new LibOci(dbnam, usr, pw);
// test
test(db);
// release database connection
delete db;
}
int main()
{
OCI_Initialize(LibExit, NULL, OCI_ENV_THREADED);
test_oci("XE", "arne", "xxxxxx");
OCI_Cleanup();
return 0;
}
// standard C++ headers
#include <iostream>
#include <cstdlib>
#include <cstring>
using namespace std;
// LIBOCI headers
#define OCI_API __stdcall
#include <ocilib.hpp>
using namespace ocilib;
static const int F2_MAX_LEN = 50;
static const int MAX_REC = 100;
// table structure
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
class DB
{
public:
// destruct instance
virtual ~DB() { };
// get f1 given f2
virtual int T1GetOne(const char *f2) = 0;
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz) = 0;
// add record
virtual void T1Put(int f1, const char *f2) = 0;
// delete record
virtual void T1Remove(int f1) = 0;
// print all records
void T1Dump();
};
// print all records
void DB::T1Dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = T1GetAll(buf, MAX_REC);
for(i = 0; i < n; i++)
{
cout << buf[i].f1 << " " << buf[i].f2 << endl;
}
}
class LibOci : public DB
{
private:
Connection con;
void ExceptionExit(Exception ex);
void OtherExit(const char *msg);
public:
// construct instance
LibOci(const char *db, const char *usr, const char *pw);
// destruct instance
virtual ~LibOci();
// get f1 given f2
virtual int T1GetOne(const char *f2);
// get all records
virtual int T1GetAll(struct t1 *buf, int bufsiz);
// add record
virtual void T1Put(int f1, const char *f2);
// delete record
virtual void T1Remove(int f1);
};
// error handling functions
void LibOci::ExceptionExit(Exception ex)
{
cout << "SQL error: " << ex.what() << endl;
exit(1);
}
void LibOci::OtherExit(const char *msg)
{
cout << msg << endl;
exit(1);
}
// construct instance
LibOci::LibOci(const char *db, const char *usr, const char *pw)
{
con.Open(db, usr, pw, Environment::SessionDefault);
}
// destruct instance
LibOci::~LibOci()
{
}
// get f1 given f2
int LibOci::T1GetOne(const char *f2)
{
try
{
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1 WHERE f2 = :f2";
Statement stmt(con);
// prepare
stmt.Prepare(sqlstr);
// bind parameters
ostring xf2(f2);
stmt.Bind(":f2", xf2, xf2.length(), BindInfo::In);
// execute
stmt.ExecutePrepared();
// process result
int f1;
Resultset rs = stmt.GetResultset();
if (rs++)
{
f1 = rs.Get<int>(1);
}
else
{
OtherExit("Row not found");
return 0; // will never be reached
}
return f1;
}
catch (Exception &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// get all records
int LibOci::T1GetAll(struct t1 *buf, int bufsiz)
{
try
{
// get statement
const char *sqlstr = "SELECT f1,f2 FROM t1";
Statement stmt(con);
// execute
stmt.Execute(sqlstr);
// process result
Resultset rs = stmt.GetResultset();
int count = 0;
while (rs++ && count < bufsiz)
{
buf[count].f1 = rs.Get<int>(1);
strcpy(buf[count].f2, rs.Get<ostring>(2).c_str());
count++;
}
return count;
}
catch (Exception &ex)
{
ExceptionExit(ex);
return 0; // will never be reached
}
}
// add record
void LibOci::T1Put(int f1, const char *f2)
{
try
{
// get statement
const char *sqlstr = "INSERT INTO t1 VALUES(:f1, :f2)";
Statement stmt(con);
// prepare
stmt.Prepare(sqlstr);
// bind parameters
stmt.Bind(":f1", f1, BindInfo::In);
ostring xf2(f2);
stmt.Bind(":f2", xf2, xf2.length(), BindInfo::In);
// execute
stmt.ExecutePrepared();
// check if OK
if(stmt.GetAffectedRows() != 1)
{
OtherExit("INSERT did not insert 1 row");
}
}
catch (Exception &ex)
{
ExceptionExit(ex);
}
}
// delete record
void LibOci::T1Remove(int f1)
{
try
{
// get statement
const char *sqlstr = "DELETE FROM t1 WHERE f1 = :f1";
Statement stmt(con);
// prepare
stmt.Prepare(sqlstr);
// bind parameters
stmt.Bind(":f1", f1, BindInfo::In);
// execute
stmt.ExecutePrepared();
// check if OK
if(stmt.GetAffectedRows() != 1)
{
OtherExit("DELETE did not delete 1 row");
}
}
catch (Exception &ex)
{
ExceptionExit(ex);
}
}
void test(DB *db)
{
// test T1GetOne
int f1 = db->T1GetOne("BB");
cout << f1 << endl;
// test T1GetAll
db->T1Dump();
// test t1_put
db->T1Put(999, "XXX");
db->T1Dump();
// test t1_remove
db->T1Remove(999);
db->T1Dump();
}
void test_dsnless(const char *constr)
{
cout << "Connection string=" << constr << endl;;
}
void test_oci(const char *dbnam, const char *usr, const char *pw)
{
// get database connection
DB *db = new LibOci(dbnam, usr, pw);
// test
test(db);
// release database connection
delete db;
}
int main()
{
Environment::Initialize(Environment::Default | Environment::Threaded);
test_oci("XE", "arne", "xxxxxx");
Environment::Cleanup();
return 0;
}
Windows GCC C build:
gcc -m32 -Wall -I%LIBOCI%\include -L%LIBOCI%\lib32 %1.c -lociliba -o %1.exe
Windows GCC C++ build:
g++ -m32 -Wall -I%LIBOCI%\include -L%LIBOCI%\lib32 %1.cpp -lociliba -o %1.exe
Oracle Rdb (not to be confused with Oracle DB) has a SQL Services API.
| Names | |
| History | Part of Rdb since 1992 |
| Supported databases | Oracle Rdb |
| Supported platforms | VMS, Windows, Linux, HP-UX |
| Status | Supported but practically unused |
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* Rdb SQLSRV headers */
#include <sqlsrv.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void con_exit(ASSOCIATE_ID con, long int stat)
{
short int buflen;
char buf[1000];
sqlsrv_sqlca_error_text(con, &buflen, buf, sizeof(buf));
buf[buflen] = 0;
printf("SQL error: %ld - %s\n", stat,buf);
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get f1 given f2 */
int t1_get_one(ASSOCIATE_ID con, char *f2)
{
long int stmt;
SQLDA_ID param_sqlda;
SQLDA_ID select_sqlda;
long int f1;
char *sqlstr, *qnam;
long int stat;
short int nullval;
char tmp[1000];
unsigned short int tmplen;
/* prepare statement */
sqlstr = "SELECT f1 FROM t1 WHERE f2 = ?";
select_sqlda = NULL;
param_sqlda = NULL;
stat = sqlsrv_prepare(con, 0, sqlstr, &stmt, ¶m_sqlda, &select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_allocate_sqlda_data(con, param_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_allocate_sqlda_data(con, select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* execute statement and open cursor */
*((short int *)tmp) = strlen(f2);
strcpy(tmp + 2, f2);
nullval = 0;
stat = sqlsrv_sqlda_set_data(param_sqlda, 0, 0, (CHARPTR)&tmp[0], 2 + strlen(f2), nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
qnam = "Qone";
stat = sqlsrv_open_cursor(con, qnam, stmt, param_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* process result */
stat = sqlsrv_fetch(con, qnam, 0, 0, select_sqlda);
if(stat != SQL_SUCCESS)
{
other_exit("Row not found");
return 0; /* will never be reached */
}
stat = sqlsrv_sqlda_get_data(select_sqlda, 0, 0, (CHARPTR)&tmp[0], sizeof(tmp), &nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
f1 = atoi(tmp);
/* close cursor and statement */
sqlsrv_close_cursor(con, qnam);
sqlsrv_free_sqlda_data(con, param_sqlda);
sqlsrv_free_sqlda_data(con, select_sqlda);
sqlsrv_release_statement(con, 1, &stmt);
return f1;
}
/* get all records */
int t1_get_all(ASSOCIATE_ID con, struct t1 *buf, int bufsiz)
{
long int stmt;
SQLDA_ID param_sqlda;
SQLDA_ID select_sqlda;
long int f1;
char *sqlstr, *qnam, f2[F2_MAX_LEN + 1];
long int stat;
int count;
short int nullval;
char tmp[1000];
unsigned short int tmplen;
/* prepare statement */
sqlstr = "SELECT f1,f2 FROM t1";
select_sqlda = NULL;
param_sqlda = NULL;
stat = sqlsrv_prepare(con, 0, sqlstr, &stmt, ¶m_sqlda, &select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_allocate_sqlda_data(con, select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* execute statement and open cursor */
qnam = "Qall";
stat = sqlsrv_open_cursor(con, qnam, stmt, param_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* process result */
count = 0;
for(;;)
{
stat = sqlsrv_fetch(con, qnam, 0, 0, select_sqlda);
if(stat != SQL_SUCCESS) break;
stat = sqlsrv_sqlda_get_data(select_sqlda, 0, 0, (CHARPTR)&tmp[0], sizeof(tmp), &nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
f1 = atoi(tmp);
stat = sqlsrv_sqlda_get_data(select_sqlda, 1, 0, (CHARPTR)&tmp[0], sizeof(tmp), &nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
strcpy(f2, tmp + 2);
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
/* close cursor and statement */
sqlsrv_close_cursor(con, qnam);
sqlsrv_free_sqlda_data(con, select_sqlda);
sqlsrv_release_statement(con, 1, &stmt);
return count;
}
/* add record */
void t1_put(ASSOCIATE_ID con, int f1, char *f2)
{
long int stmt;
SQLDA_ID param_sqlda;
SQLDA_ID select_sqlda;
char *sqlstr;
long int stat;
int n;
short int nullval;
char tmp[1000];
unsigned short int tmplen;
/* prepare statement */
sqlstr = "INSERT INTO t1 VALUES(?, ?)";
select_sqlda = NULL;
param_sqlda = NULL;
stat = sqlsrv_prepare(con, 0, sqlstr, &stmt, ¶m_sqlda, &select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_allocate_sqlda_data(con, param_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* execute statement */
sprintf(tmp, "%d", f1);
nullval = 0;
stat = sqlsrv_sqlda_set_data(param_sqlda, 0, 0, (CHARPTR)&tmp[0], strlen(tmp), nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
*((short int *)tmp) = strlen(f2);
strcpy(tmp + 2, f2);
nullval = 0;
stat = sqlsrv_sqlda_set_data(param_sqlda, 1, 0, (CHARPTR)&tmp[0], 2 + strlen(f2), nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_execute_in_out(con, 0, stmt, SQLSRV_EXE_W_DATA, param_sqlda, select_sqlda);
if(stat != SQL_SUCCESS && stat != SQL_EOS)
{
con_exit(con, stat);
}
/* check if OK */
n = sqlsrv_sqlca_count(con);
if(n != 1)
{
other_exit("INSERT did not insert 1 row");
}
/* close cursor and statement */
sqlsrv_free_sqlda_data(con, param_sqlda);
sqlsrv_release_statement(con, 1, &stmt);
}
/* delete record */
void t1_remove(ASSOCIATE_ID con, int f1)
{
long int stmt;
SQLDA_ID param_sqlda;
SQLDA_ID select_sqlda;
char *sqlstr;
long int stat;
int n;
short int nullval;
char tmp[1000];
unsigned short int tmplen;
/* prepare statement */
sqlstr = "DELETE FROM t1 WHERE f1 = ?";
select_sqlda = NULL;
param_sqlda = NULL;
stat = sqlsrv_prepare(con, 0, sqlstr, &stmt, ¶m_sqlda, &select_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_allocate_sqlda_data(con, param_sqlda);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
/* execute statement */
sprintf(tmp, "%d", f1);
nullval = 0;
stat = sqlsrv_sqlda_set_data(param_sqlda, 0, 0, (CHARPTR)&tmp[0], strlen(tmp), nullval, &tmplen);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_execute_in_out(con, 0, stmt, SQLSRV_EXE_W_DATA, param_sqlda, select_sqlda);
if(stat != SQL_SUCCESS && stat != SQL_EOS)
{
con_exit(con, stat);
}
/* check if OK */
n = sqlsrv_sqlca_count(con);
if(n != 1)
{
other_exit("DELETE did not delete 1 row");
}
/* close cursor and statement */
sqlsrv_free_sqlda_data(con, param_sqlda);
sqlsrv_release_statement(con, 1, &stmt);
}
/* print all records */
void t1_dump(ASSOCIATE_ID con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
void test(ASSOCIATE_ID con)
{
int f1;
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
}
int main()
{
ASSOCIATE_ID con;
ASSOCIATE_STR coninf;
long int stat;
/* get connection */
memset(&coninf, 0, sizeof(coninf));
coninf.VERSION = SQLSRV_V730;
coninf.xpttyp = SQLSRV_XPT_TCPIP ;
stat = sqlsrv_associate("arne1.vajhoej.dk", "arne", "hemmeligt", NULL, NULL, 0, 0, NULL, &coninf, &con);
if(stat != SQL_SUCCESS)
{
con_exit(con, stat);
}
stat = sqlsrv_execute_immediate(con, 0, "ATTACH 'FILENAME disk4:[rdb]test'");
/* test */
test(con);
/* close connection */
sqlsrv_release(con, NULL);
return 0;
}
VMS build:
$ cc sqlsrv
$ link sqlsrv + sys$library:sqlsrv$api/opt
Windows MSVC++ build:
cl /I"%SQLSRV%" sqlsrv.c "%SQLSRV%\SQSAPI64.LIB"
Linux build:
gcc -I. sqlsrv.c libsqs.a -o sqlsrv
The DBLIB API was created by Sybase and inherited by Microsoft. Both has since then deprecated/dropped it.
Today there is also an open source implementation called FreeTDS.
One characteristics is rather unique for DBLIB. It is not just a client library but also a server library. It supports writing a server application that can be accessed via any client talking TDS including ODBC drivers, ADO.NET providers, JDBC drivers, DBLIB etc..
| Names | |
| History |
Part of Sybase from the beginning (1986)
Part of MS SQLServer from beginning (1989) Deprecated by Sybase in 1993 for CTLIB Dropped by MS in SQLServer 2005 |
| Supported databases |
Sybase / Sybase ASE
MS SQLServer |
| Supported platforms |
Sybase: several Unix, Linux, Windows
Microsoft: Windows |
| Status | Today its usage is rare |
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* DBLIB headers */
#include <sybfront.h>
#include <sybdb.h>
#include <syberror.h>
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void tds_exit(char *func)
{
printf("TDS error in %s\n", func);
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
static int err_handler(DBPROCESS *con, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
if(severity > EXINFO)
{
printf("TDS DBLIB error: %s\n", dberrstr);
if(oserr != DBNOERR)
{
printf("TDS OS error: %s\n", oserrstr);
}
}
if((con == NULL) || DBDEAD(con)) return INT_EXIT;
return INT_CANCEL;
}
static int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvnam, char *proicnam, int line)
{
if(severity > EXINFO)
{
printf("TDS Server error: %s\n", msgtext);
}
return 0;
}
/* poor mans escape */
static void escape(char *outbuf, int outbuflen, char *inbuf)
{
int ix, i;
ix = 0;
for(i = 0; i <= strlen(inbuf); i++)
{
outbuf[ix] = inbuf[i];
ix++;
if(inbuf[i] == '\'')
{
outbuf[ix] = inbuf[i];
ix++;
}
}
}
/* get connection */
DBPROCESS *get_connection(char *server, char *un, char *pw, char *db)
{
DBPROCESS *con;
LOGINREC *login;
RETCODE stat;
stat = dbinit();
if(stat != SUCCEED) tds_exit("dbinit");
dberrhandle(err_handler);
dbmsghandle(msg_handler);
login = dblogin();
if(login == NULL) tds_exit("dblogin");
stat = DBSETLUSER(login, un);
if(stat != SUCCEED) tds_exit("DBSETLUSER");
stat = DBSETLPWD(login, pw);
if(stat != SUCCEED) tds_exit("DBSETLPWD");
con = dbopen(login, server);
if(con == NULL) tds_exit("dbopen");
dbloginfree(login);
stat = dbuse(con, db);
if(stat != SUCCEED) tds_exit("dbuse");
return con;
}
/* get f1 given f2 */
int t1_get_one(DBPROCESS *con, char *f2)
{
//
RETCODE stat;
char f2esc[2 * F2_MAX_LEN];
/* build command */
int f1;
escape(f2esc, sizeof(f2esc), f2);
stat = dbfcmd(con, "SELECT f1 FROM t1 WHERE f2 = '%s'", f2esc);
if(stat != SUCCEED) tds_exit("dbcmd");
/* execute */
stat = dbsqlexec(con);
if(stat != SUCCEED) tds_exit("dbsqlexec");
/* bind results */
stat = dbresults(con);
if(stat != SUCCEED) tds_exit("dbresults");
stat = dbbind(con, 1, INTBIND, 0, (BYTE *)&f1);
if(stat != SUCCEED) tds_exit("dbbind");
/* process results */
if(dbnextrow(con) != NO_MORE_ROWS)
{
return f1;
}
else
{
other_exit("Row not found");
return 0; /* will never be reached */
}
}
/* get all records */
int t1_get_all(DBPROCESS *con, struct t1 *buf, int bufsiz)
{
RETCODE stat;
char f2[F2_MAX_LEN];
int f1, count;
/* build command */
stat = dbcmd(con, "SELECT f1,f2 FROM t1");
if(stat != SUCCEED) tds_exit("dbcmd");
/* execute */
stat = dbsqlexec(con);
if(stat != SUCCEED) tds_exit("dbsqlexec");
/* bind results */
stat = dbresults(con);
if(stat != SUCCEED) tds_exit("dbresults");
stat = dbbind(con, 1, INTBIND, 0, (BYTE *)&f1);
if(stat != SUCCEED) tds_exit("dbbind");
stat = dbbind(con, 2, STRINGBIND, 0, (BYTE *)f2); /* using varlen=sizeof(f2) result in padding with spaces */
if(stat != SUCCEED) tds_exit("dbbin");
/* process results */
count = 0;
while((dbnextrow(con) != NO_MORE_ROWS) && (count < bufsiz))
{
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
return count;
}
/* add record */
void t1_put(DBPROCESS *con, int f1, char *f2)
{
RETCODE stat;
char f2esc[2 * F2_MAX_LEN];
int n;
/* build command */
escape(f2esc, sizeof(f2esc), f2);
stat = dbfcmd(con, "INSERT INTO t1 VALUES(%d, '%s')", f1, f2esc);
if(stat != SUCCEED) tds_exit("dbcmd");
/* execute */
stat = dbsqlexec(con);
if(stat != SUCCEED) tds_exit("dbsqlexec");
/* check if OK */
n = DBCOUNT(con);
if(n != 1)
{
other_exit("INSERT did not insert 1 row");
}
}
/* delete record */
void t1_remove(DBPROCESS *con, int f1)
{
RETCODE stat;
int n;
/* build command */
stat = dbfcmd(con, "DELETE FROM t1 WHERE f1 = %d", f1);
if(stat != SUCCEED) tds_exit("dbcmd");
/* execute */
stat = dbsqlexec(con);
if(stat != SUCCEED) tds_exit("dbsqlexec");
/* check if OK */
n = DBCOUNT(con);
if(n != 1)
{
other_exit("DELETE did not delete 1 row");
}
}
/* print all records */
void t1_dump(DBPROCESS *con)
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(con, buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
int main()
{
DBPROCESS *con;
int f1;
/* get connection */
#ifdef FREETDS
con = get_connection("ARNEPC4\\SQLEXPRESS", "", "", "Test");
#endif
#ifdef SYBASE
con = get_connection("ARNEPC4", "sa", "xxxxxx", "Test");
#endif
/* test t1_get_one */
f1 = t1_get_one(con, "BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump(con);
/* test t1_put */
t1_put(con, 999, "XXX");
t1_dump(con);
/* test t1_remove */
t1_remove(con, 999);
t1_dump(con);
/* close connection */
dbclose(con);
dbexit();
return 0;
}
Windows GCC C FreeTDS build:
gcc -m32 -Wall -DFREETDS -I%TDS%\include -L%TDS%\lib -L%ICONV%\lib -L%OPENSSL%\lib %1.c -lsybdb -liconv -lssleay32 -llibeay32 -lws2_32 -o %1.exe
Note that FreeTDS requires ICONV and OpenSSL libraries.
Windows MSVC++ C Sybase build:
cl /DSYBASE /I%SYBASE%\OCS-15_0\include %1.c %SYBASE%\OCS-15_0\lib\libsybdb64.lib
Linux GCC C FreeTDS build:
gcc -Wall -DFREETDS $1.c -lsybdb -o $1
Embedded SQL is a technique where SQL statements are embedded directly in the source code and a pre-compiler generates pure source code with the embedded SQL statements replaced by ordinary code (function calls).
So the flow is:
| Names |
Embedded SQL for C
Pro*C (Oracle) ECPG (PostgreSQL) |
| History | Very old - probably IBM mainframe and COBOL back in early 1980's |
| Supported databases | Several major databases (Oracle, DB2, PostgreSQL etc. but not MySQL and no longer MS SQLServer) |
| Supported platforms | Practically all (mainframe, most Unix, Linux, Windows etc.) |
| Status | Still used in C (but it is not as dominant in C as in COBOL, because there are alternatives in C) |
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* embedded SQL headers */
EXEC SQL INCLUDE SQLCA;
#if defined(DB2)
#include <sql.h>
#endif
#define F2_MAX_LEN 50
#define MAX_REC 100
/* table structure */
struct t1
{
int f1;
char f2[F2_MAX_LEN + 1];
};
/* error handling functions */
void sql_exit(char *sql)
{
#if defined(DB2)
char msg[163];
sqlaintp(msg, sizeof(msg), 80, &sqlca);
printf("SQL error in %s: %s\n", sql, msg);
#elif defined(ORACLE)
char msg[163];
size_t buflen, msglen;
buflen = sizeof(msg);
sqlglm(msg, &buflen, &msglen);
msg[msglen] = 0;
printf("SQL error in %s: %s\n", sql, msg);
#elif defined(PGSQL)
printf("SQL error in %s: %s\n", sql, sqlca.sqlerrm.sqlerrmc);
#else
printf("SQL error in %s\n", sql);
#endif
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
/* get f1 given f2 */
int t1_get_one(char *f2)
{
EXEC SQL BEGIN DECLARE SECTION;
char inf2[51];
long int outf1;
EXEC SQL END DECLARE SECTION;
/* open cursor */
strcpy(inf2, f2);
EXEC SQL DECLARE cone CURSOR FOR SELECT f1 FROM t1 WHERE f2 = :inf2;
if(sqlca.sqlcode != 0) sql_exit("declare cursor");
EXEC SQL OPEN cone;
if(sqlca.sqlcode != 0) sql_exit("open cursor");
/* read from cursor */
EXEC SQL FETCH cone INTO :outf1;
if(sqlca.sqlcode != 0)
{
other_exit("Row not found");
}
/* close cursor */
EXEC SQL CLOSE cone;
return outf1;
}
/* get all records */
int t1_get_all(struct t1 *buf, int bufsiz)
{
EXEC SQL BEGIN DECLARE SECTION;
char f2[51];
long int f1;
EXEC SQL END DECLARE SECTION;
int count;
/* open cursor */
EXEC SQL DECLARE call CURSOR FOR SELECT f1,f2 FROM t1;
if(sqlca.sqlcode != 0) sql_exit("declare cursor");
EXEC SQL OPEN call;
if(sqlca.sqlcode != 0) sql_exit("open cursor");
/* read from cursor */
count = 0;
for(;;)
{
if(count >= bufsiz) break;
EXEC SQL FETCH call INTO :f1, :f2;
if(sqlca.sqlcode != 0) break;
buf[count].f1 = f1;
strcpy(buf[count].f2, f2);
count++;
}
/* close cursor */
EXEC SQL CLOSE call;
return count;
}
/* add record */
void t1_put(int f1, char *f2)
{
EXEC SQL BEGIN DECLARE SECTION;
char insf2[51];
long int insf1;
EXEC SQL END DECLARE SECTION;
/* execute */
insf1 = f1;
strcpy(insf2, f2);
EXEC SQL INSERT INTO t1 VALUES(:insf1, :insf2);
if(sqlca.sqlcode != 0) sql_exit("insert");
/* check if OK */
if(sqlca.sqlerrd[2] != 1)
{
other_exit("INSERT did not insert 1 row");
}
}
/* delete record */
void t1_remove(int f1)
{
EXEC SQL BEGIN DECLARE SECTION;
long int delf1;
EXEC SQL END DECLARE SECTION;
/* execute */
delf1 = f1;
EXEC SQL DELETE FROM t1 WHERE f1 = :delf1;
if(sqlca.sqlcode != 0) sql_exit("delete");
/* check if OK */
if(sqlca.sqlerrd[2] != 1)
{
other_exit("DELETE did not delete 1 row");
}
}
/* print all records */
void t1_dump()
{
struct t1 buf[MAX_REC];
int i, n;
n = t1_get_all(buf, MAX_REC);
for(i = 0; i < n; i++)
{
printf("%d \"%s\"\n", buf[i].f1, buf[i].f2);
}
}
int main()
{
#if defined(ORACLE)
EXEC SQL BEGIN DECLARE SECTION;
char con[256];
EXEC SQL END DECLARE SECTION;
#endif
#if defined(PGSQL)
EXEC SQL BEGIN DECLARE SECTION;
char srv[50], un[50], pw[50];
EXEC SQL END DECLARE SECTION;
#endif
int f1;
/* get connection */
#if defined(DB2)
EXEC SQL CONNECT TO Test;
#elif defined(ORACLE)
strcpy(con, "arne/xxxxxx@//localhost/XE");
/* TODO : outcomment below for IBM as SQL preprocessor does not use C preprocessor directives */
EXEC SQL CONNECT :con;
#elif defined(PGSQL)
strcpy(srv, "tcp:postgresql://localhost/Test");
strcpy(un, "postgres");
strcpy(pw, "xxxxxx");
EXEC SQL CONNECT TO :srv USER :un USING :pw;
#else
#error "No connect defined"
#endif
if(sqlca.sqlcode != 0) sql_exit("connect");
/* test t1_get_one */
f1 = t1_get_one("BB");
printf("%d\n", f1);
/* test t1_get_all */
t1_dump();
/* test t1_put */
t1_put(999, "XXX");
t1_dump();
/* test t1_remove */
t1_remove(999);
t1_dump();
return 0;
}
Windows DB2 MSVC++ C build:
db2 connect to %2
db2 prep %1.sqc bindfile
db2 bind %1.bnd
db2 connect reset
cl /W2 /DDB2 /DWIN32 %1.c db2api.lib
Windows Oracle MSVC++ C build:
proc parse=partial iname=%1.sqc define=(WIN64,ORACLE) include=("%ICHOME%\sdk\include",".","%VCINSTALLDIR%include","%VCINSTALLDIR%PlatformSDK\include")
cl /DORACLE /I%ICHOME%\sdk\include /I. /I"%VCINSTALLDIR%include" /I"%VCINSTALLDIR%PlatformSDK\include" %1.c /link /LIBPATH:%ICHOME%\sdk\lib\msvc orasql11.lib msvcrt.lib /nodefaultlib:libcmt
Windows PostgreSQL MSVC++ C build:
ecpg %1.sqc
cl /DPGSQL /I"%PGSQL%\include" %1.c /link /LIBPATH:"%PGSQL%\lib" libecpg.lib
I have practically no experience with embedded SQL so the above code may not be optimal.
Embedded SQL is actual portable if using only standard SQL except for connect and error handling.
The usage of stored procedures are not universally considered a good idea. But the reality is that they are sometimes used. Especially in Oracle DB and MS SQLServer environments.
The basic API for accessing stored procedures is the same as for using plain SQL statements with parameters, but stored procedures do come with a few special features that require special handling in the API.
Two such features are:
The following example illustrates how to handle that.
Stored procedure (for MS SQLServer):
CREATE PROCEDURE usp_multi @arg INTEGER
AS
BEGIN
SELECT @arg+1 AS v
SELECT 2*@arg AS v
END;
GO
CREATE PROCEDURE usp_return @inarg INTEGER, @outarg INTEGER OUT
AS
BEGIN
SELECT @inarg+1 AS v
SELECT @outarg = @inarg+2
RETURN @inarg+3
END;
GO
Yes - they are trivial, but they will illustrate the points just fine.
ODBC code:
/* standard C headers */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* platform specific header files */
#include <windows.h>
/* ODBC headers */
#include <sql.h>
#include <sqlext.h>
/* error handling functions */
void env_exit(SQLHENV env)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_ENV, env, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void con_exit(SQLHDBC con)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_DBC, con, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void stmt_exit(SQLHSTMT stmt)
{
unsigned char state[6], buf[200];
long int code;
short int buflen;
SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, state, &code, buf, sizeof(buf), &buflen);
buf[buflen] = 0;
printf("SQL error: %s - %ld - %s\n", state, code, buf);
exit(1);
}
void other_exit(char *msg)
{
printf("%s\n", msg);
exit(1);
}
const char *dsn = "ARNEPC4_SQLSRV";
const char *un = "";
const char *pw = "";
/* test multiple result sets */
void test_multi_result_sets()
{
SQLHENV env;
SQLHDBC con;
SQLHSTMT stmt;
char *sqlstr;
int stat, arg, v;
/* get connection */
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
/* get statement */
sqlstr = "{ CALL usp_multi(?) }";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
arg = 123;
stat = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &arg, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &v, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
for(;;)
{
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
printf("%d\n", v);
}
/* next result set */
stat = SQLMoreResults(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &v, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
for(;;)
{
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
printf("%d\n", v);
}
/* close statement */
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/* close connection */
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return;
}
/* test multiple return types */
void test_multi_return_types()
{
SQLHENV env;
SQLHDBC con;
SQLHSTMT stmt;
char *sqlstr;
int stat, retval, inarg, outarg, v;
/* get connection */
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
other_exit("Error allocating environment");
}
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
env_exit(env);
}
/* get statement */
sqlstr = "{ ? = CALL usp_return(?,?) }";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
con_exit(con);
}
/* prepare */
stat = SQLPrepare(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind parameters */
stat = SQLBindParameter(stmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &retval, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
inarg = 123;
stat = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &inarg, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
stat = SQLBindParameter(stmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &outarg, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* execute */
stat = SQLExecute(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* bind results */
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &v, 0, NULL);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO))
{
stmt_exit(stmt);
}
/* process result */
for(;;)
{
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
printf("%d\n", v);
}
/* go to next result set to trigger return value and output argument to be populated */
stat = SQLMoreResults(stmt);
printf("return value = %d\n", retval);
printf("out parameter = %d\n", outarg);
/* close statement */
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/* close connection */
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return;
}
int main()
{
test_multi_result_sets();
test_multi_return_types();
return 0;
}
ADO code:
// standard C++ headers
#include <iostream>
#include <cstdlib>
using namespace std;
// ADO headers
#include <windows.h>
#import "msado15.dll" rename("EOF", "EndOfFile")
using namespace ADODB;
// error handling functions
void ExceptionExit(_com_error ex)
{
cout << "COM error: " << ex.ErrorMessage() << ", ADO error: " << ex.Description() << endl;
exit(1);
}
const char *constr = "Provider=SQLNCLI11;Server=localhost;Database=Test;Trusted_Connection=yes;";
const char *un = "";
const char *pw = "";
/* test multiple result sets */
void test_multi_result_sets()
{
try
{
// create connection
_ConnectionPtr con;
con.CreateInstance(__uuidof(Connection));
con->Open(constr, un, pw, adConnectUnspecified);
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "dbo.usp_multi";
cmd->CommandType = adCmdStoredProc;
VARIANT arg;
arg.vt = VT_I4;
arg.intVal = 123;
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t("arg"), adInteger, adParamInput, sizeof(arg), arg));
// open record set
_RecordsetPtr rs1 = cmd->Execute(NULL, NULL, adCmdStoredProc);
// process result
while(!rs1->EndOfFile)
{
cout << rs1->GetCollect("v").intVal << endl;
rs1->MoveNext();
}
// next record set
_RecordsetPtr rs2 = rs1->NextRecordset(NULL);
// process result
while(!rs2->EndOfFile)
{
cout << rs2->GetCollect("v").intVal << endl;
rs2->MoveNext();
}
// close record set
//rs1->Close(); // automatically closed by NextRecordset
rs1 = NULL;
rs2->Close();
rs2 = NULL;
cmd = NULL;
// close connection
con->Close();
con = NULL;
}
catch(_com_error &ex)
{
ExceptionExit(ex);
}
}
/* test multiple return types */
void test_multi_return_types()
{
try
{
// create connection
_ConnectionPtr con;
con.CreateInstance(__uuidof(Connection));
con->Open(constr, un, pw, adConnectUnspecified);
// create command
_CommandPtr cmd;
cmd.CreateInstance(__uuidof(Command));
cmd->ActiveConnection = con;
cmd->CommandText = "dbo.usp_return";
cmd->CommandType = adCmdStoredProc;
VARIANT inarg;
inarg.vt = VT_I4;
inarg.intVal = 123;
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t("retval"), adInteger, adParamReturnValue, sizeof(int)));
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t("inarg"), adInteger, adParamInput, sizeof(int), inarg));
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t("outarg"), adInteger, adParamOutput, sizeof(int)));
// open record set
_RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdStoredProc);
// process result
while(!rs->EndOfFile)
{
cout << rs->GetCollect("v").intVal << endl;
rs->MoveNext();
}
// close record set to trigger return value and output argument to be populated
rs->Close();
rs = NULL;
cout << "return value = " << cmd->Parameters->Item[_bstr_t("retval")]->Value.intVal << endl;
cout << "out parameter = " << cmd->Parameters->Item[_bstr_t("outarg")]->Value.intVal << endl;
cmd = NULL;
// close connection
con->Close();
con = NULL;
}
catch(_com_error &ex)
{
ExceptionExit(ex);
}
}
int main()
{
CoInitialize(NULL);
test_multi_result_sets();
test_multi_return_types();
CoUninitialize();
return 0;
}
All of the above are for relational (SQL) databases. For NoSQL databases see:
| Version | Date | Description |
|---|---|---|
| 1.0 | December 10th 2017 | Initial version |
| 1.1 | December 22nd 2017 | Add ADO section |
| 1.2 | August 12th 2018 | Add section on calling stored procedures |
| 1.3 | August 22nd 2018 | Add LIBOCI section |
| 1.4 | April 16th 2019 | Add Qt section |
| 1.5 | March 7th 2020 | Add DBD section |
| 1.6 | April 5th 2020 | Add LIBPQ section |
| 1.7 | May 29th 2021 | Add SQLRelay section |
| 1.8 | July 22nd 2022 | Add Rdb SQL Services section |
See list of all articles here
Please send comments to Arne Vajhøj
In general I will recommend using ODBC.
The API is OK (supports parameters) and is highly portable.
Alternative API's are less portable and does not provide major advantages that compensate for the missing portability.
ADO is relative portable, but some databases does not come with an OLE DB provider and its future is somewhat uncertain.
Embedded SQL is also relative portable but the support among database vendors is decreasing fast and it would be risky to assume that solution to continue to be viable.
Most likely exception would be if connecting to MySQL or PostgreSQL on a *nix system. Then just using MySQL C API or PostgreSQL C API instead of getting a third party ODBC package may make sense.