Reading Variables available in shell program into MySQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am migrating one of my application from Oracle to MySQL. We are using
user defined variable which we are using in the sql scripts.

In Oracle it is done using "DEFINE". The similar thing can be done in
MySQL using the SET command. But the catch here is I am invoking the
MySQL using a shell file which in turn is started by a cron job.

Here is what I need exactly, I am using some dummy file names for

# myscript.sql
select MY_VARIABLE from dual;

mysql -vvv -u myuserid -ppwd123 -D test --force --set MY_VARIABLE='XYZ'
< myscript.sql

I need the return value to be XYZ on running the

Not sure about the syntax to be used for myscript.sql.

I will really appreciate your help in solving this problem.

Thanks in advance.

Re: Reading Variables available in shell program into MySQL

Hi All,

I got the answer from my friend. Here is the solution:

mysql -vvv -u myuserid -ppwd123 -D test --force  << MY_MARKER

source myscript.sql ;

# myscript.sql
select @MY_VARIABLE from dual;

Note : MY_MARKER is some arbitray string, you can select any
non-reserved keyword.

For further details you can urefer Chapter 1.33 of O'Reilly's MySQL


Site Timeline