Mysql function return system result

Question

In mysql, I'm trying to create a function that will return the result of a bash command. I've created a simplified version of what I'm trying to do to make it easier to understand.

I have a file called mycmd.sh, and inside it, I have this line:

echo $1 'test'

When in mysql, I execute the sh file it is working fine

system ~/mycmd.sh 'test'

But I would like to be able to call it while doing a select in mysql, something like this

select myFunc(username) from user;

The result will be that, every username will have a "test" after it.

The problem is when I try to create the mysql function

CREATE FUNCTION myFunc(s VARCHAR(500)) RETURNS VARCHAR(505)
RETURN SYSTEM ~/mycmd.sh s;

I always get the error: ERROR 1064 (42000): 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 '~/mycmd.sh s' at line 2

I tried putting it in quote, or in a variable, or in a prepared statement, but nothing seem to work...

Anyone have an idea how to do so?

Thank you

pgl · Accepted Answer · 2014-04-24 15:54:54Z

1

Unfortunately what you are trying to do isn't possible without using a UDF (User Defined Plugin). The system command you're using is part of the MySQL shell, not an actual MySQL function, which is why it doesn't work in the function you're trying to define.

The only other option I can think of is to write a procedure that imitated whatever your external command was going to do.

answered Apr 24, 2014 at 15:54

pgl

8,1512 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Etienne Over a year ago

Thanks for the answer, meanwhile I did use a udf to solve my problem

Collectives™ on Stack Overflow

Mysql function return system result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related