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

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

    Re: VB .NET db2command parameters only use order?

    ‏2006-05-24T19:14:32Z  
    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

    Re: VB .NET db2command parameters only use order?

    ‏2006-05-31T15:09:22Z  
    • bpgross
    • ‏2006-05-24T19:14:32Z
    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.
    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

    Re: VB .NET db2command parameters only use order?

    ‏2006-05-31T16:00:53Z  
    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.
    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.