Re: call stored function from ecpg w/cursor

[Andrew Jarcho <ajarcho(at)nyc(dot)rr(dot)com>]

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