Wappler Version : 3.4.1
Operating System : MacOS Catalina
Database: MS SQL
I have a Server Action inserting a record into a database. I added a query to retrieve Identity. When I insert the record, the returned response is NULL.
Same Server Action in ASP.NET with same database, same table, identity is populated:
Is this a bug in NODE?
Will this be fixed in today’s update?
How does the json of the insert step look like, does it have a returning property? Use the Open in Editor to open the json.
I could not find it using a quick search:
{
"meta": {
"options": {
"linkedFile": "/views/Appointment/selectdestination.ejs",
"linkedForm": "form1"
},
"$_GET": [
{
"type": "text",
"name": "sort"
},
{
"type": "text",
"name": "dir"
}
],
"$_POST": [
{
"type": "text",
"fieldName": "createdby",
"name": "createdby"
},
{
"type": "text",
"fieldName": "locationid",
"name": "locationid"
}
]
},
"exec": {
"steps": [
"connections/Target",
{
"name": "insertAppointment",
"module": "dbupdater",
"action": "insert",
"options": {
"connection": "Target",
"sql": {
"type": "insert",
"values": [
{
"table": "DS_Appointment",
"column": "LocationID",
"type": "text",
"value": "{{$_POST.locationid}}"
},
{
"table": "DS_Appointment",
"column": "CreatedBy",
"type": "text",
"value": "{{$_POST.createdby}}"
},
{
"table": "DS_Appointment",
"column": "Createdate",
"type": "datetime",
"value": "{{NOW}}"
}
],
"table": "DS_Appointment",
"query": "INSERT INTO DS_Appointment\n(LocationID, CreatedBy, Createdate) VALUES (:P1 /* {{$_POST.locationid}} */, :P2 /* {{$_POST.createdby}} */, :P3 /* {{NOW}} */)",
"params": [
{
"name": ":P1",
"type": "expression",
"value": "{{$_POST.locationid}}"
},
{
"name": ":P2",
"type": "expression",
"value": "{{$_POST.createdby}}"
},
{
"name": ":P3",
"type": "expression",
"value": "{{NOW}}"
}
]
}
},
"meta": [
{
"name": "identity",
"type": "text"
},
{
"name": "affected",
"type": "number"
}
]
},
{
"name": "queryGetAppointmentID",
"module": "dbconnector",
"action": "select",
"options": {
"connection": "Target",
"sql": {
"type": "SELECT",
"columns": [
{
"table": "DS_Appointment",
"column": "AppointmentID"
},
{
"table": "DS_Appointment",
"column": "AppointmentNumber"
}
],
"table": {
"name": "DS_Appointment"
},
"joins": [],
"wheres": {
"condition": "AND",
"rules": [
{
"id": "DS_Appointment.AppointmentNumber",
"field": "DS_Appointment.AppointmentNumber",
"type": "double",
"operator": "equal",
"value": "{{insertAppointment.identity}}",
"data": {
"table": "DS_Appointment",
"column": "AppointmentNumber",
"type": "number"
},
"operation": "="
}
],
"conditional": null,
"valid": true
},
"query": "SELECT AppointmentID, AppointmentNumber\nFROM DS_Appointment\nWHERE AppointmentNumber = :P1 /* {{insertAppointment.identity}} */",
"params": [
{
"operator": "equal",
"type": "expression",
"name": ":P1",
"value": "{{insertAppointment.identity}}"
}
]
}
},
"output": true,
"meta": [
{
"name": "AppointmentID",
"type": "text"
},
{
"name": "AppointmentNumber",
"type": "text"
}
],
"outputType": "array"
}
]
}
}
After further investigation the returning property is only needed for Postgres, with MSSQL it should not be needed. Please turn on the output for the insert step and check if it returns affected and identity. It could be possible that the table row is still locked from the insert statement causing the select to return no records.
This is the response:
Hey Patrick, were you able to find out anything on this?
Until now nothing. It should return the identity correctly. Which version of SQL server are you using exactly?
The project I have tried it on is MS SQL Server 2017. It works without issue using Classic ASP and ASP.NET but when connecting to the same table, same Server Action steps in Node, it does not return identity.
@patrick, I hate to keep asking about this, but I’m at a stand still. We’ve been back and forth for a week now with no resolve or advice. At this point, I either need to find out what I’m doing wrong, find a work-around, or abandon Node as my project framework and go back to ASP.NET which will require a lot of extra work on my part.
You can try the following, edit the file lib/core/db.js.
Line 76 looks like:
if (ast.type == 'insert' && ast.returning && this.client.config.client == 'postgres') {
Change it to:
if (ast.type == 'insert' && ast.returning) {
In you server connect action json file find the options for the insert step:
"options": {
"connection": "Target",
"sql": {
"type": "insert",
"values": [
{
Add an extra property to it:
"options": {
"connection": "Target",
"sql": {
"type": "insert",
"returning": "AppointmentID",
"values": [
{
Check if it now returns the id.
Yes Patrick, that worked thank you. It even worked better than expected because it returns the GUID identity in my database, and all the other times I’ve used it in ASP.NET I believe it returns the incrementing number identity. It never worked with the GUID, so I had to add the incrementing identity,. This way, I can continue to use my GUID PK. Thank you again.
Will this be included in the next update?
I will apply the changes in the db.js file for the next update.
This seems to be broken for me in 3.5.7 using Node.js and SQL Server
Last week I updated Wappler to 3.5.7. Tonight for the first time since the update I had to make a change to the site that only involved some html and when I saved the changes, Wappler updated all the other related updated Server Connect files. When i logged in to the app, I started getting errors. Retrieve Identity no longer works in Node.js and SQL Server. I have not made any changes to the server connect files.
Which version of SQL server do you use?
If you undo the change, does it then work again?
edit the file lib/core/db.js .
Line 76 looks like:
if (ast.type == 'insert' && ast.returning) {
Change it to:
if (ast.type == 'insert' && ast.returning && this.client.config.client == 'postgres') {