Andy's Unix FAQ

Question;

Hi Andrew,
I'm new to C Shell Scripting and this is the first time i am doing it. Therefore, i am in desperate need of help. I am supposed to use C Shell Scripting to read some data from database base on some simple sql and then input this data into a flat file. Can you explain to me how to do this?
Thanks (:->

Answer;

This question is mostly about SQL rather than shell programming....

You need to identify which program will perform SQL queries for you. This will vary according to which database you are using.

Below is a simple C-shell script which illustrates how to do this;

#!/bin/csh -f
#
set outfile = "/tmp/sql.out"
set team = "Germany"
#
/usr/local/bin/psql worldcup << ENDSQL >! $outfile
select teamname,goals1,goals2 from teams,matches
where teamname='$team';
ENDSQL   

The keyword ENDSQL is my arbitary choice and simply marks the text that should be passed to 'psql' as stdin. The SQL itself could go in a different file in which case the initial command would be;

	psql worldcup < sqlcommandfile >! $outfile

'worldcup' is, in this instance the database name, which you will need to replace with your own database name. You may also need to expand this to a full 'connect string' ( SQL jargon ) which includes the name of system where the database resides, and possibly a username/password. Both these questions you may need to ask of your database administrator.

If you need to massage the output format, then I would suggest using 'awk' on the $outfile, this being a common approach..

Style notes:

C-shell has never been well thought of as a scripting language by a number of people. Whilst it certainly has some drawbacks relating to quoting and security, it is still useful for 'quick-and-dirty' solutions..

Currently, probably the best way (neatest, most reliable & secure) of achieving this task is Perl, with the appriopiate RDBMS module. This approach makes it far easier both to detect errors in the SQL query and format the data after extraction..

References:

For more information on SQL have a look here.

http://www.sqlcourse.com/
http://www.ilook.fsnet.co.uk/ora_sql/sqlmain.htm

And dbforums offers discussion groups covering all the major databases.