SEO

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(IfxErr Msg.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.j ava: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(IfxSmar tBlob.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.executeUpda te(IfxPreparedStatement.java:408)
at com.workingdogs.village.Record.saveWithInsert(Reco rd.java:1644)
at com.workingdogs.village.Record.save(Record.java:10 93)
at com.workingdogs.village.Record.save(Record.java:10 63)
at
org.apache.torque.util.BasePeer.insertOrUpdateReco rd(BasePeer.java:863)
at org.apache.torque.util.BasePeer.doInsert(BasePeer. java:706)
at com.mycompany.ix.db.peer.BaseIxResourcePeer.doInse rt(Unknown
Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
com.geobot.common.util.loader.ApplLauncher.main(Ap plLauncher.java:200)
Caused by: java.sql.SQLException: Smart Large Objects: No sbspace
number specified.
at com.informix.util.IfxErrMsg.getSQLException(IfxErr Msg.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(IfxErr Msg.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.j ava: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(IfxSmar tBlob.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.executeUpda te(IfxPreparedStatement.java:408)
> at com.workingdogs.village.Record.saveWithInsert(Reco rd.java:1644)
> at com.workingdogs.village.Record.save(Record.java:10 93)
> at com.workingdogs.village.Record.save(Record.java:10 63)
> at
> org.apache.torque.util.BasePeer.insertOrUpdateReco rd(BasePeer.java:863)
> at org.apache.torque.util.BasePeer.doInsert(BasePeer. java:706)
> at com.mycompany.ix.db.peer.BaseIxResourcePeer.doInse rt(Unknown
> Source)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at
> com.geobot.common.util.loader.ApplLauncher.main(Ap plLauncher.java:200)
> Caused by: java.sql.SQLException: Smart Large Objects: No sbspace
> number specified.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErr Msg.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(IfxErr Msg.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.j ava: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(IfxSmar tBlob.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.executeUpda te(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(IfxErr Msg.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

Hi All

Just wanted to let everyone know that our customer was able to get the
3.00.JC3X4 patched version of the JDBC driver from IBM and it seems to
have solved the problem! Many thx to everyone that responded.

kevin