LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 11-19-2003, 02:52 AM   #1
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Rep: Reputation: 30
Question Passing external variables/arguments to SQL program


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.
 
Old 11-19-2003, 09:11 AM   #2
jim mcnamara
Member
 
Registered: May 2002
Posts: 964

Rep: Reputation: 34
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.
 
Old 11-19-2003, 09:44 AM   #3
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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.
 
Old 11-19-2003, 01:50 PM   #4
palmercabel
Member
 
Registered: Oct 2003
Posts: 64

Rep: Reputation: 15
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
 
Old 11-19-2003, 05:44 PM   #5
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
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.

Last edited by Hko; 11-19-2003 at 05:47 PM.
 
Old 11-20-2003, 06:12 AM   #6
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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.
 
Old 11-20-2003, 06:25 AM   #7
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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
 
Old 11-20-2003, 10:40 AM   #8
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
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.
 
Old 11-20-2003, 05:36 PM   #9
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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.
 
Old 11-21-2003, 10:45 AM   #10
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
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
 
Old 11-21-2003, 11:12 AM   #11
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
what does the SCRIPT=$(basename $0) line do?
 
Old 11-21-2003, 12:04 PM   #12
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
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.
 
Old 11-25-2003, 05:30 AM   #13
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing arguments to a shell script subu_s Programming 3 09-02-2005 05:13 AM
Problem with passing arguments in Perl bahadur Programming 1 05-30-2005 01:47 AM
passing a list of arguments to a command hdagelic Linux - General 2 05-09-2005 09:30 AM
Passing Arguments into the Thread Function George_gk Programming 2 01-31-2005 05:03 AM
Handline passing arguments in C AMMullan Programming 9 03-22-2004 01:37 AM


All times are GMT -5. The time now is 09:00 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration