Possible "bug" found in gnatcoll-sql_impl.adb

["Thomas Løcke" <tl@ada-dk.org>]

On 2011-02-23 12:58, Thomas L�cke wrote:
> Hey all,
>
> When I insert data into a PostgreSQL database using parameterized
> queries, I end up with more characters than I've bargained for.
>
> If I do this:
>
> Conn.Execute ("INSERT INTO tbl (name) VALUES ('Peter')");
>
> I end up with Peter in the database. Plain Peter. 5 characters.
>
> If I instead do this:
>
> N : aliased constant String := "Peter";
> P : constant Prepared_Statement := Prepare
> ("INSERT INTO tbl (name) VALUES ($1)");
>
> Conn.Execute (Stmt => P,
> Params => (1 => +N'Access));
>
>
> I end up with 'Peter' in the database. 7 characters. Two single
> quotes added by GNATcoll.
>
> I've tried doing a manual parameterized insert using psql:
>
> PREPARE foo (text) AS INSERT INTO tbl VALUES($1);
> EXECUTE foo ('Peter');
>
> I've also fired up the phpPgAdmin web-tool and tried using that.
>
> And the result is, for both, as expected; plain old 5 character Peter.
> So PostgreSQL is apparently not at fault here.
>
> Am I doing something wrong with GNATcoll? I'd rather not have those
> extra single quotes added to all my string data. :o)


The GNATCOLL.SQL_Impl.String_To_SQL function has this in it:

    if Num_Of_Apostrophes = 0
      and then Num_Of_Backslashes = 0
    then
       return "'" & Value & "'";
    end if;

This is where the extra single quotes are added, and for SQL WHERE
string criterias this is a good thing.

It simply means that you can do

    Billy : constant String := "Billy";
    Q : constant SQL_Query := SQL_Select
     (Fields   => Tmp.Id & Tmp.Name,
      From     => Tmp,
      Where    => Tmp.Name = Billy);

and not have to worry about adding the single quotes manually to your
string.

But for assignment, using INSERT and UPDATE, it's painfully annoying
that you end up with those two extra single quotes added to all your
string data.

Replacing (in GNATCOLL.SQL_Impl.String_To_SQL)

    return "'" $ Value $ "'";

with

    return Value;

solves the INSERT/UPDATE assignment problem - we then get clean string
data added to the database, without excessive single quotes, but then
all the WHERE criterias obviously fails.

Perhaps there's a hidden function somewhere in GNATColl that alleviates
this annoying issue, but if that is the case, then I haven't been able
to find it.

Am I really the only one experiencing this? Or do GNATColl users in
general not care about those extra single quotes added to their string
data?

I'd very much like to help fix this, but GNATColl is a rather large and
intimidating beast, so I'd rather not dig in too deep if it turns out
I've just overlooked some brilliant piece of design that fixes all this.

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke