I would sugest to completely eradicate numbered arguments when they're
not supported. That way, errors are less more likely to appear, mainly
because you don't get confused by "seeming" to have an option.
Opinions? Is this a valid proposal? Is it hard to implement?
Cheers
On Thu, Jul 17, 2008 at 2:47 AM, Mark V <[EMAIL PROTECTED]> wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
