On Jul 16, 8:13 am, Jeremy Evans <[EMAIL PROTECTED]> wrote:
> Before I get started on this work, I need to read up on how different
> databases handle things so I don't tie the design to any one database.
I've done some research on prepared statements, stored procedures, and
bind variables, and I'm currently considering the following API.
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.
# 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)
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')
To create a prepared statement that you can call later (by default,
this will just store the SQL for later use):
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.
To drop a prepared statement:
ps.drop
DB.prepared(:select_number_name).drop
Below is some database specific information related to implementing
the above inferfaces with real database support. I plan to support
PostgreSQL, JDBC, SQLite, and MySQL, probably in that order.
Hopefully the interface is flexible enough to accommodate other
databases. I looked briefly at Oracle and MSSQL and it should work
for them.
As always, comments and ideas are appreciated.
Thanks,
Jeremy
PostgreSQL:
SQL Bind variables: $1, $2, ...
Ruby Bind variables: :$1, :$2
Interpolation: not required
Prepared statements: supported
Stored procedures: not supported
ds.prepare_select(:select_number_name):
conn.prepare('select_number_name', 'SELECT * FROM items WHERE ((x =
$1) AND (y = $2)')
ds.call_select(1, 'Jim'):
conn.exec('SELECT * FROM items WHERE ((x = $1) AND (y = $2)', [1,
'Jim'])
x.call(1, 'Jim'):
conn.exec_prepared('find_x_y', [1, 'Jim'])
MySQL:
SQL Bind variables: ?, ?, ...
Ruby Bind variables: :$, :$, ... OR :$1, :$2, ... (but order
matters!!)
Interpolation: required
Prepared statements: supported
Stored procedures: supported
ds.prepare_select(:select_number_name):
db.execute('PREPARE select_number_name FROM SELECT * FROM items
WHERE ((x = ?) AND (y = ?)')
ds.call_select(1, 'Jim'):
db.execute("PREPARE select_number_name FROM SELECT * FROM items
WHERE ((x = 1) AND (y = 'Jim')")
x.call(1, 'Jim'):
db.execute("EXECUTE select_number_name USING 1, 'Jim'")
DB.call(:stored_procedure, 1, 'Jim'):
db.execute("CALL stored_procedure(1, 'Jim')")
SQLite:
SQL Bind variables: ?, ?, ... OR ?1, ?2, ... OR :a, :b, ...
Ruby Bind variables: :$, :$, ... OR :$1, :$2, ... OR :$a, :$b
Interpolation: not required
Prepared statements: supported
Stored procedures: not supported
ds.prepare_select(:select_number_name):
stmt = db.prepare('SELECT * FROM items WHERE ((x = ?1) AND (y = ?
2)')
ds.call_select(1, 'Jim'):
db.execute('SELECT * FROM items WHERE ((x = ?1) AND (y = ?2)', 1,
'Jim')
x.call(1, 'Jim'):
stmt.execute(1, 'Jim')
JDBC:
SQL Bind variables: ?, ?, ...
Ruby Bind variables: :$, :$, ... OR :$1, :$2, ... (but order
matters!!)
Interpolation: not required
Prepared statements: supported
Stored procedures: supported
# For postgres, org.postgresql.PGStatement.setPrepareThreshold(2)
ds.prepare_select(:select_number_name, :type=>:select):
pstmt = conn.prepareStatement('SELECT * FROM items WHERE ((x = ?)
AND (y = ?)')
ds.call_select(1, 'Jim', :type=>:select):
pstmt = ds.prepare_select(nil, :type=>:select)
pstmt.call(1, 'Jim')
pstmt.close
x.call(1, 'Jim'):
pstmt.setInt(1, 1)
pstmt.setString(2, 'Jim')
case opts[:type]
when :select
cstmt.executeQuery
when :delete, :insert, :update
cstmt.executeUpdate
else
cstmt.execute
end
DB.call(:stored_procedure, 1, 'Jim', :type=>:select):
cstmt = conn.prepareCall("{call stored_procedure(?, ?)}")
cstmt.setInt(1, 1)
cstmt.setString(2, 'Jim')
case opts[:type]
when :select
cstmt.executeQuery
when :delete, :insert, :update
cstmt.executeUpdate
else
cstmt.execute
end
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
