Обсуждение: 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