RE: SQL CLI Parameter Value Issue

[Arturo_Duarte@xxxxxxxxxxxxxxx]

Scott, 

in your CLI example published sometime ago, you had a RPGLE program named 
WORKDAYS, that you compiled as CRTSQLRPGI WORKDAYS SRCFILE(xxx/QRPGLESRC) 
DBGVIEW(*SOURCE). Your example did not mentione to create a stored 
procedure, and have program READDAYS call WORKDAYS. I didn't include 
CREATE PROCEDURE statement since the RPG program I am calling that returns 
a results set was compiled as an RPGMOD using the CRTSQLRPGI statement.

I included the Procedure Interface of the called program below:
>      D Yestmnt1R       PI
>      D  itestNum                      9a   Const
>      D**
>      D  iTaxYear                      4a   Const
>      D  iReqSystem                    6a   Const
>      D  iTo_TaxYr                     4a   Const
>      D  iLendFlag                     4a   Const
>      D  iService_ID                   6a   Const 







rpg400-l-request@xxxxxxxxxxxx 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/02/2007 03:14 AM
Please respond to
rpg400-l@xxxxxxxxxxxx


To
rpg400-l@xxxxxxxxxxxx
cc

Subject
RPG400-L Digest, Vol 6, Issue 959








message: 2
date: Mon, 01 Oct 2007 17:51:31 -0500
from: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
subject: Re: SQL CLI Parameter Value Issue

Hello Arturo,

Can you post the CREATE PROCEDURE statement you used?  I can't reproduce 
the problem you described.  I tried your code, but I get all 9 digits 
without any errors.

Unfortunately, I had to fill in a lot of the details myself to try it 
out, since you didn't include everything in the message you posted, so I 
don't know if I'm comparing apples to apples -- and that's why I wanted 
the CREATE PROCEDURE statement that you used.

Also... have you considered using SQLBindParam() instead of 
SQLBindParameter()?  I've always used SQLBindPAram() in the past, and it 
works nicely for me.   Of course, it doesn't allow output parms, so 
that'd be one reason why you should use SQLBindParameter() instead.


Arturo_Duarte@xxxxxxxxxxxxxxx wrote:
> Greetings. 
>
> I am calling a Stored Procedure passing four character parameters. The 
> first parameter is of 9 bytes. When passing the value of '000001204', 
the 
> Stored Procedure shows the value '000001200'. Here is my CLI code:
>      D  testNum        S              9a
>      D  Frm_TXYear     S              4a
>      D  ReqSystem      S              6a
>      D  Too_TXYear     S              4a   inz(*blanks)      Optional
>      D  LenDFlag       S              4a   inz(*blanks)      Optional
>      D  Service_ID     S              6a   inz(*blanks)      Optional 
>
>       /free
>
>            EvalR testNum    = '000000000' + %Trim('1204') ;
>            Eval Frm_TXYear  = '2007' ;
>            Eval ReqSystem   = 'SPEECH' ;
>
>            SQLAllocEnv(env); 
>            SQLAllocConnect(env: conn);
>
>            SQLSetConnectAttrI( conn
>                              : SQL_ATTR_DBC_SYS_NAMING
>                              : SQL_TRUE
>                              : %size(SQLINTEGER) );
>
>            SQLConnect( conn
>                      : '*LOCAL'
>                      : SQL_NTS
>                      : *NULL
>                      : SQL_NTS
>                      : *NULL
>                      : SQL_NTS );
>
>            SQLAllocStmt( conn : stmt );
>            SQLPrepare(stmt :'Call YESTMNT1R(?,?,?,?,?,?)' :SQL_NTS) ;  
>
>            SQLBindParameter(stmt :1 :SQL_PARAM_INPUT
>                            :SQL_CHAR
>                            :SQL_CHAR
>                            :9 :0
>                            :%Addr(testNum)
>                            :%Size(testNum)
>                            :%len(testNum)); 
>            SQLBindParameter(stmt :2 :SQL_PARAM_INPUT
>                            :SQL_CHAR   :SQL_CHAR :4 :0 
:%Addr(Frm_TXYear)
>                            :%Size(Frm_TXYear)  :%len(Frm_TXYear));
>
>            SQLBindParameter(stmt :3 :SQL_PARAM_INPUT
>                            :SQL_CHAR   :SQL_CHAR :6 :0 :%Addr(ReqSystem)
>                            :%Size(ReqSystem)  :%len(ReqSystem));
>
>            SQLBindParameter(stmt :4 :SQL_PARAM_INPUT
>                            :SQL_CHAR   :SQL_CHAR :4 :0 
:%Addr(Too_TXYear)
>                            :%Size(Too_TXYear) :%len(Too_TXYear));
>
>            SQLBindParameter(stmt :5 :SQL_PARAM_INPUT
>                            :SQL_CHAR   :SQL_CHAR :4 :0 :%Addr(LenDFlag)
>                            :%Size(LenDFlag)  :%len(LenDFlag));
>
>            SQLBindParameter(stmt :6 :SQL_PARAM_INPUT
>                            :SQL_CHAR   :SQL_CHAR :6 :0 
:%Addr(Service_ID)
>                            :%Size(Service_ID)  :%len(Service_ID));
>
>            SQLExecute(stmt); 
> ...
>
> The prototype for the called procedure is 
>      D Yestmnt1R       PI
>      D  itestNum                      9a   Const
>      D**
>      D  iTaxYear                      4a   Const
>      D  iReqSystem                    6a   Const
>      D  iTo_TaxYr                     4a   Const
>      D  iLendFlag                     4a   Const
>      D  iService_ID                   6a   Const 
>
>
> Why is the value of testNum passing as '000001200' instead of 
'000001204'?
> Thank you
> Art Duarte
>
>
> ======================================================================
>
> Confidentiality Notice: The information contained in and transmitted 
with this communication is strictly confidential, is intended only for the 
use of the intended recipient, and is the property of Countrywide 
Financial Corporation or its affiliates and subsidiaries. If you are not 
the intended recipient, you are hereby notified that any use of the 
information contained in or transmitted with the communication or 
dissemination, distribution, or copying of this communication is strictly 
prohibited by law. If you have received this communication in error, 
please immediately return this communication to the sender and delete the 
original message and any copy of it in your possession.
> ======================================================================


======================================================================

Confidentiality Notice: The information contained in and transmitted with this communication is strictly confidential, is intended only for the use of the intended recipient, and is the property of Countrywide Financial Corporation or its affiliates and subsidiaries. If you are not the intended recipient, you are hereby notified that any use of the information contained in or transmitted with the communication or dissemination, distribution, or copying of this communication is strictly prohibited by law. If you have received this communication in error, please immediately return this communication to the sender and delete the original message and any copy of it in your possession.

======================================================================