[DB-SIG] PROPOSAL: Portable Argment Format

[M.-A. Lemburg]

Jim Fulton wrote:
> 
> I'd like to lobby for a portable argument format for the DBI
> interface. While this *does* require parsing SQL, this is
> not really all that hard and I think the benefits are well
> worth the effort. I volunteer to provide a utility to assist
> with this.

Actually, it only requires scanning SQL. Parsing SQL would
be overkill ;)

> Here's what I think the format needs to do:
> 
>   - Not interfere with SQL.  That is, it must be unabiguous
>     to find parameters in SQL.
> 
>   - Support optional argument names, which may be given
>     a positional interpretation.
> 
>   - Capture type information, to make type explicit.

There's a problem with this one: the database may want to
have different types than the one you explicitly state in the
SQL statement, e.g. when porting from e.g. Solid to MySQL
you'll find that MySQL will want all parameters to be strings,
even numbers, so statements like

	INSERT INTO MyTable VALUE (:t, :s, :i)

would fail on MySQL. But then: the interface could implement
the type checks and do the conversions afterwards...

> 
> Maybe this is all that's needed.
> 
> I propose the following format:
> 
>      :(name)code
> 
> where : signals a parameter and code is a type code.
> 
> Valid type codes are:
> 
>     c, b, B, h, H, i, I, l, L, f, d, and s --
>        as defined in the struct module
> 
>     t -- Date/Time

We ought to define three chars for date/time values:

	D - date only
	T - daytime only
	S - timestamp (date + daytime)

since this is what many DBs can handle.

>     $ -- Money (???)

Hmm, what would that look like ?

>     r -- Binary data (raw/blob) gotten from a string
>     others...???

Questions:

· What will databases without some of these types get ? E.g. the money
  type is not defined in ODBC.

· Which Python types are expected for each type character ? E.g. will
  passing strings to ':r' be ok ? Will more than one type be allowed per
  type char (with automatic conversion if necessary) ?

> [Examples]

Note that you can provide the whole functionality by coding
a Python function (or class) on top of cursor.execute, so no change to
the API spec is necessary. Moreover, every existing implementation
will be able to use it without modification (which is a Good Thing :).

You could even have a perpare constructor that returns a (Python)
instance with a function call interface. [We don't have a
performance issues here, since the function call overhead
is negligable w/r to the time it takes for the database to finish.]

-- 
Marc-Andre Lemburg                               Y2000: 555 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------