DB2 Multi Value Parameter

[Trevisc]

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

[G.S.]

On Sep 4, 12:27*pm, Trevisc <[e-mail]:

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

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

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

[G.S.]

On Sep 4, 8:34*pm, Arne Vajhøj <[e-mail]:

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,

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

G.S. wrote:

On Sep 4, 8:34 pm, Arne Vajhøj <[e-mail]:

>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

[Peter Morris]

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