× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. JAVA400-L
  3. April 2009

RE: Problem writing byte[] into BLOB

If you didn't specify a max size for your BLOB when you created the
table, then the max size defaults to 1MB (at least it does on my v5r4
box).


Coy Krill
Whidbey Island Bank

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Blalock, Bill
Sent: Thursday, April 23, 2009 15:49
To: Java Programming on and around the iSeries / AS400
Subject: Problem writing byte[] into BLOB

Hello!



I am looking for help figuring out why SQL exceptions are being thrown
when writing byte[] into an i5 BLOB column when the byte[] is over 1
meg.



I am writing rows with a BLOB column that represents TIFF images. SQL
exceptions are thrown when the TIFF image is over 1 meg.



I've tried this with the JDBC 4.0 and regular version of JTOpen 6.4 on
Windows XP Java 1.6.13. I haven't tried it yet on the i5.



At the end of this post are portions of the toolkit traces that failed
(TIFF over 1 meg) and successed (TIFF about 1/2 meg). You can see that
it falls apart when the BLOB value, the 6th field in the prepared
statement, is processed.



I hope someone has ideas about how to work around this problem. Some
jdbc property I missed or quirk I am missing.



Thank

Bill Blalock



Error when trying to write a blob over 1 megabyte:



Thu Apr 23 17:03:57:307 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : Data to correlate statement with cursor Cursor CRSR0001
(179514) .

Thu Apr 23 17:03:57:307 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : Preparing [INSERT INTO ACCUDOCDB.DOCIDXTB (DOC_NAME,
DOC_SIZE, IDX_DOC_CLS, CRT_LOC, DOC_TYPE, DOC_BLOB, IDX_TMSP,
IDX_DOC_TYPE, IDX_DOC_CLS_SRC, CRT_CPY, IDX_DOC_FAX, DOC_TMSP,
IDX_DOC_NOTE, JOB_NBR_USING, DOC_STORED, CRYPTO_KEY_TOKEN, CRT_TMSP,
VIEWED_TMSP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)].

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : Prepared STMT0001*, SQL Statement -->[INSERT INTO
ACCUDOCDB.DOCIDXTB (DOC_NAME, DOC_SIZE, IDX_DOC_CLS, CRT_LOC, DOC_TYPE,
DOC_BLOB, IDX_TMSP, IDX_DOC_TYPE, IDX_DOC_CLS_SRC, CRT_CPY, IDX_DOC_FAX,
DOC_TMSP, IDX_DOC_NOTE, JOB_NBR_USING, DOC_STORED, CRYPTO_KEY_TOKEN,
CRT_TMSP, VIEWED_TMSP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?)].

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setString().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 1 value: F000857161-20090413152916-69.tif.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setInt().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 2 value: 1161532.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setShort().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 3 value: 1.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setShort().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 4 value: 1.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setString().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 5 value: tif.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : setBytes().

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0001
(10038190) : parameter index: 6 length: 1161536.

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) open. Parent: Connection S1023703 (12107511) .

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Escape processing = "true".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Fetch direction = "1000".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Fetch size = "0".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Max field size = "0".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Max rows = "0".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Query timeout = "0".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Result set concurrency = "1007".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Result set holdability = "-9999".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Result set type = "1003".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Behavior Override = "0".

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Data to correlate statement with cursor Cursor CRSR0002
(28154095) .

Thu Apr 23 17:03:57:510 CDT 2009 as400: PreparedStatement STMT0002
(2545159) : Preparing [VALUES(1)].

Thu Apr 23 17:03:57:572 CDT 2009 as400: static method Connection
S1023703 (12107511) : Throwing exception, id: 2 error class: 1 return
code: -29 reason: [SQL0029] INTO clause missing from embedded statement.
Cause . . . . . : SELECT and VALUES INTO statements embedded in a
program must have an INTO clause to specify where the results of the
statement are to be placed. A dynamic VALUES INTO statement must have
an INTO clause. Recovery . . . : Add the INTO clause to the statement
and try the request again. state: 42601.java.sql.SQLException: [SQL0029]
INTO clause missing from embedded statement. Cause . . . . . : SELECT
and VALUES INTO statements embedded in a program must have an INTO
clause to specify where the results of the statement are to be placed.
A dynamic VALUES INTO statement must have an INTO clause. Recovery . .
. : Add the INTO clause to the statement and try the request again.

at
com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)



No error when write a blob less than 1 megabyte:



Thu Apr 23 17:29:30:292 CDT 2009 as400: Connection S1023703 (16868914)
: Auto commit = "false".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) open. Parent: Connection S1023703 (16868914) .

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Escape processing = "true".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Fetch direction = "1000".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Fetch size = "0".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Max field size = "0".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Max rows = "0".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Query timeout = "0".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Result set concurrency = "1007".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Result set holdability = "-9999".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Result set type = "1003".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Behavior Override = "0".

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Data to correlate statement with cursor Cursor CRSR0001
(10038190) .

