Reading Variables available in shell program into MySQL

[amitgupta007@gmail.com]

Hi,

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
reference.

-----------------------------------------------------
# myscript.sql
select MY_VARIABLE from dual;

-----------------------------------------------------
# wrapper.sh
mysql -vvv -u myuserid -ppwd123 -D test --force --set MY_VARIABLE='XY Z'
< myscript.sql
-----------------------------------------------------

I need the return value to be XYZ on running the wrapper.sh

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

I will really appreciate your help in solving this problem.

Thanks in advance.
Amit

[amitgupta007@gmail.com]

Re: Reading Variables available in shell program into MySQL

Hi All,

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

-----------------------------------------------------------------------
#wrapper.sh
mysql -vvv -u myuserid -ppwd123 -D test --force << MY_MARKER

set @MY_VARIABLE='X YZ' ;
source myscript.sql ;

MY_MARKER
------------------------------------------------------------------------
# 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
Cookbook.

Thanks,
Amit