RE: SQL CLI Parameter Value Issue

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.

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