Skip site navigation (1) Skip section navigation (2)

Re: BLOB via ODBC

From: Brodie Thiesfield <brofield2(at)jellycan(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: BLOB via ODBC
Date: 2007-03-13 20:38:16
Message-ID: 45F70BB8.7040607@jellycan.com (view raw or flat)
Thread:
Lists: pgsql-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
> 

In response to

pgsql-odbc by date

Next:From: noreplyDate: 2007-03-13 22:06:37
Subject: [ psqlodbc-Bugs-1000681 ] Error when using ODBC driver with Microsoft Access
Previous:From: David GardnerDate: 2007-03-12 20:54:30
Subject: Re: Access violation - probably not the fault of Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group