Inserting BLOB data into Oracle.

, ,
1

Hello all,

I have been trying to discover answers in this list for some of my questions and have been successful so far. However, there is one (logical perhaps) issue that I am facing at the moment, for which I havent discovered an answer.

I am trying to insert a BLOB data into an Oracle Database.
One of my colleagues suggested that I may use the Java way of doing things which is:
Step 1) Insert into the table with BLOB column reading empty_blob()
Step 2) Lock the row by selecting the BLOB column for update
Step 3) Setting the BLOB column value (as java.sql.BLOB object) with the desired bytes
Step 4) Update the desired row with the BLOB data.

Whilst the above can be done in a Java based service, is there any way of doing the needful using built in web Methods services?
Can you give me pointers on making this generic?
Could the same be done with jdbc adaptors?
Finally, is there a way to use connections from a pool (possibly that used by the webMethods adapters) to perform JDBC SQL manipulations?

Thanks

Cheers
Gautham Kasinath

2

Hi Gautham,
we are inserting a clob into an Oracle database using a Java service; here is an extraction from that service:

JDBCConnection Jconn = (JDBCConnection) IDataUtil.get( pipelineCursor, “conn” );
pipelineCursor.destroy();

Connection conn = (Connection) Jconn.getNativeConnection();
PreparedStatement ps  = conn.prepareStatement("insert into " + tabName + " values (?,?,?,?,?)");
ps.setString(1,NmSrv);
ps.setString(2,EvtType);
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.setLong(4, ID_IPROC);
ps.setCharacterStream(5,new StringReader(EvtData),EvtData.length());
ps.execute();
ps.close();

where “conn” is an object rapresenting a db connection arriving as parameters.

Hope this help.

Sandro

3

Hey Sandro,

Thanks for that mate. It looks like a good ol’ java way of inserting. Though this method differs from the one that I posted above, I will try it and let you know if it worked.
However, I did notice that you have a CLOB field on the table, whilst I have a BLOB. I m sure there must be an equivalent API method.

Thanks.

Cheers
G

4

Hey Sandro,

I have a quick question: Is it possible to get a JDBC connection from an existing JDBC connection pool? If yes, how.
I understand that the “conn” is a parameter into your service, however, in my case, I need to establish the connection w/in the JAVA context.

Please advise.

Thanks
Cheers
Gautham Kasinath
P.S. Mark (Carlson), could you advise too? Thanks.

5

HI Gautham,
we use WmDB and to get a connection simple execute

  1. pub.db:connect
  2. java service above
  3. pub.db:close

Bye