Passing external variables/arguments to SQL program

ganninu · 11-19-2003, 02:52 AM

I know basic SQL. But now I'm faced with a situation in which i need some way to pass some external data to my SQL program.

Suppose i have the very simple query:

SELECT * FROM table1

and I save it to a file test.sql

How can I pass any table of my choice to test.sql as an argument such that the program is "dynamic"?

Thanks for helping,
Ganninu.

jim mcnamara · 11-19-2003, 09:11 AM

For example, in Oracle you can do this several ways, here are two:

1. stored procedures take arguments - just what you want
you call the procedure in SQL with arguments

2. the substitution operator & allows:

Code:

select * from &1;

The user is prompted for the value

Most other DB packages have similar means of passing arguments.
Which DB are you using - it would help to know.

ganninu · 11-19-2003, 09:44 AM

I'm using MySQL. If my program is program.sql and contains:

select * from &1,

then it would be really great if i just pass an argument like:

./program.sql /var/lib/mysql/table1

The only one thing which is restricting me from testing it, is that i dunno how to connect and pass the password from a .sql script (i only know how to do it from command prompt by using 'mysql -u username -p' after which i give the password, so if someone can confirm that the above method of passing an argument, works, I would really appreciate it!

Thanks again,
Ganninu.

palmercabel · 11-19-2003, 01:50 PM

Ganninu, I'm an oracle guy so this may not help you....
Oracle's sql includes a "connect" verb, i.e. connect user@instance/pw.
IF you must connect to mysql before you run a .sql then you must find another way to do this.
HTH

Hko · 11-19-2003, 05:44 PM

About the password thing: One way is to pass the password on the command line, like this:

Code:

$ mysql -u ganninu --password=YoUrPaSsWoRd

Another way to do this with MySQL is to have a file ~/.my.cnf containing user name and password. Example of ~/.my.cnf :

Code:

[client]
user = ganninu
password = YoUrPaSsWoRd

Now you don't need the -u or -p option to the mysql program anymore (unless you want to log in as different user). The mysql program will log in to MySQL without any prompting, assuming the user/password in ~/.my.cnf is correct of course.

Also make sure the rights of the file are set like this:

Code:

$ ls -l ~/.my.cnf
-rw-------    1 heiko    heiko          41 Nov 12 00:26 /home/heiko/.my.cnf

You can set the rights like this with:

Code:

$ chmod 600 ~/.my.cnf

About the parameter thing, I don't know of any native MySQL-way to do this, but you could use a shell script as a wrapper for this. For your example, create a bash script:

Code:

#!/bin/bash
SCRIPT=$(basename $0)
if [ "$#" != 1 ] ; then
    echo "Usage:  $SCRIPT <table>"
    exit 1;
fi
echo "select * from $1" | mysql yourdatabase

When you put this in a file and make it executable (chmod u+x scriptname), you can do:
$ ./scriptname addresses

Assuming of course you have (access to) a database, changed "yourdatabase" in the above script to the name of the actual database, and it has a table called "addresses".

Hope this helps.

ganninu · 11-20-2003, 06:12 AM

thanks a lot for your help - I just discovered the password thing since i didn't know that you had already posted a solution for it

I'll check out the parameter thingie and tell you what i've discovered. thanks again.

ganninu · 11-20-2003, 06:25 AM

Ok man it worked, but i had to remove the conditional statement and leave only the last line.

This means that for some strange reason, giving the arguments through a pipe works whereas doing an input redirection doesn't work (i.e. something like this: mysql --user="my name" --password="my password" 'argument1' < some_commands_in_sql.sql

Hko · 11-20-2003, 10:40 AM

Quote:

Originally posted by ganninu
Ok man it worked, but i had to remove the conditional statement and leave only the last line.

OK. If that works for you... It does work on mu system.
But why? Do you need to pass more than 1 parameter?
Then just change the "1" in the line:

Code:

if [ "$#" != 1 ] ; then

Quote:

Originally posted by ganninu
[B]This means that for some strange reason, giving the arguments through a pipe works whereas doing an input redirection doesn't work (i.e. something like this: mysql --user="my name" --password="my password" 'argument1' < some_commands_in_sql.sql

I don't think it is for "some strange reason". MySQL itself doesn't support such dynamic qeuries (as far as I know), but a bash script does take parameters ("arguments"). So you use a bash script to take an argument, and have it construct a string containing a query with the argument substituted by a "real" table name. And then echo the complete query to mysql through a pipe.

ganninu · 11-20-2003, 05:36 PM

So this means that i can never have the bash script and the sql statements in 2 seperate files for modularity. My real aim was to have a file which contains only sql statements, and which at the same time accepts arguments from an external program. Now, I have the bash script integrated with the SQL statements, which is fine for my purpose. But suppose someone who is reading this thread has a large amount of SQL statements, it would be desirable to have them on a seperate file. Till now i haven't yet come up with that solution although i'm able to place multiple SQL statements on 1 line.

If you have any ideas abt that sol'n pls point them as it is very interesting to know abt it.

Thanks again, ganninu.

Hko · 11-21-2003, 10:45 AM

Quote:

So this means that i can never have the bash script and the sql statements in 2 seperate files for modularity.

No, you can make a different script to accomplish that. You can have a script that edits the parameters from a .sql file by using sed and pipe the resulting query to mysql.

Simple example. Have two SQL files:

Code:

# gettable.sql
#
# Get all the records of a table.

select * from ##table##

and:

Code:

# count.sql
#
# Count all records of a table.

select count(*) from ##table##

Then have a bash script like this to execute them:

Code:

#!/bin/bash

SCRIPT=$(basename $0)
if [ "$#" != 2 ] ; then
    echo "Usage:  $SCRIPT <sql-file> <table>"
    exit 1;
fi

sed "s/\(.*\)##table##\(.*\)/\1$2\2/" $1 | mysql --user="my name" --password="my password" yourdatabase

Execute the queries like this:

Code:

bash$ ./scriptname count.sql addresses
bash$ ./scriptname gettable.sql addresses

ganninu · 11-21-2003, 11:12 AM

what does the SCRIPT=$(basename $0) line do?

Hko · 11-21-2003, 12:04 PM

Nothing special.
The $0 parameter to a script is the name of the script itself.
Including the path it was started with, like /home/hko/bin/script or ./script

"basename" strips of the directory part.

This is just to output the correct script name in the help message, even when the script is renamed, or started through a symvbolic link.

ganninu · 11-25-2003, 05:30 AM

thanks very muuch - got the whole concept. Now i'm only trying to generalize to take multiple args. Gonna read a bit abt sed!!