By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,309 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,309 IT Pros & Developers. It's quick & easy.

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

P: 37
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
Mar 12 '07 #1
Share this Question
Share on Google+
38 Replies


10K+
P: 13,264
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.
Expand|Select|Wrap|Line Numbers
  1.  String s = 
  2. "kanthi~is a good girl~she is very pretty~,^mamy~is a good girl~";
  3. String[] words = s.split("~");
  4.  
Mar 12 '07 #2

P: 37
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
Mar 12 '07 #3

10K+
P: 13,264
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?
Mar 12 '07 #4

P: 37
Thanks For The Reply

Sorry, I Didnt Try Those. Can U Give Me A Sample Code
Thanks In Advance
Mar 12 '07 #5

10K+
P: 13,264
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

Expand|Select|Wrap|Line Numbers
  1.  Object o = rs.getObject("columnName"); 
passing the name og the column that contains the clob data. Then

Expand|Select|Wrap|Line Numbers
  1.  String s = o.toString(); 
Mar 12 '07 #6

P: 37
thanks for the reply.. i will try it out and will inform u
Mar 12 '07 #7

P: 37
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
Mar 12 '07 #8

10K+
P: 13,264
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
Mar 12 '07 #9

10K+
P: 13,264
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
Mar 12 '07 #10

P: 37
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
Mar 13 '07 #11

10K+
P: 13,264
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?
Mar 13 '07 #12

P: 37
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.
Mar 13 '07 #13

10K+
P: 13,264
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?
Mar 13 '07 #14

P: 37
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
Mar 13 '07 #15

P: 37
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
Mar 13 '07 #16

10K+
P: 13,264
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?
Mar 13 '07 #17

P: 37
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
Mar 13 '07 #18

10K+
P: 13,264
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?
Mar 13 '07 #19

P: 37
Exception I Am Getting Is

Exception In Connection And Insertion
And No Data Is Inserted Into The Database
Mar 13 '07 #20

10K+
P: 13,264
Exception I Am Getting Is

Exception In Connection And Insertion
And No Data Is Inserted Into The Database
Post the full exception trace.
Mar 13 '07 #21

P: 37
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
Mar 13 '07 #22

P: 37
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
Mar 13 '07 #23

10K+
P: 13,264
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?
Mar 13 '07 #24

P: 37
ok.

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

anyway,thanks
Mar 13 '07 #25

P: 37
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
Mar 14 '07 #26

P: 37
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
Mar 14 '07 #27

10K+
P: 13,264
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.
Mar 14 '07 #28

P: 37
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
Mar 14 '07 #29

P: 37
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
Mar 14 '07 #30

10K+
P: 13,264
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?
Mar 14 '07 #31

P: 37
Getting the item ParentID:java.lang.NullPointerException
Mar 14 '07 #32

10K+
P: 13,264
Getting the item ParentID:java.lang.NullPointerException
Now which line in your code is giving that exception.
Mar 14 '07 #33

P: 37
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
Mar 14 '07 #34

10K+
P: 13,264
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?
Mar 14 '07 #35

P: 37
con=DBConnection.getConnection();
Mar 14 '07 #36

P: 37
con=DBConnection.getConnection();


THIS IS THE STATEMENT FOR GETTING CONNECTION.
AND THE CONNECTION IS CLOSED ONLY AFTER THOSE STATEMENTS.
Mar 14 '07 #37

10K+
P: 13,264
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 ...
Mar 14 '07 #38

P: 37
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);
}
}
}
Mar 15 '07 #39

Post your reply

Sign in to post your reply or Sign up for a free account.