[plt-scheme] bzlib/dbi error trying to insert a null value

Okay - after some seriously thinking about the issue of last-insert-id &
side effects, below are my ideas and would love some feedbacks:

*Side Effect Data Structure
*
There are 3 options here:

   1. return the underlying side-effect (and re-export the necessary
   bindings) for each of the drivers
   2. provide my own unified side-effect object and convert the underling
   side-effect object onto the first one
   3. convert the side effect into a record set structure (so you do not
   need to test whether it's a side effect struct)

The first option's advantage is that if you already have extensive side
effect testing code you do not have to change much.  The drawback is that
you'll have more headache/effort to switch between the databases.

The second option's advantage is the reverse of the first one - you'll break
current code, but you reduce one effort to switch between databases.

The third option unifies the split between record set & side effects.  Its
advantage is that it removes your need to test the type of the return, but
the disadvantage is the access of the result is a bit harder.

I can of course provide all 3 approaches, but would be nice to reduce it
down.  Any thoughts on which one is the best?  My personal thought is #2 or
#3 (I love the unification aspect of #3 but know this is not generally how
it is done).

*Last Inserted ID
*
There are some general issues with last-inserted-id

   1. it doesn't work for multiple record inserts at the database level
   1. all databases only return a single value even if you insert multiple
      records into a table at once
      2. mysql returns the id for the first record inserted in such case
      (this feels incorrect)
      3. sqlite returns the id for the last record inserted in such case
      2. postgresql is difficult requires you to know the name of the
   particular sequence object to get the value

Although it does not always work, the fact that single record insert is the
majority of the usage makes it useful, and in that case, I think the design
employed by jaz/mysql is the correct one - i.e. *return the value as part of
the side effect*, regardless which particular options chosen above.

Both jaz/mysql & jaymccarthy/sqlite provides the api to allow this, but not
schematics/spgsql.  This is for the reason stated above that you'll need to
know (or derive the sequence) the sequence name in order to determine the
value, and that this needs to be done with a separate query (which can slow
things down if you do not need the id).

My question here: should I provide the last-inserted-id for spgsql given the
constraints?   Or just jazmysql & jsqlite?

I plan on making the changes by next week - so any feedback prior to that
would be welcome.

Thanks,
yc

On Thu, Oct 1, 2009 at 5:47 PM, YC <yinso.chen at gmail.com> wrote:

>
> On Thu, Oct 1, 2009 at 5:36 PM, Jon Zeppieri <zeppieri at gmail.com> wrote:
>
>> I'll have to think about how to approach this since each database returns
>> different side effects.  If anyone have thoughts into this matter please let
>> me know - I would love to hear it.
>>
>>>
>>> Both Perl's DBI and Java's JDBC have DB-neutral mechanisms for this, but
>> they're very different.
>>
>> Perl DBI: http://search.cpan.org/~timb/DBI/DBI.pm#last_insert_id<http://search.cpan.org/%7Etimb/DBI/DBI.pm#last_insert_id>
>> JDBC: http://www.ibm.com/developerworks/java/library/j-jdbcnew/#keys
>>
>
> Thanks Jon for the links - I'll take a look at them.
>
> yc
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20091104/9d0d35e8/attachment.html>