From mboxrd@z Thu Jan  1 00:00:00 1970
X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me
X-Spam-Level: 
X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00 autolearn=ham
	autolearn_force=no version=3.4.4
X-Google-Thread: 103376,89e2ed92b27de792
X-Google-NewGroupId: yes
X-Google-Attributes: gida07f3367d7,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII
Path: 
 g2news2.google.com!news4.google.com!feeder.news-service.com!weretis.net!feeder4.news.weretis.net!nuzba.szn.dk!news.szn.dk!pnx.dk!dotsrc.org!filter.dotsrc.org!news.dotsrc.org!not-for-mail
Date: Fri, 25 Feb 2011 19:24:27 +0100
From: =?ISO-8859-1?Q?Thomas_L=F8cke?= <tl@ada-dk.org>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
 rv:1.9.2.13) Gecko/20101210 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: comp.lang.ada
Subject: Re: Problem in GNATColl when using prepared queries:
 GNATCOLL.SQL.POSTGRES.GNADE.POSTGRESQL_ERROR
 is raised
References: <4d67ce9c$0$23765$14726298@news.sunsite.dk>
 <1on2yzc25jxpr.1offs3nb41fqk$.dlg@40tude.net>
In-Reply-To: <1on2yzc25jxpr.1offs3nb41fqk$.dlg@40tude.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Message-ID: <4d67f3db$0$23762$14726298@news.sunsite.dk>
Organization: SunSITE.dk - Supporting Open source
NNTP-Posting-Host: 83.91.213.86
X-Trace: news.sunsite.dk
 DXC=;E_RnIcPLFi`;IS0i7S8TaYSB=nbEKnkkP^4KGf[6iN`1GQX8;5?CngRED9SjB8:6iQo^8G8>Andm_\mSh>MjBL`4>6B]=RkIgg
X-Complaints-To: staff@sunsite.dk
Xref: g2news2.google.com comp.lang.ada:18572
Date: 2011-02-25T19:24:27+01:00
List-Id: <comp.lang.ada>

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