CLOB BLOB support in J2EE Engine

[Former Member]

i posted this in the Technology forum couple of days ago, but not sure that was the right place. so here it is again. apologize for repeated postings, perhaps we ought to have a J2EE Engine  forum?

-

-

---

i am in the process of porting our company's j2ee application to the SAP J2EE Engine 6.20. our app was built to run on Weblogic as the app server and Oracle as the db server. One of the things i am trying to figure out is how the CLOB and BLOB table columns are supported in SAP J2EE Engine. in java, these corresponds to java.sql.Clob and java.sql.Blob interfaces.

i did find some information in CLOB and BLOB support in "SAP Library - J2EE Technology in SAP Web Application Server". there is a section titled "Working with LOBs". but the information is too rudimentary. specifically, i need to be able to update contents in a CLOB and BLOB column after it is inserted.

jdbc driver vendors such as Oracle and Weblogic all seem to provide additional support exactly for updating CLOB and BLOB data. for example, Weblogic has the following classes:

          weblogic.jdbc.rmi.SerialOracleBlob

          weblogic.jdbc.rmi.SerialOracleClob

once we cast the ResultSet.getClob() or getBlob() to the above types, we can use the getCharacterOutputStream() or getBinaryOutputStream() methods to easily manipulate the clob/blob contents.

similarly Oracle has oracle.sql.CLOB and oracle.sql.BLOB classes, which also implement the getCharacterOutputStream() or getBinaryOutputStream() methods.

Question: does SAP J2EE Engine provide its own implementation of the java.sql.Clob and java.sql.Blob interfaces, with support for updating LOB contents similar to Oracle and Weblogic? if not, how would you suggest to deal with updating LOB table colulmns?

thanks in advance.

wentao

[Benny]

Hi Wentao,

there is no special implementation in the engine for this. Though, there nothing but the standard JDBC implementations of your driver.

Regards,

Benny

[Former Member]

hello Benny,

thanks for the verification. yes, you are right, it is the feature of the underlying jdbc driver. but it is somewhat tricky to use the driver features within the environment of an application server. i am sharing my experience here, hopefully it would be helpful to someone else as well.

option #1, i register the driver in my java code and get the connection from the driver manager directly. yes, this way i can utilize all features provided by the driver. but the down side is that i can no longer leverage the app server to maintain connection pooling and configure connection data such as db server host, user and passwd etc. i have to manage all that myself.

option #2, create and configure a data source on the app server using desired driver and obtain the connection from the data source. this way i can leverage all the services the app server provides. the problem now is that the app server puts wrappers around sql calls, i can no longer cast the returned objects to the driver specific implementation classes. for most sql operations, this is non-issue. but CLOB and BLOB are special.

for oracle jdbc driver, i had to dig pretty deep in the documentation to find out that its support for methods PreparedStatement.setBinaryStream() and .setCharacterStream() etc are available only for 816 and above oracle server, and JDBC OCI driver. in other words, thin driver and older oracle server don't support such java interfaces.

once i've confugred a dbpool using the oracle jdbc oci driver, i was able to obtain the connection from the data source and then use standard java apis for manipulating blob and clob.

wentao

[Benny]

Hi Wentao

>option #2

I hope you are on 6.30, so this is true: Be aware that those wrappers should only happen with OpenSQL set on. You can configure this in the JDBC part of Administrator tool and set it to vendor. Then there is no wrapper.

Regards,

Benny