Re: [gnome-db] Studying the posibility to add parametrized statements

[Vivien Malerba <vmalerba gmail com>]

On Mon, 27 Sep 2004 11:12:26 +0000, Jeronimo Albi
<amenofisvii yahoo com ar> wrote:
> I've been studying the posibility to add parametrized statements to
> libgda, but the modifications needed are important, so I'd need some
> advise :-).
> 
> These are the steps i thought:
> 
> 1. Set gda_command text with the parametrized statement.
> 2. Somehow, prepare the statement to let the provider get parameters
>     info (like parameters data type), and construct a list with
>     "parameter objects".
> 3. Set parameter values (using some gda_command method).
> 4. Finally execute gda_command.
> 
> This method would need:
> 
> 1. gda_command_prepare (), gda_command_get_parameter (),
>     gda_command_parameter_count (), gda_command_get_parameter_list (),
>     some execute method form parametrized statements and so no.
> 2. A new class named sql_parameter, command_parameter, cmd_parameter,
>     or any other name :-), to manage parameters.
> 3. A set of functions to let providers process parametrized statements.
> 4. To add GDA_CONNECTION_FEATURE_PARAMETRIZED_SQL or similar.
> 
> Is this right ?
> 
> Seems like a lot of work :-D.
> I would really apreciate any comment.
> Thanks
> 

I've already done this for Mergeant/libmergeant.

Basically, any SQL statement can have parameters identified by a value
of ## and parameter specifications between square brackets. For
example one can write
SELECT name FROM table WHERE name='joe' [...]
or 
SELECT name FROM table WHERE name=## [...]
the above statement require one parameter for the "name=" condition.
In the first statament, the default value for the parameter is 'joe'
and in the second example, there is no default value for the
parameter.

The para specifications between the square brackets are:
:name="my para name"
:descr="my para description"
:type="the DBMS type"
:nullok="TRUE"
:isparam="FALSE" (default=TRUE)

so the above example can be:
SELECT name FROM table WHERE name='joe' [:name="User name"
:descr="Enter the user name to look for" :type="varchar"]

The SQL parser in libgda (libsql/*) already supports this syntax.

Libmergeant also has a small parser to isolate the parameters from any
text (usefull when the SQL text can't be parsed by libgda's parser for
various reasons); it's in libmergeant/parser/*

I think it would be a good idea to start libgda's implementation of
parameters with the same syntax and the same libgda's parser.

Cheers,

Vivien