Thu Apr 23 17:29:30:292 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Preparing [INSERT INTO ACCUDOCDB.DOCIDXTB (DOC_NAME,
DOC_SIZE, IDX_DOC_CLS, CRT_LOC, DOC_TYPE, DOC_BLOB, IDX_TMSP,
IDX_DOC_TYPE, IDX_DOC_CLS_SRC, CRT_CPY, IDX_DOC_FAX, DOC_TMSP,
IDX_DOC_NOTE, JOB_NBR_USING, DOC_STORED, CRYPTO_KEY_TOKEN, CRT_TMSP,
VIEWED_TMSP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)].

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Prepared STMT0001*, SQL Statement -->[INSERT INTO
ACCUDOCDB.DOCIDXTB (DOC_NAME, DOC_SIZE, IDX_DOC_CLS, CRT_LOC, DOC_TYPE,
DOC_BLOB, IDX_TMSP, IDX_DOC_TYPE, IDX_DOC_CLS_SRC, CRT_CPY, IDX_DOC_FAX,
DOC_TMSP, IDX_DOC_NOTE, JOB_NBR_USING, DOC_STORED, CRYPTO_KEY_TOKEN,
CRT_TMSP, VIEWED_TMSP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?)].

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setString().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 1 value:
F000858066-20090423143639-610.tif.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setInt().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 2 value: 556801.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setShort().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 3 value: 1.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setShort().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 4 value: 1.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setString().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 5 value: tif.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setBytes().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 6 length: 556864.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setNull().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 7 value: NULL.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setNull().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 8 value: NULL.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setShort().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 9 value: 1.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setShort().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 10 value: 1.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setLong().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 11 value: 9516798669.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setTimeStamp().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 12 value: 2009-04-23 13:36:39.0.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setNull().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 13 value: NULL.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setNull().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 14 value: NULL.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setString().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 15 value: 1.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setInt().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 16 value: 2.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setTimeStamp().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 17 value: 2009-04-23 17:29:29.98.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : setNull().

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : parameter index: 18 value: NULL.

Thu Apr 23 17:29:30:464 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Descriptor 1 created or changed.

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Executed STMT0001*, SQL Statement --> [INSERT INTO
ACCUDOCDB.DOCIDXTB (DOC_NAME, DOC_SIZE, IDX_DOC_CLS, CRT_LOC, DOC_TYPE,
DOC_BLOB, IDX_TMSP, IDX_DOC_TYPE, IDX_DOC_CLS_SRC, CRT_CPY, IDX_DOC_FAX,
DOC_TMSP, IDX_DOC_NOTE, JOB_NBR_USING, DOC_STORED, CRYPTO_KEY_TOKEN,
CRT_TMSP, VIEWED_TMSP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?)].

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Update count = 1.

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Result set = false.

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Number of result sets = 0.

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) : Row count estimate = -1.

Thu Apr 23 17:29:32:010 CDT 2009 as400: PreparedStatement STMT0001
(31983818) closed.

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) open. Parent: Connection S1023703 (16868914) .

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Escape processing = "true".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Fetch direction = "1000".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Fetch size = "0".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Max field size = "0".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Max rows = "0".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Query timeout = "0".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Result set concurrency = "1007".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Result set holdability = "-9999".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Result set type = "1003".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Behavior Override = "0".

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Data to correlate statement with cursor Cursor CRSR0001
(21607056) .

Thu Apr 23 17:29:32:026 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Preparing [SELECT IDENTITY_VAL_LOCAL() FROM
SYSIBM.SYSDUMMY1].

Thu Apr 23 17:29:32:307 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Prepared STMT0001*, SQL Statement -->[SELECT
IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1].

Thu Apr 23 17:29:32:604 CDT 2009 as400: Cursor CRSR0001 (21607056)
open.

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344)
open. Parent: PreparedStatement STMT0001 (23459640) .

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344) :
Conncurrency = "1007".

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344) :
Fetch direction = "1000".

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344) :
Fetch size = "0".

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344) :
Max rows = "0".

Thu Apr 23 17:29:32:604 CDT 2009 as400: ResultSet CRSR0001 (5612344) :
Type = "1003".

Thu Apr 23 17:29:32:604 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Executed STMT0001*, SQL Statement --> [SELECT
IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1].

Thu Apr 23 17:29:32:604 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Update count = -1.

Thu Apr 23 17:29:32:604 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Result set = true.

Thu Apr 23 17:29:32:604 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Number of result sets = 0.

Thu Apr 23 17:29:32:604 CDT 2009 as400: PreparedStatement STMT0001
(23459640) : Row count estimate = 0.

Thu Apr 23 17:29:32:604 CDT 2009 as400: Connection S1023703 (16868914)
: Fetching a block of data from the system.

Thu Apr 23 17:29:32:635 CDT 2009 as400: Cursor CRSR0001 (21607056) :
Closing with reuse flag = 241.

Thu Apr 23 17:29:32:635 CDT 2009 as400: Cursor CRSR0001 (21607056)
closed.

Thu Apr 23 17:29:32:635 CDT 2009 as400: ResultSet CRSR0001 (5612344)
closed.

Thu Apr 23 17:29:32:682 CDT 2009 as400: PreparedStatement STMT0001
(23459640) closed.

Thu Apr 23 17:29:32:713 CDT 2009 as400: Connection S1023703 (16868914)
: Transaction commit.

Thu Apr 23 17:29:32:729 CDT 2009 as400: Connection S1023703 (16868914)
: Auto commit = "true".

F000858066-20090423143639-610.tif written to database



_____________

The information contained in this message is proprietary and/or
confidential. If you are not the
intended recipient, please: (i) delete the message and all copies; (ii)
do not disclose,
distribute or use the message in any manner; and (iii) notify the sender
immediately. In addition,
please be aware that any message addressed to our domain is subject to
archiving and review by
persons other than the intended recipient. Thank you.
_____________

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.