# sqlWrite

See SQL Statements for how to create statements.

### Demo

{% embed url="<https://youtu.be/vs3uR5BRPKU>" %}

### 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

```javascript
// 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

```javascript
// 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)

```javascript
// 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

```javascript
// 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](https://brixxbox.gitbook.io/brixxbox-documentation/client-api-reference/functions/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.