Insert Survey123 web app result directly into MySQL db with Node.js, is it possible?

Question

No feature service, no web service, no webhook, is it possible to run following custom JS function with Survey123 web app and Node.js to insert Survey result directly into database like MySQL?

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

ZacharySutherby · Accepted Answer

Hello @Ming,

You will need a feature service associated with the survey there isn't a way around that. To achieve your end goal you may be able to embed your survey in your application using the Survey123 web app JavaScript API. You can use the setOnFormSubmitted event to call a function that submits the data to your MySQL database. This event will provide your handling function that may contain multiple features as it will include the parent form and any repeats as separate features which mirrors the table structure in your feature service.

Please use this link for the Survey123 web app JS API documentation. Please refer to our Extending ArcGIS Survey123 Dev Summit presentation for more information on the JS API.

Thank you,

Zach

Ming · Accepted Answer

Thank you very much Zach.

As you said it really needs feature service. So my final solution is:

1. Refer to the schema of survey form, create a feature class related to the main section and a table related to the repeatable section that in Oracle db are two tables.

2. Write a trigger in the table that can write data to other tables after data is inserted.

3. Create relationship with geoprocessing tool to build the relationship between the feature class and table based on the globalid field.

4. Create and publish a map service that has the feature class and table.

5. In Survey123 XLSForm settings tab, put the feature layer name as form_id, and feature service url as submission_url.

6. Now after click Submit button in Survey123 form, the data can be inserted into the hosted survey feature layer and the two tables. With the trigger in the Oracle db table, the data can also be inserted into any other table in db.

7. The trick is the name in the Survey123 XLSForm must be upper case because the field name in the Oracle db is upper case.

Above steps refer to following links:

Author a survey that matches a feature layer:

https://doc.arcgis.com/en/survey123/desktop/create-surveys/survey123withexistingfeatureservices.htm

https://support.esri.com/en/technical-article/000014835

https://community.esri.com/t5/arcgis-survey123-blog/working-with-existing-feature-services-in-arcgis/ba-p/894290

Hope this can help others that have similar requirements.

Thanks,

Ming