473,698 Members | 2,579 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to get parameter value from dynamic select statement ?

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_PRICEGR P
T2, UCIT.UPS_PRICE_ CUST_GRP T3
WHERE .T1.MSG_NUM = T2.MSG_NUM AND.T2.TYPE = 'I'
AND.T2.GROUP_NA ME = T3.GROUP_NA ME AND.T3.CUSTOMER _NUMBER = ?
AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_T S >= ?

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

Nov 12 '05 #1
2 2619
t2***@hotmail.c om 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_PRICEGR P
T2, UCIT.UPS_PRICE_ CUST_GRP T3
WHERE .T1.MSG_NUM = T2.MSG_NUM AND.T2.TYPE = 'I'
AND.T2.GROUP_NA ME = T3.GROUP_NA ME AND.T3.CUSTOMER _NUMBER = ?
AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_T S >= ?

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


This statement is run through SQLPrepare(), right? The result is a
statement handle ("hstmt" in the CLI trace). You should see a few
SQLBindParamete r() 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
Nov 12 '05 #2
the is no SQLExecute

only SQLBindParamete r and SQLExecDirectW
SQLBindParamete r( hStmt=2:2, iPar=1, fParamType=SQL_ PARAM_INPUT,
fCType=SQL_C_CH AR, fSQLType=SQL_VA RCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c75 5c8, cbValueMax=128, pcbValue=&00c12 ecc )
---> Time elapsed - +3.770000E-004 seconds

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

SQLBindParamete r( hStmt=2:2, iPar=2, fParamType=SQL_ PARAM_INPUT,
fCType=SQL_C_CH AR, fSQLType=SQL_VA RCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c76 d18, cbValueMax=128, pcbValue=&01c75 484 )
---> Time elapsed - +3.490000E-004 seconds

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

SQLBindParamete r( hStmt=2:2, iPar=3, fParamType=SQL_ PARAM_INPUT,
fCType=SQL_C_CH AR, fSQLType=SQL_VA RCHAR, cbColDef=128, ibScale=0,
rgbValue=&01c77 9d0, cbValueMax=128, pcbValue=&01c75 4dc )
---> Time elapsed - +3.340000E-004 seconds

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

SQLExecDirectW( hStmt=2:2, pszSqlStr="SELE CT T1.* FROM
UCIT.UPS_INVOIC E_MSG T1, UCIT.UPS_MSG_PR ICEGRP 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_NUM BER = ? AND
T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_T S >= ?" -
X"530045004C004 500430054002000 540031002E002A0 02000460052004F 004D00200055004 300490054002E00 5500500053005F0 049004E0056004F 004900430045005 F004D0053004700 2000540031002C0 020002000550043 00490054002E005 500500053005F00 4D00530047005F0 050005200490043 004500470052005 000200054003200 2C0020005500430 0490054002E0055 00500053005F005 000520049004300 45005F004300550 0530054005F0047 005200500020005 400330020005700 480045005200450 020000900540031 002E004D0053004 7005F004E005500 4D0020003D00200 0540032002E004D 00530047005F004 E0055004D002000 41004E004400090 0540032002E0054 005900500045002 0003D0020002700 490027002000410 04E004400090054 0032002E0047005 2004F0055005000 5F004E0041004D0 0450020003D0020 00540033002E004 70052004F005500 50005F004E00410 04D004500200041 004E00440009005 40033002E004300 5500530054004F0 04D00450052005F 004E0055004D004 200450052002000 3D0020003F00200 041004E00440020 00540031002E004 500460046004500 430054004900560 045005F00540053 0020003C003D002 0003F0020004100 4E0044002000540 031002E00450058 005000490052004 100540049004F00 4E005F005400530 020003E003D0020 003F00",
cbSqlStr=256 )
---> Time elapsed - +3.530000E-004 seconds
( StmtOut="SELECT T1.* FROM UCIT.UPS_INVOIC E_MSG T1,
UCIT.UPS_MSG_PR ICEGRP 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_NUM BER = ? AND T1.EFFECTIVE_TS <= ? AND T1.EXPIRATION_T S >=
?" )

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
11806
by: Berend | last post by:
I am trying to pass multi values into a where clause with an in clause in a store procedure to use in a Crystal report. This can change depending on the user. Maybe there is another way to pass multi values. CREATE OR REPLACE PROCEDURE eva_sp_wrk014_spec_test ( p_eva_product_header_ids IN VARCHAR2, cur_spec_cd IN OUT sysadm.eva_pkg_wrk014_spec_test.ref_spec_spec_cd
4
24636
by: Robert Scheer | last post by:
Hi. I have a stored procedure on a Oracle 8.1.6 database that generates a dynamic sql statement. This stored procedure has an output parameter that needs to return a count from a view. I can generate and run the sql successfuly, but when I try to return the count I get errors. I am showing the relevant part of the procedure, since the rest of concatenations are used to compose the sql. CREATE OR REPLACE PROCEDURE getScoreEntries (user...
4
16978
by: Todd Perkins | last post by:
Hello all, surprisingly enough, this is my first newsgroup post, I usually rely on google. So I hope I have enough info contained. Thank you in advance for any help! Problem: I am getting this error when I try to pull up my edit page to display the current database information in the form, and then edit it on click:
10
3276
by: Tracy | last post by:
Dear all, I have encountered a problem in passing a VARCHAR parameter (which is a list of accepted values) to the IN predicate of a DB2 user-defined function. For example, I have a table (mytable) storing ppl names & their corresponding groups. I would like to create a UDF (myfunc) that select a list of ppl names from (mytable) who belong to some dynamic choices of groups. I have tried the following codings but it doesn't work if I
9
2693
by: serge | last post by:
/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio and run it once you've made sure there is no harmful code. Currently we have several stored procedures which final result is a select with several joins that returns many
0
2636
by: bughunter | last post by:
I found code from java - real bug with secondary parameter, should be Integer but called with String, But procedure completed without any errors and parameter correctly transformed to integer! Is't bug or "feature"? Is that documented? Because SQL0301N
3
5807
by: JJ | last post by:
When running an sql stored procedure, is it possible to pass in the sortexpression as a parameter? When I try it all the other parameters operate as expected, but the rows are not sorted. The paging works ok, but not the sorting. JJ My data layer has something like: SqlCommand cmd = new SqlCommand("Stored_Proc_Name", cn); cmd.CommandType = CommandType.StoredProcedure;
0
2565
by: Mark C. Stock | last post by:
"Mark C. Stock" <mcstockX@Xenquery .comwrote in message news:... | | "Berend" <Berend.Brinkhuis@evatone.comwrote in message | news:bdd9ac20.0401271301.22cdb65e@posting.google.com... | | I am trying to pass multi values into a where clause with an in clause | | in a store procedure to use in a Crystal report. This can change | | depending on the user. Maybe there is another way to pass multi | | values. | | | |
14
1875
by: bill | last post by:
Can someone please show me an example of passing a string value into an sql statement in vb 2005? Something like this is what I'm after: Dim sqlButton1 As String = "Select * from tblAssets where Asset_Tag = Me.cboAsset.Text" Thank you, Bill
5
5303
by: Trevisc | last post by:
Happy Thursday Everyone, I am trying to create a parameter that is one long varchar but that will be used in a SQL statement IN function: //string queryString = GetCurrentTitles(); //Below is for Test string queryString = "45322,32222,33344,55555"; dataset catalogDS = new dataset();
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8898
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7734
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4370
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3051
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2006
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.