Best/Correct way to call an Informix stored procedure via ADO.Net Command?

Question

I have an Informix database which exposes some stored procedures, I have an abstracted data accessor that handles communicating with them but I have a problem with a null value.

Directly you can call:

execute procedure some_stored_procedure(1,2,NULL,3)

and get back correct results, I would rather there not be this nullable field, but it is out of my hands. Anyway I was originally trying to call it like so:

var command = connection.CreateCommand();
command.CommandType = CommandTypes.StoredProcedure
command.CommandText = "some_stored_procedure"
// Pass in the parameters

However doing that causes Informix to throw a syntax error, so instead I have been forced to go with the:

var command = connection.CreateCommand();
command.CommandText = "execute procedure some_stored_procedure(?,?,?,?)";
// Pass in parameters

Which works but never passes back correct results, and if I try and make parameter 3 null it gives another syntax error. Am I missing something or is there a better way to call these stored procedures?

Answer 1

try parameterizing the parameters (you can use OdbcParameters if working with the odbcdriver) and then pass DbNull.Value where Null is required.

Try this:

var command = connection.CreateCommand(); 
command.CommandType = CommandTypes.Text;
command.CommandText = "call some_stored_procedure(?,?,?,?)";

command.Parameters.Add(param); //add all your parameters.