473,240 Members | 1,808 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

DB2 Multi Value Parameter

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();

StringBuilder sb = new StringBuilder();
sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHERE TITLE_NO in(?) ");

IBM.Data.DB2.DB2Command ProcedureCommand =
new IBM.Data.DB2.DB2Command(sb.ToString(),
_DB2Connection);
ProcedureCommand.CommandType = System.Data.CommandType.Text;

IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);

IBM.Data.DB2.DB2DataReader dataReader =
ProcedureCommand.ExecuteReader();
The problem is the string is passed in as a Varchar so DB2 puts single
quotes around it and the TITLE_NO field is an integer. I can do:
WHERE CAST(TITLE_NO as VARCHAR) in (?) but then it can't use the
index that was created for this field (over 6 million rows).

The results in queryString can be up to 500 title numbers coming back
so doing some kind of a static parameter query would be a nightmare
(too many ? literally :) ).

I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc because this is a mainframe
environment and I don't want to do any cobol programming if i can help
it. (no offense to you Cobol programmers)

Any help on this is appreciated!

Trevis
Sep 4 '08 #1
5 5270
On Sep 4, 12:27*pm, Trevisc <trev...@gmail.comwrote:
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();

* * * * * * StringBuilder sb = new StringBuilder();
* * * * * * sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME, ");
* * * * * * sb.Append("FROM TITLES ");
* * * * * * sb.Append("WHERE TITLE_NO in(?) ");

IBM.Data.DB2.DB2Command ProcedureCommand =
new IBM.Data.DB2.DB2Command(sb.ToString(),
_DB2Connection);
ProcedureCommand.CommandType = System.Data.CommandType.Text;

IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);

IBM.Data.DB2.DB2DataReader dataReader =
ProcedureCommand.ExecuteReader();

The problem is the string is passed in as a Varchar so DB2 puts single
quotes around it and the TITLE_NO field is an integer. *I can do:
WHERE CAST(TITLE_NO as VARCHAR) in (?) * but then it can't use the
index that was created for this field (over 6 million rows).

The results in queryString can be up to 500 title numbers coming back
so doing some kind of a static parameter query would be a nightmare
(too many ? literally :) ).

I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc because this is a mainframe
environment and I don't want to do any cobol programming if i can help
it. *(no offense to you Cobol programmers)

Any help on this is appreciated!

Trevis
Isn't the following possible?
string queryString = "45322,32222,33344,55555";
dataset catalogDS = new dataset();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHERE TITLE_NO in(" + queryString + ") ");
IBM.Data.DB2.DB2Command ProcedureCommand =
new IBM.Data.DB2.DB2Command(sb.ToString(),
_DB2Connection);
ProcedureCommand.CommandType = System.Data.CommandType.Text;

/* don't need this anymore
IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);
*/
IBM.Data.DB2.DB2DataReader dataReader =
ProcedureCommand.ExecuteReader();
Sep 4 '08 #2
Trevisc wrote:
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 = "45322,32222,33344,55555";

dataset catalogDS = new dataset();

StringBuilder sb = new StringBuilder();
sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHERE TITLE_NO in(?) ");
IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);
The problem is the string is passed in as a Varchar so DB2 puts single
quotes around it and the TITLE_NO field is an integer. I can do:
WHERE CAST(TITLE_NO as VARCHAR) in (?) but then it can't use the
index that was created for this field (over 6 million rows).
I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc
Parameters are intended to be 1 value.

I am not aware of any database/ADO.NET provider that can do what
you want.

Bad old dynamic SQL !

Using a SP doesn't solve the problem.

Arne
Sep 5 '08 #3
On Sep 4, 8:34*pm, Arne VajhÝj <a...@vajhoej.dkwrote:
Trevisc wrote:
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 = "45322,32222,33344,55555";
dataset catalogDS = new dataset();
* * * * * * StringBuilder sb = new StringBuilder();
* * * * * * sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME,");
* * * * * * sb.Append("FROM TITLES ");
* * * * * * sb.Append("WHERE TITLE_NO in(?) ");
IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);
The problem is the string is passed in as a Varchar so DB2 puts single
quotes around it and the TITLE_NO field is an integer. *I can do:
WHERE CAST(TITLE_NO as VARCHAR) in (?) * but then it can't use the
index that was created for this field (over 6 million rows).
I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc

