title: How to pass an Informix Collection Parameter (LIST, SET, MULTISET) in c# through an ODBC Connection
body: <p>I'm looking for a way to pass an Informix List Parameter in C#.</p>
  
  <p>I asked a previus question of How to pass a multi value parameter to Informix, but now I need to execute it from C#.</p>
  
  <p>The related question is <a href="https://stackoverflow.com/questions/10825468/informix-how-to-pass-use-and-execute-multiple-values-in-a-single-parameter-to">here</a>.</p>
  
  <p>In resume I have a procedure like this.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>It works fine executing it in Aqua Data Studio.8.0.22 like this</p>
  
  <pre><code> EXECUTE PROCEDURE test_3('LIST{''stspols'',''stsrepo''}');
  </code></pre>
  
  <p>So I made a quick example of how executing it in c#.</p>
  
  <p>First like a <code>CommandType.Text</code></p>
  
  <pre><code>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);
                  }
  
              }
        }
  </code></pre>
  
  <p>This one works FINE. </p>
  
  <p>So I got courious and tried to execute it but as <code>CommandType.StoredProcedure</code></p>
  
  <pre><code>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);
                          }
  
                      }
                  }
  
              }
  </code></pre>
  
  <p>But now I get <code>ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Invalid collection literal value.</code></p>
  
  <p>So after all of this, my final question is </p>
  
  <p><strong>How I can execute this kind of Informix procedures from C#, with a Collection parameter Type (LIST, SET, MULTISET) as a Stored Procedure.</strong></p>
  
  <p>Apparently I'm doing something wrong.</p>
  
  <p>Thanks in advance for your valuable help.</p>