notifyix ODBC存储过程始终返回不正确的相同值 [英] informix ODBC stored procedure always return incorrect same value

问题描述

我在Informix中创建了一个简单的sp

I have a simple sp created in Informix

create procedure test(arg1 int) returning  int;
if arg1 > 1 then return 10;
else return 0;
end if;
end procedure;

当直接调用Informix时，它可以按预期工作，但是当我使用ODBC从VB.NET调用它时，它总是返回1.

It works as expected when called directly into Informix but when I call it from VB.NET using ODBC, it returns always 1.

这是我的vb代码

Dim cmd As OdbcCommand = New OdbcCommand("{call test()}", conn)
With cmd
    .CommandType = CommandType.StoredProcedure
    .Parameters.AddWithValue("@arg1", 0)
End With
conn.Open()
Dim dt As DataTable = New System.Data.DataTable("resultTableFromDB")
Dim da As OdbcDataAdapter = New OdbcDataAdapter(cmd)
da.Fill(dt)
conn.Close()

推荐答案

我不使用VB.NET，但是在JDBC中，您必须显示该函数需要带? char的参数.因此，尝试使用"{call test(?)}"而不是"{call test()}"

I do not use VB.NET but in JDBC you must show that function needs parameters with ? char. So try "{call test(?)}" instead of "{call test()}"

我已经测试了将函数重命名为test_so()的功能，它可以与Jython的JDBC-ODBC桥一起使用.我还更改了调用字符串以检索结果.整个代码:

I have tested your function renamed to test_so() and it works with JDBC-ODBC bridge from Jython. I also changed call string to retrieve result. Whole code:

import sys
import traceback

from java.sql import DriverManager, Types
from java.lang import Class

Class.forName("com.informix.jdbc.IfxDriver")


def test_call(proc, arg1, expected):
    proc.registerOutParameter(1, Types.INTEGER)
    proc.setInt(2, arg1);
    rs = proc.executeQuery();
    while (rs.next()):
        r = rs.getInt(1)
        ok = 'ok'
        if r != expected:
            ok = ' ERROR!!!'
        print('%d: %s %s' % (arg1, r, ok))


def test(db_url, usr, passwd):
    try:
        print("\n\n%s\n--------------" % (db_url))
        db = DriverManager.getConnection(db_url, usr, passwd)
        proc = db.prepareCall("{ ? = call test_so(?) }");
        test_call(proc, 0, 0)
        test_call(proc, 1, 0)
        test_call(proc, 2, 10)
        test_call(proc, 10, 10)
        test_call(proc, -1, 0)
        test_call(proc, -10, 0)
        db.close()
    except:
        print("there were errors!")
        s = traceback.format_exc()
        sys.stderr.write("%s\n" % (s))


test('jdbc:odbc:MY_SYSTEM_DSN', 'USER', 'PASSWD')

这篇关于notifyix ODBC存储过程始终返回不正确的相同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！