Re: [gnome-db] String escaping question

[Murray Cumming <murrayc murrayc com>]

On Mon, 2009-01-26 at 13:52 +0100, Vivien Malerba wrote:
> 
> 
> 2009/1/26 Johannes Schmid <jhs jsschmid de>
>         Hi!
>         
>         I tried to execute the following query on postgresql:
>         
>         ALTER GROUP "glom_developers" ADD USER ##name::gchararray
>         
>         the holder name was defined as "jhs".
>         
>         The result was the following error:
>         Syntax error at »'jhs'«
>         LINE 1:  ALTER GROUP "glom_developer" ADD USER 'jhs'
>         
>         The same query works as
>         ALTER GROUP "glom_developers" ADD USER "jhs" (note the
>         difference
>         between ' and ".
>         
>         Is there anything wrong with the query. Can I specify
>         different
>         behaviour for the holder?
> 
> No, there is no way at the moment. The problem appears because:
> * PostgreSQL does not seem to allow variables in the ALTER GROUP
> statement, so Libgda has to render the variable itself and renders a
> string (gchararray) using single quotes as any SQL string should be
> rendered

Are you saying that we should use ' instead of "" in SQL in Glom? We can
do that, but where is this officially stated?

> * PostgreSQL does not want an SQL string but rather an unquoted string
> which Libgda cannot render.

How can a string be unquoted in SQL? What about spaces?

> Here are the options:
> * create the complete ALTER GROUP statement yourself including the
> user name literal in it

Yeah, let's just do this. They are small chunks of SQL. But, Johannes,
please do file a bug about this for the future. Then you might continue
trying to make use of variables in the other more complicated SQL
statements in Glom.

> * modify Libgda to be able to handle non quoted strings
> 
> The 1st solution is the easiest, but the 2nd is the cleanest.
> 
> Modifying Libgda is not very difficult:
> * create a new type of data in gda-value.[ch], for example
> "GdaRawString" or "GdaUnquotedString"
> * optionnaly map this name to a more user friendly name such as
> "ustring" in gda_g_type_to_string() and gda_g_type_from_string()
> * create a GdaDataHandler object for this new type
> * modify the postgres provider to make it use that data handler when
> necessary, in gda_postgres_provider_get_data_handler()
> 
> All of this is very easy to do.

> 
-- 
murrayc murrayc com
www.murrayc.com
www.openismus.com