Hi Losant team,
We need to use GEA (v1.48.1) to query an MSSQL 2019 Express (v15.0) database. My current understanding is that the relevant difference between standard SQL server & express is that the latter requires an instance name as part of the host, e.g., “192.168.1.x\\sqlexpress”. This seems to be problematic for the Losant SQL node - attempting to connect w/o instance identifier throws “NotFoundError - Could not connect (sequence)”, and attempting w/ instance ID throws “SQLNodeValidationError - Cannot resolve host”. Other tweaks / variations were tried to no avail.
As a workaround, we also tried invoking custom JS via the Function node that uses the (already built-in) mssql library, as this would give us full control over the connection string. However, this also yielded very little, as we were unable to run even watered-down async functions and get it to mutate the payload properly. These were all tested against a local DB with known, valid credentials, wrapped in the simplest possible workflow (virtual button → function → debug) that didn’t write to the payload or even log anything beyond the first await.
SQL example:
const sql = require('mssql');
const config = {
user: 'sa',
password: 'pw',
server: 'ip',
database: 'db',
port: 1433,
options: {
encrypt: false,
trustServerCertificate: true
}
};
(async () => {
try {
console.log('Connecting...');
await sql.connect(config);
console.log('Executing query...');
const result = await sql.query('SELECT COUNT(*) FROM dbo.users');
console.log('Query executed'(
payload.result = result;
} catch (err) {
console.log('Error:', err);
} finally {
await sql.close();
console.log('Connection closed.');
}
})();
Also tried treating these as synchronous which showed the full logging, including opening / closing the connection, but didn’t surface a result set in the payload as sql.query returns a promise.
Simple async function that logs & sleeps which didn’t behave as it does outside of the Losant sandboxed-context:
const f = async (s) => {
return new Promise((resolve) => {
setTimeout(() => {
console.log(s)
resolve()
}, 1000)
})
}
(async () => {
try {
console.log('Start');
await f('1')
await f('2')
} catch (err) {
console.log('Error:', err);
} finally {
console.log('End')
}
})();
The behavior here is difficult to pin down, and we found no helpful caveats called out in the docs.
We’re probably missing something obvious here - any direction on how to connect using the native SQL node, how to wield async functions properly within an edge workflow, or alternative paths would be greatly appreciated.
–NY