none
Command Parameters not updated via AS400 Stored Procedure Call RRS feed

  • Question

  • I am trying to execute a stored procedure on our AS400 using IBMDA400 driver V5R4 from a vb.net 2.0 program.  The issue is that the third inputoutput parameter is not being updated when returned.

     

    We have verified that the procedure is in fact being executed on the AS400 and verified that it is receiving the first two parameters, and we have actually for testing purposes hardcoded the the stored procedure's return value for parameter p2 to '19T'.  When the following code is executed and we interogate the command parameters after execution, "p2" is '1'.. which is what we set it to at the beginning.

     

    To make things a little more interesting, when we switch to using an ODBCCommand with a commandtype set to "storedprocedure", it works correctly.  Our preference is to use OLEDB since it manages the connections to the AS400 better and provides better performance.

     

    Any assistance would be appreciated.

     

    Thanks

     

    --Scott

     

    Here's the code...

     

    Public Function GetAS400SP(ByVal Key As String, ByVal Params As String) As DataTable

    Dim sdc As New sDataCall

    Dim sValue As New DataTable

    Dim Cmd As OleDbCommand

    Cmd = New OleDbCommand

    Dim cn As New OleDbConnection("Provider=IBMDA400;Data Source=XX.XX.XX.XX;User Id=XX;Password=XX;")

    Try

    Cmd.CommandText = "CALL APLUS2MTS.SMIA0022(?,?,?)"

    Cmd.CommandType = CommandType.Text

    Cmd.Connection = cn

    sdc = GetDataCallValues(Key)

    sdc.DataCallParams = Params.Split("|")

    Cmd.CommandTimeout = 2000

    Cmd.Connection.Open()

    'Create the parameter objects to pass and get data from procedure

    Dim v As String

    Dim i As Integer = 0

     

    Dim p As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)

    p.Value = sdc.DataCallParams(0)

    Cmd.Parameters.Add(p)

    Dim c As New DataColumn

    sValue.Columns.Add(c)

    Dim p1 As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)

    p1.Value = sdc.DataCallParams(1)

    Cmd.Parameters.Add(p1)

    Dim c1 As New DataColumn

    sValue.Columns.Add(c1)

    Dim p2 As New OleDbParameter("", OleDb.OleDbType.Char, 3, ParameterDirection.InputOutput)

    p2.Value = "1"

    Cmd.Parameters.Add(p2)

    Dim c2 As New DataColumn

    sValue.Columns.Add(c2)

     

    Cmd.ExecuteNonQuery()

     

    Dim pValue As OleDbParameter

    Dim dr As DataRow

    dr = sValue.NewRow

    Dim iParam As Integer = 0

    For Each pValue In Cmd.Parameters

    dr(iParam) = pValue.Value

    iParam += 1

    Next

    sValue.Rows.Add(dr)

    Catch ex As Exception

    Return New DataTable

    Finally

    Cmd.Connection.Close()

    End Try

    Return sValue

    End Function

    Tuesday, September 16, 2008 9:51 PM

Answers

  • Thanks Val...

    We had originally tried that and it didn't work... through additional trial and error, we finally got it to work.  The following code, slightly different than what I had originally posted is... 

    Public Sub btnDoIt2_Click()

            Dim OleDbCmd As System.Data.OleDb.OleDbCommand
            OleDbCmd = New System.Data.OleDb.OleDbCommand

            Dim OleDbCon As New System.Data.OleDb.OleDbConnection("Provider=IBMDA400;Data Source=XX.XX.XX.XX;User Id=XX;Password=XX")
            Dim parm1 As String
            Dim parm3 As String
            Dim parm2 As String

            Try
                OleDbCmd.CommandText = "APLUS2MTS.SMIA0022"
                OleDbCmd.CommandType = CommandType.StoredProcedure
                OleDbCmd.Connection = OleDbCon
                OleDbCmd.CommandTimeout = 2000
                OleDbCmd.Connection.Open()

                OleDbCmd.Parameters.Add("@SKU19", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters.Add("@SKUNEW", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters.Add("@PRICECLS", OleDb.OleDbType.Char, 3).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters(0).Value = "SY188"
                OleDbCmd.Parameters(1).Value = "SY1881"
                OleDbCmd.Parameters(2).Value = "   "

                OleDbCmd.ExecuteNonQuery()

                parm1 = OleDbCmd.Parameters(0).Value.ToString
                parm3 = OleDbCmd.Parameters(2).Value.ToString
                parm2 = OleDbCmd.Parameters(1).Value.ToString

            Catch ex As Exception

            Finally
                OleDbCmd.Connection.Close()
            End Try

        End Sub

    The primary issue has been isolated to how the command parameters are defined...  originally we had:

    Dim p As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p)
              Dim p1 As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p1)
              Dim p2 As New OleDbParameter("", OleDb.OleDbType.Char, 3, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p2)

    which does not work... the return value for third parameter is not being populated.  if we define the parameters using the following code:

    Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27)
              Cmd.Parameters(0).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27)
              Cmd.Parameters(1).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 3)
              Cmd.Parameters(2).Direction = ParameterDirection.InputOutput

    or
    Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 3).Direction = ParameterDirection.InputOutput

    it works.  Don't know why 2 would work and 1 does not... since these three approaches should yield the equivalent parameters.  If anyone has any insight into this, please don't keep it a secret.


    Wednesday, September 17, 2008 5:41 PM

