X++ data selection and manipulation overview

Note

Community interest groups have now moved from Yammer to Microsoft Viva Engage. To join a Viva Engage community and take part in the latest discussions, fill out the Request access to Finance and Operations Viva Engage Community form and choose the community you want to join.

Use SQL statements, either interactively or in source code, to retrieve and modify data stored in the database. 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.

Use these other statements 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

Each SQL statement listed earlier has a timeout set for each statement, depending on the session type that sends it.

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

When a statement times out because of blocking or a larger data volume or query plan problem, the system throws an Exception::Timeout exception with the message A time-out occurred in the database while the query was executing. X++ can handle the timeout exception and retry based on this exception. To override the default timeout, call the queryTimeout API.