Thread: Request for help on retrieving binary data from bytea column using ODBC calls

Hi Ganesh,

(2012/02/24 14:06), Ganesh Borse wrote:

Dear All,

I've a VC++ application which displays the various binary files (like
jpgs, xls, zips) stored in PostgrelSQL DB in bytea column.
Files are inserted into bytea column using hex encoding.

How did you insert the data concretely?

regards,
Hiroshi Inoue

But, when I try to access this column by executing the query "select *
from tablename;" from ODBC on Windows in this application, the buffer
contains the same hex encoded data, but not the binary data.

Application does the following steps to retrieve this binary data. This
same code works well with other DBS, like Oracle, Sybase, DB2, MSSQL &
so on.
//---------------------------------------------------------------------------------------------------
// prepare query:
wchar_t *query = _T("select * from mytable;");
   SQLRETURN ret = SQLPrepare(stmt, query, SQL_NTS);
   if (!SQL_SUCCEEDED(ret))
   {
      TRACE("SybAse:SQLprepare failed.");
      DbException::sqlErrorInfo(SQL_HANDLE_STMT, stmt);
      throw DbException(std::string(""));
   }
// bind to column:
   SQLBindCol(stmtHdl, columnNumber, SQL_C_BINARY,
             m_colValue[0], m_len, &m_LenInd[0]);
// Execute query:
ret = SQLExecute(stmt);
// fetch rows
if (SQL_SUCCESS == ret || SQL_SUCCESS_WITH_INFO == ret)
{
   do {
      ret = SQLFetchScroll(m_stmt, SQL_FETCH_NEXT, 0);
      // handle data per row here...
   } while(SQL_SUCCEEDED(ret));
}
//---------------------------------------------------------------------------------------------------

Can you please help me know, how can I retrieve the data stored in bytea
columns in binary format, so that those file can be handled appropriately?

Thanks in advance for your help and guidance.

Best Regards,
Ganesh

Log is as below:

 cf11e0a1b11ae1000000000000000000000000000000003b000300feff0900060000000000000000000000010000006a00000000000000001000006700000001000000feffffff0000000000000000ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Memory dump in VC++ debug window:

Address                     Hex Value

0x0000000000367850  a0 63 66 31 31 65 30 61 31 62 31 31 61 65 31 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 33 62 30 30 30 33 30 30 66 65   

cf11e0a1b11ae1000000000000000000000000000000003b000300fe

0x0000000000367889  66 66 30 39 30 30 30 36 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31 30 30 30 30 30 30 36 61 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31  ff0900060000000000000000000000010000006a00000000000000001

0x00000000003678C2  30 30 30 30 30 36 37 30 30 30 30 30 30 30 31 30 30 30 30 30 30 66 65 66 66 66 66 66 66 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 66 66 66 66 66 66 66 66 66 66 66 66  000006700000001000000feffffff0000000000000000ffffffffffff

0x00000000003678FB  66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66  fffffffffffffffffffffffffffffffffffffffffffffffffffffffff

0x0000000000367934  66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 66 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  fffffffffffffffffffffffffffff............................

On Sat, Feb 25, 2012 at 5:47 AM, Hiroshi Inoue <inoue@tpf.co.jp> wrote:

(2012/02/24 18:56), Ganesh Borse wrote:

Hi,

I used the steps same as outlined below:

    xxd -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hex
    DELETE FROM hexdump; -- hexdump table is: -- CREATE TABLE hexdump
(hex text);
    COPY hexdump FROM '/tmp/image.hex';
    UPDATE users SET image= (SELECT decode(hex, 'hex') FROM hexdump
LIMIT 1) where id=15489

Is this the correct way of inserting binary data into bytea column?

When I fetch a small part of this column, I get output as below:

   select substring(smallblob,0,64) from longdata where blobi d=1;

 \xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000 (1 row)

Looks OK to me.
Could you send me directly the Mylog output of the simple test case?

regards,
Hiroshi Inoue

(2012/02/28 11:54), Ganesh Borse wrote:

Hi,

I am using the following PostgreSQL ODBC Windows driver:

PostgreSQL 64bit 8.03.04.00 PSQLODBC_X64_RELEASE.DLL 8/3/2009 -- the one
which got installed with PostgreSQL 9.0 server that I downloaded from
PostgreSQL website.

Unfortunately the driver know nothing about recently introduced
hex BYTEA format.
Please download and install the latest version from
 http://www.postgresql.org/ftp/odbc/versions/ .

regards,
Hiroshi Inoue

Hi,

(2012/02/28 16:00), Ganesh Borse wrote:

Hi,

I downloaded the following file&  installed it.

But it installed the driver in 32bit directory: C:\Program Files
(x86)\psqlODBC\0901\bin

Also, this new driver is not listed in 64-bit ODBC Data Source
Administrator, but showing up in 32bit ODBC Data Source Administrator. (pls

refer attached screenshot of 32&  64 bit  odbc data source admin tools on
my PC).

psqlodbc_09_01_0100-x64.zip is for 64-bit use.
psqlodbc_09_01_0100-1.zip is packaged due to a packaging mistake
of psqlodbc_09_0100.zip (32-bit version).

regards,
Hiroshi Inoue

Thanks&  Regards.

On Tue, Feb 28, 2012 at 12:30 PM, Ganesh Borse<bganesh05@gmail.com>  wrote:

Hi,

I see this version as the most recent at :
http://www.postgresql.org/ftp/odbc/versions/msi/

psqlodbc_09_01_0100-1.zip 2012-01-04 00:32:34 3.7 MB

Hopefully this is the latest one which knows about hex bytea format?

Thanks and Regards.


On Tue, Feb 28, 2012 at 11:43 AM, Inoue, Hiroshi<inoue@tpf.co.jp>  wrote:

(2012/02/28 11:54), Ganesh Borse wrote:

Hi,

I am using the following PostgreSQL ODBC Windows driver:

PostgreSQL 64bit 8.03.04.00 PSQLODBC_X64_RELEASE.DLL 8/3/2009 -- the one
which got installed with PostgreSQL 9.0 server that I downloaded from
PostgreSQL website.

Unfortunately the driver know nothing about recently introduced
hex BYTEA format.
Please download and install the latest version from

 http://www.postgresql.org/ftp/**odbc/versions/<http://www.postgresql.org/ftp/odbc/versions/>.

regards,
Hiroshi Inoue