Create function in mysql

greywire

I have been trying to create a function in mysql (5.x). I had used Toad for MySQL to create it, and it works just fine...

So, I go to create my database update script. I put the "CREATE FUNCTION ...." SQL code into a query in my PHP code. No workie. So I tried pasting the SQL into phpMyAdmin. No workie. Come to find out you can do it using the mysql command line client and using the DELIMITER command. OK this makes sense, since the SQL for the function has ';' in it, you have to change the delimiter to something else temporarily.

But DELIMITER doesnt work in a query from PHP, because its not an SQL command.

So how the hell do I do this? I need this function created from my update script in php (and soon, I will probably be adding lots of stored procedures which will have the same problem) because I'm not going to use the mysql command on each database...

I've been googling and pulling my hair out trying to figure out what the hell I need to do to make this work.

MarkR

Do not use PHPMyAdmin for ANYTHING. Ever.

Run it from the CLI and it it should be fine. Use the command line "mysql" program.

I suspect that doing things like creating functions requires stuff which PhpMyAdmin either doesn't know about, or just contains bugs which stop it working.

Do not rely on PHPMyAdmin to get anything right, ever.

Mark

greywire

MarkR wrote:
Do not use PHPMyAdmin for ANYTHING. Ever.

Run it from the CLI and it it should be fine. Use the command line "mysql" program.

I suspect that doing things like creating functions requires stuff which PhpMyAdmin either doesn't know about, or just contains bugs which stop it working.

Do not rely on PHPMyAdmin to get anything right, ever.

Mark

Problem is, I dont care about phpmyadmin. What I need to do is be able to run this SQL query from within my own PHP scripts. I have an update script that makes all necessary database changes with new versions of the code. I cannot run the SQL command for creating a function (or a stored procedure) from inside of PHP for some reason.

There must be a way.

I guess I could just use exec() to run the mysql command and have a separate sql file with the changes in it. Is there a way for a php script to figure out where the mysql command is located (and it needs to work on both windows and linux)?

But this is really anoying. There HAS to be a way to do this from php..

MarkR

Don't run it from PHP at all. Log in to your shell and run it from the command line.

Or even run it remotely from the command line, with appropriate parameters.

Mark

Sxooter

sounds like a permissions issue.

greywire

Wow, I guess I am just not explaining the problem correctly.

Here's the issue: When you are creating a function or a stored procedure in MySQL (granted I know these are relatively new features for MySQL) you wind up having to use ';' on many lines inside the procedure (obviously, because you have multiple SQL statements, which is the whole point of stored procs).

From the MySQL command line, if you try to do this, it assumes that a ';' means you are done and it should start executing the command. So there is a DELIMITER command that you use to let it know not to do this (by setting the delimiter to something else, like '|', and then ending the whole stored proc statement with that instead of a ';').

If I only needed to do this once, no big deal.

Problem is I have a software application that people are actualy using currently. I am not going to log into each system, and for each database and use the mysql command to do this.

Which is why I have an upgrade.php script that makes any new changes to the database(s). Problem is, there does not seem to be an equivalent to the DELIMITER command (which is only in the MySQL command line program) in the php mysql code.

The only solution I can think of is the use the exec() command to run the mysql command and have my stored proc in a separate file for it to load. This is clunky and there has to be another way to do it.

Its not a permissions issue. Its a SQL parsing issue.

jheitz

But why use PHP at all to do this? Why not have a plain TEXT file with all your CREATE / DROP /ALTER FUNCTIONS or STORED PROCEDURES and run it from the command line? I haven't used MySQL for a while, but it must be able to do that!

greywire

jheitz wrote:
But why use PHP at all to do this? Why not have a plain TEXT file with all your CREATE / DROP /ALTER FUNCTIONS or STORED PROCEDURES and run it from the command line? I haven't used MySQL for a while, but it must be able to do that!

Because I'm not going to log into a 100 different servers and run a command every time I update my application. Furthermore I may not even have access to every customer's server and I can't exactly tell them hey, you need to open a command prompt and then run this command etc...

Not to mention they could have mysql installed in different places and so you'd have to find the mysql command in the first place if it wasnt added to your current paths..

I'm not sure why nobody seems to be even understanding my dilemma, much less providing any clues as to what to do about it...

MarkR

I suspect it's to do with batching an delimiters. It's likely that the mysql_ functions don't use a recent enough API to be able to do this.

Try mysqli_ or PDO Mysql instead - but if these aren't available - yes, running the MySQL CLI seems like a sensible suggestion. Surely you can run this on 100 servers or however many is necessary (you can script it, of course).

Mark

greywire

MarkR wrote:
I suspect it's to do with batching an delimiters. It's likely that the mysql_ functions don't use a recent enough API to be able to do this.

Try mysqli_ or PDO Mysql instead - but if these aren't available - yes, running the MySQL CLI seems like a sensible suggestion. Surely you can run this on 100 servers or however many is necessary (you can script it, of course).

Mark

Its funny you say "you can script it..". That's what I am trying to do! Or did you mean I should use something other than PHP to script it? Perl? Shell script?

It seems likely that perhaps this does require mysqli, but I cant find any settings in the mysqli api for changing the delimiter...

MarkR

When I said "You can script it", I really meant a shell script to run the mysql client program directly.

It seems much more reasonable to use the command line client, because that is what the docs tell you to do and it's pretty straightforward.

Plus creating functions is something you need to do sufficiently rarely that there is no reason to do it from PHP anyway.

Mark