Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

If this should be moved to the DBA exchange, I apologize. Feels more like linux than DB to me, so here goes:

I've got some machines that run scheduled cron jobs every night and email me the output. I do not want emails for things like this. In general I think the way we use email is broken, but that's another story.

So I started thinking that I could keep a central SQLite database that stored information about when the jobs started, and finished, and maybe even the output. Then I could just build a webpage that queries that and let's me know was going on last night.

So I came up with a simple schema and can run this command at the beginning of a script.

sqlite3 dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"

So now I have a record that states that my job started and at what time. Hooray. Then I can run a similar command to put the time that the job ends.

So. That works great if the database and the tasks are on the same machine. I go to another machine and need to update the sqlite database.... How can I do that efficiently?

I tried this

ssh aaron@10.1.150.53 'sqlite3 /home/aaron/dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"'

But that returns:

Error: no such column: NOW

I tried some variations but didn't get anywhere.

Am I close? Should I be doing something totally different? Am I reinventing the wheel?

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

That's all comes from quoting. Try this one:

ssh aaron@10.1.150.53 'sqlite3 /home/aaron/dbname.db "UPDATE data SET \
LastStart = DATETIME('''NOW''') WHERE TaskName = '''taskname'''"'

ps. You need to quote NOW, otherwise sqlite will try to find column with such name. But your quotes ' will be eaten by quotes from ssh. You can't escape ', therefore three quotes ''' are used (the first off ssh quote, second it the quote you need to pass to sqlite, and the last one open ssh quote again).

pps. Furthermore you can inverse quotes like this:

ssh aaron@10.1.150.53 "sqlite3 /home/aaron/dbname.db \"UPDATE data SET \
LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'\""
share|improve this answer
    
The first command you gave does not work; same issue. –  LVLAaron Jun 26 '12 at 0:46
1  
Second command, however, works great. –  LVLAaron Jun 26 '12 at 0:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.