Re: [gnome-db] String escaping question

[Vivien Malerba <vmalerba gmail com>]

2009/1/26 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?

No, obviously PostgreSQL does not like that...
 

> * 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?

I don't know, this is how PostgreSQL does it: the doc mentions groupname and username as being 'parameters', and the examples don't have quotes around the groupname or username... It's as if it considers the username and groupname as SQL identifiers (for which you may add double quotes around if you need to).

I'll mention somewhere in the doc that variables should not be used for places where SQL identifiers are expected (for example table names in SELECT, INSERT, ...) for the time being.
 

Vivien