Обсуждение: call stored function from ecpg w/cursor

hi--
is there a way to retrieve several tuples via a pl/pgsql stored function
from ecpg code? i've tried using cursors 6 ways from Sunday and I'm
having no luck. i've searched the documentation and the mailing list
archives. i'd appreciate any help anyone could give.
--andy

On Sat, Apr 28, 2007 at 04:20:09AM -0400, Andrew Jarcho wrote:
> is there a way to retrieve several tuples via a pl/pgsql stored function
> from ecpg code? i've tried using cursors 6 ways from Sunday and I'm
> having no luck. i've searched the documentation and the mailing list
> archives. i'd appreciate any help anyone could give.

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.

--
Michael Fuhr

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

--
Michael Fuhr

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

On Sun, Apr 29, 2007 at 03:39:27PM -0400, Andrew Jarcho wrote:
> 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.

Could you post a complete test case instead of just excerpts?  By
"complete" I mean all SQL and C (ECPG) code that somebody could use
in an attempt to reproduce the problem on their system.  When pieces
are missing we have to guess at what those pieces are and then we
can't be certain that we've created the same conditions as on your
system.

What OS and version of PostgreSQL are you using?  What compiler?
Did you regenerate the .c file after changing the .pgc file?

> in file floop.pgc:
[...]
> RETURN -1;

This code doesn't even compile on my system because of "RETURN"
instead of "return".  Are you sure you're compiling and running the
code you think you are?

--
Michael Fuhr

Hi Michael--
Thank you for your continued help. A stripped down but fully
(mal)functioning version of the code is attached. The error, according
to the logfile ecpg_debug.log, occurs in the file callProcsViaC.pgc and
is clearly marked there. A lot of the included code is just for
completeness/reference.

The school's system is a Solaris UNIX system. The PostgreSQL version is
8.0.3. The code is compiled with gcc, and linked as you can see in the
Makefile with g++, since the full program includes some C++ files. I
have consistently used the -B switch when make-ing, which on this system
forces the compiler to start from scratch, and re-pre-compile the .gpc
files into .c files.

Please let me know if there is other information I can provide that
might be useful in diagnosing this problem.
--Andy

On Tue, May 01, 2007 at 03:03:58AM -0400, Andrew Jarcho wrote:
> Thank you for your continued help. A stripped down but fully
> (mal)functioning version of the code is attached. The error, according
> to the logfile ecpg_debug.log, occurs in the file callProcsViaC.pgc and
> is clearly marked there. A lot of the included code is just for
> completeness/reference.

The ms_getInvolvedInCase() function returns a composite type.
Instead of

  EXEC SQL DECLARE c_1 CURSOR FOR SELECT ms_getInvolvedInCase(:mstsCaseID);

try

  EXEC SQL DECLARE c_1 CURSOR FOR SELECT * FROM ms_getInvolvedInCase(:mstsCaseID);

--
Michael Fuhr

Michael Fuhr wrote:
> On Tue, May 01, 2007 at 03:03:58AM -0400, Andrew Jarcho wrote:
>
>> Thank you for your continued help. A stripped down but fully
>> (mal)functioning version of the code is attached. The error, according
>> to the logfile ecpg_debug.log, occurs in the file callProcsViaC.pgc and
>> is clearly marked there. A lot of the included code is just for
>> completeness/reference.
>>
>
> The ms_getInvolvedInCase() function returns a composite type.
> Instead of
>
>   EXEC SQL DECLARE c_1 CURSOR FOR SELECT ms_getInvolvedInCase(:mstsCaseID);
>
> try
>
>   EXEC SQL DECLARE c_1 CURSOR FOR SELECT * FROM ms_getInvolvedInCase(:mstsCaseID);
>
>
Hi Michael--
Thank you very much for your kind assistance; that solved the problem,
which has occupied me all day for several days.

