[DB-SIG] PROPOSAL: Portable Argment Format

[Jim Fulton]

M.-A. Lemburg wrote:
> 
> 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.

No, because the MySQL interface would convert values to strings 
as part of the conversion process.

> But then: the interface could implement
> the type checks and do the conversions afterwards...

Right.
 
> >
> > 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)

Fine.
 
> since this is what many DBs can handle.
> 
> >     $ -- Money (???)
> 
> Hmm, what would that look like ?

No idea.  That's why I added question marks.  OK, Punt.
 
> >     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.

Perform some reasonable conversion.
 
> · Which Python types are expected for each type character ? E.g. will
>   passing strings to ':r' be ok ?

:r is defined to come from a string above.  The proposal should 
spell this out.

>   Will more than one type be allowed per
>   type char (with automatic conversion if necessary) ?

Yes.  This should be spelled out.
 
> > [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 :).

How?  Each implementation has to be involved in implementing this.
For example, portable aguments have to be converted to platform-
specific arguments, and platform specific conversions have to be 
applied.  Utilities can be provided to make the implementation simpler, 
but implementation-specific code is needed.
 
> 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.]

Right.  With type information in the sql statement, I can do much
of the binding work up front and make the actual callinr process 
simpler and faster.

Jim

--
Jim Fulton           mailto:jim@digicool.com
Technical Director   (888) 344-4332              Python Powered!
Digital Creations    http://www.digicool.com     http://www.python.org

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.