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).
Last updated
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).
Last updated
See SQL Statements for how to create statements.
sqlWrite(statementName, parameterJson, cmdOptions)
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
The method returns a Promise that resolves to an object with the following properties:
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
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.
The SQL statements must be predefined in the system.
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.
Use for pure read operations when you don't need to modify data.