Re: call stored function from ecpg w/cursor

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).
>
>