Understanding Direct SQL Query Execution in D365 FO X++

 

Introduction

X++ is very powerful and has SQL like statements that can be used to interact with data.

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.

1)

class RunDirectSqlSelect
{
    public static void main(Args _args)
    {
        UserConnection                  connection = new UserConnection();
        Statement                       statement  = connection.createStatement();
        ResultSet                       resultSet;
        str                             sqlQuery;
        SqlStatementExecutePermission   permission;

        // Define your T-SQL query string
        sqlQuery = "SELECT ACCOUNTNUM, NAME FROM CUSTTABLE WHERE DATAAREAID = 'usmf'";

        // Assert the necessary runtime execution permissions
        permission = new SqlStatementExecutePermission(sqlQuery);
        permission.assert();

        try
        {
            // Execute the query and capture results
            resultSet = statement.executeQuery(sqlQuery);

            // Process the row-by-row results
            while (resultSet.next())
            {
                info(strFmt("Customer Account: %1 - Name: %2", 
                    resultSet.getString(1), 
                    resultSet.getString(2)));
            }
        }
        catch (Exception::Error)
        {
            error("An error occurred during direct SQL execution.");
        }

        // Revert permissions immediately after execution
        CodeAccessPermission::revertAssert();
    }
}



Option 2: Modifying Data (UPDATE, INSERT, DELETE)

Use the executeUpdate method when executing a query that manipulates records and does not expect rows back.

class RunDirectSqlUpdate

{
    public static void main(Args _args)
    {
        UserConnection                  connection = new UserConnection();
        Statement                       statement  = connection.createStatement();
        str                             sqlQuery;
        SqlStatementExecutePermission   permission;

        // Define your modification query string
        sqlQuery = "UPDATE CUSTTABLE SET NAME = 'Updated Name' WHERE
          ACCOUNTNUM = 'US-001' AND DATAAREAID = 'usmf'";

        // Assert runtime execution permissions
        permission = new SqlStatementExecutePermission(sqlQuery);
        permission.assert();

        try
        {
            // Execute the manipulation command
            statement.executeUpdate(sqlQuery);
            info("Database updated successfully.");
        }
        catch (Exception::Error)
        {
            error("Database modification failed.");
        }

        // Revert asserted permissions
        CodeAccessPermission::revertAssert();
    }
}




Critical Rules & Best Practices
Enforce Permission Revocation: Always call CodeAccessPermission::revertAssert() 
immediately after your execution line to mitigate subsequent security risks.
Mind Database Bypasses: Executing raw SQL directly skips important built-in application behavior 
including table-level insert(), update(), and delete() overrides, as well as database-level delete actions.
Prevent SQL Injection: Avoid hardcoding parameters through dynamic string concatenation.
 For complex or dynamic filters, leverage the executeQueryWithParameters API alongside proper
 SQL parameter binding arrays to isolate data inputs safely.
Handle Cross-Company Logic: Native T-SQL statements will target all records unless manually restricted. 
You must explicitly filter on the DATAAREAID field inside your string to isolate data by legal entity.