Re: [gnome-db] parameters in SQL commands

[Gonzalo Paniagua Javier <gonzalo gnome-db org>]

* [ Fernando Martins <fmartins hetnet nl>
* Tue, 01 Oct 2002 21:18:06 +0200 ]
> Just a clarification: this "idea" is how MS Access works. It is quite useful
> (from the developers point of view) and it's proven technology.

The :name idea is used, at least, in DB2 and Oracle.

> 
> Parametrized queries on the client have one goal: supporting interface
> development by providing integration with the environment (usually forms).

I'm not talking about stored procedures and such. I'm talking about
prepared statements. Some databases allows you to 'register' an
statement that contains placeholders for parameters. Then you run the
prepared statement using actual values for the parameters. This improves
performance because the DB backend does not have to bind the path for
each statement every time you run it. It only does it 1 time, when you
'register' the prepared statement.

> 
> Of course, if those statements are stored on the server (as Gonzalo said,
> for performance and also data consistency (business rules) reasons) then the
> syntax is defined by the DBMS itself. The role of the providers is then to
> provide a facility for the client to define those parameter values, not to
> parse the SQL statement and replace parameter names by values.

For providers that supports preparing statements, they have to translate
our representation of the paramenters (ie, :name or [name] or whatever
we choose) to the representation that their backend uses, then
'register' the prepared statement and reuse it on every query.

For the rest, we have to follow the slow path: the provider must
substitute the parameter placeholders by the parameter values to create
each statement for every query.

-Gonzalo

-- 
Gonzalo Paniagua Javier <gonzalo gnome-db org>
http://www.gnome-db.org/~gonzalo/