I need to execute a query against an Informix database using output parameters to retrieve the desired value. I’ve tried many different variants of the query, but so far none has worked. The only examples I’ve seen in the documentations have used stored procedures to get the result but a stored procedure is not an option in this case.
The relevant code from what I’m trying to do is:
DB2Connection con = new DB2Connection(ConStr); DB2Command cmd = con.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select ? = Column1 from Table1 where Table1.serial = 1"; cmd.Parameters.Add("param", DB2Type.Integer).Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteNonQuery(); con.Close(); object o = cmd.Parameters["param"].Value;
This is with the same query as I use against SQL Server as I'm not sure of the correct syntax for Informix
Visual Studio 2010
.NET Framework 2.0
Informix 11.5 (DB2 driver, IBM.Data.DB2.dll)
The same method that I’ve got working for Oracle and MS SQL Server by changing the query and using their corresponding ado.net driver
cmd.CommandType = CommandType.Text; cmd.CommandText = "BEGIN select Column1 INTO :param from Table1 where Table1.serial = 1; END;"; cmd.Parameters.Add(":param ", OracleDbType.Int32).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT @param = Column1 from Table1 where Table1.serial;"; cmd.Parameters.Add("@param", SqlDbType.Int).Direction = ParameterDirection.Output;
Does anyone know if this is possible with informix without the use of stored procedures? cmd.ExecuteScalar(); is not an option either because the framework that is being used requires the use of output parameters.