SQL Query issue

[Raphael Awoseyin]

I wonder if there was some tinkering with the SQL Query parsing in the Tasker 4.9u3 that broke something. I executed the following query in aSQLiteManager and it gave me the expected result set:

SELECT MemberPhone, MemberDebt from vxmembers WHERE MemberPhone LIKE '%%vxmemberphone%';

I had set variable %vxmemberphone to something like +44123456789

When I execute the same query on the same database file from Tasker, I get an empty result set - no error messages. I had executed a similar query in Tasker 4.9u1 and I thought it worked well.

Any comment?

[Chris Brown]

Having same issue on 4.9u4.

No error, no output.

Query is :
SELECT stock.stock_code, stock.stock_description FROM stock WHERE stock.stock_description LIKE '%new%';

[Raph]

I think the issue is that Tasker is confused on how to treat the % sign. In Tasker's parlance, '%new is a variable. However, in SQL, the statement
LIKE '%new%'
means you are seeking values like '????new????'

I solved the problem by introducing another variable to eliminate the confusion.
 

[Chris Brown]

Could you post an example of your working query? 

I've previously been able to get LIKE values to work using Shell and sqlite3 install binary. However I can't even get the Tasker SQL Query to SELECT where 'Cond = 1' for example.

SQL Query works fine with INSERT.

I've rolled back to 4.9u1 as you mentioned SQL Query was working then but I'm still getting no output and no error.

[Chris Brown]

Tasker SQLite plugin also returns expected result

[Pent]

SELECT stock.stock_code, stock.stock_description FROM stock WHERE stock.stock_description LIKE '%new%';

And have you got a variable called %new in the task ?

Pent

[Chris Brown]

Apologies, I should been a bit clearer. I'm having trouble with getting any SELECT statement to work, regardless of whether they contain tasker variables or not. 

The exact query in the Action: SQL Query is

SELECT stock.stock_code, stock.stock_description FROM stock WHERE stock.stock_description LIKE '%%arg%';

the variable %arg is text input from a scene, for example 'new'.

Even if I change the WHERE statement to something like stock.stock_code = 1; it still gives no output when there should be something.

The SELECT statement works both works in Action: Run Shell, with the command:

export LD_LIBRARY_PATH=/vendor/lib*:/system/lib*
sqlite3 %dbFileLocation "SELECT stock.stock_code, stock.stock_description FROM stock WHERE stock.stock_description LIKE '%%arg%';"

The SELECT statement also works with the Tasker SQLite Plugin.

The only statement I've been able to get to work correctly in the Action: SQL Query is:

INSERT INTO stocktake_date (stocktaking_date) VALUES ('%openDate');

I'm only a beginner with sql, but the fact that it works on both Run Shell and Tasker SQLite Plugin has really thrown me. Trying to export the profile with App Factory to an unrooted phone, so Run Shell option is out and would prefer not have to install the plugin..

[Pent]

You realise that the output variable is an array, so the first DB row is in %output1 for instance ?

Maybe also try

SELECT * from stock

Also case can make a difference on the table name if I remember right, maybe
check it.

Pent

[Chris Brown]

Ah, yep that'll do it. Was trying to refer to the output as just a normal variable like with Shell and Plugin. All sorted! Thank you Pent!