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

[Hilmar Lapp]

Michael, sorry for the delay in responding. I'll add the table to 
biosql. Thanks a lot for collecting this and getting it to work. (I 
assume it does work now?)

As for fixing Biojava, that Matt's/Thomas'/Mark's domain.

	-hilmar

On Aug 24, 2004, at 7:55 AM, Michael Griffith wrote:

> Hello all,
>
> For some reason this message was rejected by the moderator of the 
> bio-sql
> mail list.  I believe it may because I had a zip file attachment of the
> bio-sql/oracle scripts attached. I was asked by the moderator to 
> resend it.
>
> I am doing so, and posting to the bioJava group as well.  I would like 
> to
> help Shepard this problem to resolution, but have had no real 
> experience
> working on Open source projects to make fixes, etc.  Please let me 
> know what
> else I may do.
>
> Cheers!
>
> MG
> ------ Forwarded Message
> From: Michael Griffith <mg at base-pair.com>
> Date: Thu, 19 Aug 2004 16:00:02 -0500
> To: Michael Heuer <heuermh at acm.org>
> Cc: <biojava-dev at biojava.org>, <biosql-l at open-bio.org>
> Subject: Re: [Biojava-dev] Re: MORE Oracle BioSQL & BioJava problems
>
> Michael,
>
> The BioSQL DDL is the current version that is in the CVS for bio-sql
> project. It is also attached here as a zip file.  In the create objects
> section there is a missing table that needs to get created after all 
> the
> other objects are created:
>
> CREATE TABLE term_relationship_term (
>     term_relationship_id    int NOT NULL,
>     term_id                 int NOT NULL
>     )
>
> In the class org.biojava.bio.seq.db.biosql.BioSQLSequenceDB.java, at 
> line
> 446, the Prepare statement reads:
>
> PreparedStatement create_biosequence = conn.prepareStatement("insert 
> into
> biosequence " + "(bioentry_id, version, length, seq, alphabet) " +
> "values(?, ?, ?, ?, ?)");
>
> I think it should be something like:
>
> PreparedStatement create_biosequence = conn.prepareStatement("insert 
> into
> biosequence " + "(bioentry_id, version, length, seq, alphabet) " +
> "values(?, ?, ?, empty_clob(), ?)");
>
> Then -- I think you can do this with the JDBC clob classes, but I have
> always used the Oracle JDBC/CLOB classes.  I would think you'd want to 
> keep
> all the oracle stuff segregated out...
>
> After your insert is successful in biosequence, you'll want to update 
> the
> clob table.  You have to select the record for update using the newly
> inserted id.
>
>     String sqlLock= "SELECT seq FROM biosequence WHERE bioentry_id = ? 
> FOR
> UPDATE";
>     String sqlUpdate="UPDATE biosequence SET seq=? where 
> bioentry_id=?";
>         try {
>             // First get the locator (necessary for Oracle)
>             //
>             // Get newly created record from DB
>             stNew = conn.prepareStatement(sqlLock);
>             stNew.setInt(1, bioentry_id);
>             rsNew = stNew.executeQuery();
>             rsNew.next();
>
>             oracle.sql.CLOB dbClob = (oracle.sql.CLOB) 
> rsNew.getClob(1);
>             pst = conn.prepareStatement(sqlUpdate);
>             dbClob.putString(1, seqstr);
>             pst.setClob(1, dbClob);
>             pst.setInt(2, bioentry_id);
>             result = pst.executeUpdate();
>             if (result < 0) {
>                 conn.rollback();
>             } else {
>                 conn.commit();
>             }
>         } catch (Exception e) {
>             // Log the error...
>         } finally {
>             try {
>                 // Close all resources...
>                 //
>             }
>             catch (Exception e) {
>                 // Log any errors
>             }
>         }
>
>     }
>
>
> On 8/18/04 2:49 PM, "Michael Heuer" <heuermh at acm.org> wrote:
>
>> Hello Michael,
>>
>> I found a couple of instances of Oracle on which to test, 8.1.7.4.0 on
>> Solaris and 9.2.0.1.0 on Solaris.
>>
>> If you could send me your version of the biosql DDL and a source code
>> example of what you mean by
>>
>>> 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.
>>
>> then I'll give it a go.
>>
>> Or alternatively, if you know what changes need to be made to the 
>> biojava
>> codebase, then you just need to do
>>
>> $ cvs -d :pserver:cvs at cvs.open-bio.org:/home/repository/biojava login
>> (when prompted, the password is 'cvs')
>>
>> $ cvs -d :pserver:cvs at cvs.open-bio.org:/home/repository/biojava co
>> biojava-live
>> $ cd biojava-live
>>
>> (make changes to source code)
>>
>> $ ant
>> (to make sure it builds properly)
>>
>> $ cvs diff -u . > patch.txt
>> (to generate the patch)
>>
>>  michael
>>
>>
>
> ------ End of Forwarded Message
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------