Capture result of Cloud Backend Trigger

Dick Honing

I think I already found it ... it's in the 'response'

Dick Honing

Now a new question arises ... how can I attach the id of a newly created record to the api response ...

var cloudbackend = require('appdrag-cloudbackend');
var appID = process.env.APPID;
var APIKey = process.env.APIKEY;
cloudbackend.init(APIKey, appID);

exports.handler = (event, context, callback) => {

    var tableName = "Vitamines";
    var columns = "id, Productnummer, Productnaam, EAN_barcode";
    var columnsNew = "Productnummer, Productnaam, EAN_barcode";
    callback(null, JSON.stringify(event));
    var id = event.POST.id;
    var productnummer = event.POST.productnummer;
    var productnaam = event.POST.productnaam;
    var eanbarcode = event.POST.eanbarcode;
    
    if (id == "") {
    var query = "INSERT INTO " + tableName + " (" + columnsNew + ") VALUES ('" + Clean(productnummer) + "', '" + Clean(productnaam) + "', '" + Clean(eanbarcode) + "')"; 
    } else { 
    var query = "UPDATE " + tableName + " SET " + "Productnummer" + " = '" + Clean(productnummer) + "', " + "Productnaam" + " = '" + Clean(productnaam) + "', " + "EAN_barcode" + " = '" + Clean(eanbarcode) + "'" + " WHERE " + "id" + " = " + id;
    }

    cloudbackend.sqlExecuteRawQuery(query)
    .then(function(response) {
        callback(null, response);
    });

};

function Clean(txt) {
    return txt.replace(/\'/g, "''");
}

Joseph Benguira

Hey Dick, you can do it like this

cloudbackend.sqlSelect("INSERT INTO Users (name, email, password) values('john doe', 'john@doe.com', 'Test97_626p'); SELECT LAST_INSERT_ID() as newUserID;") 
.then( function(response) { 
        var result = JSON.parse(response); 
        var newID= result.Table[0].newUserID; 
        callback(null, newID); //return the newID
});

This will execute your INSERT statement then return the newID as API response

Daniel Mulroy

A slight correction to @Joseph-Benguira's code:

cloudbackend.sqlSelect("INSERT INTO Users (name, email, password) values('john doe', 'john@doe.com', 'Test97_626p'); SELECT LAST_INSERT_ID() as newUserID;") 
.then( function(response) { 
        var result = JSON.parse(response); 
        var newID= result.Table[0].newUserID; 
        callback(null, newID); //return the newID
});

Dick Honing

@Daniel-Mulroy @Joseph-Benguira thanks! I think I'm very close, but I keep getting the following error:

2020-11-22T21:10:28.982Z 8aafee2c-64dd-4546-8158-504f4d024f56 ERROR Unhandled Promise Rejection {"errorType":"Runtime.UnhandledPromiseRejection","errorMessage":"TypeError: Cannot read property '0' of undefined","reason":{"errorType":"TypeError","errorMessage":"Cannot read property '0' of undefined","stack":["TypeError: Cannot read property '0' of undefined"," at /var/task/main.js:23:32"," at processTicksAndRejections (internal/process/task_queues.js:97:5)"]},"promise":{},"stack":["Runtime.UnhandledPromiseRejection: TypeError: Cannot read property '0' of undefined"," at process. (/var/runtime/index.js:35:15)"," at process.emit (events.js:314:20)"," at processPromiseRejections (internal/process/promises.js:209:33)"," at processTicksAndRejections (internal/process/task_queues.js:98:32)"]}
[ERROR] [1606079429004] LAMBDA_RUNTIME Failed to post handler success response. Http response code: 403.
RequestId: 8aafee2c-64dd-4546-8158-504f4d024f56 Error: Runtime exited with error: exit status 128
Runtime.ExitError

The record however get's created just fine and the code is:

var tableName = "Vitamines";
callback(null, JSON.stringify(event));
var id = event.POST.id;
var productnummer = event.POST.productnummer;
var productnaam = event.POST.productnaam;
var eanbarcode = event.POST.eanbarcode;
var vorm = event.POST.vorm;

if (id == "") { // NEW
var columns = "Productnummer, Productnaam, EAN_barcode, Vorm";
var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES ('" + Clean(productnummer) + "', '" + Clean(productnaam) + "', '" + Clean(eanbarcode) + "', '" + Clean(productnaam) + "'); SELECT LAST_INSERT_ID() as newID;";

cloudbackend.sqlExecuteRawQuery(query)
.then(function(response) {
    var result = JSON.parse(response); 
    var newID= result.Table[0].newID; 
    callback(null, newID); //return the newID
});

Wassim

@Dick-Honing said in Capture result of Cloud Backend Trigger:

productnummer

Hey Dick,

What you're doing is an sqlExecuteRawQuery but in their exemples they use sqlSelect (to be able to retrieve the last insert id)

I guess this is your issue

Dick Honing

@Wassim thanks! I was so focussed on checking whether my sql statement was correct, that I totally overlooked this! Always good to have a pair of fresh and sharp eyes

Dick Honing

apparently, there's still something wrong in my code, as the id still does not get returned ... what have I overlooked?

var columns = "Productnummer, Productnaam, EAN_barcode, Vorm";
var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES ('" + Clean(productnummer) + "', '" + Clean(productnaam) + "', '" + Clean(eanbarcode) + "', '" + Clean(productnaam) + "'); SELECT LAST_INSERT_ID() as newID;";

cloudbackend.sqlSelect(query)
.then(function(response) {
    var result = JSON.parse(response); 
    var newID= result.Table[0].newID; 
    callback(null, newID); //return the newID
});

Screenshot 2020-11-23 at 09.10.20.png

Joseph Benguira

@Joseph-Benguira said in Capture result of Cloud Backend Trigger:

cloudbackend.sqlSelect("INSERT INTO Users (name, email, password) values('john doe', 'john@doe.com', 'Test97_626p'); SELECT LAST_INSERT_ID() as newUserID;")
.then( function(response) {
var result = JSON.parse(response);
var newID= result.Table[0].newID;
callback(null, newID); //return the newID
});

I can see the payload in your response is the content of "event" object ...

so you are probably doing a callback(null, event); at the top of your code ... preventing execution of rest of the code

Dick Honing

@Joseph-Benguira oops! ... you're right. I removed:

callback(null, JSON.stringify(event));

and now the result is:

{
"status": "OK",
"execTime": 812,
"billedTime": 1700,
"payload": "1001762",
"logs": ""
}

Thanks again!

No idea what this piece of code was doing in the API, but that's what you get when your copying and pasting and to not understand the code exactly yet. Sorry about that!

REMINDER

Capture result of Cloud Backend Trigger