"Griffin, Patrick J." wrote:

>
> I'm considering making a change to dsql/dsql.c.
>
> Here's my problem:
>
> When accessing IB60 from a Microfocus COBOL (now Merdant I believe) my
> programs will randomly receive a -502 error message when executing an OPEN
> cursor statement:
>
> Dynamic SQL Error
> -SQL error code = -502
> -Declared cursor already exists
>
> The specific case I'm chasing involves a sub-program being called multiple
> times. I'll receive this message during the second call to the subprogram.
> I've tried to write a simple program to demonstrate the problem, but the
> simple program doesn't fail.
>
> Looking at the code generated from the OPEN cursor statement I find:
> 227* EXEC SQL
> 228* OPEN CTL-CAPA-CPCK-RC2
> 229* END-EXEC
> 230 IF isc-7 = 0 THEN
> 231 CALL "isc_compile_request2" USING ISC-STATUS-VECTOR,
> 232 BY REFERENCE CNTL, BY REFERENCE isc-7, BY VALUE 90,
> ^L^M* Micro Focus COBOL for UNIX V4.1 revision 020 06-Sep-00 21:18
> Page 5
> * demo3.cob
> 233 BY REFERENCE ISC-8
> 234 CALL "isc_sqlcode_s" USING ISC-STATUS-VECTOR,
> 235 BY REFERENCE SQLCODE
> 236 END-IF
> 237 MOVE CAP-CHK-OPER-ID TO isc-10
> 238 IF (ISC-8S = 0) AND isc-7 NOT = 0 THEN
> 239 CALL "isc_dsql_alloc_statement2" USING ISC-STATUS-VECTOR,
> 240 BY REFERENCE CNTL, BY REFERENCE ISC-8S
> 241 END-IF
> 242 IF ISC-8S NOT = 0 THEN
> 243 CALL "isc_dsql_set_cursor_name" USING ISC-STATUS-VECTOR,
> 244 BY REFERENCE ISC-8S,
> 245 BY REFERENCE ISC-CONST-CTL-CAPA-CPCK-RC2, BY VALUE 0
> 246 IF ISC-STATUS(2) = 0 THEN
> 247 CALL "isc_dsql_execute_m" USING ISC-STATUS-VECTOR,
> 248 BY REFERENCE ISC-TRANS, BY REFERENCE ISC-8S, BY VALUE 0,
> 249 BY VALUE 0, BY VALUE -1, BY VALUE 0, BY VALUE 0
> 250 IF ISC-STATUS(2) = 0 THEN
> 251 CALL "isc_start_and_send" USING ISC-STATUS-VECTOR,
> 252 BY REFERENCE isc-7, BY REFERENCE ISC-TRANS, BY VALUE 0,
> 253 BY VALUE 20, BY REFERENCE ISC-9, BY VALUE 0
> 254 CALL "isc_sqlcode_s" USING ISC-STATUS-VECTOR,
> 255 BY REFERENCE SQLCODE
> 256 END-IF
> 257 END-IF
> 258 END-IF
> 259 CALL "isc_sqlcode_s" USING ISC-STATUS-VECTOR,
> 260 BY REFERENCE SQLCODE
> 261 .
> 262
>
> and after hacking on this code I believe the "isc_dsql_set_cursor_name" is
> returning the error message.
>
> After hacking on the servers dsql code, I believe that the source of the
> message is the following code in GDS_DSQL_SET_CURSOR:
>
> /* If there already is a cursor and its name isn't the same, ditto.
> We already know there is no cursor by this name in the hash table */
>
> if (!request->req_cursor)
> request->req_cursor = MAKE_symbol (request->req_dbb, cursor,
> length, SYM_cursor, request);
> else
> {
> assert (request->req_cursor != symbol);
> ERRD_post (gds__sqlerr, gds_arg_number, (SLONG) -3502,
> gds_arg_gds, gds__dsql_decl_err,
> 0);
> };
>
> (Sorry about the -3502 -- that's how I found out which of the four -502
> conditions was being reported.)
>
> Studying the code generated by the CLOSE cursor statement generated by gpre
> I find:
> 423* EXEC SQL
> 424* CLOSE CTL-CAPA-CPCK-RC1
> 425* END-EXEC
> 426 IF ISC-1S NOT = 0 THEN
> 427 CALL "isc_dsql_free_statement" USING ISC-STATUS-VECTOR,
> 428 BY REFERENCE ISC-1S, BY VALUE 1
> 429 IF ISC-STATUS(2) = 0 THEN
> 430 CALL "isc_unwind_request" USING ISC-STATUS-VECTOR,
> 431 BY REFERENCE isc-0, BY VALUE 0
> 432 END-IF
> 433 END-IF
> 434 CALL "isc_sqlcode_s" USING ISC-STATUS-VECTOR,
> 435 BY REFERENCE SQLCODE
> 436 .
>
> Studying the "isc_dsql_free_statement" code I find a call to close_cursor.
>
> I notice that close_cursor does not release the req_cursor symbol
> (request->req_cursor).
>
> In the close_cursor routine I considering changing:
>
> from:
>
> if (open_cursor)
> {
> ALLD_release (open_cursor);
> request->req_open_cursor = NULL;
> }
>
> to:
>
> if (open_cursor)
> {
> ALLD_release (open_cursor);
> request->req_open_cursor = NULL;
> }
> HSHD_remove (request->req_cursor); /* PJPG 20000907 */
> request->req_cursor = NULL; /* PJPG 20000907 */
>
> Opinions?
>
> Will the storage that the symbol block occupied still be released?
>

Pat,

I wouldn't do that if I were you. Go back and study isc_dsql_free_statement() in
more depth. Investigate the difference between closing a cursor and dropping it.

Now come up a level from the code and discuss the SQL issues with your fellow
members in the community -- especially what you are trying to accomplish. The
members with the SQL decoder rings will happily explain that the SQL standard
doesn't provide a DROP 'named cursor' statement because that would be too
intuitive. They will then explain, probably with some humor, the obtuse SQL
mechanisms available to accomplish what you're trying to do.

Changing code is ALWAYS the last step of software engineering. If you skip the
due dilegence steps before that, it will be difficult to maintain a stable code
base.

Regards,
Charlie