writing a blob into database (informix 7.3)
User on Informix 7.3 couldn't insert a BYTE (blob) value into a table in a newly created blobspace, getting error 603 "Cannot close blob" both via dbaccess LOAD and via a JDBC program using setBinaryStream(). Suggestions included Jonathan Leffler's sqlcmd/insblob utilities (rejected: no ESQL/C available) and fixing the bogus length argument to setBinaryStream (should be the file length, not 10). The actual fix came from Superboer: after adding a new blobspace you must run 'onmode -l; onmode -c' to force a logical-log switch and checkpoint before it can be written to. The poster confirmed this solved it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have Informix 7.3 here and for various reasons we will be stuck
with it for a while. I need to write some graphical objects into a
database table and I was just assessing the feasibility but failed at
the first hurdle <sigh>. I created the simplest of tables, like this
one below:
create table tt_pics (
id char(10),
pic byte in blobspace
)
I then tried two ways of putting things into this table:
Method 1: using the LOAD command
The manual was very terse about loading stuff into a table with a byte
column. So I just took a shoot in the dark and hoped things would go
right.
I created a text file with the following content,
somebody|aabbccddee|
That 2nd column's value was just some bytes' values in 2-char hex
number form.
I then tried loading this text file into the table with an SQL command
like this:
load from 'the-text-file.txt' insert into tt_pics
Needless to say, I got an error message:
-------------------------------------------
603: Cannot close blob.
847: Error in load file line 1.
Error in line 18Near character position 22
--------------------------------------------
So, no go there. Let's talk about method 2.
Method 2:
I wrote a java program like this below:
------------------------------------------- snip
--------------------------------------------------------------------
import java.io.*;
import java.sql.*;
public class WriteBlob {
static public void main( String args[] ) {
String url = "jdbc:informix-sqli://localhost:8899/somedb:" +
"INFORMIXSERVER=ifxsvr;user=whoever;password=dontcare";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String strsql;
File picFile = new File("somepic.jpg");
java.io.BufferedInputStream bin = null;
int i;
try {
Class.forName( "com.informix.jdbc.IfxDriver" );
con = DriverManager.getConnection( url );
strsql = "insert into tt_pics values(?,?)";
pstmt = con.prepareStatement( strsql );
FileInputStream fis = new FileInputStream( picFile );
pstmt.setString( 1, "somebody" );
pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
i = pstmt.executeUpdate();
System.out.println("i=" + i);
} catch (ClassNotFoundException ce) {
System.out.println( "Class Not Found!!" );
} catch (SQLException se) {
System.out.println( "SQL exception: " + se.getMessage() );
se.printStackTrace();
} catch (Exception ie) {
System.out.println( "I/O Exception " + ie.getMessage() );
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception ep) {
System.out.println( ep.getMessage() );
}
}
}
}
------------------------------------------------ snip
-----------------------------------------------------------
When I ran it, I got the following error message:
SQL exception: Cannot close blob.
java.sql.SQLException: Cannot close blob.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:
373)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2352)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2268)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:775)
at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)
at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1233)
at
com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
ment.java:408)
at WriteBlob.main(WriteBlob.java:21)
There, I am now stuck. Can anybody give me a hand?
Get Jonathan Leffler's sqlcmd package it contains simple utilities to load
data into a blob = insblob.ec, updblob.ec, etc.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, May 14, 2010 at 3:45 AM, emrefan <dksleung@hotmail.com> wrote:
> We have Informix 7.3 here and for various reasons we will be stuck
> with it for a while. I need to write some graphical objects into a
> database table and I was just assessing the feasibility but failed at
> the first hurdle <sigh>. I created the simplest of tables, like this
> one below:
>
> create table tt_pics (
> id char(10),
> pic byte in blobspace
> )>
> I then tried two ways of putting things into this table:
>
> Method 1: using the LOAD command
>
> The manual was very terse about loading stuff into a table with a byte
> column. So I just took a shoot in the dark and hoped things would go
> right.
>
> I created a text file with the following content,
>
> somebody|aabbccddee|
>
> That 2nd column's value was just some bytes' values in 2-char hex
> number form.
>
> I then tried loading this text file into the table with an SQL command
> like this:
>
> load from 'the-text-file.txt' insert into tt_pics>
> Needless to say, I got an error message:
>
> -------------------------------------------
> 603: Cannot close blob.>
> 847: Error in load file line 1.
> Error in line 18> Near character position 22
> --------------------------------------------
>
> So, no go there. Let's talk about method 2.
>
> Method 2:
>
> I wrote a java program like this below:
>
> ------------------------------------------- snip
> --------------------------------------------------------------------
> import java.io.*;
> import java.sql.*;
>
> public class WriteBlob {
>
> static public void main( String args[] ) {
>
> String url = "jdbc:informix-sqli://localhost:8899/somedb:" +
>
> "INFORMIXSERVER=ifxsvr;user=whoever;password=dontcare";
> Connection con = null;
> PreparedStatement pstmt = null;
> ResultSet rs = null;
> String strsql;
> File picFile = new File("somepic.jpg");
> java.io.BufferedInputStream bin = null;
> int i;
>
> try {
> Class.forName( "com.informix.jdbc.IfxDriver" );
> con = DriverManager.getConnection( url );
> strsql = "insert into tt_pics values(?,?)";
> pstmt = con.prepareStatement( strsql );
> FileInputStream fis = new FileInputStream( picFile );
>
> pstmt.setString( 1, "somebody" );
> pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
>
> i = pstmt.executeUpdate();
> System.out.println("i=" + i);
>
> } catch (ClassNotFoundException ce) {
> System.out.println( "Class Not Found!!" );
> } catch (SQLException se) {
> System.out.println( "SQL exception: " + se.getMessage() );
> se.printStackTrace();
> } catch (Exception ie) {
> System.out.println( "I/O Exception " + ie.getMessage() );
> } finally {
> try {
> if (rs != null)
> rs.close();
> if (pstmt != null)
> pstmt.close();
> if (con != null)
> con.close();
> } catch (Exception ep) {
> System.out.println( ep.getMessage() );
> }
> }
> }
> }
> ------------------------------------------------ snip
> -----------------------------------------------------------
>
> When I ran it, I got the following error message:
>
> SQL exception: Cannot close blob.
> java.sql.SQLException: Cannot close blob.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:
> 373)
> at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)
> at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)
> at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2352)
> at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2268)
> at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:775)
> at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)
> at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1233)
> at
> com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
> ment.java:408)
> at WriteBlob.main(WriteBlob.java:21)
>
> There, I am now stuck. Can anybody give me a hand?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Hi,
Hi,
I am not sure about your first attempt because I am
not certain about the encoding that is used when
unloading / loading blobs in dbaccess.
But I am pretty sure at least your second attempt should
have worked.
(though the line
pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
in your java code seems weird. Why reading exactly
10 bytes from the file? Do something like
pstmt.setBinaryStream( 2, (InputStream) fis, (int) fis.lenght() );
instead.)
I never saw that error coming up and I do the same quite
often.
I assume that you freshly added the blobspace.
In that case you have to do an onmode -l; onmode -c
otherwise you can not write to the newly added blobspace.
or the damn thing was full....????
-----------
BTW IBM/Informix support folks V 11 does not show onstat -D for
blobspaces.
nr pages read/written is always zero!!
-----------
for unload format:
if your blobinfo is hex ff fc fd ad db 09
then this is found in the unload file.
so a line could look like:
-----------------------------------start unload
file-------------------
superboer|fffcfdad0907|
-----------------------------------end unload file-------------------
i did a quick look at the java stuff and it looks good.
if the above is not the solution to your issue, drop a new line,
i some working examples if you need them.
Superboer.
BTW for perf reasons if your blobs are bigger then say 120kb on
average i would gofor an blobspace, small
say 2k to 16kb i would put them in the table
create table tt_pics (
id char(10),
pic byte
)
even the new sblobspaces (V9 and higher) can not beat the performance
on update/insert.
Blobspaces will nuke obstacle when the average is 120KB or bigger.
On 14 mei, 14:22, Dirk Gunsthövel <d...@guncon.de> wrote:
> Hi,
>
> Hi,
>
> I am not sure about your first attempt because I am
> not certain about the encoding that is used when
> unloading / loading blobs in dbaccess.
>
> But I am pretty sure at least your second attempt should
> have worked.
>
> (though the line
> pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
> in your java code seems weird. Why reading exactly
> 10 bytes from the file? Do something like
> pstmt.setBinaryStream( 2, (InputStream) fis, (int) fis.lenght() );
> instead.)
>
> I never saw that error coming up and I do the same quite
> often.
>
> From the error message it looks like something is wrong
> with your DB.... maybe the blobspace?
>
> I think it would be easy and worth trying to locate the
> blob in tablespace instead of blobspace for a test.
>
> Regards,
> Dirk
>
> --
> --
> -- Dipl.-Math. Dirk Gunsthövel
> -- -professional services-
> --
> -- Dirk Gunsthövel IT Systemanalyse - GunCon
> -- Hammer Str. 13
> -- D-48153 Muenster
> -- phone: +49 (0) 251 28446- 0
> -- fax: +49 (0) 251 28446-55
> -- web: http://www.GunCon.de
> -- email: i...@GunCon.de
> -- UStId: DE 189527667
> --
> -- 'One now understands why some animals eat their young.'
> -- (Andrew in 'Bicentennial Man' 1999)
>
> "emrefan" <dksle...@hotmail.com> schrieb im Newsbeitragnews:f25c7ceb-3b61-4185-a005-5097536252bd@42g2000prb.googlegroups.com...
>
> > We have Informix 7.3 here and for various reasons we will be stuck
> > with it for a while. I need to write some graphical objects into a
> > database table and I was just assessing the feasibility but failed at
> > the first hurdle <sigh>. I created the simplest of tables, like this
> > one below:
>
> > create table tt_pics (
> > id char(10),> > pic byte in blobspace
> > )
>
> > I then tried two ways of putting things into this table:
>
> > Method 1: using the LOAD command
>
> > The manual was very terse about loading stuff into a table with a byte
> > column. So I just took a shoot in the dark and hoped things would go
> > right.
>
> > I created a text file with the following content,
>
> > somebody|aabbccddee|
>
> > That 2nd column's value was just some bytes' values in 2-char hex
> > number form.
>
> > I then tried loading this text file into the table with an SQL command
> > like this:
>
> > load from 'the-text-file.txt' insert into tt_pics>
> > Needless to say, I got an error message:
>
> > -------------------------------------------
> > 603: Cannot close blob.>
> > 847: Error in load file line 1.
> > Error in line 18> > Near character position 22
> > --------------------------------------------
>
> > So, no go there. Let's talk about method 2.
>
> > Method 2:
>
> > I wrote a java program like this below:
>
> > ------------------------------------------- snip
> > --------------------------------------------------------------------
> > import java.io.*;
> > import java.sql.*;
>
> > public class WriteBlob {
>
> > static public void main( String args[] ) {
>
> > String url = "jdbc:informix-sqli://localhost:8899/somedb:" +
>
> > "INFORMIXSERVER=ifxsvr;user=whoever;password=dontcare";
> > Connection con = null;
> > PreparedStatement pstmt = null;
> > ResultSet rs = null;
> > String strsql;
> > File picFile = new File("somepic.jpg");
> > java.io.BufferedInputStream bin = null;
> > int i;
>
> > try {
> > Class.forName( "com.informix.jdbc.IfxDriver" );
> > con = DriverManager.getConnection( url );
> > strsql = "insert into tt_pics values(?,?)";
> > pstmt = con.prepareStatement( strsql );
> > FileInputStream fis = new FileInputStream( picFile );
>
> > pstmt.setString( 1, "somebody" );
> > pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
>
> > i = pstmt.executeUpdate();
> > System.out.println("i=" + i);
>
> > } catch (ClassNotFoundException ce) {
> > System.out.println( "Class Not Found!!" );
> > } catch (SQLException se) {
> > System.out.println( "SQL exception: " + se.getMessage() );
> > se.printStackTrace();
> > } catch (Exception ie) {
> > System.out.println( "I/O Exception " + ie.getMessage() );
> > } finally {
> > try {
> > if (rs != null)
> > rs.close();
> > if (pstmt != null)
> > pstmt.close();
> > if (con != null)
> > con.close();
> > } catch (Exception ep) {
> > System.out.println( ep.getMessage() );
> > }
> > }
> > }
> > }
> > ------------------------------------------------ snip
> > -----------------------------------------------------------
>
> > When I ran it, I got the following error message:
>
> > SQL exception: Cannot close blob.
> > java.sql.SQLException: Cannot close blob.
> > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:
> > 373)
> > at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)
> > at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)
> > at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2352)
> > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2268)
> > at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:775)
> > at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)
> > at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1233)
> > at
> > com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
> > ment.java:408)
> >
On 5月14日, 下午6時23分, Art Kagel <art.ka...@gmail.com> wrote: > Get Jonathan Leffler's sqlcmd package it contains simple utilities to load > data into a blob = insblob.ec, updblob.ec, etc. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) I did look into that alternative (thinking at least I could proceed with some testing even if I still have to figure out how my final solution is going to work), but unfortuately we don't have esql/c. I think a binary version for my Solaris 2.6 (very dated, I know) won't be easy to find. But thanks for the idea.
On 5月14日, 下午8時22分, Dirk Gunsthövel <d...@guncon.de> wrote:
> Hi,
>
> Hi,
>
> I am not sure about your first attempt because I am
> not certain about the encoding that is used when
> unloading / loading blobs in dbaccess.
>
> But I am pretty sure at least your second attempt should
> have worked.
>
> (though the line
> pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
> in your java code seems weird. Why reading exactly
> 10 bytes from the file? Do something like
> pstmt.setBinaryStream( 2, (InputStream) fis, (int) fis.lenght() );
> instead.)
>
> I never saw that error coming up and I do the same quite
> often.
>
> From the error message it looks like something is wrong
> with your DB.... maybe the blobspace?
>
> I think it would be easy and worth trying to locate the
> blob in tablespace instead of blobspace for a test.
>
> Regards,
> Dirk
>
> --
> --
> -- Dipl.-Math. Dirk Gunsthövel
> -- -professional services-
> --
> -- Dirk Gunsthövel IT Systemanalyse - GunCon
> -- Hammer Str. 13
> -- D-48153 Muenster
> -- phone: +49 (0) 251 28446- 0
> -- fax: +49 (0) 251 28446-55
> -- web: http://www.GunCon.de
> -- email: i...@GunCon.de
> -- UStId: DE 189527667
> --
> -- 'One now understands why some animals eat their young.'
> -- (Andrew in 'Bicentennial Man' 1999)
>
> "emrefan" <dksle...@hotmail.com> schrieb im Newsbeitragnews:f25c7ceb-3b61-4185-a005-5097536252bd@42g2000prb.googlegroups.com...
>
>
>
> > We have Informix 7.3 here and for various reasons we will be stuck
> > with it for a while. I need to write some graphical objects into a
> > database table and I was just assessing the feasibility but failed at
> > the first hurdle <sigh>. I created the simplest of tables, like this
> > one below:
>
> > create table tt_pics (
> > id char(10),> > pic byte in blobspace
> > )
>
> > I then tried two ways of putting things into this table:
>
> > Method 1: using the LOAD command
>
> > The manual was very terse about loading stuff into a table with a byte
> > column. So I just took a shoot in the dark and hoped things would go
> > right.
>
> > I created a text file with the following content,
>
> > somebody|aabbccddee|
>
> > That 2nd column's value was just some bytes' values in 2-char hex
> > number form.
>
> > I then tried loading this text file into the table with an SQL command
> > like this:
>
> > load from 'the-text-file.txt' insert into tt_pics>
> > Needless to say, I got an error message:
>
> > -------------------------------------------
> > 603: Cannot close blob.>
> > 847: Error in load file line 1.
> > Error in line 18> > Near character position 22
> > --------------------------------------------
>
> > So, no go there. Let's talk about method 2.
>
> > Method 2:
>
> > I wrote a java program like this below:
>
> > ------------------------------------------- snip
> > --------------------------------------------------------------------
> > import java.io.*;
> > import java.sql.*;
>
> > public class WriteBlob {
>
> > static public void main( String args[] ) {
>
> > String url = "jdbc:informix-sqli://localhost:8899/somedb:" +
>
> > "INFORMIXSERVER=ifxsvr;user=whoever;password=dontcare";
> > Connection con = null;
> > PreparedStatement pstmt = null;
> > ResultSet rs = null;
> > String strsql;
> > File picFile = new File("somepic.jpg");
> > java.io.BufferedInputStream bin = null;
> > int i;
>
> > try {
> > Class.forName( "com.informix.jdbc.IfxDriver" );
> > con = DriverManager.getConnection( url );
> > strsql = "insert into tt_pics values(?,?)";
> > pstmt = con.prepareStatement( strsql );
> > FileInputStream fis = new FileInputStream( picFile );
>
> > pstmt.setString( 1, "somebody" );
> > pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
>
> > i = pstmt.executeUpdate();
> > System.out.println("i=" + i);
>
> > } catch (ClassNotFoundException ce) {
> > System.out.println( "Class Not Found!!" );
> > } catch (SQLException se) {
> > System.out.println( "SQL exception: " + se.getMessage() );
> > se.printStackTrace();
> > } catch (Exception ie) {
> > System.out.println( "I/O Exception " + ie.getMessage() );
> > } finally {
> > try {
> > if (rs != null)
> > rs.close();
> > if (pstmt != null)
> > pstmt.close();
> > if (con != null)
> > con.close();
> > } catch (Exception ep) {
> > System.out.println( ep.getMessage() );
> > }
> > }
> > }
> > }
> > ------------------------------------------------ snip
> > -----------------------------------------------------------
>
> > When I ran it, I got the following error message:
>
> > SQL exception: Cannot close blob.
> > java.sql.SQLException: Cannot close blob.
> > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:
> > 373)
> > at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)
> > at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)
> > at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2352)
> > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2268)
> > at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:775)
> > at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)
> > at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1233)
> > at
> > com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
> > ment.java:408)
> > at WriteBlob.main(WriteBlob.java:21)
>
> > There, I am now stuck. Can anybody give me a hand?- 隱藏被引用文字 -
>
> - 顯示被引用文字 -
That's third parameter to the setBinaryStream() call was the result of
a "not thinking much cut & paste". I didn't even know what that third
parameter is for until your pointed it out. Thanks. That was not the
root of the problem though. I needed to do a "onmode -l; onmode -c" as
some other nice guy here pointed out to me. That fixed it.
Many thanks for your help and your offer to help further. This is a
really nice community. You know, not many around me are still using
Informix and I feel a bit lonely and helpless, especially when I have
a problem.
On 5月14日, 下午8時43分, Superboer <superbo...@t-online.de> wrote:
> I assume that you freshly added the blobspace.
> In that case you have to do an onmode -l; onmode -c
> otherwise you can not write to the newly added blobspace.
> or the damn thing was full....????
That was magic! That fixed it! I restarted the informix server at
least twice after creating the blobspace, never aware that I still
need to run those onmode commands. Thanks a million.
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape