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

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

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