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

[Michael Griffith]

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