CODEWITHSUNDEEP
shellsqlite
Brknl
Brknl

Reputation: 97

How to get output of sqlite command in shell script

I am trying to make rollback if something went wrong during the execution of my sql crud operations or if it can not make commit with shell scripting.

I have test2.sh and test.sh test.sh:

 #!/bin/sh
 sqlite3 dB.sqlite << EOF
 begin;
 select * from Table1;

and test2.sh

#!/bin/sh
if echo `./test.sh`|grep -q "SQL error"; then
  rollback;
else
  err=commit;
  if echo $err |grep -q "error"; then
    rollback;
  fi
fi

There is no table called Table1 and i expected to get the sql error output of test.sh and rollback.

But it gives error : rollback: command not found. How can i get the error and make rollback? or Is this way that i follow right?

Upvotes: 0

Views: 3547

Answers (1)

Miklos Aubert
Miklos Aubert

Reputation: 4575

Your test2.sh script fails because the shell is trying to execute a program called rollback, hence the "command not found". You want to use the sqlite instruction rollback, whichs means you'd have at least to do this :

sqlite3 dB.sqlite << EOF
rollback;
EOF

But I don't think this will work. As I see it, rollback has to occur within the same sqlite session as the one started by test.sh to have any effect. Your test.sh script already makes sqlite plow through the commands until it reaches EOF, when you grep the errors with test2.sh, it's already too late. There's no way you can do a rollback from the test2.sh script, which sits outside that sqlite session.

So, to complete my answer, I recommend you to step away from Bash scripting and use a programming language, which will allow you to open a sqlite session, execute commands and control the transactions. Quick example in Python :

import sqlite3

conn = sqlite3.connect('dB.sqlite')

c = conn.cursor()

try:
    c.execute('INSERT INTO Table1 VALUES(1)')
    conn.commit()
except:
    conn.rollback()

c.close()

Upvotes: 1

Related Questions