[teiid-issues] [JBoss JIRA] Commented: (TEIID-1163) Provide LOB support in ODBC

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-1163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12581230#comment-12581230 ] 

Ramesh Reddy commented on TEIID-1163:
-------------------------------------

The issue in supporting this was to implement the streaming API on ODBC to over come the memory issues. PG supports streaming using the "lo" data type.

After looking into psql-odbc code and implementing good portion of the "lo" protocol in Teiid that postgres supports (lo_open, lo_read, lo_seek, lo_tell), it looks like even though psql-odbc driver uses these function calls, but never uses for streaming purposes. i.e. if there is lob with 1 GB size, it will try to read the file at once based on the buffer size that client provided, and if the data runs over the buffer size it truncates it.

The issue is not with truncation, but the issue is a single read, this forces the Teiid Server to materialize the contents of the lob to memory before it can place the content on the wire, which I wanted to avoid. Also, the lob access happens after the Statement is closed, this may be allowed in the other databases, however in Teiid when statement is closed the lobs are also closed.

Using the "bytea" as the PG data type for lobs in Teiid overcomes the last issue, however the materialization is still an issue. Since "lo" is discouraged api, and no apparent benefit of using it for odbc purposes (other clients may do this fine) and "bytea' is simple to implement, bytea is a valid option to pursue.

One HUGE con about using the "bytea" content is that the client always expects the data in "hex" format, so server need to convert the data to hex before it can send it. This is not an issue for the clobs and SQLXML as they are treated to be CHAR based content. Client convert back to binary based on the type of client data type it is mapped to. In effect there are 2 bytes going across for every byte of content in the case of blob. 

> Provide LOB support in ODBC
> ---------------------------
>
>                 Key: TEIID-1163
>                 URL: https://issues.jboss.org/browse/TEIID-1163
>             Project: Teiid
>          Issue Type: Bug
>          Components: ODBC
>    Affects Versions: 7.1
>            Reporter: Ramesh Reddy
>            Assignee: Ramesh Reddy
>             Fix For: 7.4
>
>
> LOB support was not provided with initial release, this needs to be supported for BLOBS, CLOBS and XML.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira