Click here to Skip to main content
15,886,578 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm writing a VB.Net application that needs to call a User Defined Table Function in DB2. The DB2 UDTF works correctly from a DB2 query but I can't figure out how to call it from VB.Net.

I've attached a text document ( HereIsTheCode.txt ) of the VB.Net Function Definition I wrote to call the UDTF and a bitmap ( HereIsTheQuery.bmp ) of the UDTF as run from a DB2 Query. I've been developing Business Applications that integrate with both MS/SQL and IBM/DB2 for several years but this is the first time I've had to call a UDTF and so far: no joy.......

The UDTF returns a single record table from which I need the two field values (ERRORCODE and ERRORTEXT)

In debug mode code execution never returns from the line myDB2DataReader = myDB2Command.ExecuteReader

What I have tried:

Here's the VB.Net Function Definition


Public Function PumpTransaction() As Boolean
            Dim thisResult As Boolean = True
            Status = 0
            StatusMessage = "Success."

            Dim myDB2Connection As iDB2Connection
            Dim myDB2Command As iDB2Command
            Dim myDB2DataReader As iDB2DataReader

            'Define the ODBC Connection string
            myDB2Connection = New iDB2Connection("DataSource=" & globalConnectionSelected & _
                                                 "; UserID=" & _
                                                 constAS400_UID & _
                                                 ";Password=" & _
                                                 constAS400_PWD)

            Dim myConnectionOpen As Boolean = False
            Try
                'Open the Connection
                myDB2Connection.Open()
                myConnectionOpen = True

                Dim thisCommandString As String = "  SELECT ERRORCODE, ERRORTEXT FROM TABLE(DPLIB06.SMBB("
                thisCommandString += CoNo.ToString + ",'" + _CallerID.Trim.ToUpper + "','" + PartNo.Trim.ToUpper + "','"
                thisCommandString += LocFrom.Trim.ToUpper + "','" + LocTo.Trim.ToUpper + "'," + CStr(Qty) + ")) RESULTS"

                myDB2Command = New iDB2Command(thisCommandString, myDB2Connection)

                Dim myCommandString As String = myDB2Command.CommandText

                myDB2DataReader = myDB2Command.ExecuteReader

                If myDB2DataReader.HasRows Then
                    myDB2DataReader.Read()
                End If
                myDB2DataReader.Close()
            Catch ex As Exception
                Status = -1
                StatusMessage = String.Format("{0}: {1}", System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)
            End Try

            If myConnectionOpen Then
                myDB2Connection.Close()
            End If

        End Function


This is the DB2 Query which works successfully from within IBM i Access Client Solutions - Run SQL Scripts returning ERRORCODE = 0 and ERRORTEXT = "Success"
SELECT ERRORCODE, ERRORTEXT
FROM TABLE(DPLIB06.SMBB(9,'SMBB_009','G1-515011','TESTENG','BFLSH',10)) RESULTS
Posted
Updated 16-Nov-18 4:28am

1 solution

I was using a production login in the connection but accessing a development database. The production credentials don't have an output queue defined for the development database so the .Net call (.ExecuteReader) to the DB2 UDTF was writing an error to some DB2/AS400 log and stalling.

I found out about the stalled process this morning: the log showed the User ID and the output queue error.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900