[Biojava-l] Re: [BioSQL-l] release preparation

[Hilmar Lapp]

CLOB is IMHO actually easier to handle. Also, LONG is really odd to 
deal with in SQL whereas the Oracle server will nicely on-the-fly 
convert strings to CLOB and vice versa so long as they are shorter than 
4000 chars. Some of the type-generic functions that come with Oracle 
will not accept LONG but do accept CLOB. Just as another anecdotal 
piece, the built-in BLAST searcher available in Oracle 10g expects a 
cursor returning CLOBs, not LONGs.

With the java.sql.Clob interface to get at the full value as a string 
is as simple as

        Clob clob = resultSet.getClob(<your column index here>);
        String clobValue = clob.getSubString(0, clob.length());
Inserting a new value in reality is a two-step process:
	PreparedStatement pst = conn.prepareStatement("INSERT INTO Biosequence 
(Bioentry_Id, Seq) VALUES (?, EMPTY_CLOB())");
	pst.executeUpdate(idValue);
	pst = conn.prepareStatement("SELECT Seq FROM Biosequence WHERE 
Bioentry_Id = ? FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_UPDATABLE);

	ResultSet rs = pst.executeQuery(idValue);
	Clob clob = rs.getClob(1);
	clob.setString(0, theSeq);
	// not sure this is necessary
	rs.updateClob(1, clob);
	rs.close();
	// don't forget to release lock
	conn.commit();
	
I vaguely remember that Len or somebody else from the Biojava crowd had 
this all figured out?

        -hilmar
On Monday, April 18, 2005, at 02:08  AM, Richard HOLLAND wrote:
I looked into this in a bit more detail earlier today and found that,
since some version of Oracle around the 9i point in time, the official
Oracle JDBC driver API for accessing LOBs in changed. This means that
whereas before the same code could be used in BioJava to access both
Hilmar's and Len's versions of the database, since the 9i drivers this
has no longer been possible, and BioJava only works with Len's version.
The problem is due to the way in which Len's schema uses LONG values 
for
biosequence.seq, but Hilmar's uses CLOBs.

(The nitty gritty - before 9i, Oracle JDBC allowed you to access both
LONG and CLOB columns using getString()/setString() methods to
manipulate them. Now, these methods only work with LONG columns, and 
you
have to do fancy tricks to get anything useful into/out of CLOBs).

After discussing this with Mark earlier this afternoon, I am planning 
on
changing BioJava to use the new Oracle CLOB API, at which point it will
no longer work with schemas set up using Len's version. No change to
BioSQL is required. This, from a BioJava point of view, would make the
simple schema redundant. I am not sure if there are people in the other
Bio* projects who use the simple schema though so we probably can't 
just
drop it.

Are there any objections? I have crossposted this to the BioJava list 
to
make sure everyone who might be affected gets a say.

cheers,
Richard
Richard Holland
Bioinformatics Specialist
GIS extension 8199
---------------------------------------------
This email is confidential and may be privileged. If you are not the
intended recipient, please delete it and notify us immediately. Please
do not copy or use it for any purpose, or disclose its content to any
other person. Thank you.
---------------------------------------------

-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Len Trigg
Sent: Monday, April 18, 2005 4:58 PM
To: [EMAIL PROTECTED]
Cc: Hilmar Lapp; Biosql
Subject: Re: [BioSQL-l] release preparation

Mark Schreiber wrote:
now, my bad! Agreed that from a SQL query perspective the
schemas are the
same, one just has more complexity (if I can call it that)
under the hood.
Indeed, the complexity is more to do with the complexity of installing
and understanding what's going on in all those files :-) (particularly
if you are not an oracle expert and have only been looking at the
BioSQL schemas for the other supported databases), and that's why I
did the simple version.  That's partly confirmed by the fact that the
bjia description of how to use the original schema is about 8KB, while
the description for the simple schema is about 1KB.  I'm all for
dumping the simple one if the barrier for entry for the original
schema is lowered (maybe it already has been).

I would prefer to keep instructions for the less complex
version up for
the time being as we are having difficulties getting
biojava to work
seamlessly with the more complex version. This is almost
certainly a
failing of biojava for which the oracle support seems to have been
compiled against the 'simple' schema not the 'complex schema'.
It certainly was only tested against the simple version, because
that's the only schema I had working when I wrote the Oracle support.
I am a little surprised that you are having major difficulties though,
since the original package has a compatibility layer that (supposedly)
presents the same schema as the simple version.

I expect we will soon have biojava supporting your version
and we can drop
the 'simple' schema. After all, there is not much point
using oracle if
you don't make use of the features.
In my case, it was a matter of using Oracle because that was what was
already installed :-)
Cheers,
Len.
_______________________________________________
BioSQL-l mailing list
[EMAIL PROTECTED]
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
-------------------------------------------------------------
_______________________________________________
Biojava-l mailing list  -  Biojava-l@biojava.org
http://biojava.org/mailman/listinfo/biojava-l