DB2 Multi Value Parameter

T

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

G.S.

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
Click to expand...

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

Arne Vajhøj

Trevisc said:
I am trying to create a parameter that is one long varchar but that
will be used in a SQL statement IN function:
Click to expand...
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(?) ");
Click to expand...
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);
Click to expand...
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).
Click to expand...
I am trying to get this to work as a paramaterized query instead of
passing in the string into a stored proc
Click to expand...

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

G.S.

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 -
Click to expand...

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,
 
A

Arne Vajhøj

G.S. said:
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,
Click to expand...

Yes.

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

Arne
 
P

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
 
Top