Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10

    Unanswered: Parameterized queries and functions

    Hello,
    I'm running into some very strange behavior when trying to use certain string manipulation functions in combination with parameterized queries. My environment is vb.net accessing DB2 for Z/OS using OLEDB Provider for DB2.
    The strange behavior is this, I can run a non parameterized query using the LEFT() function with no problem. Example:
    select col1, left(col2, 5) as col2 from table1 where col1 = '5';

    I can also run a parameterized query with no problem without using the LEFT() function. Example:
    select col1, col2 from table1 where col1 = ?

    The problem arises when trying to do both (run parameterized query that uses the LEFT() function) Example:
    select col1, left(col2, 5) as col2 from table1 where col1 = ?

    This query fails with following error/sql code:

    System.Data.OleDb.OleDbException: THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS SQLSTATE: 07001, SQLCODE: -313

    I've tested this with different functions and it appears some work and some don't. For example replace left(col1,5) with substr(col1,1,5) and it works! Any insight into this weird behavior would be greatly appreciated.
    Thanks
    Seamus

  2. #2
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    Sorry forgot to post db2 version info:
    DB2 for Z/OS V8 (compatibility mode?)
    .NET 1.1
    OLE DB Provider for DB2: Whatever version ships with Host Integration Server 2006?

  3. #3
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    To give a more concrete example of the problem, here's some code examples:

    ' Simple query using LEFT() function - Runs with no problem
    ' ------------------------------------------------------------------
    dim sqlText as String = "SELECT col1, LEFT(col2, 5) AS col2 FROM table1 WHERE col1 = '5';"
    Dim conn as New OleDbConnection(connString)
    conn.Open()
    Dim cmd As OleDbCommand = conn.CreateCommand
    cmd.CommandText = sqlText
    Dim rdr as OleDbDataReader = cmd.ExecuteReader()
    ' iterate over resultset


    ' Simple parameterized query excluding LEFT() function call - Runs with no problem
    ' ------------------------------------------------------------------
    dim sqlText as String = "SELECT col1, col2 FROM table1 WHERE col1 = ?;"
    dim paramValue as String = "5"
    Dim conn as New OleDbConnection(connString)
    conn.Open()
    Dim cmd As OleDbCommand = conn.CreateCommand
    cmd.CommandText = sqlText
    cmd.Parameters.Add("", paramValue)
    Dim rdr as OleDbDataReader = cmd.ExecuteReader()
    ' iterate over resultset



    ' Parameterized query including LEFT() function call - Fails with SQLCODE: -313
    ' ------------------------------------------------------------------
    dim sqlText as String = "SELECT col1, LEFT(col2, 5) AS col2 FROM table1 WHERE col1 = ?;"
    dim paramValue as String = "5"
    Dim conn as New OleDbConnection(connString)
    conn.Open()
    Dim cmd As OleDbCommand = conn.CreateCommand
    cmd.CommandText = sqlText
    cmd.Parameters.Add("", paramValue)
    Dim rdr as OleDbDataReader = cmd.ExecuteReader()
    ' !! fails here with following stacktrace:

    System.Data.OleDb.OleDbException: THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS SQLSTATE: 07001, SQLCODE: -313
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method)
    at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior)
    at scrapApp.SQLTroubleShooting.btnOK_Click(Object sender, EventArgs e)
    in \\xxx.yyy.zzz\wwwroot$\scrapApp\SQLTroubleShooting .aspx.vb:line 70



    I've tried different flavors of adding the parameter explicitly including the param datatype i.e.
    cmd.Parameters.Add("", OleDbType.Char).Value = paramValue
    The datatype of the parameterized column is char(6).
    I've trid specifying it as OleDbType.char, OleDbType.WChar, OleDbType.VarChar, and OleDbType.VarWChar
    Still no luck and still scratching my head!!!

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Looks strange. I suggest you open a PMR with IBM support.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    Quote Originally Posted by stolze
    Looks strange. I suggest you open a PMR with IBM support.
    Thanks a lot for the reply. Strange indeed. I've never gone through the whole PMR process with IBM, that should be interesting.

    I'm thinking it might be a bug with the Ole Db provider (Microsoft in this case).
    It looks like they've had similar looking bugs in the past with previous versions of their Ole Db provider for DB2:

    http://support.microsoft.com/kb/819528

    It would help narrow down the origin of this problem if anybody else could reproduce (or not reproduce) this behavior using a different provider.

    Thanks
    Seamus

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Microsoft documentation says that
    The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

    SELECT * FROM Customers WHERE CustomerID = ?

    Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.
    Try
    Code:
    cmd.Parameters.Add(paramValue)
    instead of
    Code:
    cmd.Parameters.Add("", paramValue)
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    Quote Originally Posted by n_i
    Microsoft documentation says that


    Try
    Code:
    cmd.Parameters.Add(paramValue)
    instead of
    Code:
    cmd.Parameters.Add("", paramValue)
    Thanks a lot for the reply. I did come across that documentation while searching the Ole Db provider documentation that ships with HIS 2006 but unfortunately, the suggested fix doesn't work.
    The single argument version of the Parameters.Add method takes as an argument a parameter of type OleDbParameter so passing in the parameter value only gives the following Exception:

    System.InvalidCastException: The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.
    at System.Data.OleDb.OleDbParameterCollection.Validat eType(Object value)
    at System.Data.OleDb.OleDbParameterCollection.Add(Obj ect value)
    at scrapApp.SQLTroubleShooting.btnOK_Click(Object sender, EventArgs e)

    I think the "named parameters not supported" refers to not being able to do something like the following doesn't it?

    "select col1 from table1 where col2 = aram1"
    parameters.add("param1", someValue)


    As mentioned, running parameterized queries isn't a problem by itself, it's only when I introduce certain SQL functions into the query that cause it to blow up. I'm now at the point of taking shots in the dark trying things like
    "select col1, left(col2, ?) as col2 from table where col1 = ?"
    parameters.add("", 3) '<-- add len arg of LEFT() function as param???
    parameters.add("", 6)

    and many other variations...still no luck.
    It's just bugging me at this point

  8. #8
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    Sorry, my parameter name in the example above was replaced by a smiley face

    It was supposed to be : param1 (minus the space between : and param)

    btw, is there a way to escape that sequence?

  9. #9
    Join Date
    Dec 2003
    Location
    SoCal
    Posts
    10
    ahh hah, "disable smilies in text"...

    sql statment should have read:

    select col1 from table1 where col2 = :param1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •