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:

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

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.