Can Unix shell script be used to manipulate databases?

Question

I have to read data from some files and insert the data into different tables in a database. Is Unix shell script powerful enough to do the job?

Is it easy to do the job in shell script or should I go about doing this in Java?

Answer 1

Can't test it right now, but something like:

echo "INSERT INTO foo (b,a,r) VALUES (1,2,3);" | mysql -u user -psecret -h host database

in a shell script should work. Don't know about getting Data out of it though

Answer 2

it is possible : Using your unix shell script, generate an sql script and use the cli to the database to execute the sql script.

if the amount of information is small enough you could build the SQL in memory, I advise against it though since you never know what the future holds (and it could be very large amount of data). Using one call per request doesn't allow you to benefit from bulk operations which are sometimes available.

Answer 3

You can, but it might be a bit ugly, for example if you're using mysql and suppose you have an SQL string stored in $sql

echo $sql | mysql -u[user] -p[password] -h[host]

p.s. it might be a good idea to tell us what database you're using so we can offer more specific help :p

edit: changed the example line so it actually works

Answer 4

Shell scripting (Bash or similar) primary intention is not to deal with databases. Go for Java or even better, ride this opportunity to learn the basics of a scripting language like Python or Ruby.

Answer 5

pipe is your friend.

for example, in mysql: echo 'load data infile /path/to/the/file into table table_name ...' | mysql -u mysql_user_id -p should do the work.

provided your file is somehow structured e.g. comma/tab separated etc.

for details, check the manual for your database.

Answer 6

Of course you can, assuming that you've got a command-line SQL client handy! I've done it w/ Sybase and the isql command-line client. You can even get clever and send stuff through awk and send scripts to generate commands on the fly. It might not be the most efficient way to do everything, but there's plenty of opportunity to flex your Unix hacker mojo.

Answer 7

If the data you are trying to import is in a reasonable format -- comma-delimited, for example -- and your database server has reasonable command line utilities, this should be no problem. MySQL has the "mysqlimport" command-line tool that will accept various arguments describing the format of the file:

mysqlimport --fields-terminated-by=, --ignore-lines=1 --fields-optionally-enclosed-by='"' <datafile.txt

Passing the data through perl/sed/awk one-liners can help with getting it in the proper format, and the shell script can easily handle prompting for filenames, handling arguments, etc.

Using the various command-line tools provided by Unix is the entire point of bash scripting. Perl, mysql, etc. are all part of that toolkit.

Answer 8

It depends on your Database Management System. Most of them have powerfull shell tools for importing data, doing even some ETL functions. Those tools could be even very performant if they support bulk loading - usually Java JDBC can't do that so easily.