Inserting data into a Clob

Cully Orstad

unread,

Jul 19, 2001, 1:11:21 PM7/19/01

to

I need to insert data into a Clob (>8k) and have not been able to determine how
to do it without using Oracle's extensions to the JDBC JDK.

Does anyone have a code snippet to show how to populate the Clob in the first
place?

Thanks,

Cully.

Settings from weblogic.properties:

weblogic.jdbc.connectionPool.oraclePoolCSA=\
url=jdbc:oracle:thin:@IHP273:1521:AIRCORE,\
driver=oracle.jdbc.driver.OracleDriver,\
loginDelaySecs=1,\
initialCapacity=1,\
maxCapacity=2,\
capacityIncrement=2,\
allowShrinking=true,\
shrinkPeriodMins=15,\
refreshMinutes=10,\
testTable=dual,\
props=user=csav2;password=csav2

weblogic.allow.reserve.weblogic.jdbc.connectionPool.oraclePoolCSA=\
guest,everyone
weblogic.allow.reset.weblogic.jdbc.connectionPool.oraclePoolCSA=everyone
weblogic.allow.shrink.weblogic.jdbc.connectionPool.oraclePoolCSA=everyone

weblogic.jdbc.TXDataSource.CSATXDataSource=oraclePoolCSA

Filip Hanik

unread,

Jul 19, 2001, 1:22:05 PM7/19/01

to

look under
$BEA_HOME/wlserver6.0sp1/samples/examples/jdbc/oracle/OracleBlobClob.java

--
~
Namaste - I bow to the divine in you
~
Filip Hanik
Software Architect
fi...@filip.net
www.filip.net
"Cully Orstad" <cully....@unisys.com> wrote in message
news:3b5706a9$1...@newsgroups.bea.com...

Tony Bailey

unread,

Jul 19, 2001, 1:50:46 PM7/19/01

to

Using the example, I get a NullPointerException when calling
getBinaryOutputStream() under WL6.0sp2.

"Filip Hanik" <ma...@filip.net> wrote in message
news:3b571764$1...@newsgroups.bea.com...

Filip Hanik

unread,

Jul 19, 2001, 2:32:19 PM7/19/01

to

search the newsgroups for this problem, I believe there is a fix

Filip

--
~
Namaste - I bow to the divine in you
~
Filip Hanik
Software Architect
fi...@filip.net
www.filip.net

"Tony Bailey" <tony.bai...@usa.net> wrote in message
news:3b57...@newsgroups.bea.com...

Cully Orstad

unread,

Jul 19, 2001, 3:59:47 PM7/19/01

to

Filip,

I should have stated we do not have WLS 6. We are still using WLS 5.1 sp7. I
do not have the example you refered me to.

Cully.

Tony Bailey

unread,

Jul 19, 2001, 4:06:30 PM7/19/01

to

If I step through the code in the debugger, everything is A-OK up until the
getBinaryOutputStream, then I get a NPE. The blob appears to be
instantiated when I toString() it in the previous line.

private Blob getBlob(File f, String table)
throws SQLException
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String SQL;
Blob imgblob = null;
FileInputStream fis = null;
OutputStream bos = null;

try
{
conn = dbutil.getConnection();

SQL = "SELECT image FROM " + table;

pstmt = conn.prepareStatement(SQL);
rs = pstmt.executeQuery();
rs.next();
imgblob = rs.getBlob("image");

logger.log(Debug.DEBUG, "DictTblCtrlr.getBlob(): The blob is: "
+ imgblob);

fis = new FileInputStream(f);
bos = ((OracleBlob)imgblob).getBinaryOutputStream();
byte[] buffer = new byte[65534];
int length = -1;
while ((length = fis.read(buffer)) != -1)
bos.write(buffer, 0, length);
bos.flush();
}
catch (Exception sqle)
{
logger.log(Debug.ERROR, "DictTblCtrlr.getBlob(): " +
sqle.getMessage());
sqle.printStackTrace();
throw new SQLException(sqle.getMessage());
}
finally
{
try { fis.close(); bos.close(); } catch (Exception e) {}
dbutil.release(rs, pstmt, conn);
}
return imgblob;
}

[07-19-2001 03:02:07] DEBUG:DictTblCtrlr.getBlob(): The blob is:
weblogic.jdbc.rmi.SerialBlob@1f1f12
[07-19-2001 03:02:23] ERROR:DictTblCtrlr.getBlob():
java.lang.NullPointerException
java.sql.SQLException: java.lang.NullPointerException
at
weblogic.jdbc.rmi.SerialBlob.getBinaryOutputStream(SerialBlob.java:73)
at
com.uctech.psws.persistence.datacontroller.DictionaryTableController.getBlob
(DictionaryTableController.java:410)
at
com.uctech.psws.persistence.datacontroller.DictionaryTableController.insert(
DictionaryTableController.java:363)
[....]

"Shiva Paranandi" <shi...@yahoo.com> wrote in message
news:3975F0F6...@yahoo.com...
> Could you post the exception?
>
> Shiva.

Tony Bailey

unread,

Jul 20, 2001, 11:38:23 AM7/20/01

to

Still getting the NPE. Any other ideas??? Are you able to do this
successfully on sp2?

"Shiva Paranandi" <shi...@yahoo.com> wrote in message

news:397636F3...@yahoo.com...
> Try these two things:
> 1. Change your SQL query to "SELECT image FROM " + table + " for update";
> 2. Also, set conn.setAutoCommit(false);
>
> Shiva.

Tony Bailey

unread,

Jul 23, 2001, 2:36:39 PM7/23/01

to

Shiva,

That was it....instead of doing the Blob work all in the same insert
statement, I grabbed a new sequence and inserted a row with an empty_blob().
After that I did an update on that row setting the correct value of the
Blob.

Thanks for the help!!

"Shiva Paranandi" <shi...@yahoo.com> wrote in message

news:39775BBD...@yahoo.com...
> I tried with SP2 only and it works. The problems might be that you are
getting a
> null blob. Try inserting an emtpy blob first. Check out this program which
works
> for me on WLS 6, SP2.
>
> Shiva.