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

[Vivien Malerba <vmalerba gmail com>]

On Tue, 28 Sep 2004 19:18:02 +0000, Jeronimo Albi
<amenofisvii yahoo com ar> wrote:
> 
> 
> On Mon, 27 Sep 2004 17:43:16 +0200
> Vivien Malerba <vmalerba gmail com> wrote:
> 
> >
> > 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)
> >
>   Shouldn't type and nullok be initialized by each provider ? I only have
> some experience using Firebird/Interbase, and they initialize each parameter
> to ist correct type and null flag, after preparing the statement for execution.

For the :nullok and :isparam, the default value is actually defined by
the libmergeant's library, and there is no reason why it should be
imposed (libmergeant's way is not the only way).

For the :type, it's a bit more complicated. Let me explain how
libmergeant does it: if there is a value before the square brackets
(say 'joe' [...]) and the :type is not mentionned within the square
brackets, then libmergeant tries to determine a good data type
(varchar in that example for postgres). If there is no value provided
(say ## [...]), then specifying the data type between the square
brackets is mandatory.

The problem is that libmergeant has to maintain some provider specific
information, but I think we can't have everything in libgda.

> 
>   I'd really apreciate if someone can explain me how parameters are handled
> in Postgres and MySQL.
> 
> > 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"]
> >
>   Its ok, but it seems to be impractical if you use too many parameters,
> its kinda long, dont you think ?

So far as I've used that, the SQL strings are not much longer than
without parameters and it helps if they are formatted in a nice way
before being displayed (using multiple lines and indentation).

> 
> > 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/*
> >
>   This would save lot of time, great !

It's very simple. If you think it would be better to move it into
libgda (libsql_silple for example), then it's OK, but beware of
function names to avoid a mix with the actual libsql parser (otherwise
there are some very strange results...).

> 
> > 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.
> >
>   Yes it might be a good idea, so if you and Rodrigo agree I'll use libmergeant
> format.
> 
>   Originally I thought about using a parameter format like in PHP, that is, parameter
> name between 2 "braces ?", like this:
>         SELECT name FROM table WHERE name = {param_name}
> but here we only use parameters name.

Curly braces are already used by some SQL code (like for arrays in
Postgres), so it's better to try to avoid SQL conflicts.

Cheers,

Vivien