How to create mysql stored function from c#.net

nitish_07

I have to create mysql stored function from c#.net. User will create the function at run time and i have to pass this string in odbc command like this...

rt.Text = "CREATE FUNCTION" +" "+ "`" + node + "`" +" "+ "." +" "+"'"+textBox1.Text +"'"+" "+" ()"+" "+ "@RETURNS INT"+" @BEGIN@END;";
rt.Text = rt.Text.Replace("@", System.Environment.NewLine);
OdbcCommand cmd = new OdbcCommand(rt.text, cn);
cmd.CommandText = rt1.text;
cmd.ExecuteNonQuery();

But it shows that You have syntax error.Check the manual that correspond to your mysql document...so plz tell me what is the problem.....??

Lost User

nitish_07 wrote:

But it shows that You have syntax error

If you encounter an error or an exception, than paste the entire thing into your post. I'm guessing that it's an exception that's thrown on ExecuteNonQuery as your Sql-statement is malformed. It's also damn hard to read. Concatenating strings is fun, but you don't want to overdo it. The code below is allowed too;

rt.Text = "CREATE FUNCTION `" + node + "` . '"+textBox1.Text +"' () "+
"@RETURNS INT @BEGIN@END;";

        rt.Text = rt.Text.Replace("@", System.Environment.NewLine);

OdbcCommand cmd = new OdbcCommand(rt.text, cn);
cmd.CommandText = rt1.text;
cmd.ExecuteNonQuery();

I'm going to guess again that it's the `-character that's not being recognized, and that it should be replace with a '. Further, you'd want a Debug.Print(rt.Text) so that you can easily validate the syntax of your query. Hope this helps :)

Bastard Programmer from Hell :suss:

nitish_07

Error is this System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.77-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''df' () RETURNS INT BEGIN return 1; END' at line 1 and string is i.e rt.text=

"CREATE FUNCTION `userdb` . 'df' () \nRETURNS INT \nBEGIN\nreturn 1;\nEND;"

Lost User

I think the error goes away if you change the type of quote in there;

"CREATE FUNCTION 'userdb'.'df'() \nRETURNS INT \nBEGIN\nreturn 1;\nEND;"

Again, the character ` cannot be used instead of the character '.

Bastard Programmer from Hell :suss:

nitish_07

ok I am trying...actually this is written in the orginal mysqlbrowser when i create the fucntion

CREATE FUNCTION `userdb`.`gh` () RETURNS INT
BEGIN
return 1;
END$$

so i think this is also ` not ' ???

nitish_07

Thanks man done....:)

Dave Kreskowiak

First, using string concatentation to build a query is just plain bad practice. Especially when you are doing with with consecutive, individual characters. It makes your code impossible to read and debug accurately. Oh, and your call to Replace is not needed at all. Using the @ character in a string to denote new lines in an SQL statement is a bad idea since SQL parameters usually start with that character. A better method is to use String.Format:

string queryString;
queryString = String.Format("CREATE FUNCTION '{0}'.'{1}'()\\nRETURNS INT\\nBEGIN\\nEND;", node, functionName);

Now is your code easier to read, or is mine?

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

nitish_07

Yes you are right...Thanks man....If you could provide the solution for the problem which i posted some min ago....