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

From: Tsunakawa, Takayuki [mailto:tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com]
Sent: 12 July 2016 09:08
To: Robinson John JROB <john(dot)robinson(at)vaisala(dot)com>; pgsql-odbc(at)postgresql(dot)org
Subject: RE: PSQLODBC - Selecting bytea columns in Oracle

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

From: pgsql-odbc-owner(at)postgresql(dot)org<mailto:pgsql-odbc-owner(at)postgresql(dot)org> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of john(dot)robinson(at)vaisala(dot)com<mailto:john(dot)robinson(at)vaisala(dot)com>
Sent: Monday, July 11, 2016 11:20 PM
To: pgsql-odbc(at)postgresql(dot)org<mailto:pgsql-odbc(at)postgresql(dot)org>
Subject: [ODBC] PSQLODBC - Selecting bytea columns in Oracle

Hi,

I hope you can help. We have a PostgreSQL database which stores image data in bytea columns. I am attempting to read these images from within an Oracle database using the Oracle Database Gateway for ODBC and the PSQLODBC driver with the iODBC driver manager. However, the bytea column is being truncated to a single byte when selected within Oracle. I have attached the mylog_oracle????.log file plus the odbc.ini and odbcinst.ini. The software versions we are using are:

PostgreSQL database - 9.2.17
Oracle Database - 11.2.0.4
PSQLODBC driver - 9.5.3
iODBC Driver manager - 3.52.10

I have been working with Oracle Support for a while on this issue. Initially Oracle was displaying the bytea columns as a raw datatype with a length of 255 bytes. Oracle recommended setting the ByteaAsLongVarBinary to 1 in the odbc.ini file. This resulted in the bytea columns to be truncated to 1 byte within Oracle. Oracle have seen the log files and claim the data is being truncated within the ODBC driver.

Is there anything else I can try to resolve this issue so I can read all the data within the bytea columns from Oracle? Please let me know if I can provide any further information.

Many Thanks,

Regards,

John Robinson

John Robinson | Oracle Database Administrator
Vaisala Ltd, Information Services, Elm House, 351 Bristol Road, Birmingham, B5 7SW, United Kingdom
Tel +44 121 683 1237| Mobile +44 7739 874462
Email John(dot)Robinson(at)vaisala(dot)com<mailto:John(dot)Robinson(at)vaisala(dot)com> | www.vaisala.com<http://www.vaisala.com/>
Follow us on: Twitter<http://twitter.com/vaisalagroup> | Facebook<http://www.facebook.com/Vaisala> | YouTube<http://www.youtube.com/VaisalaGroup> | LinkedIn<http://www.linkedin.com/company/166747?trk=tyah>
P Please consider our environment before printing this email