DB Query Builder - "is not null" gives error

Wappler General Bugs
#1

Wappler Version : 3.03
Operating System : Windows 10 Pro
phpMyAdmin

Expected behavior

What do you think should happen?

Trying to filter query results and exclude any results where “fee_note” is “null”

Actual behavior

What actually happens?

Without the “is not null” everything works as expected.
With “is not null” selected I get the error below.

How to reproduce

  • Detail a step by step guide to reproduce the issue
  • A screenshot or short video indicating the problem
  • A copy of your code would help. Include: JS, HTML.
  • Test your steps on a clean page to see if you still have an issue

This is the simple Database Table.
We want all results where “business_id” is “417” and “fee_note” is not “null” so query result should be a single result.

image
image705×231 16.2 KB

So with this query

image

The result is as expected

image
image898×374 20 KB

But when in Wappler, adding “is not null” like this

image
image1026×605 30.5 KB

results in this error

code: "42000"
file: "/var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/db/Connection.php"
line: 80
message: "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"
trace: "#0 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/db/Connection.php(80): PDO->prepare('SELECT `fee_id`...')↵#1 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/modules/dbconnector.php(67): lib\db\Connection->execute('SELECT `fee_id`...', Array)↵#2 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/App.php(174): modules\dbconnector->select(Object(stdClass), 'business_detail...')↵#3 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/App.php(138): lib\App->execSteps(Object(stdClass))↵#4 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/App.php(108): lib\App->execSteps(Array)↵#5 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnectLib/lib/App.php(73): lib\App->exec(Object(stdClass))↵#6 /var/www/vhosts/daviesandgray.co.uk/httpdocs/dmxConnect/api/business_folder/business_details_actions.php(8): lib\App->define(Object(stdClass))↵#7 {main}"

In phpMyAdmin the same SQL statement

image

gives the correct single result

image

#2
#3

Please enable the Debug flag on the query and then check the returned json from the server action. It should contain the query that was generated on the server.

image

#4

With “greater than 0”

query: "SELECT `fee_id`, `business_id`, `menu_list_id`, `fee_note`, `invoice`, `data_order` FROM `tbl_principals_fees` AS `fees` WHERE (`business_id` = ? AND `fee_note` > ?)"

With “is not null”

query: "SELECT `fee_id`, `business_id`, `menu_list_id`, `fee_note`, `invoice`, `data_order` FROM `tbl_principals_fees` AS `fees` WHERE (`business_id` = ? AND `fee_note` IS NOT NULL ?)"

query: "SELECT fee_id, business_id, menu_list_id, fee_note, invoice↵FROM tbl_principals_fees AS fees↵WHERE business_id = :P1 /* 417 */ AND fee_note IS NOT NULL"

I hope this helps.

#5

This bug was fixed a while back, seems that the file was not updated in your project. You can delete the dmxConnectLib folder and let it copy the new files, that should fix it.

If it doesn’t work, use the following file, replace in dmxConnectLib/lib/db.

SqlBuilder.zip (2.9 KB)

#6
#7
#8