writing a blob into database (informix 7.3)

Discussion:

(too old to reply)

emrefan

2010-05-14 07:45:47 UTC

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?

Art Kagel

2010-05-14 10:23:35 UTC

Permalink

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 (***@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.

Post by emrefan
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
create table tt_pics (
id char(10),
pic byte in blobspace
)
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
load from 'the-text-file.txt' insert into tt_pics
-------------------------------------------
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.
------------------------------------------- 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
-----------------------------------------------------------
SQL exception: Cannot close blob.
java.sql.SQLException: Cannot close blob.
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
http://www.iiug.org/mailman/listinfo/informix-list

emrefan

2010-05-15 01:34:22 UTC

Permalink

Post by Art Kagel
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)

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.

Dirk Gunsthövel

2010-05-14 12:22:00 UTC

Permalink

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: ***@GunCon.de
-- UStId: DE 189527667
--
-- 'One now understands why some animals eat their young.'
-- (Andrew in 'Bicentennial Man' 1999)

Superboer

2010-05-14 12:43:15 UTC

Permalink

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.

Post by Dirk GunsthÃ¶vel
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
-- UStId: DE 189527667
--
-- 'One now understands why some animals eat their young.'
-- (Andrew in 'Bicentennial Man' 1999)

emrefan

2010-05-15 01:45:32 UTC

Permalink

Post by Superboer
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.

emrefan

2010-05-15 01:42:45 UTC

Permalink

- 顯示被引用文字 -

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.

Jonathan Leffler

2010-05-16 05:54:27 UTC

Permalink

That's a weird error. The load file you showed should have worked fine.
I was doing some blob work last week - using SQLCMD rather than
DB-Access - but I'm 99.5% sure it would have worked OK in DB-Access too.
The one difference was I was using BYTE {IN TABLE}; can you try that
temporarily? That will help isolate the issue.

You said Informix 7.3; did you mean 7.30.UC1 or 7.31.UD9? There's about
a decade between the releases. You've left it a bit late if you are
using a 7.30 or 7.31.UCx version to upgrade, but...

Post by emrefan
So, no go there. Let's talk about method 2.
[...snip...]
There, I am now stuck. Can anybody give me a hand?

-=JL=-

emrefan

2010-05-18 03:04:15 UTC

Permalink

I think the fact I was using "in bolbspace" caused the error. After I
ran "onmode -l; onmode -c" as suggested by some nice guy here, all was
fine. And I just tried it out, with "in table", there was no such
error.

You said Informix 7.3; did you mean 7.30.UC1 or 7.31.UD9? There's about
a decade between the releases. You've left it a bit late if you are
using a 7.30 or 7.31.UCx version to upgrade, but...

We have Informix 7.30 UC6.