Topic
3 replies Latest Post - ‏2006-05-31T16:00:53Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic VB .NET db2command parameters only use order?

‏2006-05-24T15:48:57Z |
Trying VB .NET.

[code]CREATE PROCEDURE A(A INTEGER, OUT B CHAR(1)) BEGIN SET B = 'C'; END[/code]

In VB, opened a standard form, associated the .NET dll, in the code added Imports IBM.Data.DB2, and added the following code to form1.load.

[code] Dim DB As DB2Connection
Dim Command As DB2Command
Dim Parameter1 As DB2Parameter
Dim Parameter2 As DB2Parameter

DB = New DB2Connection("dbstring")
DB.Open()

Command = DB.CreateCommand
Command.CommandType = CommandType.StoredProcedure
Command.CommandText = "schema.A"

Parameter1 = New DB2Parameter("A", DB2Type.Integer, 0, ParameterDirection.Input, True, 0, 0, "A", DataRowVersion.Current, 0)
Parameter2 = New DB2Parameter("B", DB2Type.Char, 1, ParameterDirection.Output, True, 0, 0, "B", DataRowVersion.Current, "")

Command.Parameters.Add(Parameter1)
Command.Parameters.Add(Parameter2)

Command.ExecuteNonQuery()

MsgBox(Command.Parameters.Item("B").Value)

DB.Close()
[/code]
That works perfectly. Yet, if i change the order of the parameters That is, i change:
[code]Command.Parameters.Add(Parameter1)
Command.Parameters.Add(Parameter2)[/code]
to:
[code]Command.Parameters.Add(Parameter2)
Command.Parameters.Add(Parameter1)[/code]
I recieve an error:

An unhandled exception of type 'System.ArgumentException' occurred in microsoft.visualbasic.dll

Additional information: Argument 'Prompt' cannot be converted to type 'String'.

Through other testing, i think i saw that passing the name of the parameter is completely ignored, as only the order they are added is used.

Am i doing something wrong? Is name instead of order supported?

