how to get parameter value from dynamic select statement ?

[t2581]

Hi,

running DB2 UDB 7.2 F.P 12 on WIndows

Developers debug old application
and need to get value passed to db2 by application

I run db2cli trace and event monitor and both cases I get

SELECT T1.* FROM
UCIT.UPS_INVOIC
MSG T1, UCIT. UPS_MSG_PRICEGRP
T2, UCIT.UPS_PRICE_CUST_GRP T3
WHERE .T1.MSG_NUM = T2.MSG_NUM AND.T2.TYPE = 'I'
AND.T2.GROUP_NAME = T3.GROUP_NA ME AND.T3.CUSTOMER_NUMBER = ?
AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_TS >= ?

I need values to get values of T1.EFFECTIVE_TS <= ? AND
T1.EXPIRATION_TS

[Knut Stolze]

t2***@hotmail.com wrote:

Hi,

running DB2 UDB 7.2 F.P 12 on WIndows

Developers debug old application
and need to get value passed to db2 by application

I run db2cli trace and event monitor and both cases I get

SELECT T1.* FROM
UCIT.UPS_INVOIC
MSG T1, UCIT. UPS_MSG_PRICEGRP
T2, UCIT.UPS_PRICE_CUST_GRP T3
WHERE .T1.MSG_NUM = T2.MSG_NUM AND.T2.TYPE = 'I'
AND.T2.GROUP_NAME = T3.GROUP_NA ME AND.T3.CUSTOMER_NUMBER = ?
AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_TS >= ?

I need values to get values of T1.EFFECTIVE_TS <= ? AND
T1.EXPIRATION_TS

This statement is run through SQLPrepare(), right? The result is a
statement handle ("hstmt" in the CLI trace). You should see a few
SQLBindParameter() calls following that and then a SQLExecute(). Have a
look at the stuff in the SQLExecute as it should tell you the actual
parameters used for the execution of the statement.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[t2581]

the is no SQLExecute

only SQLBindParameter and SQLExecDirectW
SQLBindParameter( hStmt=2:2, iPar=1, fParamType=SQL_PARAM_INPUT,
fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c755c8, cbValueMax=128, pcbValue=&00c12ecc )
---> Time elapsed - +3.770000E-004 seconds

SQLBindParameter( )
<--- SQL_SUCCESS Time elapsed - +3.856000E-003 seconds

SQLBindParameter( hStmt=2:2, iPar=2, fParamType=SQL_PARAM_INPUT,
fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c76d18, cbValueMax=128, pcbValue=&01c75484 )
---> Time elapsed - +3.490000E-004 seconds

SQLBindParameter( )
<--- SQL_SUCCESS Time elapsed - +3.862000E-003 seconds

SQLBindParameter( hStmt=2:2, iPar=3, fParamType=SQL_PARAM_INPUT,
fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c779d0, cbValueMax=128, pcbValue=&01c754dc )
---> Time elapsed - +3.340000E-004 seconds

SQLBindParameter( )
<--- SQL_SUCCESS Time elapsed - +4.019000E-003 seconds

SQLExecDirectW( hStmt=2:2, pszSqlStr="SELECT T1.* FROM
UCIT.UPS_INVOICE_MSG T1, UCIT.UPS_MSG_PRICEGRP T2,
UCIT.UPS_PRICE_CUST_GRP T3 WHERE T1.MSG_NUM = T2.MSG_NUM AND T2.TYPE =
'I' AND T2.GROUP_NAME = T3.GROUP_NAME AND T3.CUSTOMER_NUMBER = ? AND
T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_TS >= ?" -
X"530045004C004500430054002000540031002E002A002000 460052004F004D00200055004300490054002E005500500053 005F0049004E0056004F004900430045005F004D0053004700 2000540031002C002000200055004300490054002E00550050 0053005F004D00530047005F00500052004900430045004700 520050002000540032002C00200055004300490054002E0055 00500053005F00500052004900430045005F00430055005300 54005F00470052005000200054003300200057004800450052 00450020000900540031002E004D00530047005F004E005500 4D0020003D002000540032002E004D00530047005F004E0055 004D00200041004E0044000900540032002E00540059005000 450020003D002000270049002700200041004E004400090054 0032002E00470052004F00550050005F004E0041004D004500 20003D002000540033002E00470052004F00550050005F004E 0041004D004500200041004E0044000900540033002E004300 5500530054004F004D00450052005F004E0055004D00420045 00520020003D0020003F00200041004E004400200054003100 2E004500460046004500430054004900560045005F00540053 0020003C003D0020003F00200041004E004400200054003100 2E00450058005000490052004100540049004F004E005F0054 00530020003E003D0020003F00",
cbSqlStr=256 )
---> Time elapsed - +3.530000E-004 seconds
( StmtOut="SELECT T1.* FROM UCIT.UPS_INVOICE_MSG T1,
UCIT.UPS_MSG_PRICEGRP T2, UCIT.UPS_PRICE_CUST_GRP T3 WHERE T1.MSG_NUM
= T2.MSG_NUM AND T2.TYPE = 'I' AND T2.GROUP_NAME = T3.GROUP_NAME AND
T3.CUSTOMER_NUMBER = ? AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_TS >=
?" )