Json_each not working in App Flow, but works in Custom Query Builder

Wappler General Bugs
#1

Not sure if I’m doing something wrong or if there’s a bug. I’m working on a custom query since Wappler’s Database Query Builder doesn’t support JSON functions (i.e. json_each). I am able to test the query in the Custom Query Builder, but when I add the App Flow to a page and run it, the query data is blank.

SELECT `FromName`, `Subject` FROM `Email`, json_each(`Email`.`GmailLabelIds`) WHERE json_each.value = ? ORDER BY `ReceivedDateTime` ASC;

This video shows the query builder returning records, but when I run the App Flow on a page, no results are returned.

#2

@George any idea why this works in Query Builder, but I get "Error invoking remote method 'CapacitorSQLite-query': Error: Query: Error: SelectSQL: QueryAll: SQLITE_RANGE: column index out of range" in the app flow within Electron?

image
image1396×230 7.74 KB

Within the browser I get "Query failed: SelectSQL: queryAll: undefined

image
image1387×298 17.7 KB

Here’s the query

SELECT Subject, GmailLabelIds FROM Email, json_each(Email.GmailLabelIds) WHERE json_each.value = 'INBOX' ORDER BY ReceivedDateTime ASC;

If I remove the json_each, then the query works.

SELECT Subject, GmailLabelIds FROM Email ORDER BY ReceivedDateTime ASC;

image
image931×198 11 KB

Here’s the table with sample data and app flow if it helps.
Email Table.zip (936 Bytes) testflow.zip (697 Bytes)

#3

If the query is exactly the same than the only think I can think of is that the parameters you passed are somehow empty in the runtime.

#4

I hard coded the value, so no parameters at the moment.

#5

Could there be a defect/limitation in dmxCapacitorSQLite.js that doesn’t allow json_each?

#6

json_each is a very specific json extension implemented in SQLite so you have to check the version and support at their site and github.

#7

The query works in Wappler’s query builder and using Beekeeper Studio against the SQLite db connection, that leads me to believe it’s something in capacitor or the dmx app connect adapter.

#8