On Jul 16, 12:51 pm, Jeremy Evans <[EMAIL PROTECTED]> wrote:
> I've done some research on prepared statements, stored procedures, and
> bind variables, and I'm currently considering the following API.
I've given some more thought and have some ideas that I think would
work better and be more consistent across databases. It relies on
keeping a copy of the dataset that creates the prepared statement and
overriding its #literal method, and creating a mapping object that
will transform bound variables (given as an array and/or hash). This
makes sure that the default case (using interpolation) will not
require Sequel to parse an SQL string (which is one of Sequel's
cardinal design ideas). I haven't worked on any code yet, but I'm
pretty sure it will work.
> To set bind variables, use :$1, :$2, etc. for cross database support.
> By default, this will use ? placeholders, since that is most
> common, which means the if :$2 comes up before $:1, you'll have
> issues.
Scratch that. With my new idea, if you use :$2 before :$1, :$2 will
always refer to the second bound argument. And named bound arguments
(e.g. :$name) will be supported on all databases. For the databases
that don't natively support them (all but SQLite), a mapping object
will transform the named argument hash into an array. The databases
that don't supported numbered arguments (all but SQLite and
PostgreSQL, I believe), will use an array with repeated elements if
the same numbered/named argument is used more than once in the query.
There will not be support for unnamed/unnumbered arguments (such as :
$).
> # All databases
> ds = DB[:items].filter(:number=>:$1).filter(:name=>:$2)
> # Databases without number arguments (MySQL, JDBC, Oracle, MSSQL)
> DB[:items].filter(:number=>:$).filter(:name=>:$)
> # SQLite named arguments
> ds = DB[:items].filter(:number=>:$number).filter(:name=>:$name)
With the new idea, all databases will support this:
ds = DB[:items].filter(:number=>:$1).filter(:name=>:$name)
> To use the bind variables without actually preparing a statement
> (by default, this will interpolate, so it works on all databases):
>
> ds.call_select(1, 'Jim')
> ds.call_insert([[:number, :$1], [:name, :$2]], 1, 'Jim')
> ds.call_update([[:number, :number + :$3], \
> [:name,:name.sql_string + :$4]], 1, 'Jim', 2, 'bo')
> ds.call_delete(1, 'Jim')
This won't change much, except that it will allow you to use hashes
instead of arrays of pairs for the argument to call_insert/
call_update, as well as accepting a hash for the named bound
arguments:
ds.call_insert({:number=>:$1, :name=>:$name}, 1, :name=>'Jim')
> To create a prepared statement that you can call later (by default,
> this will just store the SQL for later use):
Instead of storing the SQL string, we'll store the dataset, but the
results are basically similar.
> ps = ds.prepare_select(:select_number_name)
> ds.prepare_insert(:insert_number_name, [[:number, :$1], \
> [:name, :$2]])
> ds.prepare_update(:update_number_name, [[:number, :number + :$3], \
> [:name,:name.sql_string + :$4]])
> ds.prepare_delete(:delete_number_name)
>
> To call a statement previously prepared (by default, will interpolate
> the stored SQL, so it works on all databases):
>
> ps.call(1, 'Jim')
> DB.call(:select_number_name, 1, 'Jim')
> # SQLite named arguments
> ps.call(:number=>1, :name=>'Jim')
> DB.call(:select_number_name, :number=>1, :name=>'Jim')
>
> If the database supports stored procedures and no matching prepared
> statement with that name has been created, the DB.call method could
> be used to call a stored procedure. There won't be default support
> for that, though.
After thinking some more about it, it would probably be better to use
a different method for stored procedures anyway. I'm thinking of
Database#call_procedure.
Comments/questions are appreciated.
Thanks,
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
