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

Server Side Components Bugs
#1

Wappler Version: Latest
Operating System: W10Pro latest
Server Model: NodeJS

Expected behaviour

This SC script is supposed to insert a product record in the database and update that record’s images field to contain all uploaded image’s file names:
image

The returned Identity value of the database insert (insert_product) action is expected to be an integer (product_id in this case, generated automatically since this field is an increments field for the products table).

Actual behavior

The returned Identity has value “INSERT”:
image

The condition for the add_images database update action returns an error because of that:

image
image935×231 4.85 KB

Quite sure this is a bug. Please let me know if you thinks it’s not or you need more details. Thanks.

Database insert’s identity returning “INSERT”
#2

Where is the step that uploads the images (upload_images)?

1 Like
#3

You have a Database Update in your repeat. Shouldn’t that be a database Insert?

#4

Hey @Emiel,

Ya, that ain’t right. :slight_smile:

I wasn’t able to duplicate on a docker/node instance using mariadb. You might post some more details on your hosting environment and db in case they are the culprit.

#5

That step is above the insert_product step and that part works well. Thanks for thinking along however, appreciate it :slight_smile:

1 Like
#6

Yes that could be a problem. Guess I used db update for the conditions tab, but going to try with a db insert action and the condition set inline in the insert options.

#7

I am on NodeJS with Postgresql. But let me try the db inser instead of db update first, because if there’s no value in the db field there’s nothing to be updated indeed :slight_smile:

#8

Got a similar issue here:

From a frontend form I am sending these variables to a server action:
image

There are 2 action steps, both database inserts.
The first one insert a record to the messages table:

image
image1225×157 14.6 KB

The second one inserts a record to the support_tickets table:

image
image1103×203 16.3 KB

It all works fine, except when the in the previous action inserted message’s identity is inserted into the messages column:

image

The error in the console then shows a similar error message as in the other case I posted here:
message: “insert into “support_tickets” (“messages”, “sender_email”, “sender_name”, “subject”) values ($1, $2, $3, $4, $5) - invalid input syntax for integer: “INSERT””

Unlike the previous case these are both db insert actions (where there was an db update action in the repeater in the other case). Anyone a clue?

#9

Sure looks like a bug to me – just confirmed that on Postgres, “INSERT” is always returned instead of the id of the record inserted.

@patrick

#10
#11

Looks like some kind of regression as this was already reported and fixed a few updates ago. Either that or your json for the SC file is outdated/wrong.

#12

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:

1 Like
#13

Just to confirm, is this only happening on NodeJS with a Postgres Database or also in other combinations?

Could you please post the generated json for the action step here, remove any user/password information out of it.

#14

I use identity all the time on php/mariadb, no problems.

Here is the action from a sandbox test:

{
  "exec": {
    "steps": [
      "connections/db",
      {
        "name": "insert1",
        "module": "dbupdater",
        "action": "insert",
        "options": {
          "connection": "db",
          "sql": {
            "type": "insert",
            "values": [
              {
                "table": "cars",
                "column": "make",
                "type": "text",
                "value": "test make"
              },
              {
                "table": "cars",
                "column": "model",
                "type": "text",
                "value": "test model"
              },
              {
                "table": "cars",
                "column": "year",
                "type": "text",
                "value": "2020"
              }
            ],
            "table": "cars",
            "returning": "id",
            "query": "INSERT INTO cars\n(make, model, year) VALUES ('test make', 'test model', '2020')",
            "params": []
          }
        },
        "meta": [
          {
            "name": "identity",
            "type": "text"
          },
          {
            "name": "affected",
            "type": "number"
          }
        ],
        "output": true
      }
    ]
  }
}

I also noticed that while the column for year is an integer, the action created it as a text. The data still inserts though.

#15

Hi Patrick,

I confirm this is not the exact same bug as here although the outcome is the same.

image

I have the returning field set in the SC file.

image

Nodejs and Postgres for me

#16

What kind of field is the id?

#17

In my case it’s:

table.specificType(“id”, “integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY”);

Seen in Wappler as:

image

#18

Try with this updated file, place it in lib/core.

db.zip (1.3 KB)

#19

New error:

{"status":"500","message":"returning is not defined","stack":"ReferenceError: returning is not defined\n    at Builder.fromJSON (/Users/jon/Documents/dev/mpc-node/lib/core/db.js:77:50)\n    at Function.knex.<computed> [as fromJSON] (/Users/jon/Documents/dev/mpc-node/node_modules/knex/lib/util/make-knex.js:259:30)\n    at App.insert (/Users/jon/Documents/dev/mpc-node/lib/modules/dbupdater.js:14:33)\n    at App._exec (/Users/jon/Documents/dev/mpc-node/lib/core/app.js:255:57)\n    at App._exec (/Users/jon/Documents/dev/mpc-node/lib/core/app.js:234:28)\n    at App.exec (/Users/jon/Documents/dev/mpc-node/lib/core/app.js:205:20)\n    at App.condition (/Users/jon/Documents/dev/mpc-node/lib/modules/core.js:97:24)\n    at App._exec (/Users/jon/Documents/dev/mpc-node/lib/core/app.js:255:57)\n    at App._exec (/Users/jon/Documents/dev/mpc-node/lib/core/app.js:234:28)\n    at processTicksAndRejections (internal/process/task_queues.js:93:5)"

Shouldn’t it be ast.returning?

#20

You’re correct, it should be ast.returning.

Line 77 should become:

this[ast.type](values, Array.isArray(ast.returning) ? ast.returning : [ast.returning]);
1 Like