How to use MySQL custom variables with ADO.NET

Question

MySQL SQL commands can contain user-defined variables starting with "@".

The MySQL ADO.NET connector also uses "@" for command parameters.

Is there a way to escape the '@' character so that I can use the custom variable in the SQL statement sent to MySQL via ADO.NET?

I am trying to do something like this:

UPDATE company 
    SET next_job_id = @jobid:=next_job_id+1 
    WHERE company_id = @companyid; 
SELECT @jobid;

Where @jobid is a MySQL user variable and @companyid is a parameter.

+2

Brad robinson 08 Feb 11 at 5:08

1 answer

Brad robinson · Answer 1 · 2011-02-08T05:18:45+0000

This is the connection string string - "Allow custom variables = true"

If set to true, parameters are prefixed with "?"

Update: I've written more about this, including how to get this to work with ADO.NET and Subsonic. See here .