How do i get clob data using java and assign to a variable

[kanthi84]

Hiiiiii


i am having a table in oracle. in that table one of the column is to store clob data. and in that clob column, the data is inserted like the following using separators

kanthi~is a good girl~she is very pretty~,^mamy~is a good girl~



wat i need is , i need to get them separately
like name=kanthi
description=is a good girl


can anyone help me with the code
it is urgent
thanks in advance

[r035198x]

Hiiiiii


i am having a table in oracle. in that table one of the column is to store clob data. and in that clob column, the data is inserted like the following using separators

kanthi~is a good girl~she is very pretty~,^mamy~is a good girl~



wat i need is , i need to get them separately
like name=kanthi
description=is a good girl


can anyone help me with the code
it is urgent
thanks in advance

Split the String up using the split method.

 String s = 
"kanthi~is a good girl~she is very pretty~,^mamy~is a good girl~";
String[] words = s.split("~");
 

[kanthi84]

Thanks For The Reply.

But Wat I Was Asking Is
How To Get The Clob Data Using Java AND STORE IT IN A STRING VARIABLE AND THEN GET THE WORDS SEPARATED
Can U Help Me With The Code

[r035198x]

Thanks For The Reply.

But Wat I Was Asking Is
How To Get The Clob Data Using Java AND STORE IT IN A STRING VARIABLE AND THEN GET THE WORDS SEPARATED
Can U Help Me With The Code

Did you try the getObject method of resultset and calling the toString method on the returned object?

[kanthi84]

Thanks For The Reply

Sorry, I Didnt Try Those. Can U Give Me A Sample Code
Thanks In Advance

[r035198x]

Thanks For The Reply

Sorry, I Didnt Try Those. Can U Give Me A Sample Code
Thanks In Advance

Well it really depens on what you have so far.
If you have a resultset rs that you have retrieved the table data into, then you can simply do

 Object o = rs.getObject("columnName"); 

passing the name og the column that contains the clob data. Then

 String s = o.toString(); 

[kanthi84]

thanks for the reply.. i will try it out and will inform u

[kanthi84]

SORRY TO SAY THAT I AM NOT GETTING THE REQUIRED OUTPUT
I AM GETTING THE FOLLOWING

oracle.sql.CLOB@48af

PLEASE PROVIDE ME WITH THE SOLUTION

[r035198x]

SORRY TO SAY THAT I AM NOT GETTING THE REQUIRED OUTPUT
I AM GETTING THE FOLLOWING

oracle.sql.CLOB@48af

PLEASE PROVIDE ME WITH THE SOLUTION

There is also java.sql.Clob interface I'm sure.

Check the ResultSet API and see if there is no getClob method

[r035198x]

There is also java.sql.Clob interface I'm sure.

Check the ResultSet API and see if there is no getClob method

Just confirmed it. So you can retrieve it as a clob with

Clob myClob = rs.getClob("columnName");
then you can use the method getSubString(long pos, int length) to get the string from it which returns a String

[kanthi84]

Thanks for the reply
u r absolutely correct

i also used the same thing
clob name=rs.getClob(1);
variable= name.getSubString(1, (int)clobLength);

where variable is a string variable initialized as ""
but i am getting the output in hexadecimal
like for ~ 4E like that
i need to get it as string
wat is the problem with the code
thanks in advance

[r035198x]

Thanks for the reply
u r absolutely correct

i also used the same thing
clob name=rs.getClob(1);
variable= name.getSubString(1, (int)clobLength);

where variable is a string variable initialized as ""
but i am getting the output in hexadecimal
like for ~ 4E like that
i need to get it as string
wat is the problem with the code
thanks in advance

Try using getCharacterStream().

There could be a problem with how you stored the data in the first place.

P.S Any reason why you are using clob instead of VARCHAR?

[kanthi84]

can u help me with getCharacterStream

i need to save it as clob, because the data is very large and separated by some symbols. first i did it using blob. but then, it has to be converted to byte, n copied to another file and then retrieve.

someone told me, if stored as clob, it is easy to retrieve the parts.

can u help me with this

and about the insertion
i just created a table with a clob column.
after entering the data in the jsp page, on clicking save it is saved to the database.


thanks in advance.

[r035198x]

can u help me with getCharacterStream

i need to save it as clob, because the data is very large and separated by some symbols. first i did it using blob. but then, it has to be converted to byte, n copied to another file and then retrieve.

someone told me, if stored as clob, it is easy to retrieve the parts.

can u help me with this

and about the insertion
i just created a table with a clob column.
after entering the data in the jsp page, on clicking save it is saved to the database.


thanks in advance.

The getSubString method should work correctly anyway. Can you post the snippet that you used to insert the value?

[kanthi84]

Now I Got The Actual Problem


It Is In The Inserting Part Itself
Can U Help With The Code To Insert Into The Clob Column.

The Data Is In String
How To Convert It To Clob And Save It In Database

[kanthi84]

Properties props = new Properties();
props.put("SetBigStringTryClob", "true");
pstmt=con.prepareStatement("INSERT INTO TABLE values('','','',?,'','','')");
pstmt.executeUpdate();
pstmt.close();
pstmt.setString(4, lstr_points);
pstmt.executeUpdate();

CAN U TELL ME WAT IS WRONG IN THE CODE. THE QUERY IS NOT EXCECUTING.

THANKS IN ADVANCE

[r035198x]

Properties props = new Properties();
props.put("SetBigStringTryClob", "true");
pstmt=con.prepareStatement("INSERT INTO TABLE values('','','',?,'','','')");
pstmt.executeUpdate();
pstmt.close();
pstmt.setString(4, lstr_points);
pstmt.executeUpdate();

CAN U TELL ME WAT IS WRONG IN THE CODE. THE QUERY IS NOT EXCECUTING.

THANKS IN ADVANCE

Are you getting any exception? How are you handling the exceptions?

[kanthi84]

catch (SQLException sqlex) {
// Catch Exceptions and display messages accordingly.
System.out.println("SQLException while connecting and inserting into " +
"the database table: " + sqlex.toString());
} catch (Exception ex) {
System.out.println("Exception while connecting and inserting into the" +
" database table: " + ex.toString());
}


THE PROBLEM IS THE QUERY IS NOT EXECUTING

[r035198x]

catch (SQLException sqlex) {
// Catch Exceptions and display messages accordingly.
System.out.println("SQLException while connecting and inserting into " +
"the database table: " + sqlex.toString());
} catch (Exception ex) {
System.out.println("Exception while connecting and inserting into the" +
" database table: " + ex.toString());
}


THE PROBLEM IS THE QUERY IS NOT EXECUTING

So what happens when you run the code?
No exception?
And when you check in the database do you see nothing or do you see something else inserted instead?

[kanthi84]

Exception I Am Getting Is

Exception In Connection And Insertion
And No Data Is Inserted Into The Database

[r035198x]

Exception I Am Getting Is

Exception In Connection And Insertion
And No Data Is Inserted Into The Database

Post the full exception trace.

[kanthi84]

hi
atlast got the solution

i dint know that only upto 4000 characters can be saved as clob.

that was the problem.
when i gave less than the limit, a new row was inserted and i was able to retrieve the data as u said earlier

thanks

[kanthi84]

but i have a doubt
i got the information from net than

Maximum length of CLOB (in bytes) is 2 147 483 647 and this is the same for blob also.

now the doubt comes

i am not able to insert data into clob, which i successfully uploaded using blob.
how can this happen if they have the same size limit?



thanks in advance

[r035198x]

but i have a doubt
i got the information from net than

Maximum length of CLOB (in bytes) is 2 147 483 647 and this is the same for blob also.

now the doubt comes

i am not able to insert data into clob, which i successfully uploaded using blob.
how can this happen if they have the same size limit?



thanks in advance

Maybe the way you are inserting the data is incorrect?

[kanthi84]

ok.

i will try and will let u know what is the problem, if i get

anyway,thanks

[kanthi84]

hi good morning

it seems only less number of characters can be inserted directly into clob columb.
but, if we use a stored procedure to insert into clob more data can be inserted.
i am not aware of writing a stored procedure..


can anyone help me with a sample

thanks in advance

[kanthi84]

CAN ANYONE TELL ME THE USE OF THIS CODE?




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

private CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create new
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);

// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);

// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();

