Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

If I do this:

    declare
       --  ... stuff
       Cur : Forward_Cursor;
       Q : constant SQL_Query := SQL_Select
         (Fields   => Tmp.Id & Tmp.Name,
          From     => Tmp,
          Where    => Tmp.Name = Text_Param (1));

       P : constant Prepared_Statement := Prepare
         (Query       => Q,
          On_Server   => True);
    begin
       Cur.Fetch (Connection => Connection,
                  Stmt       => P,
                  Params     => (1 => +Some_Name'Access));
       while Cur.Has_Row loop
          Put_Line ("id=" & Cur.Integer_Value (0)'Img);
          Put_Line ("name=" & Cur.Value (1));
          Cur.Next;
       end loop;
       Put_Line (Cur.Processed_Rows'Img);
    end;

Then this exception is raised:

    GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR

and I get no output at all.

But if I instead do this

    declare
       --  ... stuff
       Cur : Forward_Cursor;
       Q : constant SQL_Query := SQL_Select
         (Fields   => Tmp.Id & Tmp.Name,
          From     => Tmp,
          Where    => Tmp.Name = Some_Name);
    begin
       Cur.Fetch (Connection => Connection,
                  Query      => Q);
       while Cur.Has_Row loop
          Put_Line ("id=" & Cur.Integer_Value (0)'Img);
          Put_Line ("name=" & Cur.Value (1));
          Cur.Next;
       end loop;
       Put_Line (Cur.Processed_Rows'Img);
    end;

Then I get some nice output, and everything works.

But I don't want to use the latter solution. I want to use prepared and
parameterized queries, but for some odd reason it wont work.

If I get rid of the Cur.Integer_Value (0) and Cur.Value (1) calls in
the while loop from the prepared example, then I also get rid of the
exception, so it appears to be some sort of issue with the *Value
functions and prepared queries.

The exception is raised in gnatcoll-sql-postgres-gnade.adb:1122.

Oh, and both queries do fetch the same amount of data - I check that
with the Processed_Rows function after the loop. It's just that the
prepared query wont allow me to "get to" the data, unless I leave out
the *Value calls.

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Fri, 25 Feb 2011 16:45:32 +0100, Thomas L�cke wrote:

> If I do this:
[...]

I don't understand your code, in particular where the statement prepared is
actually executed? Where the cursor gets closed? DBMS does not appreciate
cursors left unclosed. Does GNATColl maintain cursor states internally? Why
fetch is not called within a loop? You prepare the statement, but do not
bind parameters, Ludovic mentioned this. I can only stress that you will
never get it working with literal values. I've been there. It isn't worth
to try.

... and why don't you use GNADE ODBC?

[ At least ODBC has tracing mode, which sometimes with some drivers indeed
traces. So that you could see the actual statement. ]

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-25 17:05, Dmitry A. Kazakov wrote:
> I don't understand your code, in particular where the statement prepared is
> actually executed?


    Cur.Fetch (Connection => Connection,
               Stmt       => P,
               Params     => (1 => +Some_Name'Access));


> Where the cursor gets closed? DBMS does not appreciate
> cursors left unclosed. Does GNATColl maintain cursor states internally?


Yes.


> Why
> fetch is not called within a loop?


    while Cur.Has_Row loop
       Put_Line ("id=" & Cur.Integer_Value (0)'Img);
       Put_Line ("name=" & Cur.Value (1));
       Cur.Next;
    end loop;


This loop executes as long as there are more rows available from the
Cur.Fetch call.


> You prepare the statement, but do not
> bind parameters, Ludovic mentioned this.

First we declare the query:

    Q : constant SQL_Query := SQL_Select
         (Fields   => Tmp.Id & Tmp.Name,
          From     => Tmp,
          Where    => Tmp.Name = Text_Param (1));

Where "Text_Param (1)" setup the parameter and then:

    Cur.Fetch (Connection => Connection,
               Stmt       => P,
               Params     => (1 => +Some_Name'Access));

The parameter is bound in the "Params => (1 => +Some_Name'Access));"
line.

The actual content of "Some_Name" doesn't matter, but in this case it is
a string. The "+" function takes an Access to string and return an
SQL_Parameter.


> I can only stress that you will
> never get it working with literal values. I've been there. It isn't worth
> to try.


I don't want it to work with literal values. I want it to work with
prepared and parameterized queries. I simple show an example with
literal values because that actually works.  :o)

The problem is that GNATColl dumps an exception when I try to use
prepared and parameterized queries.


> ... and why don't you use GNADE ODBC?


I like GNATColl's interface.


>
> [ At least ODBC has tracing mode, which sometimes with some drivers indeed
> traces. So that you could see the actual statement. ]
>


GNATColl has that too. There's nothing wrong with the actual statement.
The problem occurs when I try to extract the data in the loop using
these two calls:

    Cur.Integer_Value (0)
    Cur.Value (1)

https://www.adacore.com/wp-content/files/auto_update/gnatcoll-docs/gnatcoll.html#Database-interface

:o)

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Fri, 25 Feb 2011 19:24:27 +0100, Thomas L�cke wrote:

> On 2011-02-25 17:05, Dmitry A. Kazakov wrote:
>> I don't understand your code, in particular where the statement prepared is
>> actually executed?
> 
>     Cur.Fetch (Connection => Connection,
>                Stmt       => P,
>                Params     => (1 => +Some_Name'Access));
> 

That does not look like an execution of the statement P. I would expect
fetch rather walking through the result set than executing anything. But I
don't know GNATColl.

>> Where the cursor gets closed? DBMS does not appreciate
>> cursors left unclosed. Does GNATColl maintain cursor states internally?
> 
> Yes.

Does it that right? I am asking this, because it is an issue in ODBC, where
the cursor state depends on many factors and that the same sequence of
calls may either close the cursor or not depending on the operation
outcome.

> > Why
> > fetch is not called within a loop?
>
>    while Cur.Has_Row loop
>       Put_Line ("id=" & Cur.Integer_Value (0)'Img);
>       Put_Line ("name=" & Cur.Value (1));
>       Cur.Next;
>    end loop;

What is the semantics of Has_Row does it refer the current row or that Next
would not fail when called?

>> I can only stress that you will
>> never get it working with literal values. I've been there. It isn't worth
>> to try.
> 
> I don't want it to work with literal values. I want it to work with
> prepared and parameterized queries. I simple show an example with
> literal values because that actually works.  :o)
> 
> The problem is that GNATColl dumps an exception when I try to use
> prepared and parameterized queries.

That means that you are doing something wrong. Bound values must work.

> I like GNATColl's interface.

OK

>> [ At least ODBC has tracing mode, which sometimes with some drivers indeed
>> traces. So that you could see the actual statement. ]
> 
> GNATColl has that too. There's nothing wrong with the actual statement.
> The problem occurs when I try to extract the data in the loop using
> these two calls:
> 
>     Cur.Integer_Value (0)
>     Cur.Value (1)

Tracing usually tells if the result does not contain requested columns,
the values cannot be converted, the cursor is wrong etc.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-25 21:08, Dmitry A. Kazakov wrote:
>>      Cur.Fetch (Connection =>  Connection,
>>                 Stmt       =>  P,
>>                 Params     =>  (1 =>  +Some_Name'Access));
>>
>
> That does not look like an execution of the statement P. I would expect
> fetch rather walking through the result set than executing anything. But I
> don't know GNATColl.


The comment for the Fetch procedure reads:

    --  Execute a prepared statement on the connection.


> Does it that right? I am asking this, because it is an issue in ODBC, where
> the cursor state depends on many factors and that the same sequence of
> calls may either close the cursor or not depending on the operation
> outcome.


I've not experienced any problems with GNATColl and cursors. It just
works.

Except of course in this specific case, where I can't make it work. hehe


>>     while Cur.Has_Row loop
>>        Put_Line ("id="&  Cur.Integer_Value (0)'Img);
>>        Put_Line ("name="&  Cur.Value (1));
>>        Cur.Next;
>>     end loop;
>
> What is the semantics of Has_Row does it refer the current row or that Next
> would not fail when called?


I'm not quite sure I understand what you mean Dmitry. Could you elaborate?



> That means that you are doing something wrong. Bound values must work.


Exactly. And that part does work. I can see in the trace that the query
is correct, and I can see it executed perfectly in the PostgreSQL log.

The problem occurs _after_ the query has been executed. Things go bad
when I try to extract the data, and it only fails when I'm using a
prepared and parameterized query, re. my original post:

    If I get rid of the Cur.Integer_Value (0) and Cur.Value (1) calls
    in the while loop from the prepared example, then I also get rid
    of the exception, so it appears to be some sort of issue with the
    *Value functions and prepared queries.

I'm pretty sure this is a bug somewhere in GNATColl.


-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Fri, 25 Feb 2011 22:50:47 +0100, Thomas L�cke wrote:

> On 2011-02-25 21:08, Dmitry A. Kazakov wrote:

>>>     while Cur.Has_Row loop
>>>        Put_Line ("id="&  Cur.Integer_Value (0)'Img);
>>>        Put_Line ("name="&  Cur.Value (1));
>>>        Cur.Next;
>>>     end loop;
>>
>> What is the semantics of Has_Row does it refer the current row or that Next
>> would not fail when called?
> 
> I'm not quite sure I understand what you mean Dmitry. Could you elaborate?

Does execution of the statement pre-fetches the first row? Traditionally
the semantics of such stuff rather refers to the following row, like when
file reading:

   while not EOF (File) loop
       -- No line read yet, EOF refers to the next one
       Read_Line (File, ...);
   end loop;

I.e. is Has_Row "there is another row to fetch", or "the row is already
fetched and column values are cached."

>> That means that you are doing something wrong. Bound values must work.
> 
> Exactly. And that part does work. I can see in the trace that the query
> is correct, and I can see it executed perfectly in the PostgreSQL log.
> 
> The problem occurs _after_ the query has been executed. Things go bad
> when I try to extract the data, and it only fails when I'm using a
> prepared and parameterized query, re. my original post:
> 
>     If I get rid of the Cur.Integer_Value (0) and Cur.Value (1) calls
>     in the while loop from the prepared example, then I also get rid
>     of the exception, so it appears to be some sort of issue with the
>     *Value functions and prepared queries.
> 
> I'm pretty sure this is a bug somewhere in GNATColl.

Possible reasons:

1. Value conversion failed
1.1. type is wrong
1.2. address is broken
1.3. alignment is illegal
1.4. no value
1.5. constraint error

2. The requested column number is wrong

3. The cursor is wrong

You should get the extended error code to determine the problem.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-26 20:33, Dmitry A. Kazakov wrote:
> Does execution of the statement pre-fetches the first row? Traditionally
> the semantics of such stuff rather refers to the following row, like when
> file reading:
>
>     while not EOF (File) loop
>         -- No line read yet, EOF refers to the next one
>         Read_Line (File, ...);
>     end loop;
>
> I.e. is Has_Row "there is another row to fetch", or "the row is already
> fetched and column values are cached."


Here's the spec and comment for the Forward_Cursor type:

    type Forward_Cursor is tagged private;
    No_Element : constant Forward_Cursor;
    --  A cursor that iterates over all rows of the result of an SQL
    --  query. A single row can be queried at a time, and there is no
    --  possibility to go back to a previous row (since not all DBMS
    --  backends support this).
    --  This type automatically takes care of memory management and
    --  frees its memory when no longer in use. This type is tagged only
    --  so that you can override it in your own applications (an example
    --  is to add an Element primitive operation which converts the
    --  current row into a specific Ada record for ease of use).

And then Has_Row:

    function Has_Row (Self : Forward_Cursor) return Boolean;
    --  Whether there is a row to process. Fetching all the results from
    --  a query is done in a loop similar to:
    --      Cursor := Execute (...)
    --      while Has_Row (Cursor) loop
    --         ...
    --         Next (Cursor);
    --      end loop;

So seeing as you can't go back when using a Forward_Cursor, I'd say that
the result-set is not cached.

If there are any results available, the cursor simply points to the
first row, and a call to Cursor.Next moves to the next row in the
result-set.

If you want to pre-fetch all the results, you'd instead use a
Direct_Cursor:

    type Direct_Cursor is new Forward_Cursor with private;
    No_Direct_Element : constant Direct_Cursor;
    --  A direct cursor is a cursor that keeps all its results in
    --  memory, and gives access to any of the rows in any order.
    --  As opposed to a Forward_Cursor, you can iterate several times
    --  over the results. On the other hand, a direct_cursor uses more
    --  memory locally, so might not be the best choice systematically.

Using that, you can freely move around in the result-set using various
procedures.


> Possible reasons:
>
> 1. Value conversion failed
> 1.1. type is wrong
> 1.2. address is broken
> 1.3. alignment is illegal
> 1.4. no value
> 1.5. constraint error
>
> 2. The requested column number is wrong
>
> 3. The cursor is wrong


I've done some further digging, and it appears that GNATColl thinks the
result is binary data when the query is prepared. When the same query is
executed without preparing it, the data is returned just fine as Integer
and String respectively.


>
> You should get the extended error code to determine the problem.


There's no error code. The query is executed just fine on the RDBMS;
it's GNATColl that fails at handling the result afterwards.

So the only "error code" available is the raised exception I mentioned
in my original post.

I'll see if I can convince some of the other Ada-DK members to take a
look at this very odd issue. Maybe we can do a debugging session at the
next open Ada-DK meeting. That could be fun. :o)

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Sun, 27 Feb 2011 15:53:13 +0100, Thomas L�cke wrote:

> I've done some further digging, and it appears that GNATColl thinks the
> result is binary data when the query is prepared. When the same query is
> executed without preparing it, the data is returned just fine as Integer
> and String respectively.

In that case it seems that the results of the prepared statement must be
bound. Does GNATColl support them? Technically bound results are fetched
automatically when the cursor moves through the result set.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-27 16:36, Dmitry A. Kazakov wrote:
> In that case it seems that the results of the prepared statement must be
> bound. Does GNATColl support them? Technically bound results are fetched
> automatically when the cursor moves through the result set.


It does not appear so.

https://www.adacore.com/wp-content/files/auto_update/gnatcoll-docs/gnatcoll.html#Getting-results

That's all there is in the manual about retrieving results.

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Sun, 27 Feb 2011 16:49:32 +0100, Thomas L�cke wrote:

> That's all there is in the manual about retrieving results.

OK, are you ready to ditch GNATColl and use GNADE ODBC now? (:-))

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-27 21:15, Dmitry A. Kazakov wrote:
> OK, are you ready to ditch GNATColl and use GNADE ODBC now? (:-))


I've got a lot of time and code invested in GNATColl, but if this isn't
fixed "soon", then I'm going to have to look somewhere else.

Which honestly saddens me.

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Emmanuel Briot]

> I've done some further digging, and it appears that GNATColl thinks the
> result is binary data when the query is prepared. When the same query is
> executed without preparing it, the data is returned just fine as Integer
> and String respectively.

Your analysis is correct.
The fix here is actually trivial.

in gnatcoll-sql-postgres-gnade.adb, replace the code for

 function PQexecPrepared
           (Conn : System.Address;
            Name : String;
            Nparams : Natural := 0;
            Values  : CS.chars_ptr_array;
            Lengths : System.Address := System.Null_Address;
            Formats : System.Address := System.Null_Address;
            Format  : Natural := 0) return System.Address;

so that "Format" defaults to 1 instead of 0.

That forces postgreSQL to return values as text instead of binary. The
latter format is not supported by GNATCOLL (nor was it supported by
GNADE). There is too much machine-dependent handling here.

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-02-28 14:49, Emmanuel Briot wrote:
> Your analysis is correct.
> The fix here is actually trivial.
>
> in gnatcoll-sql-postgres-gnade.adb, replace the code for
>
>   function PQexecPrepared
>             (Conn : System.Address;
>              Name : String;
>              Nparams : Natural := 0;
>              Values  : CS.chars_ptr_array;
>              Lengths : System.Address := System.Null_Address;
>              Formats : System.Address := System.Null_Address;
>              Format  : Natural := 0) return System.Address;
>
> so that "Format" defaults to 1 instead of 0.
>
> That forces postgreSQL to return values as text instead of binary. The
> latter format is not supported by GNATCOLL (nor was it supported by
> GNADE). There is too much machine-dependent handling here.


This too works without a hitch using SVN revision 170922.

Thank you very much for fixing this.

I've signed up for the GPS maillist. Would that be a proper place to
report issues like this, or should I just use comp.lang.ada?

Personally I think GNATColl is awesome enough to have it's own maillist!

:o)

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Emmanuel Briot]

> I've signed up for the GPS maillist. Would that be a proper place to
> report issues like this, or should I just use comp.lang.ada?

Thanks for the detailed reports. My recommendation is to first discuss
potential issues here on comp.lang.ada, even though we don't monitor
it closely. There are some very knowledgeable people here that might
help you understand the issue (or tell you there is no issue :-).
If that happens to be a real issue, please submit the bug on
report@adacore.com. Although we do not and cannot guarantee replies
for unsupported users, we certainly pay attention to all messages (in
particular if you provide enough details, as you generally do). Of
course, we always give priority to our supported users!

> Personally I think GNATColl is awesome enough to have it's own maillist!

Keep using it ! I am interested in getting feedback and use from more
people, that's the best way to strengthen the library (for instance,
support for prepared statement was added only recently, and we only
use it internally with sqlite, so I did not know about the postgreSQL
issues).

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Thomas Løcke]

On 2011-03-01 11:28, Emmanuel Briot wrote:
> Thanks for the detailed reports.


You're very welcome. Glad I can help.


> My recommendation is to first discuss
> potential issues here on comp.lang.ada, even though we don't monitor
> it closely. There are some very knowledgeable people here that might
> help you understand the issue (or tell you there is no issue :-).
> If that happens to be a real issue, please submit the bug on
> report@adacore.com. Although we do not and cannot guarantee replies
> for unsupported users, we certainly pay attention to all messages (in
> particular if you provide enough details, as you generally do).


I'll keep posting here, no problem.  :o)



> Of
> course, we always give priority to our supported users!


Does AdaCore offer support contracts for AWS/GNATColl? I think I wrote
to sales@adacore.com long ago asking for a quote on just that, and from
the reply I received I got the distinct feeling that AdaCore was not
very interested in offering support contracts for very small customers.

Maybe this has changed?


> Keep using it ! I am interested in getting feedback and use from more
> people, that's the best way to strengthen the library (for instance,
> support for prepared statement was added only recently, and we only
> use it internally with sqlite, so I did not know about the postgreSQL
> issues).


Have you considered bundling SQLite with GNATColl?

-- 
Thomas L�cke

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

Re: Problem in GNATColl when using prepared queries: GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR is raised

[Dmitry A. Kazakov]

On Tue, 01 Mar 2011 21:33:36 +0100, Thomas L�cke wrote:

> Have you considered bundling SQLite with GNATColl?

BTW GNADE already has bindings to SQLite3 as a dynamic library.

P.S. If you rather want static linking to the SQLite3's "amalgation", which
is IMO the only reason why anybody would like to use SQLite, then see here

   http://www.dmitry-kazakov.de/ada/components.htm#SQLite

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de