[Biojava-dev] Re: MORE Oracle BioSQL & BioJava problems

Michael Griffith mg at base-pair.com
Tue Aug 10 16:29:03 EDT 2004


Hi Hilmar, 

The DB target is Oracle 9i (9.2.0.4) running on SUSE Linux 9x.

I do believe the problem occurs on sequences that are > 4000 chars.

The offending Java code appears to be:

PreparedStatement create_biosequence = conn.prepareStatement("insert into
biosequence " + "(bioentry_id, version, length, seq, alphabet) " + "values
(?, ?, ?, ?, ?)");

String seqstr = seqToke.tokenizeSymbolList(seq);
create_biosequence.setCharacterStream(4, new StringReader(seqstr),
seqstr.length());

In all Java/Oracle applications we've developed, we've always inserted an
empty_clob(), and then updated the clob separately using the record locator.

I am a little apprehensive to hack the Opensource code, just because I want
to stay in sync with the BioJava releases...

Has anyone else using BioJava/BioSQL in Oracle run into this problem?

Thanks in advance!

MG



On 8/9/04 2:28 PM, "Hilmar Lapp" <hlapp at gnf.org> wrote:

> Which rel. is the target Oracle DB?
> 
> What is the length of the sequence string causing trouble? If it is
> indeed longer than 4000 chars, does the problem disappear when you make
> the sequence shorter than 4000 chars? Which JDBC API call is used to
> set the sequence string in the biojava language binding? If it is
> indeed setString(), what happens if you change that to the streaming
> API?
> 
> -hilmar
> 
> On Aug 9, 2004, at 10:08 AM, Michael Griffith wrote:
> 
>> Hilmar,
>> 
>> Thanks for the reply.
>> 
>> Just to make sure I had the latest and greatest JDBC driver, I
>> downloaded
>> 9.2.0.3 from Oracle's web site.  I got the same exact error, in the
>> same
>> exact order.
>> 
>> I am still puzzled as to what is going on.
>> 
>> MG
>> 
>> 
>> On 8/9/04 11:25 AM, "Hilmar Lapp" <hlapp at gnf.org> wrote:
>> 
>>> This smells like a problem with one of the LOB columns, which is
>>> Anncomment.Comment_Text and Biosequence.Seq, and the stack trace looks
>>> like it's the Seq column (which holds the sequence).
>>> 
>>> LOB columns in Oracle need to be streamed if they are over 4000 chars
>>> (otherwise the server can do the conversion). I believe the more
>>> recent
>>> versions of the Oracle JDBC driver do that transparently behind the
>>> scenes if you call {set,get}String() on a column that in reality is a
>>> LOB.
>>> 
>>> Are you by any chance trying to communicate with a 9i+ database using
>>> an 8i driver?
>>> 
>>> -hilmar
>>> 
>>> On Monday, August 9, 2004, at 09:03  AM, Michael Griffith wrote:
>>> 
>>>> Hi,
>>>> 
>>>> I have been trying to get the latest BioSQL (Oracle) and BioJava to
>>>> play
>>>> nicely -- and I feel that I am close, but I am still getting errors.
>>>> I am
>>>> trying to read a GenBank file to the Oracle BioSQL schema with the
>>>> following
>>>> code:
>>>> 
>>>> SequenceDB db = new BioSQLSequenceDB(dbDriver, dbURL, dbUser, dbPass
>>>> biodatabase, create);
>>>> 
>>>>     SequenceIterator iter =
>>>>             (SequenceIterator)SeqIOTools.fileToBiojava(format, alpha,
>>>> br);
>>>>    int counter= 0;
>>>> 
>>>>     while (iter.hasNext()) {
>>>> 
>>>>         Sequence seq = iter.nextSequence();
>>>> 
>>>>         try {
>>>>             db.addSequence(seq);
>>>>         }
>>>>         catch (Exception e) {
>>>>             e.printStackTrace();
>>>>         }
>>>>         ...
>>>>     }
>>>> 
>>>> This code works perfectly well with the mySQL version of the bio-sql
>>>> schema,
>>>> however with the oracle version, I get the following SQLException
>>>> stack.
>>>> 
>>>> The loop loads about 65 of the first 70 records, and hangs on record
>>>> #71,
>>>> every time.  What is puzzling, is I have never had any sort of these
>>>> kinds
>>>> of errors with any other Java/Oracle application.
>>>> 
>>>>  [java] org.biojava.bio.BioRuntimeException: Error adding sequence:
>>>> NM_019764
>>>>      [java]     at
>>>> org.biojava.bio.seq.db.biosql.BioSQLSequenceDB._addSequence(BioSQLSeq
>>>> ue
>>>> nceDB
>>>> .java:498)
>>>>      [java] Trying to add: NM_021274 to the database -- insert attemp
>>>> #:71
>>>>      [java]     at
>>>> org.biojava.bio.seq.db.biosql.BioSQLSequenceDB.addSequence(BioSQLSequ
>>>> en
>>>> ceDB.
>>>> java:365)
>>>>      [java]     at
>>>> com.gts.genebank.GeneralReader.main(GeneralReader.java:74)
>>>>      [java] Caused by: java.sql.SQLException: No more data to read
>>>> from
>>>> socket
>>>>      [java]     at
>>>> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
>>>>      [java]     at
>>>> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
>>>>      [java]     at
>>>> oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
>>>>      [java]     at
>>>> oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
>>>>      [java]     at
>>>> oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
>>>>      [java]     at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369)
>>>>      [java]     at
>>>> oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
>>>>      [java]     at
>>>> oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:
>>>> 1093)
>>>>      [java]     at
>>>> oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja
>>>> va
>>>> :2047
>>>> )
>>>>      [java]     at
>>>> oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
>>>> a:
>>>> 1940)
>>>>      [java]     at
>>>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
>>>> nt
>>>> .java
>>>> :2709)
>>>>      [java]     at
>>>> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
>>>> ed
>>>> State
>>>> ment.java:589)
>>>>      [java]     at
>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del
>>>> eg
>>>> ating
>>>> PreparedStatement.java:233)
>>>>      [java]     at
>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del
>>>> eg
>>>> ating
>>>> PreparedStatement.java:233)
>>>>      [java]     at
>>>> org.biojava.bio.seq.db.biosql.BioSQLSequenceDB._addSequence(BioSQLSeq
>>>> ue
>>>> nceDB
>>>> .java:455)
>>>>      [java]     ... 2 more
>>>> 
>>>> Any help would be greatly appreciated!
>>>> 
>>>> Cheers!
>>>> 
>>>> MG
>>>> 
>>>> 
>> 
>> 



More information about the biojava-dev mailing list