Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

inserting very long strings into Oracle DB using Java

Posted on 2003-03-13
22
Medium Priority
?
1,258 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month11 days, 2 hours left to enroll

572 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