Re: [gnome-db] parameterized queries

[Neil Zanella <nzanella gmail com>]

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.
2. Encode your query as XML.
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.

> 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.

> 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...

Regards,

Neil