Parameters are intended to be 1 value.

I am not aware of any database/ADO.NET provider that can do what
you want.

Bad old dynamic SQL !

Using a SP doesn't solve the problem.

Arne- Hide quoted text -

- Show quoted text -
He could have a sproc, asuming DB2 implements the equivalent of EXEC,
available under MS SQL Server like this:
CREATE PROC
@param1 varchar(4000)
AS
DECLARE @SQL varchar(8000)
SET @SQL= 'SELECT
....
WHERE WHERE TITLE_NO in(' + @Param1 + ') '

EXEC @Param1

Not the optimal solution, as far as I know, but possible,
Sep 5 '08 #4
G.S. wrote:
On Sep 4, 8:34 pm, Arne VajhÝj <a...@vajhoej.dkwrote:
>Trevisc wrote:
>>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 = "45322,32222,33344,55555";
dataset catalogDS = new dataset();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHERE TITLE_NO in(?) ");
IBM.Data.DB2.DB2Parameter parameter = new
IBM.Data.DB2.DB2Parameter("TITLE_NO", IBM.Data.DB2.DB2Type.VarChar);
parameter.Direction = System.Data.ParameterDirection.Input;
parameter.Size = queryString.Length;
parameter.Value = queryString;
ProcedureCommand.Parameters.Add(parameter);
The problem is the string is passed in as a Varchar so DB2 puts single
quotes around it and the TITLE_NO field is an integer. I can do:
WHERE CAST(TITLE_NO as VARCHAR) in (?) but then it can't use the
index that was created for this field (over 6 million rows).
I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc
Parameters are intended to be 1 value.

I am not aware of any database/ADO.NET provider that can do what
you want.

Bad old dynamic SQL !

Using a SP doesn't solve the problem.

He could have a sproc, asuming DB2 implements the equivalent of EXEC,
available under MS SQL Server like this:
CREATE PROC
@param1 varchar(4000)
AS
DECLARE @SQL varchar(8000)
SET @SQL= 'SELECT
...
WHERE WHERE TITLE_NO in(' + @Param1 + ') '

EXEC @Param1

Not the optimal solution, as far as I know, but possible,
Yes.

But that is not the SP solving the problem - that is the dynamic SQL !

Arne
Sep 5 '08 #5
Loop through the values, add an additional clause to your SQL checking

title_no begins with param_1,
or
title_no contains ,param_1,
or
title_no ends with ,param_1

then you can loop through the values again with a normal FOR loop and set
the parameters to their values before executing.
Then you can fire the DB admin :-)

Pete

Sep 5 '08 #6

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

Similar topics

1
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...
0
by: VictorCorey | last post by:
Is it possible to use a multi-parameter query in .NET Here's the method Public Function SearchCatalog(ByVal searchString As String, ByVal allWords As String) As OleDbDataReade ' Create the...
10
by: Resant | last post by:
I have a query : Exec 'Select * From Receiving Where Code In (' + @pCode + ')' @pCode will contain more than one string parameter, eg : A1, A2, A3 How can i write that parameters, I try use :...
11
by: truckaxle | last post by:
I am trying to pass a slice from a larger 2-dimensional array to a function that will work on a smaller region of the array space. The code below is a distillation of what I am trying to...
4
by: entitledX | last post by:
Hi, I'm trying to use the HDF library to read a few HDF files that I need to process. The data in each file varies in rows, but the columns remain constant. Because of that, I had dynamically...
1
by: monadel | last post by:
Hi all, I am a beginner of SQL Server 2005. How do you display multi-values parameters with coma in the SQL Server 2005 reporting?. I believe we have to create a fucntion to do this, but how do...
137
by: mathieu.dutour | last post by:
Dear all, I want to do multiprecision floating point, i.e. I want to go beyond single precision, double precision and have quadruple precision, octuple precision and the like, and possibly with...
0
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...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just canít get any where on. My databse mostly includes...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youíll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

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.