Insert identity returning text 'INSERT' instead of actual ID

[Philip_J]

Wappler Version : 3.8.2
Operating System : Win10
Server Model: NodeJS
Database Type: PostgreSQL
Hosting Type: Docker

Expected behavior

What do you think should happen?

When you select the identity output from an INSERT server action step, it should return the ID of the record that was just inserted.

Actual behavior

What actually happens?

It is returning the text ‘INSERT’ instead.

This link below seems to be the same bug from last year that was closed off:

Database insert's identity called from repeat returning "INSERT"

Check your json generated by the insert action and make sure the “returning” key has “id” as value. More info here towards the end of the topic:

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

As above, simply duplicate my server/wappler environment and create a simple insert step and then a ‘set value’ step to output the identity of the previous insert.

Here is a sample i’ve created:

Here is the JSON API file:

[
  {
    "name": "insert",
    "module": "dbupdater",
    "action": "insert",
    "options": {
      "connection": "db",
      "sql": {
        "type": "insert",
        "values": [
          {
            "table": "rosters",
            "column": "name",
            "type": "text",
            "value": "Identity Test Insert"
          }
        ],
        "table": "rosters",
        "query": "INSERT INTO rosters\n(name) VALUES ('Identity Test Insert')",
        "params": []
      }
    },
    "meta": [
      {
        "name": "identity",
        "type": "text"
      },
      {
        "name": "affected",
        "type": "number"
      }
    ],
    "output": true
  },
  {
    "name": "value_identity",
    "module": "core",
    "action": "setvalue",
    "options": {
      "value": "{{insert.identity}}"
    },
    "output": true
  }
]

Here is the output in Chrome:

{"insert":{"affected":1,"identity":"INSERT"},"value_identity":"INSERT"}

I am using the latest Wappler and the db.js file in lib/core was updated in Nov/2020 since the previous post/bug was fixed.

[otavionestares]

same here!

[Philip_J]

Ok, so I have a workaround that is working for me. It seems for whatever reason the JSON file is not generating/writing the ‘return ID’ part of the SQL query.

I was able to edit the server action step and add this to my query and it is now returning the identity/ID correctly.

Here is the data from the JSON file:

You can see that after params[] i have now added "returning": "id" and it will output the identity correctly.

In my original JSON text posted above, this "returning": "id" was missing.

{
    "name": "NewName",
    "module": "dbupdater",
    "action": "insert",
    "options": {
      "connection": "db",
      "sql": {
        "type": "insert",
        "values": [
          {
            "table": "departments",
            "column": "name",
            "type": "text",
            "value": "test Dep Name"
          }
        ],
        "table": "departments",
        "query": "INSERT INTO departments\n(name) VALUES ('test Dep Name')",
        "params": [],
        "returning": "id"
      }
    },
    "meta": [
      {
        "name": "identity",
        "type": "text"
      },
      {
        "name": "affected",
        "type": "number"
      }
    ],
    "output": true
  }

[otavionestares]

Great @Philip_J! Thanks! I’ll try tomorrow morning!

[Philip_J]

The only problem with this is that any time you open and then save the INSERT step then it overides the manual addition to the JSON file and removes the "returning": "id" text.

I imagine you could use a custom INSERT query and return the ID as well. I’m sure Wappler will implement a fix soon though.

[patrick]

This should be fixed with the latest Wappler update.