Re: [gnome-db] parameterized queries

[Vivien Malerba <vmalerba gmail com>]

On Tue, 9 Nov 2004 19:06:36 -0700, Neil Zanella <nzanella gmail com> wrote:
> On Tue, 9 Nov 2004 09:12:51 +0100, Vivien Malerba <vmalerba gmail com> wrote:
> 
> 
> > On Mon, 8 Nov 2004 12:37:34 -0700, Neil Zanella <nzanella gmail com> wrote:
> >
> >
> > > Hello,
> > >
> > > I need to build 'printf style' or similar parameterized queries. My
> > > understanding is that
> > > I can do this with:
> > >
> > > gint        gda_connection_execute_non_query
> > >                                             (GdaConnection *cnc,
> > >                                              GdaCommand *cmd,
> > >                                              GdaParameterList *params);
> > >
> > > using the GdaParameterList (instead of setting it to null, maybe I can
> > > place a list
> > > of intst, gchar *, etc... or whatever).
> > >
> > > Can someone please be so kind to give me an example? (is this really what those
> > > parameters are for or am I imagining?).
> > >
> >
> > If the queries are DML queries, you can use libmergeant (soon this
> > will be into libgnomedb) like:
> >
> > MgDict *the_dictionnary;
> > MgQuery *query;
> > MgContext *context;
> > MgParameter *param;
> > GdaValue *myval;
> > gchar *str;
> >
> > the_dictionnary = mg_dict_new ();
> > query = mg_query_new_from_sql (the_dictionnary, "SELECT * FROM table
> > where id=##[:type="int4" :name="myparam"]");
> 
> Not bad but there is something I think could be improved here:
> 
> 1. Encode your database schema as XML.

This is done.

> 2. Encode your query as XML.

This is almost done; it needs some documentation, though.

> 3. Then, you can use a simpler syntax: where id = $myparam.
> 
> How?
> 
> Cause you look up id in the XML query, you find it is coming from table Foo,
> so you look up the XML in table foo, to find out that it's of type int4, and can
> thus figure out the type of myparam. Right? And just use hash function
> lookups to speed things up.

What if the parameter is for something like "... WHERE my_defined_func
($myparam)...", how can you guess the data type? You could use the
functions into the data dictionnary, and then it's ok, but what if the
parameter is used at several places in the query where some implicit
casts must be made? I think to avoid problems, then the data type must
be specified (of course we could add some "guesses" for simple cases
like "id = $myparam" to make it easier for the user, so the ##[...]
syntax can't be avoided.

This syntax also allows you to set a default value for the parameter,
such as "..=1234[:name="myparam"]", the type is automatically found in
this case.

> 
> 
> 
> > context = mg_entity_get_exec_context (MG_ENTITY (query));
> > param = context->parameters->data; /* first and only parameter */
> > myval = gda_value_new_integer (1234);
> > mg_parameter_set_value (param, myval);
> > gda_value_free (myval);
> > str = mg_renderer_render_as_sql (MG_RENDERER (query), context, 0, NULL);
> > /* use str */
> > g_free (str);
> > g_object_unref (context);
> > g_object_unref (query);
> > g_object_unref (the_dictionnary);
> 
> Cool, but there seem to be lots of fucnction calls and stuff not clear
> to me right
> now (renderer/dictionary/etc...). I'll have to check the API.

Basically the dictionnary (MgDict) is an in-memory representation of
all the database structures, data types, etc and queries.

The MgEntity is an interface for anything that looks like a tabular,
to be able to get the columns, etc in a consistent way, and also the
execution parameters.

The MgRenderer is an interface to "render" an object into SQL, or into
other things like XML queries, when implemented.

> 
> > Check the docs for more options and explanations about the different objects.
> 
> Thanks, I'll have a look.
> 
> Anyways, I'd be relly happy if you could get that GDA parameter stuff to work:
> the libgda parameters don't work since 2002 as some google search reveals,
> and the libmg stuff is going to be merged with libgda so will be gone, and
> doesn't seem to do the automatic type lookup I describe, so I think I'll
> have to code yet another set of functions to do what I want myself.
> I don't know, maybe I'm coming from a different point of view.
> Please consider the idea of having automatic type lookup for
> variables and the possibilities associated with allowing apps
> to read XML encoded schemas at runtime and define
> variables that reference SQL fields within XML files
> cause apps can then really build on these ideas.
> 
> GdaVariable var = the_one_I_should_use_in_table_for_field("TableX", "fieldFoo");
> 
> Imagine how simple: then I can change my DB database types without changing the
> application code if the application code is designed properly...
> 

If I understand what you want to do in your application is:
-> load an XML file which ayou have written
-> use a query (with or without parameters) defined in that file to
make some queries to the database to which you are connected
-> AND/OR use grids, forms, etc for the same purpose

you can already do _all_ of this with libmergeant (I do it at work),
this is why I have written libmergeant for. I'm now merging all that
work into libgnomedb (and probably libgda will also be modified for
coherence later).

Regards,

Vivien