[Dbix-class] DBI + DBD::Oracle LOB/LONG fetches and LongReadLen LongTruncOk

[Jess Robinson]

Hi,

If you'd like to implement this for future versions of DBIx::Class, I 
suggest you take a look at the DBIx-Class-current branch, which can be 
found at:
http://dev.catalyst.perl.org/repos/bast/branches/DBIx-Class-current

In the -current release, we already have separate 
DBIx::Class::Storage::DBI subclasses for different DB types, so you can 
add Oracle specific code to ::Oracle, without affecting the others.

Send us patches including tests, and we'll be happy to add them!

Jess


On Fri, 17 Feb 2006, Leandro Hermida wrote:

>
> Hi everyone,
>
> Just was looking at the internals of DBIx::Class::Storage::DBI and I have a
> couple of questions/concerns.  I work with Oracle, MySQL and PostgreSQL
> using DBI with their respective DBD::* drivers and an particular feature of
> DBD::Oracle is the LongReadLen and LongTruncOk database handle attributes.
> Both of these attributes are extremely important to set when
> fetching/selecting LONG/LOB data using DBI and the underlying DBD::Oracle
> driver.  LongReadLen is an unsigned integer value that needs to be set to at
> least as large as the length of the LONG/LOB data you are selecting.
> LongTrunkOk is a boolean value which tells DBI what to do if you try to
> fetch LONG/LOB data that is longer than LongReadLen (1 - truncate data
> silently to LongReadLen length, 0 - raise an error).  In almost all cases
> you want LongTruncOk = 0 which I think is the DBI default.  Both attributes
> are not implemented or used in DBD::mysql or DBD::Pg so no problems there.
>
> I was trying to find where DBIx::Class sets or modifies the database handle
> LongReadLen attribute.  It doesn't seem to be set or modified anywhere (I
> grepped the entire DBIx tree) other than in the unrelated
> Class::PK::Auto::Oracle package.  Now I think you guys are going to say
> "well you just pass your value for LongReadLen in the database handle $attrs
> hashref { ..., LongReadLen => xxxxx, ... } when you create your
> DBIx::Class::Schema object, a la my $schema =
> My::Schema->connect($dsn,$user,$pass,$attrs);".  But for many applications
> this will not work because we cannot know ahead of time for our entire
> database what the maximum length of any LONG/LOB column data might be.  Or
> if we set it to some extremely high value to cover (hopefully) all
> possibilties then there will be a big waste of memory since
> $dbh->{LongReadLen} directly affects the memory used by resulting statement
> handles.  The DBI docs say don't just set it to some huge value and they
> advise doing a dynamic calculation of LongReadLen.  So the LongReadLen
> attribute needs to get dynamically set on the $dbh before doing the
> $dbh->prepare() or $dbh->prepare_cached() call for the SQL SELECT statement
> that is fetching any LONG/LOB data.  What I do in my code to dynamically
> calculate LongReadLen (based off of the idea presented in the DBI docs) is
> the following:
>
> As an example, suppose this is the SQL statement for the data I want to
> fetch which contains two LOB columns:
>
> SELECT  t1.col1, t1.col2, t1.lob_col3, t2.lob_col4, t2.col5
> FROM    table1 as t1 INNER JOIN table2 as t2 ON t1.col1 = t2.col1
> WHERE   t1.col2 = ?
>        AND
>        t2.col5 > ?
>
> Before running the above SQL, I first need to run the following SQL
> statement below to get the LongReadLen.  It finds the maximum length in
> bytes of the LONG/LOB data I will return from the SQL above.  It is
> important to note that between the fetch data SQL above and the get
> LongReadLen SQL below all of the SQL clauses that determine what rows of
> data we are getting back (FROM, WHERE, GROUP BY, HAVING) need to be exactly
> the same.  Clauses like ORDER BY can be omitted in the get LongReadLen SQL
> because they do not determine what rows come back and of course it improves
> performance to omit it.
>
> SELECT GREATEST(MAX(LENGTHB(t1.lob_col3)), MAX(LENGTHB(t2.lob_col4)))
> FROM   table1 as t1 INNER JOIN table2 as t2 ON t1.col1 = t2.col1
> WHERE  t1.col2 = ?
>       AND
>       t2.col5 > ?
>
> In Oracle the GREATEST funtion will even work with one argument as in the
> case if we have only one LONG/LOB column in the fetch data SELECT statement.
> It will just return the same length as if it weren't used (i.e. SELECT
> MAX(LENGTHB(lob_column)) ).
>
> Now in my Perl code I do things very differently compared to DBIx::Class so
> it is hard to just cut and paste what I do.  But when looking at
> DBIx::Class::Storage::DBI code I will try to put what needs to go on in the
> right places (below).  The sth method needs to accept two extra parameters,
> the SQL operation (SELECT/INSERT/UPDATE/DELETE) and an array reference of
> the bind values $bind_ref.  From what I can find in the entire DBIx::Class
> tree, the sth method is only called from within DBIx::Class::Storage::DBI
> inside the _execute method.  So I also show the required code changes within
> the _execute method.
>
> sub _execute {
>    ... existing code ...
>
>    # stringify @bind args operation is moved from just after sth
>    # creation to just before sth creation because now I need to
>    # pass \@bind to sth method
>    @bind = map { ref $_ ? ''.$_ : $_ } @bind; # stringify args
>    my $sth = $self->sth($sql, $op, \@bind);
>
>    ... existing code ...
> }
>
> sub sth {
>    my ($self, $sql, $op, $bind_ref) = @_;
>    if (uc($self->dbh->{Driver}->{Name}) eq 'ORACLE' and uc($op) eq
> 'SELECT') {
>        if ($self->_lobs_in_select_col_list()) {
>            $self->dbh->{LongReadLen} =
> $self->dbh->selectrow_array($self->_get_lrl_sql(), undef, @$bind_ref) || 0;
>        } else {
>            $self->dbh->{LongReadLen} = 0;
>        }
>    }
>    # 3 is the if_active parameter which avoids active sth re-use
>    return $dbh->prepare_cached($sql, {}, 3);
> }
>
> In the _execute method I move the line of code which stringifies the @bind
> array args to just above the line of code which calls the sth method to
> create the sth since I now need to pass \@bind to the sth method.  Inside of
> the sth method I first use the DBI database handle attribute
> $self->dbh->{Driver}->{Name} to check if I have a driver which needs
> LongReadLen attention (like Oracle) and I check if I have a SELECT
> statement.  Then I check if any of the columns to return in the statement
> are of the LONG/LOB type.  If yes, I need to generate the get LongReadLen
> SQL statement and set $self->dbh->{LongReadLen} to the value returned from
> executing the statement.  If the statement returns undef then I don't have
> any data in the LONG/LOB columns so I should set LongReadLen to 0.  If I
> don't have any LONG/LOB columns to return in my SQL statement then
> LongReadLen should also be set to 0.
>
> Now I am not familiar enough yet with all the internals of DBIx::Class and
> all the modules it uses.  So I don't know yet how from within the sth method
> in DBIx::Class::Storage::DBI I can find out if I have any LONG/LOB columns
> (_lobs_in_select_col_list()) and also generate the get LongReadLen SQL
> statement (_get_lrl_sql()).  I just put these dummy methods there to show
> where it needs to be done.  I would prefer not to have to do some regex
> stuff on the $sql statement passed in order to get the individual components
> because I think somewhere in DBIx::Class they were all individual components
> before they became $sql.  Where are these components?  ResultSource or
> ResultSet?  Hmmm...  If I could get access from within the sth method to
> some object which has the individual columns from $sql and the tables they
> come from then I could call the "column_info_for" method and find whether a
> column is LONG/LOB.  And I think generating the get LongReadLen SQL would
> also be straightforward knowing this and from the same or other object the
> FROM, WHERE, GROUP BY, HAVING components which were used to generate $sql.
>
> I hope I am going in the right direction with this,
>
> Leandro Hermida
>
>
>
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>
>