C/C++ Database Access

Content:

  1. Introduction
  2. ODBC/CLI C API
  3. ADO
  4. Qt SQL
  5. MySQL C API
  6. MySQL C++ API
  7. Oracle OCI, OCCI and LIBOCI
  8. DBLIB
  9. Embedded SQL
  10. Recommendations
  11. Calling stored procedures

Introduction:

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.

ODBC/CLI C API

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:

non-ODBC CLI

Architecture for ODBC:

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:

old style
as defined in ODBC 1.x and 2.x
new style
as defined in ODBC 3.x and 4.x

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

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:

ADO & OLE DB
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

Qt SQL

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, OCI, 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

MySQL C API

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:

old style
without statements (equivalent of PHP mysql extension)
new style
with statements (equivalent of PHP mysqli extension)

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 C++ API

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.

Oracle OCI, OCCI and LIBOCI

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

DBLIB

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

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:

Embedded SQL flow
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.

Recommendations

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 on a *nix system. Then just using MySQL C API instead of getting a third party ODBC package may make sense.

Calling stored procedures:

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 procedures (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;
}

Article history:

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

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj