f



writing a blob into database (informix 7.3)

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?
0
emrefan
5/14/2010 7:45:47 AM
comp.databases.informix 16083 articles. 0 followers. Post Follow

8 Replies
1460 Views

Similar Articles

[PageSpeed] 55

--000e0cd6083ec302ac04868b4527
Content-Type: text/plain; charset=ISO-8859-1

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
>

--000e0cd6083ec302ac04868b4527
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Get Jonathan Leffler&#39;s sqlcmd package it contains simple utilities to l=
oad data into a blob =3D <a href=3D"http://insblob.ec">insblob.ec</a>, <a h=
ref=3D"http://updblob.ec">updblob.ec</a>, etc.<br><br>Art<br><br clear=3D"a=
ll">Art S. Kagel<br>

Advanced DataTools (<a href=3D"http://www.advancedatatools.com">www.advance=
datatools.com</a>)<br>IIUG Board of Directors (<a href=3D"mailto:art@iiug.o=
rg">art@iiug.org</a>)<br><br>Disclaimer: Please keep in mind that my own op=
inions are my own opinions and do not reflect on my employer, Advanced Data=
Tools, the IIUG, nor any other organization with which I am associated eith=
er explicitly, implicitly, or by inference. =A0Neither do those opinions re=
flect those of other individuals affiliated with any entity with which I am=
 affiliated nor those of the entities themselves.<br>

<br>
<br><br><div class=3D"gmail_quote">On Fri, May 14, 2010 at 3:45 AM, emrefan=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:dksleung@hotmail.com">dksleung@hot=
mail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=
=3D"margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); p=
adding-left: 1ex;">

We have Informix 7.3 here and for various reasons we will be stuck<br>
with it for a while. I need to write some graphical objects into a<br>
database table and I was just assessing the feasibility but failed at<br>
the first hurdle &lt;sigh&gt;. =A0I created the simplest of tables, like th=
is<br>
one below:<br>
<br>
create table tt_pics (<br>
 =A0 =A0 id =A0 =A0 =A0 =A0char(10),<br>
 =A0 =A0 pic =A0 =A0 =A0byte in blobspace<br>
)<br>
<br>
I then tried two ways of putting things into this table:<br>
<br>
Method 1: using the LOAD command<br>
<br>
The manual was very terse about loading stuff into a table with a byte<br>
column. So I just took a shoot in the dark and hoped things would go<br>
right.<br>
<br>
I created a text file with the following content,<br>
<br>
somebody|aabbccddee|<br>
<br>
That 2nd column&#39;s value was just some bytes&#39; values in 2-char hex<b=
r>
number form.<br>
<br>
I then tried loading this text file into the table with an SQL command<br>
like this:<br>
<br>
load from &#39;the-text-file.txt&#39; insert into tt_pics<br>
<br>
Needless to say, I got an error message:<br>
<br>
-------------------------------------------<br>
 =A0603: Cannot close blob.<br>
<br>
 =A0847: Error in load file line 1.<br>
Error in line 18<br>
Near character position 22<br>
--------------------------------------------<br>
<br>
So, no go there. Let&#39;s talk about method 2.<br>
<br>
Method 2:<br>
<br>
I wrote a java program like this below:<br>
<br>
------------------------------------------- snip<br>
--------------------------------------------------------------------<br>
import java.io.*;<br>
import java.sql.*;<br>
<br>
public class WriteBlob {<br>
<br>
 =A0 static public void main( String args[] ) {<br>
<br>
 =A0 =A0 =A0String url =3D &quot;jdbc:informix-sqli://localhost:8899/somedb=
:&quot; +<br>
<br>
&quot;INFORMIXSERVER=3Difxsvr;user=3Dwhoever;password=3Ddontcare&quot;;<br>
 =A0 =A0 =A0Connection con =3D null;<br>
 =A0 =A0 =A0PreparedStatement pstmt =3D null;<br>
 =A0 =A0 =A0ResultSet rs =3D null;<br>
 =A0 =A0 =A0String strsql;<br>
 =A0 =A0 =A0File picFile =3D new File(&quot;somepic.jpg&quot;);<br>
 =A0 =A0 =A0java.io.BufferedInputStream bin =3D null;<br>
 =A0 =A0 =A0int i;<br>
<br>
 =A0 =A0 =A0try {<br>
 =A0 =A0 =A0 =A0 Class.forName( &quot;com.informix.jdbc.IfxDriver&quot; );<=
br>
 =A0 =A0 =A0 =A0 con =3D DriverManager.getConnection( url );<br>
 =A0 =A0 =A0 =A0 strsql =3D &quot;insert into tt_pics values(?,?)&quot;;<br=
>
 =A0 =A0 =A0 =A0 pstmt =3D con.prepareStatement( strsql );<br>
 =A0 =A0 =A0 =A0 FileInputStream fis =3D new FileInputStream( picFile );<br=
>
<br>
 =A0 =A0 =A0 =A0 pstmt.setString( 1, &quot;somebody&quot; );<br>
 =A0 =A0 =A0 =A0 pstmt.setBinaryStream( 2, (InputStream) fis, 10 );<br>
<br>
 =A0 =A0 =A0 =A0 i =3D pstmt.executeUpdate();<br>
 =A0 =A0 =A0 =A0 System.out.println(&quot;i=3D&quot; + i);<br>
<br>
 =A0 =A0 =A0} catch (ClassNotFoundException ce) {<br>
 =A0 =A0 =A0 =A0 System.out.println( &quot;Class Not Found!!&quot; );<br>
 =A0 =A0 =A0} catch (SQLException =A0se) {<br>
 =A0 =A0 =A0 =A0 System.out.println( &quot;SQL exception: &quot; + se.getMe=
ssage() );<br>
 =A0 =A0 =A0 =A0 se.printStackTrace();<br>
 =A0 =A0 =A0} catch (Exception ie) {<br>
 =A0 =A0 =A0 =A0 System.out.println( &quot;I/O Exception &quot; + ie.getMes=
sage() );<br>
 =A0 =A0 =A0} finally {<br>
 =A0 =A0 =A0 =A0 try {<br>
 =A0 =A0 =A0 =A0 =A0 =A0if (rs !=3D null)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 rs.close();<br>
 =A0 =A0 =A0 =A0 =A0 =A0if (pstmt !=3D null)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 pstmt.close();<br>
 =A0 =A0 =A0 =A0 =A0 =A0if (con !=3D null)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 con.close();<br>
 =A0 =A0 =A0 =A0 } catch (Exception ep) {<br>
 =A0 =A0 =A0 =A0 =A0 System.out.println( ep.getMessage() );<br>
 =A0 =A0 =A0 =A0 }<br>
 =A0 =A0 =A0}<br>
 =A0 }<br>
}<br>
------------------------------------------------ snip<br>
-----------------------------------------------------------<br>
<br>
When I ran it, I got the following error message:<br>
<br>
SQL exception: Cannot close blob.<br>
java.sql.SQLException: Cannot close blob.<br>
 =A0 =A0 =A0 =A0at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.ja=
va:<br>
373)<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2352)=
<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:22=
68)<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:77=
5)<br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)<=
br>
 =A0 =A0 =A0 =A0at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1233)=
<br>
 =A0 =A0 =A0 =A0at<br>
com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState<br>
ment.java:408)<br>
 =A0 =A0 =A0 =A0at WriteBlob.main(WriteBlob.java:21)<br>
<br>
There, I am now stuck. =A0Can anybody give me a hand?<br>
_______________________________________________<br>
Informix-list mailing list<br>
<a href=3D"mailto:Informix-list@iiug.org">Informix-list@iiug.org</a><br>
<a href=3D"http://www.iiug.org/mailman/listinfo/informix-list" target=3D"_b=
lank">http://www.iiug.org/mailman/listinfo/informix-list</a><br>
</blockquote></div><br>

--000e0cd6083ec302ac04868b4527--
0
Art
5/14/2010 10:23:35 AM
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: info@GunCon.de
-- UStId: DE 189527667
--
--     'One now understands why some animals eat their young.'
--     (Andrew in 'Bicentennial Man' 1999)



"emrefan" <dksleung@hotmail.com> schrieb im Newsbeitrag news: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? 

0
iso
5/14/2010 12:22:00 PM
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=F6vel <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
> =A0 =A0 pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
> in your java code seems weird. Why reading exactly
> 10 bytes from the file? Do something like
> =A0 =A0 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=F6vel
> -- -professional services-
> --
> -- Dirk Gunsth=F6vel IT Systemanalyse - GunCon
> -- Hammer Str. 13
> -- D-48153 Muenster
> -- phone: +49 (0) 251 28446- 0
> -- fax: =A0 +49 (0) 251 28446-55
> -- web: =A0http://www.GunCon.de
> -- email: i...@GunCon.de
> -- UStId: DE 189527667
> --
> -- =A0 =A0 'One now understands why some animals eat their young.'
> -- =A0 =A0 (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>. =A0I created the simplest of tables, like this
> > one below:
>
> > create table tt_pics (
> > =A0 =A0 id =A0 =A0 =A0 =A0char(10),
> > =A0 =A0 pic =A0 =A0 =A0byte 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:
>
> > -------------------------------------------
> > =A0603: Cannot close blob.
>
> > =A0847: 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 {
>
> > =A0 static public void main( String args[] ) {
>
> > =A0 =A0 =A0String url =3D "jdbc:informix-sqli://localhost:8899/somedb:"=
 +
>
> > "INFORMIXSERVER=3Difxsvr;user=3Dwhoever;password=3Ddontcare";
> > =A0 =A0 =A0Connection con =3D null;
> > =A0 =A0 =A0PreparedStatement pstmt =3D null;
> > =A0 =A0 =A0ResultSet rs =3D null;
> > =A0 =A0 =A0String strsql;
> > =A0 =A0 =A0File picFile =3D new File("somepic.jpg");
> > =A0 =A0 =A0java.io.BufferedInputStream bin =3D null;
> > =A0 =A0 =A0int i;
>
> > =A0 =A0 =A0try {
> > =A0 =A0 =A0 =A0 Class.forName( "com.informix.jdbc.IfxDriver" );
> > =A0 =A0 =A0 =A0 con =3D DriverManager.getConnection( url );
> > =A0 =A0 =A0 =A0 strsql =3D "insert into tt_pics values(?,?)";
> > =A0 =A0 =A0 =A0 pstmt =3D con.prepareStatement( strsql );
> > =A0 =A0 =A0 =A0 FileInputStream fis =3D new FileInputStream( picFile );
>
> > =A0 =A0 =A0 =A0 pstmt.setString( 1, "somebody" );
> > =A0 =A0 =A0 =A0 pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
>
> > =A0 =A0 =A0 =A0 i =3D pstmt.executeUpdate();
> > =A0 =A0 =A0 =A0 System.out.println("i=3D" + i);
>
> > =A0 =A0 =A0} catch (ClassNotFoundException ce) {
> > =A0 =A0 =A0 =A0 System.out.println( "Class Not Found!!" );
> > =A0 =A0 =A0} catch (SQLException =A0se) {
> > =A0 =A0 =A0 =A0 System.out.println( "SQL exception: " + se.getMessage()=
 );
> > =A0 =A0 =A0 =A0 se.printStackTrace();
> > =A0 =A0 =A0} catch (Exception ie) {
> > =A0 =A0 =A0 =A0 System.out.println( "I/O Exception " + ie.getMessage() =
);
> > =A0 =A0 =A0} finally {
> > =A0 =A0 =A0 =A0 try {
> > =A0 =A0 =A0 =A0 =A0 =A0if (rs !=3D null)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 rs.close();
> > =A0 =A0 =A0 =A0 =A0 =A0if (pstmt !=3D null)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 pstmt.close();
> > =A0 =A0 =A0 =A0 =A0 =A0if (con !=3D null)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 con.close();
> > =A0 =A0 =A0 =A0 } catch (Exception ep) {
> > =A0 =A0 =A0 =A0 =A0 System.out.println( ep.getMessage() );
> > =A0 =A0 =A0 =A0 }
> > =A0 =A0 =A0}
> > =A0 }
> > }
> > ------------------------------------------------ snip
> > -----------------------------------------------------------
>
> > When I ran it, I got the following error message:
>
> > SQL exception: Cannot close blob.
> > java.sql.SQLException: Cannot close blob.
> > =A0 =A0 =A0 =A0at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg=
..java:
> > 373)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3207)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3517)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:23=
52)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java=
:2268)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java=
:775)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:29=
1)
> > =A0 =A0 =A0 =A0at com.informix.jdbc.IfxStatement.c(IfxStatement.java:12=
33)
> > =A0 =A0 =A0 =A0at
> > com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
> > ment.java:408)
> > =A0 =A0 =A0 =A0at WriteBlob.main(WriteBlob.java:21)
>
> > There, I am now stuck. =A0Can anybody give me a hand?

0
Superboer
5/14/2010 12:43:15 PM
On 5=E6=9C=8814=E6=97=A5, =E4=B8=8B=E5=8D=886=E6=99=8223=E5=88=86, Art Kage=
l <art.ka...@gmail.com> wrote:
> Get Jonathan Leffler's sqlcmd package it contains simple utilities to loa=
d
> data into a blob =3D 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.
0
emrefan
5/15/2010 1:34:22 AM
On 5=E6=9C=8814=E6=97=A5, =E4=B8=8B=E5=8D=888=E6=99=8222=E5=88=86, Dirk Gun=
sth=C3=B6vel <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
> =C2=A0 =C2=A0 pstmt.setBinaryStream( 2, (InputStream) fis, 10 );
> in your java code seems weird. Why reading exactly
> 10 bytes from the file? Do something like
> =C2=A0 =C2=A0 pstmt.setBinaryStream( 2, (InputStream) fis, (int) fis.leng=
ht() );
> 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=C3=B6vel
> -- -professional services-
> --
> -- Dirk Gunsth=C3=B6vel IT Systemanalyse - GunCon
> -- Hammer Str. 13
> -- D-48153 Muenster
> -- phone: +49 (0) 251 28446- 0
> -- fax: =C2=A0 +49 (0) 251 28446-55
> -- web: =C2=A0http://www.GunCon.de
> -- email: i...@GunCon.de
> -- UStId: DE 189527667
> --
> -- =C2=A0 =C2=A0 'One now understands why some animals eat their young.'
> -- =C2=A0 =C2=A0 (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>. =C2=A0I created the simplest of tables, like t=
his
> > one below:
>
> > create table tt_pics (
> > =C2=A0 =C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0char(10),
> > =C2=A0 =C2=A0 pic =C2=A0 =C2=A0 =C2=A0byte 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:
>
> > -------------------------------------------
> > =C2=A0603: Cannot close blob.
>
> > =C2=A0847: 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 {
>
> > =C2=A0 static public void main( String args[] ) {
>
> > =C2=A0 =C2=A0 =C2=A0String url =3D "jdbc:informix-sqli://localhost:8899=
/somedb:" +
>
> > "INFORMIXSERVER=3Difxsvr;user=3Dwhoever;password=3Ddontcare";
> > =C2=A0 =C2=A0 =C2=A0Connection con =3D null;
> > =C2=A0 =C2=A0 =C2=A0PreparedStatement pstmt =3D null;
> > =C2=A0 =C2=A0 =C2=A0ResultSet rs =3D null;
> > =C2=A0 =C2=A0 =C2=A0String strsql;
> > =C2=A0 =C2=A0 =C2=A0File picFile =3D new File("somepic.jpg");
> > =C2=A0 =C2=A0 =C2=A0java.io.BufferedInputStream bin =3D null;
> > =C2=A0 =C2=A0 =C2=A0int i;
>
> > =C2=A0 =C2=A0 =C2=A0try {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 Class.forName( "com.informix.jdbc.IfxDriver=
" );
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 con =3D DriverManager.getConnection( url );
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 strsql =3D "insert into tt_pics values(?,?)=
";
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 pstmt =3D con.prepareStatement( strsql );
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 FileInputStream fis =3D new FileInputStream=
( picFile );
>
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 pstmt.setString( 1, "somebody" );
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 pstmt.setBinaryStream( 2, (InputStream) fis=
, 10 );
>
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 i =3D pstmt.executeUpdate();
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println("i=3D" + i);
>
> > =C2=A0 =C2=A0 =C2=A0} catch (ClassNotFoundException ce) {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println( "Class Not Found!!" );
> > =C2=A0 =C2=A0 =C2=A0} catch (SQLException =C2=A0se) {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println( "SQL exception: " + se.=
getMessage() );
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 se.printStackTrace();
> > =C2=A0 =C2=A0 =C2=A0} catch (Exception ie) {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println( "I/O Exception " + ie.g=
etMessage() );
> > =C2=A0 =C2=A0 =C2=A0} finally {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 try {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (rs !=3D null)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rs.close();
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (pstmt !=3D null)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pstmt.close();
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (con !=3D null)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 con.close();
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 } catch (Exception ep) {
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println( ep.getMessage() =
);
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 }
> > =C2=A0 =C2=A0 =C2=A0}
> > =C2=A0 }
> > }
> > ------------------------------------------------ snip
> > -----------------------------------------------------------
>
> > When I ran it, I got the following error message:
>
> > SQL exception: Cannot close blob.
> > java.sql.SQLException: Cannot close blob.
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.util.IfxErrMsg.getSQLExcepti=
on(IfxErrMsg.java:
> > 373)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxSqli.a(IfxSqli.java:=
3207)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxSqli.E(IfxSqli.java:=
3517)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxSqli.dispatchMsg(Ifx=
Sqli.java:2352)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxSqli.receiveMessage(=
IfxSqli.java:2268)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxSqli.executeCommand(=
IfxSqli.java:775)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxResultSet.b(IfxResul=
tSet.java:291)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at com.informix.jdbc.IfxStatement.c(IfxState=
ment.java:1233)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at
> > com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedState
> > ment.java:408)
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0at WriteBlob.main(WriteBlob.java:21)
>
> > There, I am now stuck. =C2=A0Can anybody give me a hand?- =E9=9A=B1=E8=
=97=8F=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -
>
> - =E9=A1=AF=E7=A4=BA=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -

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.
0
emrefan
5/15/2010 1:42:45 AM
On 5=E6=9C=8814=E6=97=A5, =E4=B8=8B=E5=8D=888=E6=99=8243=E5=88=86, Superboe=
r <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.
0
emrefan
5/15/2010 1:45:32 AM
On 5/14/10 12:45 AM, emrefan 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
> --------------------------------------------

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...



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

-=JL=-
0
Jonathan
5/16/2010 5:54:27 AM
On 5=E6=9C=8816=E6=97=A5, =E4=B8=8B=E5=8D=881=E6=99=8254=E5=88=86, Jonathan=
 Leffler <jleff...@earthlink.net> wrote:
> On 5/14/10 12:45 AM, emrefan 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>. =C2=A0I created the simplest of tables, like th=
is
> > one below:
>
> > create table tt_pics (
> > =C2=A0 =C2=A0 =C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0char(10),
> > =C2=A0 =C2=A0 =C2=A0 pic =C2=A0 =C2=A0 =C2=A0byte 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:
>
> > -------------------------------------------
> > =C2=A0 =C2=A0603: Cannot close blob.
>
> > =C2=A0 =C2=A0847: Error in load file line 1.
> > Error in line 18
> > Near character position 22
> > --------------------------------------------
>
> That's a weird error. =C2=A0The load file you showed should have worked f=
ine.
> 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.
> =C2=A0 The one difference was I was using BYTE {IN TABLE}; can you try th=
at
> temporarily? =C2=A0That will help isolate the issue.

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? =C2=A0There's a=
bout
> a decade between the releases. =C2=A0You'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.
0
emrefan
5/18/2010 3:04:15 AM
Reply: