Re: DBD::Oracle + BLOB

Thu, 30 Oct 2008 14:27:09 -0700 

Martin Gainty wrote:

any reason why you want to 'describe' before the executing the statement?
thanks
Martin _____________
I'm not wishing to put words in Merijn's mouth but having attempted to write a perl app which worked with a number of DBDs I have some experience of how difficult this is. 


DBD::ODBC always runs a describe (SQLDescribeCol) on the result-set so 
DBD::ODBC knows what the column types really are. At the end of the day 
if you do a select on a table the data is returned to perl scalars and 
DBD::ODBC internally, even if you do not not use bind_col binds the 
columns in a way that allows them to be copied to perl scalars. In a 
similar way if you use a select/insert/update with a parameter DBD::ODBC 
always tries to use SQLDescribeParam to find out the type of the 
parameter - this allows it to attempt to automatically do the right 
thing without you having to specify the bind type. This mostly does the 
right thing except for a) ODBC drivers which do not support 
SQLDescribeParam (few and far between but they do exist) and b) drivers 
which support SQLDescribeParam but which struggle to return the correct 
info because they attempt to rearrange the SQL to do a select on the 
tables and find the parameter types. In particular, the MS SQL Server 
driver has a lot of problems with subselects in a query as it attempts 
to rearrange your sql with parameters and fails horribly (see rt_xxx.t 
tests in DBD::ODBC).


<rant>

I cannot speak for other DBD maintainers but even though I look after 
DBD::ODBC currently, I mostly use DBD::Oracle in the work I'm doing 
currently.  Previous to that we attempted to make our app work with DB2, 
mysql, Oracle and ODBC (before we settled on a database) and failed 
horribly. Most of our app would work with most DBDs but we ran into 
severe problems with last_insert_id, quite a few problems calling 
procedures/functions (mostly we worked around - exception was returning 
cursors from procedures and functions which ODBC does not do)  and I'm 
sure loads of other issues which I could probably dig out in time. Our 
app now runs to Oracle only  but even then we have our own module over 
DBI to handle clobs/blobs and cursors. The reason for this is that if 
you want a [cb]lob from DBD::Oracle you currently need to specify the 
bind type and if you want a cursor you need to bind with ora_rset. In 
addition, retrieving a [cb]lob from DBD::Oracle is annoying since it 
automatically tries to hide the lob locator and retrieve the [cb]lob for 
you but this then is subject to LongReadLen (which is impossible to use 
if you have no idea how long the [cb]lob will be). As a result (and this 
was subject to postings and changes in dbi-dev/dbi-users since the auto 
lob setting was not inherited in magicked statement handles via returned 
cursors) we have changed our app to turn DBD::Oracle auto lobs off and 
then retrieve the [cb]lob ourselves with ora_lob_read (since this 
guarantees to return the full [cb]lob without you needing to know 
beforehand how big it might be.



Practically, if you attempt to write perl and DBI code which you can use 
with different DBDs you are mostly ok if you keep things "really simple" 
but even documented APIs like last_insert_id are poorly supported 
(DBD::ODBC has loads of problems with this in particular and in 
DBD::Oracle you are better using "returning xxx", mysql has a select 
last_insert_id or something like that and SQL Server has select 
@@identity etc etc). As soon as you start using procedures/functions you 
enter a world of pain. Our app does not even have select permission to 
tables and all the logic is in procedures and functions and this is 
really difficult to do across DBDs (e.g., returning of cursors vs 
more_results etc).

</rant>


Please don't read this as an attack on DBD::Oracle - it isn't, it could 
just as easily be DBD::ODBC or DBD::DB2 or DBD::something but I'm using 
DBD::Oracle mostly these days. I do however, have a lot of sympathy with 
Merijn's point as I've tried to support multiple DBDs and got stuck (and 
that is ignoring SQL differences which we all know can be a pain).



Out of rant mode, I certainly think that if a DBD can just do the right 
thing (whatever that is) then we should encourage it. I'm somewhat out 
of this now since we settled on Oracle for this project. I do however, 
think that with DBD::Oracle it is annoying to have to say bind this as a 
blob and then (when it is a select) to have it automatically subject to 
LongReadLen and LongTruncOk as these introduce their own problems if you 
cannot know how big a [bc]lob is but KNOW you want it all i.e., I would 
be ok with it if there was a LongReadLen = "as much as it takes" 
(perhaps there is and it is too late for me to remember it).



I've probably strayed off topic now but Merijn's post struck a cord and 
Martin 's question was just unlucky enough to be the current last post 
when this struck me. I always intended to post my results of trying to 
cope with multiple DBDs but project schedules somewhat hampered that. No 
doubt this we rebound on me - steel jacket donned and ready ;-)


Martin


_________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. 




  

Date: Thu, 30 Oct 2008 21:00:38 +0100
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]; [EMAIL PROTECTED]; dbi-users@perl.org
Subject: Re: DBD::Oracle + BLOB

On Thu, 30 Oct 2008 15:06:00 -0400, John Scoles <[EMAIL PROTECTED]>
wrote:


    

Again Tim and his wisdom of the ages comes to the rescue.


A quick look at DBD::ORacle and his suggestion will take only a few 
seconds to do.
      

Well, it was like I suggested, but it still does not remove the need
for bind_param (), something I would like to see possible.


    

Looks like a patch coming up in the AM.

Tim Bunce wrote:

      

On Thu, Oct 30, 2008 at 10:43:22AM -0400, John Scoles wrote:

  
        

I will look into it tomorrow Merijn suggested something like dbd_blob or 
dbd_binay at  the dbd level


so we could  just do this

$s->bind_param (":p1","foo\nbar", {TYPE=>dbd_blob});

and let the DBD figure out what dbd_blob is for them

          

Using TYPE => $x implies that $x is an ANSI/ISO standard constant.

    use DBI qw(:sql_types);

    $sth->bind_param(":p1", "foo\nbar", { TYPE=> SQL_BLOB });

That *may* be the right thing to do, but be sure to read the ODBC manual
to ensure you can implement the exact semantics. Otherwise you're just
creating a new portability problem.

Generally, driver authors should aim to do what ODBC (and ideally
DBD::ODBC) does for these types.


        

The tack I am looking at is some sort of flag where you tell you
DBD to explicitly describe before an execute.

Not sure if that could even be done with a update though at least with
Oracle

Will work on in on the morrow

          

--
H.Merijn Brand          Amsterdam Perl Mongers  http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, SuSE 10.1, 10.2, and 10.3, AIX 5.2, and Cygwin.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/

    


_________________________________________________________________
When your life is on the go—take your life with you.
http://clk.atdmt.com/MRT/go/115298558/direct/01/

  

























					Reply via email to