Insight.Database procedure call using ODBC fails

Question

I am using Insight.Database in C# via the ODBC provider to connect to a iSeries DB2 database. There is a stored proc in db2 called InsertBeer. All the following calls work great!:

List<Beer> beers = conn.QuerySql<Beer>("SELECT * FROM Beer WHERE Typee = @Typee", new { Typee = "IPA" }).ToList();

var beer = new Beer { ID=41,Typee="Medium", Description = "From dotNet Neither light or dark"};

conn.ExecuteSql("INSERT INTO Beer VALUES (@ID, @Typee, @Description)", new { ID = 4, Typee = "Medium", Description = "From dotNet Neither light or dark" });

conn.ExecuteSql("CALL INSERTBEER (@ID, @Typee, @Description) ", new { ID = 4, Typee = "MediumOD", Description = "From dotNet Neither light or dark" });

conn.ExecuteSql("INSERT INTO Beer VALUES (@ID, @Typee, @Description)", beer);

Which is awesome, I am really digging this micro-ORM. Already half the battle is won, the beer object in call above saves tons of hand-wired parameter binding. But I dont want to type out the LHS parameters in the call to the InsertBeer stored procedure. I believe that is the type of plumbing work that was intended to be avoided with the Insight Execute() method. Sadly, these calls DONT work:

conn.Execute("INSERTBEER", beer);

I also tried:

conn.Execute("insertbeer", new Beer {ID = 4, Typee = "Medium", Description = "From dotNet Neither light or dark"}, CommandType.StoredProcedure, true, Int32.MaxValue, null, beer);   

The error I am getting is:

{"ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token INSERTBEER was not valid. Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH."}

Any ideas greatly appreciated!

Answer 1

I haven't tried insight with that version of db2, but let me see if i can help.

1) have you tried using the db2 provider in insight.database.providers.db2?

2) can you post a working example with odbc/.net directly? Then maybe i can see what the difference is.

Best to open an issue on github. I try to take care of open issues there (although i'm WAY behind on them).