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

[Michael Griffith]

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
> 
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/octet-stream
Size: 208478 bytes
Desc: not available
Url : http://portal.open-bio.org/pipermail/biojava-dev/attachments/20040819/b8a368ec/attachment-0001.obj