Skip site navigation (1) Skip section navigation (2)

Re: call stored function from ecpg w/cursor

From: Andrew Jarcho <ajarcho(at)nyc(dot)rr(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: call stored function from ecpg w/cursor
Date: 2007-04-29 19:39:27
Message-ID: 4634F46F.9020707@nyc.rr.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thank you very much Michael for your reply. I have tried your 
suggestions, but am still having problems. The code still prints 0 as 
its result, and ECPGdebug reports "raising sqlcode -201 in line ##, 'Too 
many arguments in line ##.'". This is the way the code sample looks now.

in file flop.sql:
CREATE OR REPLACE FUNCTION foo (cID INTEGER) RETURNS INTEGER AS $$

DECLARE
 result INTEGER;

BEGIN
 SELECT value
 INTO result
 FROM my_relation
 WHERE caseID = cID;
 IF result IS NOT NULL THEN
   RETURN result;
 ELSE
   RETURN 0;
 END IF;

EXCEPTION
 WHEN OTHERS THEN
   RETURN -1;

END;
$$ LANGUAGE plpgsql;

in file floop.pgc:
int get_result(int cID)
{
 EXEC SQL BEGIN DECLARE SECTION;
 int caseID = cID;
 int result;
 short result_ind;
 EXEC SQL END DECLARE SECTION;

 /* a problem here */
 EXEC SQL DECLARE c_1 CURSOR FOR SELECT foo(:caseID);

 connect_to_postgresql();

 EXEC SQL WHENEVER NOT FOUND GOTO notfound;

 EXEC SQL OPEN c_1;

 do {
   EXEC SQL FETCH c_1 INTO :result:result_ind;
   printf("%d\n", result);
 } while (result !=0 && result_ind == 0);

 EXEC SQL CLOSE c_1;

 disconnect_from_postgresql();

 return 1;

notfound:
 disconnect_from_postgresql_error();
 RETURN -1;
}

Any ideas?
--Andy Jarcho

Michael Fuhr wrote:
> On Sat, Apr 28, 2007 at 10:26:15AM -0400, Andrew Jarcho wrote:
>   
>> Michael Fuhr wrote:
>>     
>>> Could you post a simple example that shows what you're trying to
>>> do?  Please describe what you'd like to happen and what actually
>>> does happen.
>>>  
>>>       
>> Thank you very much Michael for your reply. Here's an example:
>>     
>
> Please copy the mailing list on replies so others can contribute
> to and learn from the discussion.
>
>   
>>  /* a problem here */
>>  EXEC SQL DECLARE c_1 (inval integer) CURSOR FOR SELECT foo(:caseID);
>>     
>
> Try this:
>
>    EXEC SQL DECLARE c_1 CURSOR FOR SELECT foo(:caseID);
>
>   
>>  EXEC SQL OPEN c_1 (:caseID);
>>     
>
> And this:
>
>    EXEC SQL OPEN c_1;
>
> Also, the code you sent me privately declares and refers to an
> indicator variable (result_ind) but doesn't set it in the FETCH
> statement.  If your compiler didn't warn about that then consider
> turning on whatever flags would print such warnings (e.g., -Wall
> if you're using gcc).
>
>   


In response to

Responses

pgsql-novice by date

Next:From: hostel Nate Date: 2007-04-30 05:04:33
Subject: table design
Previous:From: Michael FuhrDate: 2007-04-29 13:43:14
Subject: Re: call stored function from ecpg w/cursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group