// Demonstrate running a query and getting results by querying the tables
// system table for a list of all tables in the current schema.
// Some standard headers
#include <stdlib.h>
#include <sstream>
#include <iostream>
#include <assert.h>
// Standard ODBC headers
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
// Use std namespace to make output easier
using namespace std;
// Helper function to print SQL error messages.
template <typename HandleT>
void reportError(int handleTypeEnum, HandleT hdl)
{
// Get the status records.
SQLSMALLINT i, MsgLen;
SQLRETURN ret2;
SQLCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER NativeError;
i = 1;
cout << endl;
while ((ret2 = SQLGetDiagRec(handleTypeEnum, hdl, i, SqlState, &NativeError,
Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
cout << "error record #" << i++ << endl;
cout << "sqlstate: " << SqlState << endl;
cout << "detailed msg: " << Msg << endl;
cout << "native error code: " << NativeError << endl;
}
}
typedef struct {
SQLHENV hdlEnv;
SQLHDBC hdlDbc;
} DBConnection;
void connect(DBConnection *pConnInfo)
{
// Set up the ODBC environment
SQLRETURN ret;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &pConnInfo->hdlEnv);
assert(SQL_SUCCEEDED(ret));
// Tell ODBC that the application uses ODBC 3.
ret = SQLSetEnvAttr(pConnInfo->hdlEnv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
assert(SQL_SUCCEEDED(ret));
// Allocate a database handle.
ret = SQLAllocHandle(SQL_HANDLE_DBC, pConnInfo->hdlEnv, &pConnInfo->hdlDbc);
assert(SQL_SUCCEEDED(ret));
// Connect to the database
cout << "Connecting to database." << endl;
const char* dsnName = "ExampleDB";
const char* userID = "dbadmin";
const char* passwd = "password123";
ret = SQLConnect(pConnInfo->hdlDbc, (SQLCHAR*)dsnName,
SQL_NTS, (SQLCHAR*)userID, SQL_NTS,
(SQLCHAR*)passwd, SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
cout << "Could not connect to database" << endl;
reportError<SQLHDBC>(SQL_HANDLE_DBC, pConnInfo->hdlDbc);
exit(EXIT_FAILURE);
}
else {
cout << "Connected to database." << endl;
}
}
void disconnect(DBConnection *pConnInfo)
{
SQLRETURN ret;
// Clean up by shutting down the connection
cout << "Free handles." << endl;
ret = SQLDisconnect(pConnInfo->hdlDbc);
if (!SQL_SUCCEEDED(ret)) {
cout << "Error disconnecting. Transaction still open?" << endl;
exit(EXIT_FAILURE);
}
SQLFreeHandle(SQL_HANDLE_DBC, pConnInfo->hdlDbc);
SQLFreeHandle(SQL_HANDLE_ENV, pConnInfo->hdlEnv);
}
void executeQuery(SQLHDBC hdlDbc, SQLCHAR* pQuery)
{
SQLRETURN ret;
// Set up a statement handle
SQLHSTMT hdlStmt;
SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
assert(SQL_SUCCEEDED(ret));
// Execute a query to get the names and IDs of all tables in the schema
// search p[ath (usually public).
ret = SQLExecDirect(hdlStmt, pQuery, SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
// Report error an go no further if statement failed.
cout << "Error executing statement." << endl;
reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
exit(EXIT_FAILURE);
}
else {
// Query succeeded, so bind two variables to the two colums in the
// result set,
cout << "Fetching results..." << endl;
SQLBIGINT table_id; // Holds the ID of the table.
SQLTCHAR table_name[256]; // buffer to hold name of table
ret = SQLBindCol(hdlStmt, 1, SQL_C_SBIGINT, (SQLPOINTER)&table_id,
sizeof(table_id), NULL);
ret = SQLBindCol(hdlStmt, 2, SQL_C_TCHAR, (SQLPOINTER)table_name,
sizeof(table_name), NULL);
// Loop through the results,
while (SQL_SUCCEEDED(ret = SQLFetchScroll(hdlStmt, SQL_FETCH_NEXT, 1))) {
// Print the bound variables, which now contain the values from the
// fetched row.
cout << table_id << " | " << table_name << endl;
}
// See if loop exited for reasons other than running out of data
if (ret != SQL_NO_DATA) {
// Exited for a reason other than no more data... report the error.
reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
}
}
SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
}
int main()
{
DBConnection conn;
connect(&conn);
executeQuery(conn.hdlDbc,
(SQLCHAR*)"SELECT table_id, table_name FROM tables ORDER BY table_name");
executeQuery(conn.hdlDbc,
(SQLCHAR*)"SELECT table_id, table_name FROM tables ORDER BY table_id");
disconnect(&conn);
exit(EXIT_SUCCESS);
}