ODBC and DB2 stored procedures from C#: Routine not found

I seem have come across a short coming with ODBC and DB2 when running stored procedures. It seems that it is not possible to return data from a stored procedure, and I have a prexisting query that I need to use. Has anyone got around this particular issue?

Thanks in advance

Update

The code that I am calling is as follows (assuming that the connection is already opened):

string BaseSQL = "CALL B6009822.O#04666803.PUT";

OdbcCommand command = new OdbcCommand(BaseSQL, myConnection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@Owner", OdbcType.VarChar).Value = "MH";

int rows = command.ExecuteNonQuery();

myConnection.Close();

I get the following error ..

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0440 - Routine PUT in O#04666803 not found with specified parameters.

It seems to be objecting to the catalog/library and procedure name. Any idea on what I need to do to get the procedure called ?

Second Update - real example

string BaseSQL = "{ CALL B6009822.O#04666803.PUT(?,?,?,?,?,?,?,?,?) }";

OdbcCommand command = myConnection.CreateCommand();
command.CommandText = BaseSQL;

//OdbcCommand command = new OdbcCommand(BaseSQL, myConnection);
command.CommandType = CommandType.StoredProcedure;

/*
@Param1 VarChar(4), @Param2 dec(8,0), 
@Param3 dec(4,0), @Param4 dec(8,0),      
@Param5 VarChar(60), @Param6 dec(9,2), 
@Param7 dec(9,0), @Param8 dec(9,2), 
@Param9 VarChar(10))                
 */

command.Parameters.Add("@Param1", OdbcType.VarChar, 4).Value = "MH";
command.Parameters.Add("@Param2", OdbcType.Decimal, 8).Value = 20110217;
command.Parameters.Add("@Param3", OdbcType.Decimal, 4).Value = 1;
command.Parameters.Add("@Param4", OdbcType.Decimal, 8).Value = 178377;
command.Parameters.Add("@Param5", OdbcType.VarChar, 60).Value = "Description";
command.Parameters.Add("@Param6", OdbcType.Decimal, 9).Value = 0;
command.Parameters.Add("@Param7", OdbcType.Decimal, 开发者_JAVA技巧9).Value = 45;
command.Parameters.Add("@Param8", OdbcType.Decimal, 9).Value = 0;
command.Parameters.Add("@Param9", OdbcType.VarChar, 10).Value = "*CREATE";

int rows = command.ExecuteNonQuery();

myConnection.Close();

Have you tried the CALL syntax?

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.odbc/db2z_odbcspcall.htm

EDIT: Just in case my comment above is correct:

From this resource, have you tried:

string BaseSQL = "CALL B6009822.O#04666803.PUT (?)";

Important: Unlike ODBC, DB2 ODBC does not support literals as procedure arguments. You must use parameter markers to specify a procedure parameter.

First of all I had to make the library (or whatever the proper term is) visible in the "System i Navigator". It seems that there is an issue with stored procedures that return results, so I had to call it as a SELECT, as shown below ...

"SELECT PUT('{4}',{1},1,{0},'{2}',0,{3},0,'{5}') as A from LIBRARY.EARNER where EAR = '{4}'"

Not sure if this helps anyone else other than me though!

For any DB2 n00bs like myself hitting this page searching for answers to "SQL0440 - Routine [YourRoutine] in [*N | LIBRARY] not found with specified parameters"...

For me, my problem was where I had defined the constant for the stored procedure name, like so:

    // Database Constants
    public const string DB_PROC_GET_MYPROC = @"LIBRARY.MYPROC";

How I should have declared it was:

    // Database Constants
    public const string DB_PROC_GET_MYPROC = @"LIBRARY.MYPROC(@stateCode, @productCode, @tranType)";

...with the parameters included.

I've spent so long with Entity Framework, I have all but forgotten direct database connection basics in c#.

Hope this saves someone some time.