replacing sqlplus commands with mysql commands

Question

i am trying to rewrite a script that is written in c-shell script to that uses sql plus command to get information from an oracle database but i am replacing it with mysql and i would like to replace all sqlplus syntax with mysql syntax. I am asking all the c-shell gurus to explain to me what this command means

 set SQLPLUS=${ORACLE_HOME}/bin/sqlplus 

 set REPORT=${MYBD_HOME}/Scripts/report.sql

so somewhere along the line i invoke the sql plus command using the follwing

 ${SQLPLUS} ${MYDBUSER} @ &{REPORT}

i am able to say i undertand what the right hand values mean ({ORACLE_HOME}/bin/sqlplus) is the path to where my sqplus command is located and thus i need it to invoke the command and the {REPORT=$(MYBD_HOME}/Scripts.report.sql) is the path where my sql script that is to be ran by invoking the sqplus command resides correct?

what i dont understand is what the set command is initializing this to. is SQLPLUS a variable so i dont have to type the path when i try to put it in my .csh script?

If so then all i need to do to run this script on a mysql database is simply set the SQLPLUS(problably change it to MYSQL) to point to the path where my msql exec is right

  set MYSQL=${MYSQL_HOME}/bin/mysql

then just invoke mysql and run the sql statement

  ${MYSQL}${MYDBUSER}@${REPORT}

is this what i need to do ro tun the same .tsch script to get data from a mysql table?

Answer 1

You'll need something like this:

${MYSQL} -u $username -p$password -D $database < ${REPORT}

(The username and password are passed in differently to the mysql executable than they are passed to SQLPlus. You'll need to parse out the username and the password from ${MYDBUSER}. Likely, that contains a string such as "scott/tiger". The equivalent on the mysql command line client would be "-u scott -ptiger -D scott".

That @ (at sign) is a SQLPlus thing; it tells SQLPLus to read input from the specified filename. The equivalent in mysql would be the source command, e.g.

${MYSQL} -u $username -p$password <_EOD!
use $database 
source ${REPORT}
_EOD!

Also, your report.sql file likely includes spool and other SQLPLus specific commands. The mysql command line client is NOT ANYWHERE near as powerful a reporting tool as SQLPlus is.

---

Addendum:

Q: what exactly does the spool do?

The SQLPlus spool command directs output to a file. It's frequently used to create a log file from a SQLPLus session, and is also useful for creating report files.

set trimspool on
spool /tmp/file.lis
select 'foo' as foo from dual;
spool off

Q: Why can't i set the user name and passowrd to a variable and use that?

You could set a variable, the end result of the command line sent to the OS would be the same.

set MYDBUSER="-u username -ppassword -D database"
${MYSQL} ${MYDBUSER} <${REPORT}

Q:Seems like mysql is more verbose than sqlplus.

The mysql command line client takes unix-style options. These are equivalent:

mysql -u myusername -pmypassword -D mydatabase
mysql --user=myusername --password=mypassword --database=mydatabase