Re: Date in External Stored Procedure not as expected...

Thanks Eric, Chuck, comments in line.

On 2012-02-02 1:34 PM, CRPence wrote:

On 02-Feb-2012 06:56 , Robert Rogerson wrote:

I have the following stored procedure:

CREATE PROCEDURE CANLIB/GETSALES (
IN LISTTYPE VARCHAR(1) ,
<<SNIP>>
IN STARTDATE DATE ,
IN ENDDATE DATE ,
IN DATERANGE VARCHAR(3) )
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
SPECIFIC CANLIB/GETSALES
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'CANLIB/SPR0055 '
PARAMETER STYLE SQL ;

If I start a SEP on SPR0055 and call GetSales from
iNav Run Sql Scripts

call getsales('I',1,0,'','','','','','',0,'','','0001-01-01','WTD');

When the program breaks (prior to the first executable statement) and
I press F5 (to execute the first statement) the value of the 12th
parameter, STARTDATE has a value of '2012-02-02' (today's date) even
though I specified ''.

Can anyone explain how or why STARTDATE is being populated with
'2012-02-02'?


I would expect an error message SQL0181 on the CALL; i.e. not even
having gotten into debug. However...

What was the value of the indicator variable for that same argument?

Debug showed a value of 0 (zero)


What is stored as the value of the parameter is not relevant if the
indicator suggests a NULL value or an error.

My understanding is that a NULL value has a negative value and since zero
is not negative the value is not NULL


What happens when called from STRSQL [which should be fine, since all
are IN parameters]?

When I run the statement in STRSQL I get the SQL0181 error you expected.
I reran the call in both sessions as

call getsales('I',1,0,'','','','','','',0,'',NULL,NULL,'WTD');

and in both cases the indicator variable was set to -1 as expected.

Maybe iNav database run SQL feature "helps" by
passing NULL or DEFAULT as the argument? That "help" may be a side
effect of the capability provided to deal with INOUT and OUT parameters.

Regards, Chuck

I will modify the code to handle this problem/feature.

Thanks again,

Robert Rogerson