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