bind options

[Sergiy Zuban]

Hi Laurent,

we are working with PostgreSQL and trying to deal with PostgreSQL's specific types. In terms of plain DBI this can be done by passing 3rd parameter to $sth->bind_param and then calling $sth->execute without parameters:

$sth->bind_param($param_num, $bind_value, { pg_type => PG_BYTEA });

Currently we found the way (via placeholder & $statement->bind) how to pass extra options for SELECT, but it seems like there is no way to do that for INSERT/UPDATE.

I see the following in the DBIx::DataModel::Source::Table::_rawInsert

my ($sql, @bind) = $sqla->insert(....);
my $sth = $schema->dbh->prepare($sql);
$sqla->bind_params($sth, @bind); <-- no ways to pass 3rd parameter here

SQL::Abstract itself has the option bindtype => 'columns' which they suggest to use to deal with Oracle's binary type (see 

http://search.cpan.org/~frew/SQL-Abstract-1.73/lib/SQL/Abstract.pm#bindtype for more details) to allow binding columns by name.

Sure this may have a negative impact on performance, but it would be nice to let developers to use both types of binding and the way to somehow pass 3rd parameter to $sth->bind_params (SQL::Abstract::More::bind_params needs to support 3rd parameter as well).

From user point it would be perfect to be able to define specific options at type or at least table level. For example:

 $class->metadm->define_type(
        name           => 'Binary',
        bind_options => { pg_type => PG_BYTEA },
 );

   

What do think about this idea?

Thank you.

-- 

Sergiy Zuban

[laurent dami]

Le 08.03.2013 19:42, Sergiy Zuban a écrit :

[..]

Sure this may have a negative impact on performance, but it would be nice to let developers to use both types of binding and the way to somehow pass 3rd parameter to $sth->bind_params (SQL::Abstract::More::bind_params needs to support 3rd parameter as well).

From user point it would be perfect to be able to define specific options at type or at least table level. For example:

 $class->metadm->define_type(
        name           => 'Binary',
        bind_options => { pg_type => PG_BYTEA },
 );

   

What do think about this idea?

Thank you.

Hi Sergiy,

Thanks for this detailed study. You have a very good point, and I realize that indeed the treatment of select/insert/update with respect to bind options is both inconsistent and incomplete. I never worked with PostgreSQL ; I wanted to , but after heavy debate my company chose Oracle :-(

Your suggestion looks good, but it's not trivial to implement. Several details need to be sorted out, since it would imply changes in SQL::Abstract::More, in DBIDM::Table.pm, in DBIDM::ConnectedSource, and possibly other places. So I'm afraid it will require some time before before a new release with a coherent and complete implementation of such features can be published.

Of course, you are welcome to send pull requests to https://github.com/damil/DBIx-DataModel and https://github.com/damil/SQL-Abstract-More.Or before that, we can enter into a more detailed discussion about design issues if you already studied deeper aspects of that proposal.

Thanks again, and I hope we can come up with something practical without waiting too long.

Cheers, Laurent D.

[Sergiy Zuban]

Laurent,

Thank you for prompt reply and openness for cooperation.

I'll try to dive more deeply in your code and get back to you with a roadmap of required changes.

-- 

Sergiy Zuban

[Ross Attrill]

Hi Sergiy and Laurent,

I feel that this would be a valuable change in due course.

I have a similar need with MSSQL database and DBD::ODBC where I must set the bind_type to SQL_VARCHAR for CLOB columns (nvarchar(max)).   My workaround has been to use raw DBI in this case.

I would be happy to help with documentation and testing later on.

Regards,

Ross Attrill.

--
 
---
You received this message because you are subscribed to the Google Groups "dbix-datamodel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbix-datamode...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[laurent dami]

Le 08.03.2013 19:42, Sergiy Zuban a écrit :

Hi Laurent,

we are working with PostgreSQL and trying to deal with PostgreSQL's specific types. In terms of plain DBI this can be done by passing 3rd parameter to $sth->bind_param and then calling $sth->execute without parameters:

$sth->bind_param($param_num, $bind_value, { pg_type => PG_BYTEA });

Currently we found the way (via placeholder & $statement->bind) how to pass extra options for SELECT, but it seems like there is no way to do that for INSERT/UPDATE.

Hi Sergiy,

I started studying this issue. While working on it, I came to a bug in DBIx:DataModel : $statement->bind($num, $val, $type_attr) used the wrong index when calling $sth->bind_param(...); it used a 0-based index while DBI expects a 1-based index.

This wil be fixed in next release, but I just wondered : if you used this feature, how can it be that you did not hit the bug ?

Cheers, Laurent D.

[laurent dami]

Le 08.03.2013 19:42, Sergiy Zuban a écrit :

Hi Laurent,

we are working with PostgreSQL and trying to deal with PostgreSQL's specific types.

Hi Sergiy,

I just uploaded v2.34 to CPAN, which implements support for specific types.
When you need to specify a type, replace the value by an arrayref of shape
[$original_value, \%datatype_spec]; this works for select(), insert() and update().

You can also use define_type() in your schema to automate the process. There is an example in t/v2_bind_param.t; here is an excerpt :

#--------------------------

DBIx::DataModel->Schema('HR') # Human Resources
->Table(Employee   => T_Employee   => qw/emp_id/);

use constant ORA_XMLTYPE => 108;
HR->Type(ORA_XML =>
  to_DB   => sub {$_[0] = [$_[0], { ora_type => ORA_XMLTYPE }];},
);

HR->table('Employee')->metadm->define_column_type(ORA_XML => qw/xml1 xml2/);

#--------------------------


PS : You will need to update SQL::Abstract::More as well.

I hope this answers your need. Let me know of any problems.

Cheers, Laurent Dami