如何通过ODBC连接在c#中传递Informix集合参数(LIST，SET，MULTISET)

问题描述

我正在寻找一种在C#中传递Informix列表参数的方法.

I'm looking for a way to pass an Informix List Parameter in C#.

我问过一个关于如何将多值参数传递给Informix的问题，但是现在我需要从C#中执行它.

I asked a previus question of How to pass a multi value parameter to Informix, but now I need to execute it from C#.

相关问题是.

The related question is here.

在简历中，我有这样的过程.

In resume I have a procedure like this.

CREATE PROCEDURE test_3(c LIST(CHAR(10) NOT NULL))
    RETURNING CHAR(10) AS r;
    DEFINE r CHAR(10);
    FOREACH SELECT * INTO r FROM TABLE(c)
        RETURN r WITH RESUME;
    END FOREACH;
END PROCEDURE;

像这样在Aqua Data Studio.8.0.22中执行它可以正常工作

It works fine executing it in Aqua Data Studio.8.0.22 like this

 EXECUTE PROCEDURE test_3('LIST{''stspols'',''stsrepo''}');

因此，我举了一个简短的示例说明如何在c#中执行它.

So I made a quick example of how executing it in c#.

首先像CommandType.Text

string strParameters = "LIST{''stspols'',''stsrepo''}";
using (OdbcConnection oConnection = new OdbcConnection("DSN=MYDSN;UID=MYUID;PWD=MYPWD;"))
      {
           oConnection.Open();
           using (OdbcDataAdapter oCommand = new OdbcDataAdapter(string.Format("EXECUTE PROCEDURE test_3('{0}')", strParameters), oConnection))
           {                   
                using (DataTable dt = new DataTable())
                {
                            oCommand.Fill(dt);
                }

            }
      }

这是很好的作品.

所以我很好奇并尝试执行它，但是作为CommandType.StoredProcedure

So I got courious and tried to execute it but as CommandType.StoredProcedure

string strParameters = "LIST{''stspols'',''stsrepo''}";
            using (OdbcConnection oConnection = new OdbcConnection("DSN=MYDSN;UID=MYUID;PWD=MYPWD;"))
            {
                oConnection.Open();
                using (OdbcCommand oCommand = new OdbcCommand("{CALL test_3(?)}", oConnection))
                {
                    oCommand.CommandType = CommandType.StoredProcedure;
                    OdbcParameter oParameter = new OdbcParameter("c", OdbcType.Char, 4000);
                    oParameter.Value = strParameters;
                    oCommand.Parameters.Add(oParameter);

                    using (OdbcDataAdapter oDataAdapter = new OdbcDataAdapter(oCommand))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            oDataAdapter.Fill(dt);
                        }

                    }
                }

            }

但是现在我得到了ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Invalid collection literal value.

所有这些之后，我的最后一个问题是

So after all of this, my final question is

如何使用收集参数类型(LIST，SET，MULTISET)作为存储过程从C#执行这种Informix过程.

显然我做错了.

在此先感谢您的宝贵帮助.

Thanks in advance for your valuable help.

推荐答案

在原始SQL中，双引号是必需的，但在参数化查询中则不需要.您应该可以替换:

The doubled-up single quotes were necessary in raw SQL, but should not be needed in the parameterized query. You should be able to replace:

string strParameters = "LIST{''stspols'',''stsrepo''}";

具有:

string strParameters = "LIST{'stspols','stsrepo'}";

了解何时需要将引号加倍以及何时不加引号是很棘手的，但是不在占位符值中"是准确的.

Understanding when quotes need to be doubled and when they don't is tricky, but 'not in placeholder values' is accurate.

这篇关于如何通过ODBC连接在c#中传递Informix集合参数(LIST，SET，MULTISET)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！