Re: BLOB via ODBC

No help here for me on this list, but so future seekers of wisdom can
get some help...

* use BYTEA as the column definition
* add ByteaAsLongVarBinary=1 to the connection string
* input bind call is

SQLBindParameter(hStatement, (SQLUSMALLINT) nColumn,
SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,
nMaxBinarySize, 0, (SQLCHAR *) pData, nDataLen, &nLen);

* output get data call is

SQLGetData(hStatement, (SQLUSMALLINT) nColumn,
SQL_C_BINARY, (SQLCHAR *) pDataBuf, nDataBufSiz,
&nActualDataLen);

Note that as of ODBC driver 8.02.0200 there is a bug in the encoding of
input data. The driver is converting the binary input to an escaped form
so that it could pass it to the server but it doesn't embellish the
syntax correctly. The driver was doing something like

INSERT INTO X VALUES '\000\001xyz';

which resulted in those bytes actually being inserted in the table,
verbatim, not their binary equivalent. You need to hack the insert and
update session syntax to include the correct embellishment (a quick
squizz in the manual tells you this is 'the way'):

INSERT INTO X VALUES E'\000\001xyz'::bytea;

So instead of binding to a statement like
"INSERT INTO BLOB (data) VALUES (?);"
You need to use
"INSERT INTO BLOB (data) VALUES (E?::bytea);"
or else escape it all manually.

Hopefully this will be fixed by a future driver update. Or perhaps I'm
wrong about the bug, but this workaround fixes the problem here.

Brodie

Brodie Thiesfield wrote:
> Hi,
>
> Can someone please point me at a FAQ or basic documentation for how to
> read/write blob data via the postgresql ODBC driver (using C/C++)? I
> haven't been able to find anything using google and have basic
> questions on how to do it. i.e. Do I use BYTEA type in the DB? Do I
> use SQL_VARBINARY in the call? How do I determine the length of the
> data while reading? etc
>
> Regards,
> Brodie
>