Oracle BLOB Query Failure

[gchianes]

We are having problems with getting Oracle BLOB out of the database. Inserting into the database works from JSP and query works until the cache is flushed. After about 20 minutes, no errors appear in the log but the JSP errors with 'com.inet.ora.i'... and fails to return the BLOB data.

We have tried versions 2.4.x, 3.0.0 and 3.0.2 of JBoss with the Oracle thin driver and the Seropto Oracle driver.

Any suggestions would be appreciated.

George

[dsundstrom]

The Oracle drivers a broken with regards to BLOBs. I have been told that the newest JBossCMP code works with the OCI drivers. If you are coding the JDBC your self, you will have to use the custom Oracle BLOB APIs, so your application will be less portable.

[gchianes]

I'm not sure I understand. Are you saying both the Oracle thin driver and the Seropto Oracle drivers have the same problem?

Also, you and Mark Kent had exchanged email/postings about the same issue. I thought that the fixes were made to both the CMP and JAWS code in 3.0.1. Is that not correct?

I'm not sure how to invoke the Oracle OCI drivers.
If we do, will this fix the problem without rewriting our application?

Thanks for your time.
George

[bleupen]

so far i have had no problems with the LONG RAW type mapping for storing objects in Oracle 8i/9i. just make sure that you initialize your objects to non-null values before attempting to store them.

b

[gchianes]

Without a catch, we get a little more detail. It's basically the same error whether we use Oracle Thin or Seropto driver. Is there something else we could do with the JBoss internal code to fix this?



Apache Tomcat/4.0.4 - HTTP Status 500 - Internal Server Error

--------------------------------------------------------------------------------

type Exception report

message Internal Server Error

description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.

exception

java.lang.ClassCastException: com.inet.ora.i
at org.apache.jsp.show_0005fhelp$jsp._jspService(show_0005fhelp$jsp.java:597)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)
at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)
at java.lang.Thread.run(Thread.java:498)



--------------------------------------------------------------------------------

[mkent]

> so far i have had no problems with the LONG RAW type
> mapping for storing objects in Oracle 8i/9i. just
> make sure that you initialize your objects to
> non-null values before attempting to store them.

Could you provide a bit more detail in your setup? Here is ours:
- Running the jboss-3.0.2_tomcat-4.0.4 package

- We have an Oracle 8.1.6 table with a "BLOB" column type

- The standardjaws.xml mapping uses the Oracle8 mappings

<java-type>java.lang.Object</java-type>
<jdbc-type>JAVA_OBJECT</jdbc-type>
<sql-type>BLOB</sql-type> (is this where you used LONG RAW?)


- The data type in the .java bean files is "Object" (should this be "Blob"?)
public OurData(
Integer our_id,
String our_title,
Object our_text,
Integer fk_our_id
)

- The form field request.getParameter returns a String which is cast to Object to pass to bean
Object blob_col = (Object)request.getParameter( "fieldname" );


Bleupen, is that like your set up?

To read the BLOB value from the database is another issue.

[bleupen]

mkent,

i see that you do not have the luxery of redefining your Oracle table schema. i have the following type mapping for Oracle in my jbosscmp-jdbc.xml file:


<java-type>java.lang.Object</java-type>
<jdbc-type>JAVA_OBJECT</jdbc-type>
<sql-type>LONG RAW</sql-type>


thus, jboss creates LONG RAW columns to store objects. this works as long as the objects i attempt to store are not null.

b

[baillieul]

got Oracle blobs working with data direct jdbc drivers. oracle thin is not working at all...

[gchianes]

Brad,

We are now getting a nullpointerexception when attempting to myBlob.setBytes in ebjCreate. Your message refers to initializing the Blob before attempting to store it.

This is the code snippet:

public class HelpMessageBean implements javax.ejb.EntityBean
{
public Integer help_message_id;
public String help_message_title;
public Blob help_message_text;
....
public Integer ejbCreate( HelpMessageData helpmessage )
throws RemoteException, PrecyxFindException
{
....
try
{
String blobStr = helpmessage.getHelp_message_text();
System.out.println("help_message_text is "+blobStr);

help_message_text.setBytes( 1, blobStr.getBytes() );
}
catch( java.sql.SQLException e )
{
e.printStackTrace();
throw new PrecyxFindException( e );
}
....
}


This seems like it should work, but the log shows java.lang.NullPointerException on setBytes. Is this how you are storing your Blob? Is there a specific method I need to use to initalize the Blob?

Thanks,
George

[scoy]

George,

I think your problem here is that you cannot use a Blob as an EJB bean property. It must be serializable, so it can be transmitted to your client - the JSP page.

On the other hand, it doesn't look like a java.sql.Blob either, because there is no such method as java.sql.Blob.setBytes.

Are you using BMP or CMP?

On the topic of Oracle JDBC drivers, the "thin" variety cannot in general store data of any kind larger than 4-5k. The exception seems to be LONG VARCHAR(n) columns, which can be assigned using setCharacterStream.
On the other hand, the "oci" driver does not have this size limitation. You can use the oci drivers if you install Oracle client software on your JBoss machine. Make sure that you have the Oracle jar file from a 9.2 distribution. All bets are off with earlier versions. The same Oracle jar file is used for both "thin" and "oci" access, only the URL changes.

Steve Coy

[gchianes]

Steve,

You may be right about not being able to use Blob in an entity bean. On the other hand, J2SE JDK 1.4 includes additional methods for java.sql.Blob, including setBytes.

To answer your question, we are using BMP.

There are other postings and messages that say Seropto and DataDirect drivers work with JBoss and Blobs. We've tried both of those without success. We can now successfully query the blob data, but not write it.

I have to believe that the problem is either something simple in the way we've coded the application and the beans. Or, as you point out, Blobs can't be used as an EBJ property. If that's the case, then we're going down the wrong path.

Thanks,
George

[gchianes]

Steve,

I mispoke in my answer to your question. We actually use CMP.

G

[scoy]

If your are using JDK1.4, then make sure that your Oracle JDBC implementation is in a jar called ojdbc14.jar, and not classes.jar.

I think that I'm right about the applicability of Blobs in general to what you are doing. It's a requirement that they be transmittable via RMI. There is a remote possibility that the Oracle implementation is serializable, but I doubt it.