Executing direct simple SQL statement in D365FO
Chaitanya Golla
17,225
Hi,
In this post we will see the demo of executing simple SQL statement directly from D365FO.
- Created new tables by name SampleTable , SampleTrans and loaded some data into it.
- Constructed SQL statement operates on table SampleTable and display its data.
- Note: CG_ExecuteSimpleSQLStmt is a runnable class
class CG_ExecuteSimpleSQLStmt
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sql;
SQLSystem system;
SQLStatementExecutePermission permission;
ResultSet resultSet;
DictTable sampleTable = new DictTable(tableNum(SampleTable));
DictField Id = new DictField(tableNum(SampleTable), fieldNum(SampleTable, Id));
DictField tableDueDate = new DictField(tableNum(SampleTable), fieldNum(SampleTable, dueDate));
system = new SqlSystem();
sql = 'SELECT %1, %2 FROM %3';
sql = strFmt(sql,
tableDueDate.name(DbBackend::Sql), // 1
Id.name(DbBackend::Sql), // 2
sampleTable.name(DbBackend::Sql)); // 3
userConnection = new UserConnection();
statement = userConnection.createStatement();
new SqlStatementExecutePermission(sql).assert();
resultSet = statement.executeQuery(sql);
CodeAccessPermission::revertAssert();
while (resultSet.next())
{
info(strFmt("Id %1 and due date %2", resultSet.getString(1), resultSet.getString(2)));
}
}
}
Regards,
Chaitanya Golla
*This post is locked for comments