Re: PSQLODBC - Selecting bytea columns in Oracle

Hi Takayuki,

Thanks very much for your reply.

I have a test table in the PostgreSQL database that contains a single row.  So, although my query doesn’t specify any predicates, only one row is returned via the ODBC driver.

I can answer your query on pass-through queries.  Oracle has a package called DBMS_HS_PASSTHROUGH which can be used to pass queries to be executed natively in a remote database.  I am not using this feature, I am simply trying to query the PostgreSQL table from within an Oracle query.

I’ll pass on your comment regarding the buffer to Oracle Support and ask them to investigate this.

Thanks again for your help.

Regards,

John

Hello, John,

You tried retrieving one row with one column of type bytea with the following query, didn't you?

SELECT A1."icon" FROM "oracle_dbgw_test" A1

According to the line 3512 in your log, The driver seems to have gotten 7402 bytes from the database server.  So the driver has the data.

qresult: len=7402, buffer='\xffd8ffe000104a46494600010100000100010000ffdb0043000d090a0b0a080d0b0a0b0e0e0d0f13201513121213271c1e17202e2931302e292d2c333a4a3e333646372c2d405741464c4e525352323e5a615a50604a51524fffdb0043010e0e0e131113261515264f352d354f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4f4fffc00011080050007803012200021101031101ffc4001f0000010501010101010100000000000000000102030405060708090a0bffc400b5100002010303020403050504040000017d01020300041105122131410613516107227114328191a1082342b1c11552d1f02433627282090a161718191a25262728292a3435363738393a434445464748494a535455565758595a636465666768696a737475767778797a838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae1e2e3e4e5e6e7e8e9eaf1f2f3f4f5f6f7f8f9faffc4001f0100030101010101010101010000000000000102030405060708090a0bffc400b51100020102040403040705040400010277000102031104052131061241510761711322328108144291a1b1c109233352f0156272d10a162434e125f11718191a2627282

...

On the other hand, the following line shows that buffer is null, meaning that the caller (Oracle Gateway in your case) didn't specify the buffer to receive the data.

fetch: cols=1, lf=0, opts = 0xa035d70, opts->bindings = 0xa034bb0, buffer[] = (nil)

I'm not sure whether this is related, but the following manual page mentions "BLOB and CLOB data cannot be read by pass-through queries".  Why don't you ask the Oracle support what this means?

http://docs.oracle.com/database/121/ODBCU/feature.htm#ODBCU763

FYI, I could successfully get the content of a bytea column using isql command of unixODBC as follows.  Before this, I did "CREATE TABLE a (c bytea); INSERT INTO a VALUES('abcde');".

[isql session]

SQL> select * from a;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| c                                                                                                                                                                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 6162636465                                                                                                                                                                                                                                                     |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

For psqlODBC developers: Doesn't column_size = -4 indicate any problem?

PARSE: fieldtype=17, col_name='icon', column_size=-4

Regards

Takayuki Tsunakawa