VC++ access PostgreSQL via ODBC

#include "windows.h"

#include "sql.h"

#include "sqlext.h"

#include "stdio.h"

#include "boost/shared_ptr.hpp"

#include <map>

#include <deque>

#include <iostream>

#include <sstream>

//Onega 2005/02/15

//build on windows 2003 with VC2003, PostgreSQL 8.0

//bool column may be SQL_CHAR and '1'(ASCII 49) = true, '0'= false

namespace ODBCAgent

{

SQLRETURN SQLFetch(

SQLHSTMT     StatementHandle)

{

SQLRETURN rc = ::SQLFetch(StatementHandle);

std::stringstream ss;

ss<<"SQLFetch return "<<rc;



return rc;

}

}

//using namespace ODBCAgent;

//using ODBCAgent::SQLFetch;

std::string SqlTypeToString(SQLSMALLINT type)

{

switch(type)

{

case SQL_CHAR:

return "SQL_CHAR";

case SQL_VARCHAR:

return "SQL_VARCHAR";

case SQL_LONGVARCHAR:

return "SQL_LONGVARCHAR";

case SQL_WCHAR:

return "SQL_WCHAR";

case SQL_WVARCHAR:

return "SQL_WVARCHAR";

case SQL_WLONGVARCHAR:

return "SQL_WLONGVARCHAR";

case SQL_DECIMAL:

return "SQL_DECIMAL";

case SQL_NUMERIC:

return "SQL_NUMERIC";

case SQL_SMALLINT:

return "SQL_SMALLINT";

case SQL_INTEGER:

return "SQL_INTEGER";

case SQL_REAL:

return "SQL_REAL";

case SQL_FLOAT:

return "SQL_FLOAT";

case SQL_DOUBLE:

return "SQL_DOUBLE";

case SQL_BIT:

return "SQL_BIT";

case SQL_TINYINT:

return "SQL_TINYINT";

case SQL_BIGINT:

return "SQL_BIGINT";

case SQL_BINARY:

return "SQL_BINARY";

case SQL_VARBINARY:

return "SQL_VARBINARY";

case SQL_LONGVARBINARY:

return "SQL_LONGVARBINARY";

case SQL_TYPE_DATE:

return "SQL_TYPE_DATE";

case SQL_TYPE_TIME:

return "SQL_TYPE_TIME";

case SQL_TYPE_TIMESTAMP:

return "SQL_TYPE_TIMESTAMP";

//case SQL_TYPE_UTCDATETIME:

// return "SQL_TYPE_UTCDATETIME";

//case SQL_TYPE_UTCTIME:

// return "SQL_TYPE_UTCTIME";

case SQL_GUID:

return "SQL_GUID";

}

std::stringstream ss;

ss<<"unknown type:"<<(long)type;

return ss.str();

}

int main()

{

SQLRETURN rc = SQL_SUCCESS;

SQLHENV hEnv = NULL;

rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,

(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

SQLHDBC hDbc;

rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

char szInConnStr[1024];

strcpy(szInConnStr,"Driver={PostgreSQL};Server=localhost;Port=5432;Da

tabase=testdb;Uid=postgres;Pwd=postgres;BoolsAsChar=1;");

char szOutConnStr[1024];

SQLSMALLINT dwOutConnStrLen = sizeof(szOutConnStr);

rc = SQLDriverConnect(hDbc, NULL, 

reinterpret_cast<SQLCHAR*>(szInConnStr), strlen(szInConnStr), 

reinterpret_cast<SQLCHAR*>(szOutConnStr), dwOutConnStrLen,

&dwOutConnStrLen,

SQL_DRIVER_NOPROMPT);

SQLHSTMT hStmt;

rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

char szStmt[1024];

strcpy(szStmt, "SELECT * FROM testdb.pg_catalog.pg_database;");

//strcpy(szStmt, "SELECT * FROM test_table;");

SQLPrepare(hStmt,reinterpret_cast<SQLCHAR*>(szStmt), SQL_NTS);

rc = SQLExecute(hStmt);

//rc = SQLExecDirect(hStmt, reinterpret_cast<SQLCHAR*>(szStmt),

SQL_NTS);

SQLSMALLINT nCols = 0;

const int MAX_DATA_LEN = 1024;

char szHeader[256];

SQLSMALLINT swColLen;

SQLSMALLINT swColType = 0;

SQLUINTEGER udwColDef =0;

SQLSMALLINT swColScale;

SQLSMALLINT swColNull;

SQLCHAR szRowBuffer[1024];

SQLINTEGER bindcollenret;

rc = SQLNumResultCols(hStmt, &nCols); // Get Num of Result Columns,

typedef boost::shared_ptr<char> SmartBuffer;

//std::map<int,SmartBuffer > record_buffer_list;

std::deque<std::string> column_name_list;

//std::map<std::string,SQLSMALLINT> column_name_list;

std::deque<short> column_type_list;

std::deque<SmartBuffer> record_buffer_list;

std::deque<int> column_size_list;

for (int nColCount=0; nColCount<nCols; nColCount++) 

{

szHeader[0] = 0;

rc = SQLDescribeCol(hStmt, nColCount,

reinterpret_cast<SQLCHAR*>(szHeader),

MAX_DATA_LEN, &swColLen, &swColType, &udwColDef,

&swColScale, &swColNull

); // Get the header/title of each column

column_name_list.push_back(reinterpret_cast<const char*>(szHeader));

//column_name_list[reinterpret_cast<const char*>(szHeader)] =

swColType;

column_type_list.push_back(swColType);

std::cout<<szHeader<<" type: "<<SqlTypeToString(swColType)

<<"  column size:"<<(long)udwColDef <<std::endl;

//if(udwColDef>0)

{

//int col_size = (udwColDef==1?udwColDef+1:udwColDef);

column_size_list.push_back(udwColDef);

SmartBuffer col_buf(new char[udwColDef+2]);

rc = SQLBindCol(hStmt, nColCount, swColType,

col_buf.get(), udwColDef+2, &bindcollenret

); // Bind Column with row data array, Get the Describe of each

Column.

//record_buffer_list[reinterpret_cast<const

char*>(szHeader)]=(col_buf);

record_buffer_list.push_back(col_buf);

}

}

for(int i=0;i<record_buffer_list.size();i++)

{

if(column_size_list[i]>0)

memset(record_buffer_list[i].get(),0,column_size_list[i]+2);

}

rc = SQLFetch(hStmt); // Fetch row data from the statement cursor.

while(SQL_SUCCESS==rc||SQL_SUCCESS_WITH_INFO==rc)

{

//for(std::deque<SmartBuffer>::const_iterator it =

record_buffer_list.begin();

// it!=record_buffer_list.end();

// it++)

for(int i=0;i<column_name_list.size();i++)

{

SmartBuffer sb = record_buffer_list[i];

int column_size = column_size_list[i];

std::string column_name = column_name_list[i];

int column_type = column_type_list[i];

if(column_name.length()==0)

continue;

std::cout<<column_name.c_str()<<"=";

if(column_type==SQL_C_CHAR || SQL_VARCHAR == column_type)

{

//if(column_size==1)

//{

// char* pbuf = sb.get();

// char b = pbuf[0];

// //bool bval = (bool)b;

// //std::cout<< bval;

// //byte b = record_buffer_list[i].get()[0];

// std::cout<< (long)b<<"("<<b<<")";

//}

//else

std::cout<<record_buffer_list[i];

}

if(SQL_INTEGER == column_type_list[i])

{

int nlen;

memcpy(&nlen,sb.get(),column_size+2);

std::cout<<nlen;

}

std::cout<<"    ";

}

std::cout<<std::endl;

for(int i=0;i<record_buffer_list.size();i++)

{

if(column_size_list[i]>0)

memset(record_buffer_list[i].get(),0,column_size_list[i]+2);

}



rc = SQLFetch(hStmt);

}

SQLCloseCursor(hStmt); // Close current opened cursor of statement

when finished query.

SQLFreeHandle(SQL_HANDLE_STMT, hStmt); // Free statement handle and

related resources.

SQLDisconnect(hDbc); // Disconnect from the database

SQLFreeHandle(SQL_HANDLE_DBC, hDbc); // Free DB connection handle ...

SQLFreeHandle(SQL_HANDLE_ENV, hEnv); // Free SQL environment handle...

system("pause");

}