如何从VB.NET调用DB2 UDTF [英] How to call DB2 UDTF from VB.NET

问题描述

我正在编写一个需要在DB2中调用用户定义表函数的VB.Net应用程序。 DB2 UDTF可以从DB2查询中正常工作，但我无法弄清楚如何从VB.Net中调用它。



我附上了我写的VB.Net函数定义的文本文档（ HereIsTheCode.txt ）来调用UDTF和从DB2 Query运行的UDTF的位图（ HereIsTheQuery.bmp ）。我已经开发了几年与MS / SQL和IBM / DB2集成的业务应用程序，但这是我第一次打电话给UDTF并且到目前为止：没有快乐.......



UDTF返回一个记录表，我需要两个字段值（ ERRORCODE 和 ERRORTEXT ）



在调试模式下，代码执行永远不会从行返回 myDB2DataReader = myDB2Command.ExecuteReader



我尝试过：



这是VB.Net功能定义

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

这是从 IBM i Access Client解决方案 - 运行SQL脚本返回ERRORCODE = 0和ERRORTEXT =成功

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

推荐答案

我在连接中使用生产登录但访问开发数据库。生产凭证没有为开发数据库定义的输出队列，因此DB2 UDTF的.Net调用（.ExecuteReader）正在向某些DB2 / AS400日志写入错误并停止运行。



我今天早上发现了停滞过程：日志显示用户ID和输出队列错误。

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.

这篇关于如何从VB.NET调用DB2 UDTF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！