On 02 May 2012 15:24, Gqcy wrote:
Initially I was just performing the call to a program
with 2 parms, the 2nd parm being 64 long.
CALL GEMLIB.GEMC911('ACTQRY8 '
,'Query over the Accounts Payable system Returns Unpaid vendors')
So the above was a CALL from SQL without having declared or created a
PROCEDURE to define the interface; as implied by the next comment.? Was
there verification that a PROCEDURE by that name does not already exist,
but with CHAR(32); query SYSROUTINE or SYSPROCS, and if found, then
SYSPARMS.
What release? A change to how undeclared procedure calls are made
was mentioned in the v6r1 or v7r1 Memo to Users.
Was the program under debug and the program variable displayed, from
which the "getting truncated at 32 positions" was concluded? If not,
debug should be used to verify. If so, how was debug used to verify the
effect; e.g. EVAL ParmVarName, EVAL ParmVarName:x, etc.?
For an undeclared procedure for which the called program is CLP [or
perhaps for any OPM, the SQL automatically passes all arguments as IN,
and I believe also as CHAR versus VARCHAR]. In order to ensure padded
data is sent, the following should suffice; though the subject seems to
imply this is not merely padding:
CALL GEMLIB.GEMC911
('ACTQRY8 X' /* force full 10 bytes into CLP parm-1; 64 parm-2 */
,'Query over the Accounts Payable system Returns Unpaid vendors X')
/*....+....1....+....2....+....3....+....4....+....5....+....6....+
I then tried to create a stored procedure, and it was doing the same
truncation:
CREATE PROCEDURE GEMLIB/GEMC911
(IN APPID CHAR (10 ), IN APPDESC CHAR (64 ))
LANGUAGE CL DETERMINISTIC NO SQL CALLED ON NULL INPUT
EXTERNAL NAME PPROGLIB/GEMC911 PARAMETER STYLE GENERAL
Because normally the constant [literal] for the second argument would
be typed as VARCHAR, one could imagine possibly that the SQL had
dismissed that PROCEDURE definition via /function resolution/ and thus
just called the existing program, same as when no declaration was
available. And that would explain the same effect for both CALLs.
However in my experience, the SQL never defers to an undefined call when
at least one routine definition exists [¿with the same number of
parameters?]; be that a declared or created procedure.
FWiW the parameter style and allowance for NULL seem in conflict for
the LANGUAGE CL. The identical CREATE PROCEDURE is not prevented in
v5r3 [either]. Nonetheless, should probably use the RETURNS NULL ON
NULL INPUT or the equivalent synonym because the CL would not know of NULL.?
I created a command, and defined the parms correctly, but can I
"execute" the command from SQL???
Yes, but the described issue should still be investigated. And the
same origin for the described difficulty might impact the attempt to
circumvent by using a command, because the command string is going to be
another long string. Here is an example [using an undeclared procedure
call to the CL command string processor QSYS/QCMDEXC]:
CALL QSYS.QCMDEXC
('GEMLIB/GEMC911CMD APPID(ACTQRY8) APPDESC(''Some text'')'
/*....+....1....+....2....+....3....+....4.\...+....5.\..*/
, 0000000053.00000) -- adjust 2nd parm to reflect CmdStrLen
What do I need to do to get the data not to truncate?
Is the client truncating the data somehow; e.g. perhaps not really
using a literal? How is the request being made from the client? Does
the client perhaps know there is no routine defined, and transforms the
request into a CL CALL instead? That would
There is an API that will show the last SQL run in the [server] job.
I recall that information is available from within iNav; I do not
recall where, probably jobs vs database. That information might be
telling. I do not recall how helpful information might be for a CALL,
from the details logged by the DB monitor.
Regards, Chuck
midrange.com
