As experimenting the integration of MySql database with c# code I came across to a problem that seemed to be a common issue.
Mostly, after executing an INSERT statement you’d like to get back the new ID of the record.
Intuitively, you would think about creating an output parameter and use @@IDENTITY or scope_identity() or LAST_INSERT_ID() to assign the value to it.
Unfortunately, it seems that when I use this statement: “SET @newID := @@IDENTITY” I get an exception about sql syntax. Reviewing the exception reveals that somehow it repaced the “@newID” with NULL and therefore not allowing execution. (In my case i used SQL statement and not stored procedure).
As stated here, MySql does not support out parameters in the protocol yet.
All the suggested solutions I found didn’t made this work.
Read the rest of this entry »