PostgreSQL ODBC Visual C++ BLOB Getting?

Question

I am trying to read a large object from a PostgreSQL database using ODBC from Visual Studio C++. I can't get it to work.

The driver is PostgresSQL ANSI(x64) 9.50.04.00.

The data type of the column is lo, created with:

CREATE DOMAIN lo AS oid;

The column contains the object ids of the large objects.

My understanding is that when the driver sees a lo type, it will handle it as SQL_LONGVARBINARY.

However, the query fails. The diagnostics message is:

HY003 [Microsoft][OBDC Driver Manager] Program type out of range

Here is the code with the noise removed:

wchar_t* connect0 = L"DSN=picdb;";

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

SQLLEN sqllen = -1;
PBYTE image = new BYTE[0];

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
SQLDriverConnect(dbc, NULL, connect0, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
SQLExecDirect(stmt, L"select lo from image_list where id=964945", SQL_NTS);
SQLFetch(stmt);

SQLGetData(stmt, 1, SQL_LONGVARBINARY, image, 0, &sqllen);  //<----- It fails here
image = new BYTE[sqllen];
SQLGetData(stmt, 1, SQL_LONGVARBINARY, image, sqllen, &sqllen);

SQLDisconnect(dbc);     
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);

I can read regular data from the database just fine. And I can read the BLOB data from the database with Java/JDBC, but I need it to work in C++.

Why do I get the error, and what can I do to fix it?

user1145922 user1145922 · Accepted Answer · 2016-08-05T17:58:11

This seems to work:

wchar_t* connect0 = L"DSN=picdb;";

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

SQLLEN sqllen = -1;
PBYTE image = new BYTE[0];

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
SQLDriverConnect(dbc, NULL, connect0, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
SQLExecDirect(stmt, L"select lo from image_list where id=964945", SQL_NTS);
SQLFetch(stmt);

SQLGetData(stmt, 1, SQL_C_BINARY, image, 0, &sqllen);
image = new BYTE[sqllen];
SQLBindParameter(stmt, 1, SQL_PARAM_OUTPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0, image, sqllen, NULL );
SQLGetData(stmt, 1, SQL_C_BINARY, image, sqllen, &sqllen);

SQLDisconnect(dbc);     
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);

PostgreSQL ODBC Visual C++ BLOB Getting?

1 Answers