I made a change to the code, however, and although it now appears to
work correctly, it produces an error very similar to the one I was
getting before. It also produces a warning on compile. The error, raised
by ECPGdebug, is -202 'Too few arguments in line 51'.  The compile-time
warning, cut and pasted from the output, is:

    WARNING: cursor `c_1´ has been declared but ot opened (sic).

The change I made was to alter the function stored in the db so that it
returns a refcursor, and adjust my ecpg code accordingly. The cursor is
declared using:

    EXEC SQL DECLARE c_1 CURSOR FOR SELECT
    ms_getInvolvedInCase(:mstsCaseID, refcursor);

and instantiated using:

      EXEC SQL SELECT ms_getInvolvedInCase(:mstsCaseID, 'c_1');

The stored function is coded as

    CREATE OR REPLACE FUNCTION ms_getInvolvedInCase (cID INTEGER,
    REFCURSOR) RETURNS REFCURSOR AS $$

    BEGIN
      OPEN $2 FOR
        SELECT ACR.logname, A.lastName, A.firstName, ACR.role
        FROM ms_account A, ms_accountCaseRole ACR
        WHERE ACR.caseID = cID AND
              ACR.logname = A.logname;

      RETURN $2;

    END;

    $$ LANGUAGE plpgsql;

and tuples are retrieved using:

        EXEC SQL FETCH NEXT FROM c_1 INTO :lognm:lognm_ind,
    :lastnm:lastnm_ind, :firstnm:firstnm_ind,
                                          :rol:rol_ind;

inside a loop.

I'm afraid to use this code (updated version attached) because of the
error messages, although it works. As before, I'm compiling with gcc,
and linking with g++, on a Solaris UNIX system running PostgreSQL 8.0.3.
I'd appreciate any help you can give me.
Yours,
--Andy J.

Hi Michael--
I've (sort of) solved the problem I posted earlier today. I've inserted
the statements:

      EXEC SQL OPEN c_1;
      EXEC SQL CLOSE c_1;

and changed:

      EXEC SQL SELECT ms_getInvolvedInCase(:mstsCaseID, 'c_1');


to

      EXEC SQL SELECT ms_getInvolvedInCase(:mstsCaseID, 'c_1') INTO :dummy;


The complete subroutine now looks like:

    ms_involvedincase_tuple get_involved_in_case(int mstsCID)
    {
      EXEC SQL BEGIN DECLARE SECTION;
      int mstsCaseID = mstsCID;            // DECLARE the (input) case
    ID variable
      char lognm[LOG_NAME_LEN];
      char lastnm[LAST_NAME_LEN];
      char firstnm[FIRST_NAME_LEN];
      char rol[ROLE_LEN];
      int success;
      short lognm_ind;
      short lastnm_ind;
      short firstnm_ind;
      short rol_ind;
      short success_ind;
      char dummy[40];
      EXEC SQL END DECLARE SECTION;

      EXEC SQL DECLARE c_1 CURSOR FOR SELECT
    ms_getInvolvedInCase(:mstsCaseID, refcursor);

      EXEC SQL OPEN c_1;
      EXEC SQL CLOSE c_1;

      ms_involvedincase_tuple retval_tuple;

      connect_to_postgresql();
      /* Branch to the notfound label when the "No data found" condition
    occurs. */
      EXEC SQL WHENEVER NOT FOUND GOTO notfound;

      EXEC SQL SELECT ms_getInvolvedInCase(:mstsCaseID, 'c_1') INTO :dummy;

      do {
        EXEC SQL FETCH NEXT FROM c_1 INTO :lognm:lognm_ind,
    :lastnm:lastnm_ind, :firstnm:firstnm_ind,
                                          :rol:rol_ind;

        strcpy(retval_tuple.lognm, lognm);
        strcpy(retval_tuple.lastnm, lastnm);
        strcpy(retval_tuple.firstnm, firstnm);
        strcpy(retval_tuple.rol, rol);

        printf("%s  %s  %s  %s\n", retval_tuple.lognm,
    retval_tuple.lastnm, retval_tuple.firstnm,
                                   retval_tuple.rol);
      } while (1);

      EXEC SQL CLOSE c_1;

      disconnect_from_postgresql();

      return retval_tuple;

    notfound:
      disconnect_from_postgresql();

      strcpy(retval_tuple.lognm, "");
      strcpy(retval_tuple.lastnm, "");
      strcpy(retval_tuple.firstnm, "");
      strcpy(retval_tuple.rol, "");

      return retval_tuple;
    }

The code compiles without error and runs correctly. My question, though,
is: do the extra OPEN and CLOSE statements do anything other than keep
the compiler happy? I notice that the CLOSE statement is completely
spurious; the code compiles and runs fine without it.
Thank you very much for your help and patience throughout.
Yours,
--Andy