On Wed, Jul 16, 2008 at 7:51 PM, Jeremy Evans <[EMAIL PROTECTED]> wrote:
>
> 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')
>
I know stuck records are annoying :)
Please consider using call_* and prepare_*, where * is one of: create,
read, update, delete.
Cheers
> 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
-~----------~----~----~----~------~----~------~--~---
