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 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>

Solution

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).

Edit: Note that if thru your development testing you go with 'EOS' quoting as mentioned in a comment below, the O.P.'s quoting ('\'''$1''\'') may still be appropriate depending on levels of shell nesting. Correct quoting of embedded code can be quite a project to get right )-;