Using shell script to insert data into remote MYSQL database

Question

I've been trying to get a shell(bash) script to insert a row into a REMOTE database, but I've been having some trouble :(

The script is meant to upload a file to a server, get a URL, HASH, and a file size, connect to a remote mysql database, and insert the data into an existing table. I've gotten it working until the remote MYSQL database bit.

It looks like this:

#!/bin/bash

zxw=randomtext
description=randomtext2

for file in "$@"
 do
 echo -n *****
 ident= *****
 data= ****
 size=` ****
 hash=`****
mysql --host=randomhost --user=randomuser --password=randompass randomdb
insert into table (field1,field2,field3) values('http://www.example.com/$hash','$file','$size');
echo "done"
done

I'm a total noob at programming so yeah :P

Anyway, I added the \ to escape the brackets as I was getting errors. As it is right now, the script is works fine until connects to the mysql database. It just connects to the mysql database and doesn't do the insert command (and I don't even know if the insert command would work in bash).

PS: I've tried both the mysql commands from the command line one by one, and they worked, though I defined the hash/file/size and didn't have the escaping "".

Anyway, what do you guys think? Is what I'm trying to do even possible? If so how?

Any help would be appreciated :)

Answer 1

The insert statement has to be sent to mysql, not another line in the shell script, so you need to make it a "here document".

mysql --host=randomhost --user=randomuser --password=randompass randomdb << EOF
insert into table (field1,field2,field3) values('http://www.site.com/$hash','$file','$size');
EOF

The << EOF means take everything before the next line that contains nothing but EOF (no whitespace at the beginning) as standard input to the program.