Re: Problem with EVAL of SQL proc

On 03-Sep-2014 14:26 -0500, Gary Thompson wrote:

CREATE PROCEDURE TEST/GETLIB
( IN out_loc CHARACTER (4)
, OUT div_lib VARCHAR(10)
) LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
P1: BEGIN
SELECT MAX(lclib) INTO div_lib
FROM test/locf
WHERE lcwhse = out_loc
AND lcbas IN ('S','V');
END P1

FWiW: To debug procedures like this, I often create a LOG_MSG procedure that calls a CLLE that does a SNDPGMMSG of some text; sending an impromptu message. Also add declarations of SQLCODE and SQLSTATE; likely use the logging to record those in the joblog. I would generally also put the result of any OUT parameter into a local variable [rather than only\directly into that parameter], and then as the final statement before the end of the compound statement, allowing me to be sure the visibility should be available twice and in two-places:
SET DIV_LIB = LCL_DIV_LIB ;

I would remove the logging [probably by commenting-out the statements] and possibly\optionally remove the local copy before finalizing the code.

I have GETLIB in debug break on END P1
This works as I expect: EVAL *GETLIB.OUT_LOC:S
Returning: *GETLIB.OUT_LOC:S = "82 "

This fails: EVAL *GETLIB.DIV_LIB:S
As does this: EVAL *GETLIB.DIV_LIB:C
And this: EVAL *GETLIB.DIV_LIB:x 10
All return: Pointer type error occurred.

This does 'work' (kinda): EVAL %localvars
Returning:
EVAL %localvars
argc = 4
argv = SPP:FDF64C5B78009BE0
GETLIB.OUT_LOC = SPP:FDF64C5B78009CE0
GETLIB.SQLP_I1 = 0
GETLIB.DIV_LIB.LEN = 10
GETLIB.DIV_LIB.DAT = SPP:FDF64C5B78009CE8

The last two should be revealing; i.e. mimicking the "works as I expect" EVAL requests, in conjunction with knowing how to get the dereferenced string:

EVAL GETLIB.DIV_LIB.LEN
EVAL *GETLIB.DIV_LIB.DAT:S

Of course, the first is already known from &localvars output. However the .LEN value is possibly incorrect, being the maximum length [so possibly not the expected trimmed\varying]; the query might want to instead have selected the expression RTRIM(MAX(lclib)) to get a proper varying length string from the LCLLIB which is quite probably CHAR(10).?

Thanks to Chuck for the hint about typing HELP on the debug cmd
line, but I did not see anything like VARCHAR - but apparently
DIV_LIB is NOT a 'string' type ? and GETLIB.DIV_LIB.DAT, using my
System/370 Reference Summary, does not decode into anything
meaningful to me.

The VARCHAR is split into a _structure_ [effectively an RPG DS] of the string_LENgth and the string_DATa, and those individual components are viewed separately. The C has [or at least had] no /varying/ data type that matches both that RPG Alpha [varying] data type and the VARCHAR SQL data type; thus the need to review the components separately. However the GETLIB.DIV_LIB should exist, although not as a C /string/ type, such that the following EVAL should be revealing:

EVAL GETLIB.DIV_LIB:X

After about 10 presses of F10 I return to my SQLRPGLE where,
surprisingly SQLCOD = 0, but, not surprisingly, the library name
returned is blank.

What was the value of the indicator variable specified for the second argument of the SQL CALL requested from the SQLRPGLE?

The values of SQLCODE and SQLSTATE in the SQL routine [SQL PROCEDURE] might be of interest; plus the value of the DIV_LIB at the break-point for [the _final step_ of] the "END P1;"

Additional Message Information
Message ID . . . . . . : CPF7E18 <<SNIP>>
Message . . . . : Pointer type error occurred.
Cause . . . . . : The identifier in position 21 of the input buffer
is not a pointer.

OK, so probably an invalid EVAL request was made, but the context of the failure was unstated; i.e. most importantly... What was the failing EVAL request?