Can't INSERT blob into specific blob space using JDBC
A JDBC application failed to INSERT BLOBs on a server with no default SBSPACENAME in onconfig, even though the table was created with "put BLOB_TYPE in (mysbs)" — the driver still tried the default sbspace, giving errors -9810/-12053 ("No sbspace number specified"). This reproduced with IBM and DataDirect drivers, Informix 10, and even the driver's own blob demos. It was identified as a known JDBC driver bug (APAR 174988), present in 3.00.JC3; a patch, 3.00.JC3X4, was said to fix it, obtainable via IBM tech support. No confirmation posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hello all,
We have an application that uses JDBC for database access. We have a
customer using Informix 9.x and when we installed our application, all
our INSERTs were failing. We determined that it was because there was
no default blob space(s) configured for the Informix server. This
customer does not/will not configure a default blob space in their
Informix database. They were however willing to create a blob space for
our use, so we modified our CREATE TABLE scripts to specify the newly
created blob space for the different columns to use like the following
example:
CREATE TABLE MY_DB_TYPES
(
PKEY INTEGER NOT NULL,
VARCHAR_TYPE LVARCHAR,
INT_TYPE INTEGER,
BIGINT_TYPE INT8,
DOUBLE_TYPE FLOAT,
BLOB_TYPE BLOB,
PRIMARY KEY (PKEY)
) put BLOB_TYPE in (mysbs)
What we found when we tested it against our Informix DB was that even
if we specified the blob space to use when we created the table, when
we performed an INSERT it was still putting the data in the default
blob space, not in the blob space we created/specified. On the
customers system we get an exception like the one below since they do
not have a default blob space configured. Works just fine if a default
space is configured.
We have tried the JDBC driver from both IBM and DataDirect with the
same result. We can reproduce in house if we remove the default
SBSPACENAME parameter from the onconfig file and bounce the informix
server.
Is there some other issue or bug that anyone is aware of with
JDBC/Informix? Is it possible that even when specifying a specific blob
space when creating a table that Informix needs to use the default blob
space as a temporary space before it is ultimately stored in the
specified space?
Wish I had more detail, but sort of an Informix noob. Any and all
suggestions are much appreciated!
kevin
Top-level SQLException: java.sql.SQLException: Smart-large-object
error. SQLException error code: -9810
Nested Cause: Smart Large Objects: No sbspace number specified.
SQLException error code: -12053
Stack Trace:
java.sql.SQLException: Smart-large-object error.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5640)
at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5565)
at com.informix.jdbc.IfxSmartBlob.IfxLoCreate(IfxSmartBlob.java:376)
at com.informix.jdbc.IfxSmBlob.i(IfxSmBlob.java:770)
at com.informix.jdbc.IfxSmBlob.a(IfxSmBlob.java:505)
at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:282)
at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1253)
at
com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:408)
at com.workingdogs.village.Record.saveWithInsert(Record.java:1644)
at com.workingdogs.village.Record.save(Record.java:1093)
at com.workingdogs.village.Record.save(Record.java:1063)
at
org.apache.torque.util.BasePeer.insertOrUpdateRecord(BasePeer.java:863)
at org.apache.torque.util.BasePeer.doInsert(BasePeer.java:706)
at com.mycompany.ix.db.peer.BaseIxResourcePeer.doInsert(Unknown
Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.geobot.common.util.loader.ApplLauncher.main(ApplLauncher.java:200)
Caused by: java.sql.SQLException: Smart Large Objects: No sbspace
number specified.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
... 28 more
What version of the JDBC driver are you using? There is one bug that
has been mentioned on 3.00.JC2 version of the JDBC drive similar to
what you are seeing...
174988
IN USING JDBC, THE BLOB DATA ISN'T LOADED IN THE SBSPACE WHERE THE
TABLE IS SAYING (PUT COL IN SBSPACE)
I will get the details of the version with a fix for the above bug and
a response to your other question of having a default SBSPACENAME in
the ONCONFIG file in a separate note.
VG.
kyancy@gmail.com wrote:
> hello all,
>
> We have an application that uses JDBC for database access. We have a
> customer using Informix 9.x and when we installed our application, all
> our INSERTs were failing. We determined that it was because there was
> no default blob space(s) configured for the Informix server. This
> customer does not/will not configure a default blob space in their
> Informix database. They were however willing to create a blob space for
> our use, so we modified our CREATE TABLE scripts to specify the newly
> created blob space for the different columns to use like the following
> example:
>
> CREATE TABLE MY_DB_TYPES
> (
> PKEY INTEGER NOT NULL,
> VARCHAR_TYPE LVARCHAR,
> INT_TYPE INTEGER,
> BIGINT_TYPE INT8,
> DOUBLE_TYPE FLOAT,
> BLOB_TYPE BLOB,
> PRIMARY KEY (PKEY)
> ) put BLOB_TYPE in (mysbs)>
> What we found when we tested it against our Informix DB was that even
> if we specified the blob space to use when we created the table, when
> we performed an INSERT it was still putting the data in the default
> blob space, not in the blob space we created/specified. On the
> customers system we get an exception like the one below since they do
> not have a default blob space configured. Works just fine if a default
> space is configured.
>
> We have tried the JDBC driver from both IBM and DataDirect with the
> same result. We can reproduce in house if we remove the default
> SBSPACENAME parameter from the onconfig file and bounce the informix
> server.
>
> Is there some other issue or bug that anyone is aware of with
> JDBC/Informix? Is it possible that even when specifying a specific blob
> space when creating a table that Informix needs to use the default blob
> space as a temporary space before it is ultimately stored in the
> specified space?
>
> Wish I had more detail, but sort of an Informix noob. Any and all
> suggestions are much appreciated!
>
> kevin
>
>
> Top-level SQLException: java.sql.SQLException: Smart-large-object
> error. SQLException error code: -9810
> Nested Cause: Smart Large Objects: No sbspace number specified.
> SQLException error code: -12053
> Stack Trace:
> java.sql.SQLException: Smart-large-object error.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
> at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208)
> at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
> at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
> at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
> at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5640)
> at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5565)
> at com.informix.jdbc.IfxSmartBlob.IfxLoCreate(IfxSmartBlob.java:376)
> at com.informix.jdbc.IfxSmBlob.i(IfxSmBlob.java:770)
> at com.informix.jdbc.IfxSmBlob.a(IfxSmBlob.java:505)
> at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:282)
> at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1253)
> at
> com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:408)
> at com.workingdogs.village.Record.saveWithInsert(Record.java:1644)
> at com.workingdogs.village.Record.save(Record.java:1093)
> at com.workingdogs.village.Record.save(Record.java:1063)
> at
> org.apache.torque.util.BasePeer.insertOrUpdateRecord(BasePeer.java:863)
> at org.apache.torque.util.BasePeer.doInsert(BasePeer.java:706)
> at com.mycompany.ix.db.peer.BaseIxResourcePeer.doInsert(Unknown
> Source)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at
> com.geobot.common.util.loader.ApplLauncher.main(ApplLauncher.java:200)
> Caused by: java.sql.SQLException: Smart Large Objects: No sbspace
> number specified.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
> at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
> ... 28 more
I tried an example with 3.00.JC3 and it works fine without having a default SBSPACENAME in the onconfig file. VG.
Thank you very much for your response. We are using 3.00.JC3 with version 10 of Informix but still see the problem. The table create works fine, but when we try to do the INSERT with no default space defined we get the exception above. Any other suggestions are greatly appreciated. thx again for trying to help out. kevin
Hello again to all, Thx for everyone's responses. Just wanted to note that I tried the blob examples that come with version 3.0.JC3 of the JDBC driver and they too fail with the same error when I try to do the insert. I modified the example to include the PUT for the blob columns in a specific space but even the example complains about no sbspace number if a default not configured when running version 10 of informix so I am at a loss. thx again for any insights u may have. =========== Test Starts Creating Table demo1_1 and demo1_2 ... Trying to insert data into demo1_1.. Inserting data ... FAILED to Insert into tab: java.sql.SQLException: Smart-large-object error. java.sql.SQLException: Smart-large-object error. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269) at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5640) at com.informix.jdbc.IfxSqli.executeFastPath(IfxSqli.java:5565) at com.informix.jdbc.IfxSmartBlob.IfxLoCreate(IfxSmartBlob.java:376) at com.informix.jdbc.IfxSmBlob.i(IfxSmBlob.java:770) at com.informix.jdbc.IfxSmBlob.a(IfxSmBlob.java:505) at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:282) at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1253) at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:408) at demo1.set_execute(demo1.java:382) at demo1.main(demo1.java:237) Caused by: java.sql.SQLException: Smart Large Objects: No sbspace number specified. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523) ... 12 more
Sorry for the incorrect response earlier. It still appears on version 3.00.JC3. There is a patch available with the fix (3.00.JC3X4) . Please work with tech support to get this patch. VG.
Hello, Can't tell you how grateful I am for this information! We don't have a support contract unfortunately, but I suspect I can work through our customer to get this patch as they should have an IBM support agreement in place. Will post if this solves the issue. Thank you again for your help. Much appreciated. Kevin