Meaningless Drivel is fun!*
The moose likes Servlets and the fly likes How can I display CLOB & BLOB data in a JSP? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "How can I display CLOB & BLOB data in a JSP?" Watch "How can I display CLOB & BLOB data in a JSP?" New topic
Author

How can I display CLOB & BLOB data in a JSP?

Hal Watson
Greenhorn

Joined: Jul 06, 2001
Posts: 5
Hello all, I'm a JSP newbie, so bear with me if you don't mind.
I'm connecting to an Oracle database, and in one case I need to display on my jsp page some text that resides in a CLOB field, and in another case I need to display a .gif image that resides in a BLOB field. I could really use a few examples.
My SQL queries to the CLOB field return a value of NULL. I didn't try SQL on the BLOB field assuming that won't work.
Below is my code for retrieving the CLOB text, which returns NULL instead the text I know is in the table. In this example, the field 'cpcname' is a regular VARCHAR(2) field, and 'cpccartlin' is the CLOB field:
try {
ResultSet results_2;
String sql_2 = "select cpcname, cpccartlin from core_parent_cart where corelayerid = " + clid;
sth_2 = dbh.createStatement();
results_2 = sth_2.executeQuery(sql_2);
while (results_2.next())
{out.println("cpcname: "+ (String)results_2.getString("cpcname") +
" cpccartlin: " + (String)results_2.getString("cpccartlin"));
}
results_2.close();
} catch (SQLException e) {
System.err.println("Error executing SQL statement.");
}
Thanks for any suggestions you can offer!
[This message has been edited by Hal Watson (edited August 31, 2001).]
[This message has been edited by Hal Watson (edited August 31, 2001).]
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12769
    
    5
I can't say whats wrong with your SQL but I suggest you put the code in a JavaBean rather than directly in your JSP. It is MUCH easier to write a simple utility to debug a bean than to cope with all the things that can go wrong with a JSP.
That will also make it easier to serve your binary image from a servlet.
Bill
Kareem Gad
Ranch Hand

Joined: Aug 06, 2001
Posts: 89
Hal,
The result set class has many methods to retrieve different kinds of data, i see here that you're using getString method while you actually have a getBlob method which actually returns a java Blob Object.Same goes from Clob. RTFM man.
http://java.sun.com/j2se/1.3/docs/api/java/sql/ResultSet.html
you will find a full list of the ResultSet class.
------------------
KaReEm
[This message has been edited by Kareem Gad (edited August 31, 2001).]


<b><i>KaReEm</i><br /><ul type="square"><li>SCJP-Free Range Web Developer <br /></ul></b>
Dmitriy Pavlyuk
Ranch Hand

Joined: Mar 25, 2001
Posts: 33
You must use rs.getBytes("clobfield") and after that you may use the byte array and cast it to String or bitmap.
Dmitriy.
Gulab Singh
Greenhorn

Joined: Aug 09, 2001
Posts: 8
You can refer to following URL's for help. Also I am sure if you search on net you will get a ready made code for this. You need to define a datatype of CLOB/BLOB and then read the retrieved value into this variable using a method provided for CLOB/BLOB.
Also can you please send me the sample code which you used to connect to oracle database via JSP
you can reply at gulabs@delhi.tcs.co.in
Cheers,
Gulab
Gulab Singh
Greenhorn

Joined: Aug 09, 2001
Posts: 8
sorry forgot to mention the URL
http://iron.gps.caltech.edu/doc/java.815/a64685/toc.htm
Gulab
Hal Watson
Greenhorn

Joined: Jul 06, 2001
Posts: 5
Thanks very much everyone!
As usual it looks like there is more than one way to skin a cat.
I took your hints and did a little more investigating and ended up with the following...
The key piece for me was this line, creating an object of the correct type and assinging it the value from the current record of the result set:
oracle.jdbc2.Clob cpccartlin = (oracle.jdbc2.Clob)results_2.getObject(2);
Then to display the lengthy text I used the following line, which determines the length of the clob text and puts that into a substring request:
out.println(cpccartlin.getSubString(1,(int)cpccartlin.length()));
The web links were very helpful - I'll let you know how the BLOB problem works out.
- Hal
Hal Watson
Greenhorn

Joined: Jul 06, 2001
Posts: 5
Gulab, here is how I am connecting to Oracle in my .jsp page:
// INITIALIZE VARIABLES FOR ORACLE CONNECTION
String sqlDriver = "oracle.jdbc.driver.OracleDriver";
String sqlURL = "jdbc racle:thin:@999.999.99.999:9999:xxx";
String sqlUser = "xxx";
String sqlPassword = "xxx";
Connection dbh = null;
// CONNECT TO ORACLE
try {
Class.forName(sqlDriver);
dbh = DriverManager.getConnection(sqlURL, sqlUser, sqlPassword);
} catch (ClassNotFoundException e) {
System.err.println("Unable to load database driver.");
} catch (SQLException e) {
System.err.println("Unable to connect to database.");
}
 
Don't get me started about those stupid light bulbs.
 
subject: How can I display CLOB & BLOB data in a JSP?