sqlWrite

This method executes an SQL statement that can modify data (INSERT, UPDATE, DELETE) or return data (when the statement includes SELECT, OUTPUT INSERTED, or RETURNING clauses).

See SQL Statements for how to create statements.

Demo

Syntax

sqlWrite(statementName, parameterJson, cmdOptions)

Parameters

Parameter	Type	Description
statementName	string	The name of the SQL statement to execute
parameterJson	object	A JSON object with additional parameters. All controls in the current configuration are automatically set as parameters.
cmdOptions	object	(optional) A JSON object with options for the request
cmdOptions.timeout	number	(optional) Timeout for the SQL request in milliseconds. Default is 30 seconds.
cmdOptions.connectionKey	string	(optional) A key to a custom settings entry with the connection string to an external MSSQL database

Return Value

The method returns a Promise that resolves to an object with the following properties:

Property	Type	Description
ModifiedRecordCount	number	The number of affected rows for INSERT, UPDATE or DELETE operations
SubData	array	Returned data when the SQL statement includes SELECT, OUTPUT INSERTED, or RETURNING clauses

Description

The sqlWrite method executes an SQL statement that is stored in the database. This method is intended for operations that modify data in the database, but it can also return data if the SQL statement is appropriately structured.

The method automatically detects whether the SQL statement should return results:

• For simple INSERT, UPDATE, or DELETE statements, only the count of affected records is returned.

• If the SQL statement begins with SELECT or includes OUTPUT INSERTED or RETURNING clauses, the returned data is made available in the SubData array.

Examples

Simple INSERT Statement without Return Value

// Execute a simple INSERT statement
const result = app.sqlWrite("InsertNewCustomer", {
    customerName: "Example Company Ltd", 
    contactPerson: "Jane Doe"
});
console.log(`${result.ModifiedRecordCount} records were inserted`);

UPDATE Statement with Custom Options

// UPDATE with longer timeout
const result = app.sqlWrite("UpdateCustomerAddress", {
    customerId: 12345,
    street: "123 Main Street",
    city: "London",
    zipCode: "E1 6AN"
}, {
    timeout: 60000 // 60 seconds timeout
});
console.log(`${result.ModifiedRecordCount} customer addresses were updated`);

Statement with Data Return (OUTPUT INSERTED)

// INSERT with OUTPUT INSERTED clause
const result = app.sqlWrite("InsertProductWithReturn", {
    productName: "New Product",
    price: 99.99,
    category: "Electronics"
});

if (result.SubData && result.SubData.length > 0) {
    console.log(`Product with ID ${result.SubData[0].ProductId} was created`);
    // Access other returned fields
    console.log(`Creation date: ${result.SubData[0].CreatedDate}`);
}

Using an External Database Connection

// Execute SQL statement on external database
const result = app.sqlWrite("DeleteExpiredOrders", {
    olderThanDays: 365
}, {
    connectionKey: "ExternalArchiveDB"
});
console.log(`${result.ModifiedRecordCount} old orders were deleted`);

Notes

• The SQL statements must be predefined in the system.

• Use sqlRead for pure read operations when you don't need to modify data.

• All parameters are automatically protected against SQL injection attacks.

• When using OUTPUT INSERTED or RETURNING clauses, the results are returned in the SubData array while the number of inserted/updated rows is stored in ModifiedRecordCount.