Shell scripting SQLite

Question

How do I write a shell script that displays SQLite results? I have written a script that adds an entry to the SQLite database. Now I want to display the results after adding that entry. Here's my script:

echo 'insert into myTable (Date, Details, Category, Average) values (datetime('\''now'\'','\''localtime'\''), '\'''$1''\'', '\'''$2''\'', '$3');'|sqlite3 /Users/user/Documents/Test/dbName.db

After this I want the script to echo/spit the output of statements:

select sum(Average) from (select * from myTable where Category = 'category1');
select sum(Average) from (select * from myTable where Category = 'category2');

The format should be like this:

Category1 total = <output of first statement>
Category2 total = <output of second statement>

Thats it. I am quite new to SQL and not that great with shell scripting. I am also looking for good tutorials explaining problems like this.

Answer 1

If you need to assign sqlite SELECT result to a shell variable, you can do this.

r=$(sqlite3 your_db_path.db "select something from some_table where condition")

$r will be your variable.

Single row also can be fetched. You can do some work to split it into an array, may be using IFS

Additionally keep in mind to use #!/bin/bash convention on top of your every shell script. It'll solve many unwanted issues. Some times old #!/bin/sh convention gives troubles. :).

Answer 2

One common way to solve this problem is to use a shell feature called a here document, try this:

 sqlite3 /Users/user/Documents/Test/dbName.dba <<EOS
     insert into myTable (Date, Details, Category, Average) 
               values(datetime('now','localtime'), '$1', '$2', '$3');

     select "Category1 total = " sum(Average) from (
          select * from myTable where Category = 'category1'
     );

     select "Category2 total = " sum(Average) from (
         select * from myTable where Category = 'category2'
     );

 EOS

Note that EOS can be any string you like (I think of EndOfScript), but it must be alone on the last line of text with no trailing whitespace.

As I don't use sqlite3, you may need some statment to close off the batch that I'm not aware of. Also, I'm not certain that the '$1' stuff will work, if sqlite3 is forgiving, try "$1", etc instead. Also, you may need to an a comma after the "CategoryN total = " string.

Note that this solution allows you to create your sql DML statements pretty much as big/long as you want. For stuff that will happen regularly and it ranging over large tables, if you have permissions on our system, you may want your DML to a stored procedure and call that.

I hope this helps.

(If this doesn't work, please edit your post to indicate shell you are using, OS/Linux Ver and a minimal version of error messages that you are getting).