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

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)

Thanks and Regards.
-----------------------------
On Fri, Feb 24, 2012 at 5:06 PM, Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp> wrote:

> 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
>>
>