All replies

  • Can you try to call it as stored procedure, not as SQl statement, changing next lines of code

     

    Cmd.CommandText = "APLUS2MTS.SMIA0022"

    Cmd.CommandType = CommandType.StoredProcedurfe

     

    If same code works fine with ODBC driver, but does not work with OLEDB provider it looks (to me) like a bug in IBMDA400 OLEDB provider.

     

    Wednesday, September 17, 2008 10:02 AM
    Moderator
  • Thanks Val...

    We had originally tried that and it didn't work... through additional trial and error, we finally got it to work.  The following code, slightly different than what I had originally posted is... 

    Public Sub btnDoIt2_Click()

            Dim OleDbCmd As System.Data.OleDb.OleDbCommand
            OleDbCmd = New System.Data.OleDb.OleDbCommand

            Dim OleDbCon As New System.Data.OleDb.OleDbConnection("Provider=IBMDA400;Data Source=XX.XX.XX.XX;User Id=XX;Password=XX")
            Dim parm1 As String
            Dim parm3 As String
            Dim parm2 As String

            Try
                OleDbCmd.CommandText = "APLUS2MTS.SMIA0022"
                OleDbCmd.CommandType = CommandType.StoredProcedure
                OleDbCmd.Connection = OleDbCon
                OleDbCmd.CommandTimeout = 2000
                OleDbCmd.Connection.Open()

                OleDbCmd.Parameters.Add("@SKU19", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters.Add("@SKUNEW", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters.Add("@PRICECLS", OleDb.OleDbType.Char, 3).Direction = ParameterDirection.InputOutput
                OleDbCmd.Parameters(0).Value = "SY188"
                OleDbCmd.Parameters(1).Value = "SY1881"
                OleDbCmd.Parameters(2).Value = "   "

                OleDbCmd.ExecuteNonQuery()

                parm1 = OleDbCmd.Parameters(0).Value.ToString
                parm3 = OleDbCmd.Parameters(2).Value.ToString
                parm2 = OleDbCmd.Parameters(1).Value.ToString

            Catch ex As Exception

            Finally
                OleDbCmd.Connection.Close()
            End Try

        End Sub

    The primary issue has been isolated to how the command parameters are defined...  originally we had:

    Dim p As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p)
              Dim p1 As New OleDbParameter("", OleDb.OleDbType.Char, 27, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p1)
              Dim p2 As New OleDbParameter("", OleDb.OleDbType.Char, 3, ParameterDirection.InputOutput)
              Cmd.Parameters.Add(p2)

    which does not work... the return value for third parameter is not being populated.  if we define the parameters using the following code:

    Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27)
              Cmd.Parameters(0).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27)
              Cmd.Parameters(1).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 3)
              Cmd.Parameters(2).Direction = ParameterDirection.InputOutput

    or
    Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 27).Direction = ParameterDirection.InputOutput
              Cmd.Parameters.Add("", OleDb.OleDbType.Char, 3).Direction = ParameterDirection.InputOutput

    it works.  Don't know why 2 would work and 1 does not... since these three approaches should yield the equivalent parameters.  If anyone has any insight into this, please don't keep it a secret.


    Wednesday, September 17, 2008 5:41 PM