[BioSQL-l] Re: MORE Oracle BioSQL & BioJava problems
Hilmar Lapp
hlapp at gnf.org
Tue Aug 10 16:48:27 EDT 2004
Can you reduce this to a simple test case using a dummy table with e.g.
a single column of type CLOB, and then try to insert a value > 4000
chars through the JDBC driver?
There's a remote possibility that the driver is trying to do behind the
scenes what you describe if it determines the target column to be of a
LOB type, and it may fail to determine that when the table is in fact a
view (which it is here). To exclude that possibility, do the following:
SQL> DROP VIEW biosequence;
SQL> RENAME SG_Biosequence TO biosequence;
SQL> ALTER TABLE biosequence RENAME ent_oid TO bioentry_id;
and then try again the offending sequence. (I don't give this a high
likelihood though. What you describe as the way to insert LOBs is e.g.
the same way Tim Bunce wrote it for DBD::Oracle, so chances are this is
where the problem is.)
-hilm
On Aug 10, 2004, at 1:29 PM, Michael Griffith wrote:
> 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(BioSQLS
>>>>> eq
>>>>> 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(BioSQLSe
>>>>> qu
>>>>> 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.j
>>>>> av
>>>>> a:
>>>>> 1940)
>>>>> [java] at
>>>>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleState
>>>>> me
>>>>> nt
>>>>> .java
>>>>> :2709)
>>>>> [java] at
>>>>> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrep
>>>>> ar
>>>>> ed
>>>>> State
>>>>> ment.java:589)
>>>>> [java] at
>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(D
>>>>> el
>>>>> eg
>>>>> ating
>>>>> PreparedStatement.java:233)
>>>>> [java] at
>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(D
>>>>> el
>>>>> eg
>>>>> ating
>>>>> PreparedStatement.java:233)
>>>>> [java] at
>>>>> org.biojava.bio.seq.db.biosql.BioSQLSequenceDB._addSequence(BioSQLS
>>>>> eq
>>>>> ue
>>>>> nceDB
>>>>> .java:455)
>>>>> [java] ... 2 more
>>>>>
>>>>> Any help would be greatly appreciated!
>>>>>
>>>>> Cheers!
>>>>>
>>>>> MG
>>>>>
>>>>>
>>>
>>>
>
>
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------
More information about the BioSQL-l
mailing list