RE: [gnome-db] parameters in SQL commands

["Daniel Morgan" <danmorg sc rr com>]

The System.Data.SqlClient (MS SQL Server 7/2000) .NET Data Provider uses the
@ (at) symbol to denote a named parameter.

SELECT NAME
FROM ADDRESS
WHERE POSTAL_CODE = @postal_code

The System.Data.OleDb (ADO/OLE-DB) .NET Data Provider uses the ? (question
mark) symbol to
denote a parameter.

Maybe, we should allow this parameter marker character(s) to be changed via
some libgda API. However, it should have a default parameter marker.  What
would the default be?  Will every provider have the same default?

Here are the four parameter markers:
1.  @ (at) named parameter
2.  : (colon) named parameter
3.  [] (square bracketers) delimited named parameter
4.  ? (question mark) positioned parameter

Are there anymore?

-----Original Message-----
From: gnome-db-list-admin gnome org
[mailto:gnome-db-list-admin gnome org]On Behalf Of Gonzalo Paniagua
Javier
Sent: Tuesday, October 01, 2002 3:41 PM
To: GDA
Subject: Re: [gnome-db] parameters in SQL commands


* [ 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/

_______________________________________________
gnome-db-list mailing list
gnome-db-list gnome org
http://mail.gnome.org/mailman/listinfo/gnome-db-list