Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
No replies
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.