[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

Insert CLOB into Oracle from JSP

I realize that this has been asked many times, but I'm unable to understand what I'm doing wrong. I'm trying to insert a large amount of data into a CLOB in Oracle. Whenever text < 4000 is entered the insert works fine. However, for anything > 4000 the insert doesn't work, but I don't get an exception either.

I have the following relevant code:

//first part
String InputData = request.getParameter("InputData");
if (InputData == null)
InputData = "";
//more code.......
//then the textarea
<TEXTAREA NAME="InputData" ROWS=4 COLS=40 WRAP=virtual value="<%=InputData%>"></TEXTAREA>
//more code.....
String sql "INSERT INTO TABLE VALUES("'"+InputData+"'");
stmt.executeUpdate(sql);
 



Any help would be greatly appreciated. Thanks in advance.
0
jhughes4
Asked:
jhughes4
  • 8
  • 6
1 Solution
 
kiranhkCommented:
try this...


pstmt = connection.prepareStatement(
                        "INSERT INTO "+tableName+" (ID,SOMESTRING) VALUES (?,?);"
                  );
 
                  clob = CLOB.createTemporary(pstmt.getConnection(), true, CLOB.DURATION_SESSION);
                  clob.open(CLOB.MODE_READWRITE);
 
                  Writer clobWriter = clob.getCharacterOutputStream();
                  clobWriter.write("Caf? 4,90? TTC");
                  clobWriter.flush();
                  clobWriter.close();
 
                  clob.close();
 
                  OraclePreparedStatement opstmt = (OraclePreparedStatement)pstmt;
                  opstmt.setInt(1,1);
                  opstmt.setCLOB(2, clob);
                  System.err.println("Rows affected: "+opstmt.executeUpdate());
0
 
jhughes4Author Commented:
Thanks for the response, but what do I reference in the TextArea?
0
 
kiranhkCommented:
whatever is already there...
this code is only for inserting...
if you want for reading it from CLOB also check out

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/HowToLoadLargeXML.html
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jhughes4Author Commented:
Here's are the errors that I'm receiving

org.apache.jasper.JasperException: Unable to compile C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_2.java:293: Method open(int) not found in interface java.sql.Clob.
                                   clob.open(CLOB.MODE_READWRITE);
                                            ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_2.java:295: Class Writer not found.
                                   Writer clobWriter = clob.getCharacterOutputStream();
                                   ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_2.java:295: Method getCharacterOutputStream() not found in interface java.sql.Clob.
                                   Writer clobWriter = clob.getCharacterOutputStream();
                                                                                    ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_2.java:300: Method close() not found in interface java.sql.Clob.
                                   clob.close();
                                             ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_2.java:304: Incompatible type for method. Explicit cast needed to convert java.sql.Clob to oracle.sql.CLOB.
                                   opstmt.setCLOB(2, clob);



Here are the changes I made....

String tableName = "SomeTable";
       PreparedStatement pstmt = con.prepareStatement("INSERT INTO "+tableName+" (ID,SOMESTRING) VALUES (?,?);" );
 
              Clob clob = CLOB.createTemporary(pstmt.getConnection(), true, CLOB.DURATION_SESSION);
               clob.open(CLOB.MODE_READWRITE);
 
               Writer clobWriter = clob.getCharacterOutputStream();
               clobWriter.write("Caf? 4,90? TTC");
               clobWriter.flush();
               clobWriter.close();
 
               clob.close();
 
               OraclePreparedStatement opstmt = (OraclePreparedStatement)pstmt;
               opstmt.setInt(1,1);
               opstmt.setCLOB(2, clob);
               System.err.println("Rows affected: "+opstmt.executeUpdate());

Is there something I'm missing?

thanks again
0
 
kiranhkCommented:
you need to import  

import oracle.sql.CLOB;
import java.sql.Connection;
import java.sql.SQLException;
import java.io.Writer;

you also need to have the classes12.zip in classpath of the server
0
 
jhughes4Author Commented:
The imports that I had
<%@ page import="oracle.sql.CLOB, java.sql.Connection, java.sql.SQLException, java.io.Writer, java.util.Date, java.text.SimpleDateFormat,java.sql.*, oracle.jdbc.driver.*,oracle.sql.* " %>

The error that I get:

org.apache.jasper.JasperException: Unable to compile C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_4.java:304: Method open(int) not found in interface java.sql.Clob.
                                   clob.open(CLOB.MODE_READWRITE);
                                            ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_4.java:306: Method getCharacterOutputStream() not found in interface java.sql.Clob.
                                   Writer clobWriter = clob.getCharacterOutputStream();
                                                                                    ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_4.java:311: Method close() not found in interface java.sql.Clob.
                                   clob.close();
                                             ^
C:\jakarta-tomcat-3.3.2\work\DEFAULT\BCBS\CreateNewTest_4.java:315: Incompatible type for method. Explicit cast needed to convert java.sql.Clob to oracle.sql.CLOB.
                                   opstmt.setCLOB(2, clob);
0
 
jhughes4Author Commented:
I think my problem is more related to getting the text out of the textarea.  I had created a separate main where I used StringReader to parse the text that I'm pasting into textarea, and was able to parse through the text successfully.  So how can I assign the TextArea contents to a String so that I can insert it as a Clob?

thanks
0
 
kiranhkCommented:
you just need to assign it to a string..
can you put your StringReader code here...
0
 
jhughes4Author Commented:
Here's what I'm now trying.  I've actually created a String within the JSP page that contains the text I'm trying to insert into the CLOB column.  Oddly enough I'm able to display that String within the page when it loads with <%=InputData%>.

String InputData = "SOME LONG TEXT"; //more than 1700 characters

<textarea name="InputData" rows=20 cols=100 wrap-virtural><%=InputData%></textarea>

String sql = "INSERT INTO MYTABLE VALUES(???)";
 PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, SomeString.toUpperCase());
stmt.setString(2, AnotherString.toUpperCase());

stmt.setString(3, InputData);  
//I also tried stmt.setClob(3, InputData); but I received the exception Incompatible type for method. Can't convert    
//java.lang.String to java.sql.Clob

stmt.executeUpdate();

Please Help!




0
 
jhughes4Author Commented:
I just changed the column of the table to a Long and it still does nothing.....
0
 
kiranhkCommented:
>>>>stmt.setString(3, InputData);  
>>>>//I also tried stmt.setClob(3, InputData); but I received the exception Incompatible type for method. Can't convert    
>>>>//java.lang.String to java.sql.Clob

you cannot use a string for a CLOB datatype.. try this

               String sql = "INSERT INTO MYTABLE VALUES(?,?,?)";
 PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, SomeString.toUpperCase());
stmt.setString(2, AnotherString.toUpperCase());

               CLOB clob = null;
               clob = CLOB.createTemporary(pstmt.getConnection(), true, CLOB.DURATION_SESSION);
               clob.open(CLOB.MODE_READWRITE);
 
               Writer clobWriter = clob.getCharacterOutputStream();
               clobWriter.write(InputData);
               clobWriter.flush();
               clobWriter.close();
 
               clob.close();
 
               stmt.setObject(3, clob);
// Execute the Prepared Statement
    if (stmt.executeUpdate () == 1) {
    System.out.println ("Successfully inserted a Purchase Order !");
    }
0
 
jhughes4Author Commented:
This works perfectly if I'm able to take my text and hard code it as a String within the JSP page.  However, when I attempt to do the same via TEXTAREA, nothing happens.  Is there some limitation on TEXTAREA that you know of?  I apolgoize for what appears to be the newbie question.  What do you think?

thanks again

0
 
kiranhkCommented:
no... text are is also the same... just post your full jsp code and i will tell u what the problem is ...
0
 
jhughes4Author Commented:
actually it was a rouge <form> that had no </form>.  I added the </form> and everything works...thanks a bunch for the help!
0

Featured Post

Industry Leaders: 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!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now