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