Converting String to Clob

[Kazeem Akinrinde]

Please can anybody help with the most efficient way of saving a string into a java.sql.Clob column type of Oracle9i?

Thanx in anticipation of your support

Kazeem

[Pradyut Bhattacharya]

i think if u can change the type using any jar packages provided by oracle

[Adeel Ansari]

Originally posted by Pradyut Bhattacharya:
i think if u can change the type using any jar packages provided by oracle

try out oracle.sql.CLOB.

I was working with BLOBs but could make it via java.sql.Blob, but oracle.sql.BLOB worked fine for me.

cheers.
[ February 02, 2005: Message edited by: Adeel Ansari ]

[Hareesh Ram Chanchali]

hi,

try using oracle CLOB class ...but if it is from file then u must first store empty_clob() thru insert statement and then use the syntax of
SELECT clobcolumn where ....FOR UPDATE

with the help of streams u can insert the data into the clob

for further clarifications visit
http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oralob.htm#1043272

[Sanjay Dixit]

Originally posted by Kazeem Akinrinde:
Please can anybody help with the most efficient way of saving a string into a java.sql.Clob column type of Oracle9i?

Thanx in anticipation of your support

Kazeem

I think code is available at java.sun.com
Probably the code given below may help you.(http://forum.java.sun.com/thread.jspa?threadID=349880)

import java.sql.*;
import java.util.*;
import java.text.*;

/* -------------------------------------------------------------------
* This program demonstrates a technique for converting a large String
* to an oracle.sql.CLOB then inserting that CLOB into a database.
* I believe most of this is specific to Oracle, specifically the
* ability to create a temporary CLOB within your program.
* -------------------------------------------------------------------
*/
class TestOraCLobInsert {

public static void main (String args []) throws SQLException {

try {

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection ("jdbc racle:thin:@server:1521:sid", "uid", "pwd");

PreparedStatement ps = conn.prepareStatement("INSERT INTO CLOBTABLE VALUES (?)");

oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_CALL);

newClob.putString(1,"This string, up to 4 gigabytes will be inserted into the CLOB");

ps.setClob(1, newClob);

int rowcnt = ps.executeUpdate();

System.out.println("Successful update of "+rowcnt+" row");

ps.close();
conn.close();
}
catch (Exception e) {
System.out.println("Java Exception caught, error message="+e.getMessage());
}
}

}

[Kazeem Akinrinde]

Thanx for your suggestions, I have resolved it by using the piecce of code shown below:

message column is a Clob field

PreparedStatement ps = null;
sql = "update erletter set subject=?, message=? where letter_code=?";
ps = conn.prepareStatement(sql);
ps.setString(1, subject);
ps.setObject(2, message);
ps.setString(3, letter_code);
ps.executeUpdate();


and since we are dealing with SQL Server and Oracle database, this code works fine for both. Since message is already a string, when it is casted to Object its string value is still preserved.
Thanx once again
Kazeem