B.
Updated on 2006-05-31T16:00:53Z at 2006-05-31T16:00:53Z by SystemAdmin
  • bpgross
    bpgross
    627 Posts
    ACCEPTED ANSWER

    Re: VB .NET db2command parameters only use order?

    ‏2006-05-24T19:14:32Z  in response to SystemAdmin
    In the DB2 .NET docs, we have indicated that the CommandType.StoredProcedure is equivalent to "CALL <sp> ( ?, ? )" (where the # of ?'s depends on the number of parameters added). With parameter markers, the parameters must ALWAYS be added in the same order as they are referenced in the statement.

    If, you changed your CommandType to CommandText, and changed the text to "CALL A( @A, @B )", then you would be using named parameters, and would be free to add them in any order.

    Brent.
    • SystemAdmin
      SystemAdmin
      2826 Posts
      ACCEPTED ANSWER

      Re: VB .NET db2command parameters only use order?

      ‏2006-05-31T15:09:22Z  in response to bpgross
      Thanx for repling so quickly. (Sorry for the delay on my part.)

      The documentation:

      Developing=>Database Applications=>Programming Applications=>ADO .NET, OLE DB, and ODBC=>DB2 .NET Data Provider=>Programming Applications to use the DB2 .NET Data Provider=>Calling Stored Procedures

      states: "However, calling stored procedures is easier when you set CommandType to CommandType.StoredProcedure. In this case, you only need to specify the stored procedure name and any parameters."

      It makes no mention of this, and even encourages it's use, with no mention of this lack of functionality.

      Also:

      Reference=>APIs=>DB2 .NET Data Provider=>DB2Command Class=>Properties=>CommandType Property

      states: "When the CommandType property is set to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command then executes this stored procedure when you call one of the Execute methods (for example, ExecuteReader or ExecuteNonQuery).

      The Connection, CommandType and CommandText properties cannot be set if the current connection is performing an execute or fetch operation.

      When passing parameters to SQL statements or stored procedures called by a DB2Command, the DB2 .NET Data Provider supports named parameters or positioned parameters using parameter markers."

      I don't see any difference mentioned.

      ===============

      I am trying to get this to worked, as named parameters would be nice.

      CODE
      Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim DB As DB2Connection
      Dim Command As DB2Command
      Dim Parameter1 As DB2Parameter
      Dim Parameter2 As DB2Parameter
      DB = New DB2Connection(db connect string)
      DB.Open()

      Command = DB.CreateCommand
      Command.CommandType = CommandType.Text
      Command.CommandText = "CALL schema.A(@A, @B)"

      Parameter1 = New DB2Parameter("A", DB2Type.Integer, 0, ParameterDirection.Input, True, 0, 0, "A", DataRowVersion.Current, 0)
      Parameter2 = New DB2Parameter("B", DB2Type.Char, 1, ParameterDirection.Output, True, 0, 0, "B", DataRowVersion.Current, "")

      Command.Parameters.Add(Parameter1)
      Command.Parameters.Add(Parameter2)
      Command.ExecuteNonQuery()

      MsgBox(Command.Parameters.Item("B").Value)

      DB.Close()

      End Sub
      CODE
      Results in:

      "An unhandled exception of type 'System.IndexOutOfRangeException' occurred in ibm.data.db2.dll

      Additional information: An DB2Parameter with ParameterName '@B' is not contained by this DB2ParameterCollection."

      (As a side note "an DB2" should be "a DB2" the "n" is not required. A simple search in the db2 doocumentation shows this four times there as well. (I point this out as a help, not a complaint.))

      I'm going to play with it a little more, because knowing me, i'm missing something overhwelmmingly obvious.

      Thanx for the responses. I appreciate them.

      B.
      • SystemAdmin
        SystemAdmin
        2826 Posts
        ACCEPTED ANSWER

        Re: VB .NET db2command parameters only use order?

        ‏2006-05-31T16:00:53Z  in response to SystemAdmin
        OK, the error in the code i just posted, is that the at-sign (@) was missing from the named parameter.

        That is,

        CODE
        Parameter1 = New DB2Parameter("A", DB2Type.Integer, 0, ParameterDirection.Input, True, 0, 0, "A", DataRowVersion.Current, 0)
        Parameter2 = New DB2Parameter("B", DB2Type.Char, 1, ParameterDirection.Output, True, 0, 0, "B", DataRowVersion.Current, "")
        CODE

        Must instead be
        CODE
        Parameter1 = New DB2Parameter("@A", DB2Type.Integer, 0, ParameterDirection.Input, True, 0, 0, "A", DataRowVersion.Current, 0)
        Parameter2 = New DB2Parameter("@B", DB2Type.Char, 1, ParameterDirection.Output, True, 0, 0, "B", DataRowVersion.Current, "")
        CODE

        The at-sign must be used is the statement itself, so it knows it's a variable, and then must be repeated when creating the DB2Parameter. Is that intended?

        Either way, the "named" part of this parameter, is just the name of the host variable, and has nothing to do with the name of the parameter itself for the PROCEDURE.

        That is:

        CODE

        Command.CommandText = "CALL SCHEMA.A(@Z, @X)"

        Parameter1 = New DB2Parameter("@Z", DB2Type.Integer, 0, ParameterDirection.Input, True, 0, 0, "A", DataRowVersion.Current, 0)
        Parameter2 = New DB2Parameter("@X", DB2Type.Char, 1, ParameterDirection.Output, True, 0, 0, "B", DataRowVersion.Current, "")
        CODE

        Works just as well, even though the PROCEDURE itself calls them "A" and "B".

        I did not understand that's what named parameters meant.

        Is there any way to use the actual names of the parameters in the Stored PROCEDURE itself (without adding it as host variables)?

        B.