Re: call stored function from ecpg w/cursor - Mailing list pgsql-novice

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