[BioSQL-l] 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 BioSQL-l
mailing list