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

[Jeronimo Albi <amenofisvii yahoo com ar>]

On Wed, 29 Sep 2004 08:58:33 +0200
Vivien Malerba <vmalerba gmail com> wrote:

> 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

  I'll explain what i undestood about your code in libgda/libsql:
    1. I should use sql_parse() to create an #sql_statement.
    2. Inside #sql_statement record i have a field named "statement" to
       get the structures #sql_select_statement, #sql_insert_statement,
       and so on, depending on the statement type.
    3. Inside some of them i have a #GList of #sql_field items, and
       inside each item a #param_spec containing parameters info, that
       is what i'm looking for :-).
    4. sql_stringify() shuld return a string containing the parsed
       statement

  How should i replace parameters with a token to be interpreted by 
a database engine as parameter. For example in Firebird:

    a. Original statement:
           SELECT name FROM table WHERE name=## [:name="User name"]
    b. Parsed statement shuld be:
           SELECT name FROM table WHERE name=?

where "?" is interpreted as parameter.
		
  Does #sql_update_statement and #sql_delete_statement structures 
support parameters ?

  Regards.

-- 
Jeronimo Albi
Linux User #345522

Jabber ID: gtk_jeronimo jabber org
ICQ UIN:   136196282