// Close the temporary CLOB
tempClob.close();
} catch(SQLException sqlexp){
tempClob.freeTemporary();
sqlexp.printStackTrace();
} catch(Exception exp){
tempClob.freeTemporary();
exp.printStackTrace();
}
return tempClob;


THANKS IN ADVANCE

[r035198x]

CAN ANYONE TELL ME THE USE OF THIS CODE?




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

private CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create new
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);

// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);

// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();

// Close the temporary CLOB
tempClob.close();
} catch(SQLException sqlexp){
tempClob.freeTemporary();
sqlexp.printStackTrace();
} catch(Exception exp){
tempClob.freeTemporary();
exp.printStackTrace();
}
return tempClob;


THANKS IN ADVANCE

Where did you get it from? You should have asked there what they wanted to do with it.

[kanthi84]

I Got It While Searching In Google.

It Is To Store The String To Be Inserted Into Clob Column In A Temporary Clob. And This Can Be Inserted.
The Main Advantage Is That , By Using This, I Heard We Can Store Large No Of Characters Than Inserting Directly

[kanthi84]

CAN U TELL ME WAT IS WRONG WITH THE CODE.
THE CODE IS NOT EXECUTING


pstmt=con.prepareStatement("INSERT INTO TABLE values('','','',?,'','','')");
oracle.sql.CLOB newClob =oracle.sql.CLOB.createTemporary(con, false, oracle.sql.CLOB.DURATION_CALL);
newClob.putString(1,VALUE);
pstmt.setClob(4, newClob);
int rowcnt = pstmt.executeUpdate();

THANK U IN ADVANCE

[r035198x]

CAN U TELL ME WAT IS WRONG WITH THE CODE.
THE CODE IS NOT EXECUTING


pstmt=con.prepareStatement("INSERT INTO TABLE values('','','',?,'','','')");
oracle.sql.CLOB newClob =oracle.sql.CLOB.createTemporary(con, false, oracle.sql.CLOB.DURATION_CALL);
newClob.putString(1,VALUE);
pstmt.setClob(4, newClob);
int rowcnt = pstmt.executeUpdate();

THANK U IN ADVANCE

What is the full Exception that you get when you execute it?

[kanthi84]

Getting the item ParentID:java.lang.NullPointerException

[r035198x]

Getting the item ParentID:java.lang.NullPointerException

Now which line in your code is giving that exception.

[kanthi84]

THE EXCEPTION IS GETTING AFTER THE FOLLOWING LINE


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

AFTER THIS LINE,ST.CLOSE() IS EXCECUTED AND EXCEPTION IS DISPLAYED

[r035198x]

THE EXCEPTION IS GETTING AFTER THE FOLLOWING LINE


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

AFTER THIS LINE,ST.CLOSE() IS EXCECUTED AND EXCEPTION IS DISPLAYED

If you are sure that's the line throwing the nullpointer exception then check your value of con. It could be the one that is null at the time of call. Where are you getting it from?

[kanthi84]

con=DBConnection.getConnection();

[kanthi84]

con=DBConnection.getConnection();


THIS IS THE STATEMENT FOR GETTING CONNECTION.
AND THE CONNECTION IS CLOSED ONLY AFTER THOSE STATEMENTS.

[r035198x]

con=DBConnection.getConnection();


THIS IS THE STATEMENT FOR GETTING CONNECTION.
AND THE CONNECTION IS CLOSED ONLY AFTER THOSE STATEMENTS.

You might have the post all the code then ...

[kanthi84]

private void Details(String A,String B,String C,String D)
{
Clob tempClob = null;
try
{
con=DBConnection.getConnection();

pstmt=con.prepareStatement("INSERT INTO TABLE values('','"+A+"','',EMPTY_CLOB(),'"+B+"','"+D+"', '')");
tempClob = (Clob) oracle.sql.CLOB.createTemporary(con, false,oracle.sql.CLOB.DURATION_SESSION);
((oracle.sql.CLOB) tempClob).putString(1, C);
pstmt.setClob(4, tempClob);
pstmt.executeUpdate();
}

catch(SQLException e)
{
System.out.println("Getting the item ParentID:"+e);
}
catch(Exception e)
{
System.out.println("Getting the item ParentID:"+e);
}
finally
{
try{
pstmt.close();
st.close();
con.close();
}catch(SQLException e)
{
System.out.println(e);
}
}
}