[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: biosql-l-bounces at portal.open-bio.org
>> [mailto:biosql-l-bounces at portal.open-bio.org] On Behalf Of Len Trigg
>> Sent: Monday, April 18, 2005 4:58 PM
>> To: mark.schreiber at novartis.com
>> 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
>> 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
-------------------------------------------------------------