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 = GetCurrentTitle s();
//Below is for Test
string queryString = "45322,32222,33 344,55555";

dataset catalogDS = new dataset();

StringBuilder sb = new StringBuilder() ;
sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHER E TITLE_NO in(?) ");

IBM.Data.DB2.DB 2Command ProcedureComman d =
new IBM.Data.DB2.DB 2Command(sb.ToS tring(),
_DB2Connection) ;
ProcedureComman d.CommandType = System.Data.Com mandType.Text;

IBM.Data.DB2.DB 2Parameter parameter = new
IBM.Data.DB2.DB 2Parameter("TIT LE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Direc tion = System.Data.Par ameterDirection .Input;
parameter.Size = queryString.Len gth;
parameter.Value = queryString;
ProcedureComman d.Parameters.Ad d(parameter);

IBM.Data.DB2.DB 2DataReader dataReader =
ProcedureComman d.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 <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 = GetCurrentTitle s();
//Below is for Test
string queryString = "45322,32222,33 344,55555";

dataset catalogDS = new dataset();

* * * * * * StringBuilder sb = new StringBuilder() ;
* * * * * * sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME, ");
* * * * * * sb.Append("FROM TITLES ");
* * * * * * sb.Append("WHER E TITLE_NO in(?) ");

IBM.Data.DB2.DB 2Command ProcedureComman d =
new IBM.Data.DB2.DB 2Command(sb.ToS tring(),
_DB2Connection) ;
ProcedureComman d.CommandType = System.Data.Com mandType.Text;

IBM.Data.DB2.DB 2Parameter parameter = new
IBM.Data.DB2.DB 2Parameter("TIT LE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Direc tion = System.Data.Par ameterDirection .Input;
parameter.Size = queryString.Len gth;
parameter.Value = queryString;
ProcedureComman d.Parameters.Ad d(parameter);

IBM.Data.DB2.DB 2DataReader dataReader =
ProcedureComman d.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,33 344,55555";
dataset catalogDS = new dataset();
StringBuilder sb = new StringBuilder() ;
sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHER E TITLE_NO in(" + queryString + ") ");
IBM.Data.DB2.DB 2Command ProcedureComman d =
new IBM.Data.DB2.DB 2Command(sb.ToS tring(),
_DB2Connection) ;
ProcedureComman d.CommandType = System.Data.Com mandType.Text;

/* don't need this anymore
IBM.Data.DB2.DB 2Parameter parameter = new
IBM.Data.DB2.DB 2Parameter("TIT LE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Direc tion = System.Data.Par ameterDirection .Input;
parameter.Size = queryString.Len gth;
parameter.Value = queryString;
ProcedureComman d.Parameters.Ad d(parameter);
*/
IBM.Data.DB2.DB 2DataReader dataReader =
ProcedureComman d.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,33 344,55555";

dataset catalogDS = new dataset();

StringBuilder sb = new StringBuilder() ;
sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHER E TITLE_NO in(?) ");

IBM.Data.DB2.DB 2Parameter parameter = new
IBM.Data.DB2.DB 2Parameter("TIT LE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Direc tion = System.Data.Par ameterDirection .Input;
parameter.Size = queryString.Len gth;
parameter.Value = queryString;
ProcedureComman d.Parameters.Ad d(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 <a...@vajhoej.d kwrote:

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,33 344,55555";

dataset catalogDS = new dataset();

* * * * * * StringBuilder sb = new StringBuilder() ;
* * * * * * sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME,");
* * * * * * sb.Append("FROM TITLES ");
* * * * * * sb.Append("WHER E TITLE_NO in(?) ");
IBM.Data.DB2.DB 2Parameter parameter = new
IBM.Data.DB2.DB 2Parameter("TIT LE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Direc tion = System.Data.Par ameterDirection .Input;
parameter.Size = queryString.Len gth;
parameter.Value = queryString;
ProcedureComman d.Parameters.Ad d(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 <a...@vajhoej.d kwrote:

>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,33 344,55555";
dataset catalogDS = new dataset();
StringBuilder sb = new StringBuilder() ;
sb.Append("SELE CT TITLE_NO, SEQ_NO, TITLE_NAME, ");
sb.Append("FROM TITLES ");
sb.Append("WHER E TITLE_NO in(?) ");
IBM.Data.DB2. DB2Parameter parameter = new
IBM.Data.DB2. DB2Parameter("T ITLE_NO", IBM.Data.DB2.DB 2Type.VarChar);
parameter.Dir ection = System.Data.Par ameterDirection .Input;
parameter.Siz e = queryString.Len gth;
parameter.Val ue = queryString;
ProcedureComm and.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