?
Solved

inserting very long strings into Oracle DB using Java

Posted on 2003-03-13
22
Medium Priority
?
1,248 Views
Last Modified: 2008-05-23
hi all,

i'm trying to insert a string into an Oracle DB.
I chose column type LONG which allows for 2GB.
I managed to make this work but now it seems that if the string is actually really big, it wont do the insert and i get SQLException.

If my explanation wasn't clear ask me what you need to know
Here's my code :
(all i_... variables are of type String)

String sql="INSERT INTO maildata2(MId,MDate,MDateSent,Msender,Msender_email,MSubject,MLongBody) VALUES(MAKEID2.nextval,sysdate,TO_DATE('" + i_date + "','DD-MON-YYYY hh24:mi:ss'),'" + i_sender + "','" + i_sender_email + "','" + i_subject + "',?)";

PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setAsciiStream(1,new java.io.ByteArrayInputStream(i_body.getBytes()),i_body.length());
pstmt.executeUpdate();
pstmt.close();

these are the errors i get :
java.sql.SQLException: Io exception: Char array not long enough: javaCharsToUtf8Bytes


hope someone can help
Gstarr
0
Comment
Question by:Gstarr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 5
  • +1
22 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 8128486
 Whoah!! You have a string over 2GB big? Why don't you save it into a file and then store the file's location into the database?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8128517
I'd use a CLOB...seems like LONG doesn't work right :-( (from searching on Google)

Have a look at this on how to use CLOBs

http://www.weblogic.com/docs51/classdocs/API_joci.html#1071858
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8128533
what happens if you use
pstmt.setBinaryStream(1,new java.io.ByteArrayInputStream(i_body.getBytes()),i_body.length());

btw: LONG type is oblolated - you should use CLOB instead
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Gstarr
ID: 8128608
@Venci75
pstmt.setBinaryStream(1,new java.io.ByteArrayInputStream(i_body.getBytes()),i_body.length());

this just puts it in binary format, but then i have to hassle again if i want to read it from the DB :(

@Tim
thx i took a quick look and it seems helpful, but i saw that it imports some WEBLOGIC package, i'd rather not do this...

Gstarr
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8128614
Yeah...sorry...my link was cack

Try this one

http://www.wamoz.com/JDBC_and_Oracle_LOB.asp
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8128619
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8128626
and sorry about the colorscheme on that link -- but it is the best one I could find...
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8128647
>>> pstmt.setAsciiStream(1,new java.io.ByteArrayInputStream(i_body.getBytes()),i_body.length());

the length is probably not correct. Can you try:
pstmt.setAsciiStream(1,new java.io.ByteArrayInputStream(i_body.getBytes("ENCODING")),i_body.length());

where encoding is your database encoding
0
 
LVL 35

Expert Comment

by:girionis
ID: 8128708
 If you want to put it in binary format you might as well use BLOB.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 8128720
Also, you aren't using PreparedStatement very well...

(sorry to pick) ;-)

Why not do :

String sql="INSERT INTO maildata2( MId, MDate, MDateSent, Msender, Msender_email, MSubject, MLongBody ) VALUES( MAKEID2.nextval, sysdate,TO_DATE( ?,'DD-MON-YYYY hh24:mi:ss' ), ?, ?, ?,?)";

PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString( 1, i_date ) ;
pstmt.setString( 2, i_sender ) ;
pstmt.setString( 3, i_sender_email ) ;
pstmt.setString( 4, i_subject ) ;
pstmt.setAsciiStream( 5,new java.io.ByteArrayInputStream( i_body.getBytes() ), i_body.length() ) ;
pstmt.executeUpdate();
0
 

Author Comment

by:Gstarr
ID: 8129325
the main reason i'm not using the preparedstatement entirely is because then i can't do this : TO_DATE(i_date ,'DD-MON-YYYY hh24:mi:ss' )
for the date formatting i really have to use the oracle TO_DATE function

0
 
LVL 9

Expert Comment

by:Venci75
ID: 8134587
did you try my suggestion?
0
 

Author Comment

by:Gstarr
ID: 8134679
Venci,

i tried this :
pstmt.setAsciiStream(1,new java.io.ByteArrayInputStream(i_body.getBytes("ASCII")),i_body.length());

and it seems to work, i'll just test some more if the problem's solved but it think it did the trick
thx

Gstarr
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8134703
If this is working then 'i_body' contains some non-ascii characters, which in UTF8 encoding (this seems to be the encoding of your database) are represented by more that one byte. To be more precise - the correct code for inserting in the database is:

pstmt.setAsciiStream(5, new java.io.ByteArrayInputStream(i_body.getBytes("UTF-8")), i_body.length());

when you use i_body.getBytes("ASCII") you will probably get a corrupted data

0
 

Author Comment

by:Gstarr
ID: 8135187
i_body contains the message body of email messages, i guess there could be some non-ascii characters in there...

i tried with UTF-8 but then i get the same error as before : java.sql.SQLException: Io exception: Char array not long enough: javaCharsToUtf8Bytes

changed it back to ASCII and no probs so far. I must say I haven't used the stored data up to now, so i dont really know if its corrupted.
Is it really that bad to use ASCII ???

Gstarr
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8135197
opss - sorry - the code should be :
byte[] buffer = i_body.getBytes("UTF-8");
pstmt.setAsciiStream(5, new java.io.ByteArrayInputStream(buffer), buffer.length);

0
 

Author Comment

by:Gstarr
ID: 8135209
i_body contains the message body of email messages, i guess there could be some non-ascii characters in there...

i tried with UTF-8 but then i get the same error as before : java.sql.SQLException: Io exception: Char array not long enough: javaCharsToUtf8Bytes

changed it back to ASCII and no probs so far. I must say I haven't used the stored data up to now, so i dont really know if its corrupted.
Is it really that bad to use ASCII ???

Gstarr
0
 

Author Comment

by:Gstarr
ID: 8135295
i_body contains the message body of email messages, i guess there could be some non-ascii characters in there...

i tried with UTF-8 but then i get the same error as before : java.sql.SQLException: Io exception: Char array not long enough: javaCharsToUtf8Bytes

changed it back to ASCII and no probs so far. I must say I haven't used the stored data up to now, so i dont really know if its corrupted.
Is it really that bad to use ASCII ???

Gstarr
0
 

Author Comment

by:Gstarr
ID: 8135732
nope, UTF-8 really doesn't work...
ASCII seems to be the only option that works for me
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8135771
ops - another suggestion:

pstmt.setCharacterStream(5, new java.io.StringReader(i_body), i_body.length());
0
 

Author Comment

by:Gstarr
ID: 8136144
pstmt.setCharacterStream(5, new java.io.StringReader(i_body), i_body.length())
this works too !!! now should i use it or rather use :
pstmt.setAsciiStream(5,new java.io.ByteArrayInputStream(i_body.getBytes("ASCII")),i_body.length())

Is the first the safest regarding the corruption u told about with ASCII ?

Gstarr

btw thx man, you really deserve the points!
0
 
LVL 9

Accepted Solution

by:
Venci75 earned 1000 total points
ID: 8136171
setCharacterStream() is the better method - because you have prepared character data.
if you use setAsciiStream() - you will have to convert the String to byte[] and after that the driver will try to convert this byte[] (byte stream) back to characters.

Actually - the problem was caused exatly by the incorrect transformation of the String to byte[]
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month9 days, 1 hour left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question