By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

DB2 Multi Value Parameter

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.