Quantcast

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

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ganesh Borse
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.

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Inoue, Hiroshi
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

--
Sent via pgsql-odbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ganesh Borse
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Inoue, Hiroshi
(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

--
Sent via pgsql-odbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ganesh Borse
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.

Thanks,

On Mon, Feb 27, 2012 at 4:30 PM, Ganesh Borse <[hidden email]> wrote:
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Inoue, Hiroshi
(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

--
Sent via pgsql-odbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ganesh Borse
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 <[hidden email]> 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/ .

regards,
Hiroshi Inoue

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Inoue, Hiroshi
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<[hidden email]>  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<[hidden email]>  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

--
Sent via pgsql-odbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ganesh Borse
Hi,

Thanks for your great help.

With the new ODBC driver, bytea (binary) data could be fetched successfully.

Best Regards

On Tue, Feb 28, 2012 at 5:10 PM, Inoue, Hiroshi <[hidden email]> wrote:
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<[hidden email]>  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<[hidden email]>  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

Loading...