[BioSQL-l] Re: MORE Oracle BioSQL & BioJava problems
Michael Griffith
mg at base-pair.com
Tue Aug 17 11:39:18 EDT 2004
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(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
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
More information about the BioSQL-l
mailing list