Обсуждение: select on bytea column returns hex encoded data instead of binary data
Hi,
I am new to postgresql.
I've an application which displays the various binary files (like jpgs, xls, zips) stored in PostgrelSQL DB in bytea column.
I inserted these files in bytea column in a table using hex encoding.
But, when I try to access this column by executing (SQLExec) the query "select * from tablename;" from ODBC on Windows, the buffer contains the same hex encoded data.
I am using SQL_C_BINARY data type for SQLBindCol() function.
Can you please help me know, how can I retrieve the data stored in bytea columns in binary format?
Thanks in advance for help and guidance.
Best Regards,
Ganesh
On Wed, Feb 22, 2012 at 11:02:16AM +0800, Ganesh Borse wrote: > Hi, > > I am new to postgresql. > > I've an application which displays the various binary files (like jpgs, > xls, zips) stored in PostgrelSQL DB in bytea column. > > I inserted these files in bytea column in a table using hex encoding. > > But, when I try to access this column by executing (SQLExec) the query > "select * from tablename;" from ODBC on Windows, the buffer contains the > same hex encoded data. > > I am using SQL_C_BINARY data type for SQLBindCol() function. > > Can you please help me know, how can I retrieve the data stored in bytea > columns in binary format? > > Thanks in advance for help and guidance. > > Best Regards, > Ganesh It sounds like ODBC is not using binary mode for the result retrieval but is using text which results in either hex or escape encoding as you see. You will need to find someone to help you with ODBC on Windows. Regards, Ken
Hi,
Thanks for this guidance.
Incidentally this same code (as-is) works well (i.e. fetches the binary data as binary data) with few other different databases like DB2, Oracle, MS-SQL, MYSQL, Sqlite2, Access.
Can this be an issue with Postgresql's ODBC driver? Is there a different forum for PGSQL's ODBC driver?
Or, does postgresql databases does not directly give binary data with query like "select binarycolumn from table" ? Do we need to convert this to binary type while selecting?
Best Regards,
Ganesh
On Wed, Feb 22, 2012 at 10:07 PM, ktm@rice.edu <ktm@rice.edu> wrote:
It sounds like ODBC is not using binary mode for the result retrieval butOn Wed, Feb 22, 2012 at 11:02:16AM +0800, Ganesh Borse wrote:
> Hi,
>
> I am new to postgresql.
>
> I've an application which displays the various binary files (like jpgs,
> xls, zips) stored in PostgrelSQL DB in bytea column.
>
> I inserted these files in bytea column in a table using hex encoding.
>
> But, when I try to access this column by executing (SQLExec) the query
> "select * from tablename;" from ODBC on Windows, the buffer contains the
> same hex encoded data.
>
> I am using SQL_C_BINARY data type for SQLBindCol() function.
>
> Can you please help me know, how can I retrieve the data stored in bytea
> columns in binary format?
>
> Thanks in advance for help and guidance.
>
> Best Regards,
> Ganesh
is using text which results in either hex or escape encoding as you see.
You will need to find someone to help you with ODBC on Windows.
Regards,
Ken
On Thu, Feb 23, 2012 at 09:48:43AM +0800, Ganesh Borse wrote: > Hi, > > Thanks for this guidance. > > Incidentally this same code (as-is) works well (i.e. fetches the binary > data as binary data) with few other different databases like DB2, Oracle, > MS-SQL, MYSQL, Sqlite2, Access. > > Can this be an issue with Postgresql's ODBC driver? Is there a different > forum for PGSQL's ODBC driver? > > Or, does postgresql databases does not directly give binary data with query > like "select binarycolumn from table" ? Do we need to convert this to > binary type while selecting? > > Best Regards, > Ganesh > Hi Ganesh, I am not really familiar with the PostgreSQL ODBC driver at all, but I know that in libpq you need to specify a binary output format to get the bytea info as binary. I do not know how that is done in the PostgreSQL ODBC driver. Good luck and maybe someone with more ODBC knowledge will comment. Regards, Ken
Hi,
Thanks for this clarification.
Do we've another forum for ODBC related queries? Or can I try in developer forum?
Thanks,
Ganesh
On Thu, Feb 23, 2012 at 11:14 PM, ktm@rice.edu <ktm@rice.edu> wrote:
On Thu, Feb 23, 2012 at 09:48:43AM +0800, Ganesh Borse wrote:
> Hi,
>> Thanks for this guidance.Hi Ganesh,
>
> Incidentally this same code (as-is) works well (i.e. fetches the binary
> data as binary data) with few other different databases like DB2, Oracle,
> MS-SQL, MYSQL, Sqlite2, Access.
>
> Can this be an issue with Postgresql's ODBC driver? Is there a different
> forum for PGSQL's ODBC driver?
>
> Or, does postgresql databases does not directly give binary data with query
> like "select binarycolumn from table" ? Do we need to convert this to
> binary type while selecting?
>
> Best Regards,
> Ganesh
>
I am not really familiar with the PostgreSQL ODBC driver at all, but I
know that in libpq you need to specify a binary output format to get
the bytea info as binary. I do not know how that is done in the PostgreSQL
ODBC driver. Good luck and maybe someone with more ODBC knowledge will
comment.
Regards,
Ken
Ganesh Borse <bganesh05@gmail.com> writes:
> Do we've another forum for ODBC related queries?
pgsql-odbc mailing list would be the best place for those.
regards, tom lane