SQlite Run Shell Command Not Working

[Rory Harnisch]

Task: Notify me if there is a new episode of the Jason Ellis Show.

I do this using a run shell command looking into the database to retrieve the necessary episode information. Then I massage the variables into an If statement to notify me if there is another new episode (see attached Tasker Description of the tasks). The only way that the Run Shell command works is if I check "Continue Task After Error."

It won't work otherwise. Is this normal??? I'm not sure where to start looking into the problem. Can someone help me?

[Marta Hintz]

run the task and check the log file.  See where it errors out.  

[Rory Harnisch]

This is what was shown from the logs.

20131218 19.33.54 T Running  ID185    Check For New Jason Ellis Show

20131218 19.33.54 A OK       ID185.1  Check For New Jason Ellis Show.Run Shell

20131218 19.33.55 T ExitErr  ID185    Check For New Jason Ellis Show

It never ran anything passed action 1.

[Bob Hansen]

Have you assigned a variable to the Run Shell Store Errors In: option to see what the error is?

[Rory Harnisch]

The problem is that if "continue task after error" isn't checked, the task errors out and nothing returns. If it is checked, I get the following:

Output: 1387396800000

Error: sh: <stdin>[2]: : not found

Result: 127

The output is what I want but I'm wondering why it only works under the condition above.

[Bob Hansen]

That error message indicates that the Run Shell command is failing. Have you tested the command using a terminal emulator?

[Rory Harnisch]

No, please walk me through it if you have a minute. I have terminal emulator installed on my LG G2 vs980 (VZW).

[Bob Hansen]

Open the terminal emulator.

Type su <enter>   <== this command gets superuser (su) access 

Then copy and paste the command line entry and hit enter. It should work returning the 1 result.

* I have never included the "system/xbin/" before sqlite, but I think it should be ok.

[Rory Harnisch]

Ok so I ran it and this is what I got back:

u0_a44@vs980:/ $ su

root@vs980:/ # sqlite3 /data/data/com.sirius/databases/aod.db "SELECT AIR_DATE FROM EPISODES ORDER BY AIR_DATE DESC limit 1";

sqlite3 /data/data/com.sirius/databases/aod.db "SELECT AIR_Dtabases/aod.db "SELECT AIR_DATE                             ISODES ORDER BY AIR_DATE DESC limit 1";                                       <

1387396800000

sh: : not found

127|root@vs980:/ #

[Bob Hansen]

Remove the quotes ("SELECT....limit 1"). 

[Rory Harnisch]

Results without the quotations:

u0_a44@vs980:/ $ su
root@vs980:/ # sqlite3 /data/data/com.sirius/databases/aod.db SELECT AIR_DATE FROM EPISODES ORDER BY AIR_DATE DESC limit 1;

sqlite3 /data/data/com.sirius/databases/aod.db SELECT AIR_DAtabases/aod.db SELECT AIR_DATE                              FROM EPISODES ORDER BY AIR_DATE DESC limit 1;
sqlite3: Error: too many options: "AIR_DATE"
Use -help for a list of options.
1|root@vs980:/ #

--
You received this message because you are subscribed to a topic in the Google Groups "Tasker" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tasker/zfngOIfWAsA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tasker+un...@googlegroups.com.
Visit this group at http://groups.google.com/group/tasker.
For more options, visit https://groups.google.com/groups/opt_out.

[Bob Hansen]

Well, that, believe it or not, was an improvement!

The result caused me to go look up "sqlite underscore". Turns out an underscore character is considered a wildcard in sqlite. To use them literally they must be escaped using a backslash \. 

See this: http://stackoverflow.com/questions/7323162/sqlite-like-and

Try changing your SELECT statement to this:

SELECT AIR\_DATE FROM EPISODES ORDER BY AIR\_DATE DESC limit 1;

[Rory Harnisch]

This was the error:

u0_a44@vs980:/ $ su
root@vs980:/ # sqlite3 /data/data/com.sirius/databases/aod.db SELECT AIR\_DATE FROM EPISODES ORDER BY AIR\_DATE DESC limit 1;
sqlite3 /data/data/com.sirius/databases/aod.db SELECT AIR\_Dtabases/aod.db SELECT AIR\_DATE                             ISODES ORDER BY AIR\_DATE DESC limit 1;                                       <

sqlite3: Error: too many options: "AIR_DATE"
Use -help for a list of options.

sh: : not found
127|root@vs980:/ #

The reason why I used that exact phrase (from the beginning) was because I got it from SQlite Debugger.

[Bob Hansen]

What app is associated with the  /data/data/com.sirius/databases/aod.db?

If I can, I will install it and see if I can figure out what is wrong.

[Rory Harnisch]

It's Sirius XM radio. It's a free app but you have to pay for the internet radio service. You could just do the free one week trial (highly suggested if in the US) so you don't have to pay any money. If you're not willing or if it's too much work, lets try a different app like the android calendar since we both have it.

[Bob Hansen]

I thought maybe the database would be included in the app install but apparently not.

Try this test, it will tell you whether sqlite3 is able to open the database.

If  you run:

     sqlite3 /data/data/com.sirius/databases/aod.db

What do you get?

[Rory Harnisch]

Error: inclompete SQL: exit

[Bob Hansen]

I should have said run it in the terminal emulator. Don't forget su first.

[Rory Harnisch]

Here you go:

u0_a44@vs980:/ $ su
root@vs980:/ # sqlite3 /data/data/com.sirius/databases/aod.db

sqlite3 /data/data/com.sirius/databases/aod.db
SQLite version 3.7.16 2013-03-18 11:39:23 [www.ptsoft.org] [www.ptdave.com]
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

[Bob Hansen]

Ok, that indicates that it can access the database.

So try this:

sqlite3 /data/data/com.sirius/databases/aod.db "SELECT * FROM EPISODES"

*Include the quotes. Apparently it can work with or without quotes.

[Rory Harnisch]

Success!!! I got the entire database with episode information. I'm not sure why it works now.... the syntax must have been off because I have no idea what else it could have been. I narrowed down the field and got what I wanted. Check out my XML description below:

Check For New Jason Ellis Show (185)
A1: Run Shell [ Command:sqlite3 /data/data/com.sirius/databases/aod.db "SELECT AIR_DATE FROM EPISODES ORDER BY AIR_DATE DESC LIMIT 1" Timeout (Seconds):0 Use Root:On Store Output In:%latest_air_date_ms Store Errors In: Store Result In: ] 
A2: Variable Set [ Name:%latest_air_date_sec To:%latest_air_date_ms/1000 Do Maths:On Append:Off ] 
A3: Variable Convert [ Name:%latest_air_date_sec Function:Seconds to Date Time Store Result In:%latest_air_date ] 
A4: Variable Split [ Name:%latest_air_date Splitter: Delete Base:Off ] 
A5: If [ %latest_air_date1 ~ %DATE ]
A6: AutoNotification [ Configuration:Title: New Jason Ellis Show Episode Available
Text: %DATE episode now available
Action on Touch: Open Jason Ellis Show
Icon: file:///storage/emulated/0/Android/data/com.dropbox.android/files/scratch/Autonotification%20Icons/ellis_wolfknives_sticker.jpg
Status Bar Icon: ic_action_signal
Id: Jason Ellis Show
Persistent: true Package:com.joaomgcd.autonotification Name:AutoNotification Timeout (Seconds):0 ] 
A7: Profile Status [ Name:New Jason Ellis Show Available Set:Off ] 
A8: End If 
A9: Flash [ Text:No new Jason Ellis Show Episode Available. Latest Episode is on %latest_air_date1 Long:On ] 

Thank you thank you thank you! Much appreciated!