Is Not Null doesn't work on timestamp field

Wappler General Bugs
#1

======== TEMPLATE BUG FORM ========

Wappler Version : 2.3.1
Operating System : PHP 7.2 / mySql 10.3

Expected behavior

What do you think should happen?

Should be able to use the Is Not Null condition on a timestamp field and return results

Actual behavior

What actually happens?

{"code":"42000","file":"\/public_html\/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 \/public_html\/dmxConnectLib\/lib\/db\/Connection.php(80): PDO->prepare('SELECT * FROM `...')\n#1 \/public_html\/dmxConnectLib\/modules\/dbconnector.php(90): lib\\db\\Connection->execute('SELECT * FROM `...', Array)\n#2 \/public_html\/dmxConnectLib\/lib\/App.php(163): modules\\dbconnector->single(Object(stdClass), 'query1')\n#3 \/public_html\/dmxConnectLib\/lib\/App.php(128): lib\\App->execSteps(Object(stdClass))\n#4 \/public_html\/dmxConnectLib\/lib\/App.php(98): lib\\App->execSteps(Array)\n#5 \/public_html\/dmxConnectLib\/lib\/App.php(71): lib\\App->exec(Object(stdClass))\n#6 \/public_html\/dmxConnect\/api\/scratch\/null.php(8): lib\\App->define(Object(stdClass))\n#7 {main}"}

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

Create a query action in server connect and use the Is Not Null condition on a timestamp data type field, then execute.

Perhaps this is related to my other bug report:

#2
#3

@George @patrick Is this something that could be addressed soon? I’ve hit another point where I should be using the IS NOT NULL using mysql (maria) and it generates an error.

–Ken

#4

I should add that taking the results and using a where clause to remove the null values is a very easy work-around, but doing within the select would be preferred.

#5

It must have generated some incorrect query, enable the debug flag in the query step and let me know the query it is shown in the output.

#6

Thanks @patrick

10.3.20-MariaDB

{
  "query1": {
    "options": {
      "connection": "sandbox",
      "sql": {
        "type": "select",
        "columns": [
          {
            "table": "scratch",
            "column": "id"
          }
        ],
        "table": {
          "name": "scratch"
        },
        "joins": [],
        "wheres": {
          "condition": "AND",
          "rules": [
            {
              "id": "scratch.a_text",
              "field": "scratch.a_text",
              "type": "string",
              "operator": "is_not_null",
              "value": null,
              "data": {
                "table": "scratch",
                "column": "a_text",
                "type": "text"
              },
              "operation": "IS NOT NULL"
            }
          ],
          "conditional": null,
          "valid": true
        },
        "query": "SELECT id\nFROM scratch\nWHERE a_text IS NOT NULL",
        "params": []
      },
      "test": true
    },
    "query": "SELECT `id` FROM `scratch` WHERE (`a_text` IS NOT NULL ?)",
    "params": [
      {
        "id": "scratch.a_text",
        "field": "scratch.a_text",
        "type": "string",
        "operator": "is_not_null",
        "value": null,
        "data": {
          "table": "scratch",
          "column": "a_text",
          "type": "text"
        },
        "operation": "IS NOT NULL"
      }
    ]
  }
}

And the error generated:

{
  "code": "42000",
  "file": "/home/tos/domains/wappler.uniqueideas.com/public_html/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 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/lib/db/Connection.php(80): PDO->prepare('SELECT `id` FRO...')\n#1 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/modules/dbconnector.php(98): lib\\db\\Connection->execute('SELECT `id` FRO...', Array)\n#2 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/lib/App.php(173): modules\\dbconnector->single(Object(stdClass), 'query1')\n#3 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/lib/App.php(137): lib\\App->execSteps(Object(stdClass))\n#4 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/lib/App.php(107): lib\\App->execSteps(Array)\n#5 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnectLib/lib/App.php(72): lib\\App->exec(Object(stdClass))\n#6 /home/tos/domains/wappler.uniqueideas.com/public_html/dmxConnect/api/null.php(8): lib\\App->define(Object(stdClass))\n#7 {main}"
}
#7

Seems that there is a question mark in the query that doesn’t belong there

SELECT `id` FROM `scratch` WHERE (`a_text` IS NOT NULL ?)

I believe this was an old bug and should be fixed, will check if there is still a problem. Best is to remove the dmxConnectLib folder and let Wappler recreate it, just opening an action file and saving it again should copy the files into your site. It is possible that you aren’t using the latest version.

#8

Just tried that with no change. Closed Wappler. Deleted dmxConnectLib for this project. Opened Wappler. Opened server action and saved. New dmxConnectLib was created locally and uploaded to site, but the results are the same.

Note to anybody listening…make sure to save any custom formatters because they live within this folder tree.

#9

Perhaps just renaming dmxConnectLIb would be a safer route, can always be deleted later

#10

Please test if it is fixed with this update, overwrite the file in dmxConnectLib/lib/db.

SqlBuilder.zip (2.9 KB)

Query runs fine until IS NOT NULL condition is set
#11

Thanks @patrick. That does indeed fix the issue.

#12
#13

@patrick did this ever make it into current releases?

#14

It should have, but I will check tomorrow when I’m back at the office.

#15

I ran into the same issue @patrick so I think maybe you forgot to add this file to the latest builds.