4

There is a bash script that will do "some magic"™ to files which will be referenced to a sqlite database and after doing "some magic"™ the database should be updated. Here are the simplified code

sqlite3 database.db "select NUMBER from table WHERE STATUS = 'N'" | while read line; do
    SELECTION=$(echo $line | awk -F'|' '{ print $1 }')
    [some magic]™
    sqlite3 database.db "update table SET STATUS='Y' WHERE NUMBER='$SELECTION'"
done

Everything works, the sqlite database will be read line by line and I could do "some magic"™ to the referenced files, but I can´t update the line in the database - I´ve got an error:

Error: database is locked

Anybody knows how I could update a database while reading a database? Or are there another solution how I could do this?

2 Answers 2

5

if you don't want to move to a real SQL database, and temporarily storing the result is not possible, you'll have to make sure you don't run concurrent SELECT and UPDATE on the same table.

see LIMIT directive for SELECT.

so you'd do something like:

line=x
while [ -n "$line" ]
do
    line=$(sqlite3 database.db "select NUMBER from table WHERE STATUS = 'N'" LIMIT 1)
    SELECTION=$(echo $line | awk -F'|' '{ print $1 }')
    [some magic]™
    sqlite3 database.db "update table SET STATUS='Y' WHERE NUMBER='$SELECTION'"
done

That would make SELECT always return just one result and finish, which you would then process and UPDATE, and rerun SELECT to get next result (as STATUS changed, SELECT would get next value, not the old one as it no longer matches "N")

or maybe you could do "some magic (tm)" via SQL instead of a shell, so you could offload all the work to SQL engine...

2
  • You´re right. In the mean time I´ve found other solution with two sql querys (One for setting all selects in a bash array, another for setting variables) but your solution are easier and with lower ressources (I think).a
    – UsersUser
    Oct 28, 2014 at 9:11
  • 1
    This worked for me. I set my limit to a much larger number (1000) and worked in batches so that it wouldn't be rerunning the query or committing on every single record update. I had a very large data set so this was important. I was at first a little apprehensive about rerunning the query every iteration but this did not pose any noticeable performance issues after all.
    – Tristan
    Mar 28, 2019 at 0:11
1

You have to proceed in two steps. Either, store the result of the first query to a temp file (or variable) and then process it; or, accumulate the update statements in a temp file or variable and execute after the select is finished.

3
  • Well, the problem is: this is a huge database so I could´t store the result as a file or a variable to catch all results. And I can´t separate it into two steps because I need a loop that will catch all results (or I did´t know I could catch all lines of the database).
    – UsersUser
    Oct 23, 2014 at 18:46
  • @UsersUser: Use a real database, then.
    – choroba
    Oct 23, 2014 at 20:45
  • Yes, a real database would be easier with more options but it also need an installation of a database server which I don´t want on my desktop computer.
    – UsersUser
    Oct 28, 2014 at 9:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .