[DB-SIG] paramstyles, again

[Carsten Haese]

On Wed, 2007-05-23 at 12:36 +0200, M.-A. Lemburg wrote:
> Just an aside:
> 
> Note that the named style allows binding the same parameter
> more than once.

Ditto for numeric style.

> This poses a few problems for interfaces which
> rely on the database telling the interface how to best bind
> a parameter, since it may well be that case that a parameter
> needs to be bound as e.g. integer in one place (e.g. as index)
> and float in another (e.g. if used in a formula).
> 
> For numbers, it's fairly obvious what to do (create multiple
> bindings for the object), but it's not for objects that don't
> easily allow retrieving the same value twice, such as
> iterators or files.
>
> I don't know how interfaces that do support named style deal
> with this problem.

InformixDB supports binding by name, and in the real world this is not a
problem at all. The parameter values must be supplied in a mapping, and
the input binding loop simply calls __getitem__ on that mapping,
possibly requesting the same key twice. If the same key is requested
twice, you'd have to work hard to make that *not* return the same object
twice. You'd have to pass in a dictionary-like object whose __getitem__
method has deliberate side-effects, and if you do that, you deserve to
be punished.

In all cases I've seen, the parameter mapping is a plain dictionary
which is either built "by hand" or uses locals() to emulate host
variables:

start_date = datetime(2007,1,1)
end_date = datetime.date.today()
cur.execute("""
select * from orders where order_date between :start_date and :end_date
""", locals() )

Even if one of the entries in the dictionary is the result of calling
it.next() or f.readline(), the dictionary stores the result, and that
result can be retrieved repeatedly without any problems.

[Footnote: The locals() idiom for emulating host variables is what
convinced me to implement named binding in the first place. For
programmers coming to Python from a "4GL" or ESQL/C, having something
similar to host variables is a neat feature.]

Best regards,

-- 
Carsten Haese
http://informixdb.sourceforge.net