X++ data selection and manipulation overview

You can use SQL statements, either interactively or in source code, to retrieve and modify data that is stored in the database. You can use the select statement and API methods for these tasks:

• Select data: Select the data to view or modify.

  • select statement – Fetch records.

• Insert data: Add one or more new records to a table.

  • insert and doInsert methods – Insert one record at a time.
  • insert_recordset, RecordInsertList.insertDatabase, and RecordSortedList.insertDatabase methods – Insert multiple records at the same time.

• Update data: Modify the data in existing table records.

  • update and doUpdate methods – Update one record at a time.
  • update_recordset statement – Update multiple records at the same time.

• Delete data: Remove existing records from a table.

  • delete and doDelete methods – Delete one record at a time.
  • delete_from statement – Delete multiple records at the same time.

Here are some other statements that you will use in data access:

• while select statement
• select expression
• next statement

Transactional integrity helps prevent data corruption and improve scalability.

The Conversion of operations from set-based to record-by-record article provides information about how you can use the record set–based statements and methods more efficiently.

You can also use the SysDa classes to retrieve and modify data. The extensible SysDa API provides almost all the data access possibilities that are available in X++.

The executeQueryWithParameters API can help mitigate a SQL injection attack.

For information about using joins, see Common misconception about Exists and Notexists joins.

SQL statement timeout

All of the SQL statements listed above have a timeout set per statement, depending on the session type that issues them.

• Interactive session - 30 minutes.
• Non-interactive (batch, services, OData) - 3 hours.

When a statement times out due to blocking or a larger than anticipated data volume or query plan issue, an exception of type Exception::Timeout will be thrown with the message A time-out occurred in the database while the query was executing. X++ allows a handle timeout exception and retry based on this exception. You can override the default timeout by calling queryTimeout API.