Insert into postgres bytea

[nicholas.d]

Hello, i'm trying to insert a file into postgres bytea column using teiid-client 10.2.1.

If i insert via `setBytes` like this

try (PreparedStatement stmt = con.prepareStatement("insert into wpp_file_content(external_file_id, content) values (?, ?)")) {

    stmt.setString(1, externalFileId);

    stmt.setBytes(2, content);

    int result = stmt.executeUpdate();

}

I get the same message as in this (How to resolve io.netty.handler.codec.TooLongFrameException in jboss teiid. ) thread:

Caused by: org.teiid.jdbc.TeiidSQLException: Remote io.netty.handler.codec.TooLongFrameException: Adjusted frame length exceeds 4194304: 5519153 - discarded

at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:131)

at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:67)

at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:574)

at org.teiid.jdbc.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:319)

Thread says that it's a restriction for non-lob messages.

If i use `setBlob`

try (PreparedStatement stmt = con.prepareStatement("insert into wpp_file_content(external_file_id, content) values (?, ?)")) {

    stmt.setString(1, externalFileId);

    stmt.setBinaryStream(2, new ByteArrayInputStream(content), content.length);

    int result = stmt.executeUpdate();

}

i get exception like this

Caused by: org.teiid.core.TeiidProcessingException: TEIID30558 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30558 Error converting parameter number 2 with value "javax.sql.rowset.serial.SerialBlob@f767b5fd" of class javax.sql.rowset.serial.SerialBlob to expected type varbinary.

at org.teiid.dqp.internal.process.PreparedStatementRequest.resolveParameterValues(PreparedStatementRequest.java:261)

at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:146)

at org.teiid.dqp.internal.process.Request.processRequest(Request.java:481)

at org.teiid.dqp.internal.process.PreparedStatementRequest.processRequest(PreparedStatementRequest.java:294)

at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:655)

at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:338)

at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

... 1 more

Caused by: org.teiid.core.TeiidProcessingException: TEIID30082 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30082 Expected value of type 'varbinary' but '?' is of type 'blob' and no implicit conversion is available.

at org.teiid.query.resolver.util.ResolverUtil.convertExpression(ResolverUtil.java:232)

at org.teiid.query.resolver.util.ResolverUtil.convertExpression(ResolverUtil.java:197)

at org.teiid.dqp.internal.process.PreparedStatementRequest.resolveParameterValues(PreparedStatementRequest.java:254)

Also if i use this (Using Large Objects (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics) ) manual it turns out that `createBlob` is not supported.

Could you please advice how to insert blobs into postgres?

[rareddy]

The error saying that the table/view where you are inserting it still says it is modeled as "varbinary" and there is explicit conversion between blob and varbinary. Teiid does have BlobToBinaryTransform which should come into play, not sure why it is not You should probably log an issue into JIRA

[nicholas.d]

Ramesh, thanks for the answer. Does it mean that if it's converted from blob to varbinary i'll hit this data frame 2Mb cap again?

I'm using bytea porgresql type, VDB is dynamic, so i haven't manually defined any types for Teiid. Maybe i just have to use different data type in postgres?

[rareddy]

PostgreSQL does the Blob handling little different so I am not sure how Teiid supports this in its translator, I did a quick look at the translator nothing obvious I saw, maybe shawkins knows how this is handled.

[shawkins]

Varbinary values for Teiid are sent in the request message, so large values risk going over the maximum message size.

Blob values are sent as an adjunct and thus do not trigger the maximum message size issue, but are not seen as implicitly convertable to varbinary - as it's generally a lossful conversion.

There are a couple of things that can be done:

* raise the max message size - see the system property org.teiid.maxMessageSize

* add the import property importer.importLargeAsLob=true that should change the Teiid source table to blob rather than varbinary.  Alternatively you can add ddl to your vdb that includes and alter column statement.