[BioSQL-l] Re: MORE Oracle BioSQL & BioJava problems
Hilmar Lapp
hlapp at gnf.org
Tue Aug 17 12:07:24 EDT 2004
I can imagine that the biojava folks will be very appreciative if you
can submit a patch, also given that you successfully solved this very
problem before :-)
I believe the biojava folks don't have a notion of different driver
code for different RDBMSs, so that fix may need to be enclosed in
something that tests for Oracle being the JDBC driver.
-hilmar
On Tuesday, August 17, 2004, at 08:39 AM, Michael Griffith wrote:
> Hilmar,
>
>
> The problem is definitely related to clob handling. I adjusted the
> schema
> with the sql statements below, and ran the test. It made no difference.
>
> I think my only hope of getting this to work is if the BioJava group
> recognizes this as a bug and changes the code to first insert an
> empty_clob(), then to update the clob using the record locator.
>
> MG
>
>
> On 8/10/04 3:48 PM, "Hilmar Lapp" <hlapp at gnf.org> wrote:
>
>> 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(BioSQ
>>>>>>> LS
>>>>>>> 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(BioSQL
>>>>>>> Se
>>>>>>> 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(OracleStatemen
>>>>>>> t.
>>>>>>> ja
>>>>>>> va
>>>>>>> :2047
>>>>>>> )
>>>>>>> [java] at
>>>>>>> oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement
>>>>>>> .j
>>>>>>> av
>>>>>>> a:
>>>>>>> 1940)
>>>>>>> [java] at
>>>>>>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleSta
>>>>>>> te
>>>>>>> me
>>>>>>> nt
>>>>>>> .java
>>>>>>> :2709)
>>>>>>> [java] at
>>>>>>> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePr
>>>>>>> ep
>>>>>>> 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(BioSQ
>>>>>>> LS
>>>>>>> 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