MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

Re: SQL performing a CALL with parms...



fixed

On 27-Aug-2014 14:55 -0500, Gqcy wrote:
we are executing a one line SQL script:

CALL SYS001C PARM('parm1', 'parm2')

That is a CL command-string, a CALL command invocation, not a SQL CALL. From where [what script processor] is the script being run? If composed as part of an SQL script [a semicolon following the statement is optional], should not the record be prefixed with 'CL:' [without the apostrophes] to identify to the script processor that the request should be sent to the CL command processor rather than processed by the SQL statement processor?


the parms are varying length on the input SQL...

The two parameters, if composed in a SQL CALL [instead of the CL CALL], would be considered to be varying length strings; e.g. as an SQL CALL:
CALL SYS001C ('parm1', 'parm2') ;

But the routine definition and parameter definitions are not given. If that CALL was an SQL [like just above versus as shown in the intro], and the _routine name_ SYS001C is registered to the SQL as an External Stored Procedure, then what was the CREATE PROCEDURE that had defined that CLP to the SQL as a callable routine?

on the CL I have identified parm1 as 18 char (was 10 char), and
parm2 as 64 char (was 32 char).

The CL does not have a varying string data type. If the SQL invokes a CLP via an SQL CALL using variable length string arguments, then the CLP must declare the two-byte prefix that represents the length attribute of the string data.

we have recently been changing the CL to accept different length
parms, and it appears that we are still receiving the old lengths...
and.... won't accept the longer length parms...

If the CALL was an SQL CALL and the registration of the CLP to the SQL as an External Stored Procedure has not been dropped and re-created [¿or altered with ALTER PROCEDURE request?], then the SQL would correctly be calling the CLP with the "old lengths"; that is of course, what the SQL was told to do, according to the contractual agreement established between the program and the SQL when defined using the CREATE PROCEDURE.

I get a "conversion error ... 9 -- Truncation when mapping a variable
or constant to a character or binary parameter on a CALL statement,
or when using a character or graphic variable in a GET DESCRIPTOR or
SET DESCRIPTOR statement."

What SQL request exhibits that error? From the subject we might infer an SQL CALL, but there is nowhere in this message showing what that CALL might be.

There are at least four USEnglish SQL messages including "onversion error" within the first-level text. Why not just offer\give the actual message identifier [or at least the SQLcode] to the readers who are willing to help, so they do not have to go looking for those details.? As well, if the message appears in a joblog, then the /context/ of the message, as provided by a the output from a spooled LOG(4 0 *SECLVL) joblog is often quite beneficial; that of course, includes the message identifier. The additional /context/ is what request precipitated the failure; somewhat difficult to comment on the failure without the necessary context of the failure to fill-in the details.

FWiW, when searching for that text, one might find these several messages, after which review of the 2nd-level text might find an apparent match being the -302 condition:
SQL0304 30 Conversion error in assignment to host variable &2.
SQL0406 30 Conversion error on assignment to column &2.
SQL0446 30 Conversion error in assignment of argument &2.
SQL0302 30 Conversion error on input host variable or parameter &2.
... 9 -- Truncation when mapping a host variable or constant to a character or binary parameter on a CALL statement.


on my dumpclpgm

So the Dump CL Program (DMPCLPGM) output apparently shows the following, but my email\news reader shows only 17 bytes for the first and 26 of the second; I have added a scale line above the data as apparent output from the dump:

_...................._ ....+....1....+....2....+....3....+....4....+

&parm1 *CHAR 18 'parm1 parm2val'
&parm2 *CHAR 64 'parm2value '


So apparently the invocation was, instead of what was shown in the introduction, was one of?:

CALL SYS001C PARM('parm1', 'parm2value') /* if CL */
CALL SYS001C('parm1', 'parm2value') /* if SQL */






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact