CODEWITHSUNDEEP
c#asp.netsqlodbcinformix
Hector Sanchez
Hector Sanchez

Reputation: 2317

How to pass an Informix Collection Parameter (LIST, SET, MULTISET) in c# through an ODBC Connection

I'm looking for a way to pass an Informix List Parameter in 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;

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

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

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

First like a 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);
                }

            }
      }

This one works FINE.

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);
                        }

                    }
                }

            }

But now I get ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Invalid collection literal value.

So after all of this, my final question is

How I can execute this kind of Informix procedures from C#, with a Collection parameter Type (LIST, SET, MULTISET) as a Stored Procedure.

Apparently I'm doing something wrong.

Thanks in advance for your valuable help.

Upvotes: 1

Views: 3506

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754410

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''}";

with:

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.

Upvotes: 1

Related Questions