Share via

Dynamics Ax 2012: Accessing the database

  • Article

To Retrieve data from the database using a select statement

Table Buffers:

A table buffer is declared like a variable – the table name is specified in the declaration. A table buffer stores complete records in a variable.

Select Statements:

Select statements are used to retrieve data from a database. The select statement returns records to a table buffer.

Example:

static void Q1_Select1(Args _args)

{ CustTable CustTable;

;

select Address from CustTable;

where CustTable.AccountNum == '1102';

print CustTable.Address;

pause;

}

// To Loop Records

while select AccountNum, Name, Address from CustTable

{ print CustTable.AccountNum+ ": " + CustTable.Name + ": " + CustTable.Address;

}

pause;

Sort:

You can sort data retrieved from the database in many ways. This includes:

  • Using existing indexes on the tables.
  • Using the order by clause in a select statement.
  • Using the group by clause in a select statement.

Example:

while select custTable index AccountIdx { print custTable.AccountNum, " ", custTable.currency;

}

Inner join:

while select ledgerTable
join ledgerTrans
where ledgerTrans.accountNum == ledgerTable.accountNum
{ amountMST += ledgerTrans.amountMST;

}

Exist:

while select AccountNum, Name from custTable order by AccountNum exists join * from ctr where (ctr.AccountNum == custTable.AccountNum)

notExist:

while select AccountNum, Name from custTable order by AccountNum notExists join * from ctr where (ctr.AccountNum == custTable.AccountNum)

outer:

while select AccountNum from custTable order by AccountNum outer join * from custBankAccount where custBankAccount.AccountNum == custTable.AccountNum { print custTable.AccountNum, " , ", custBankAccount.DlvMode;

} pause;

Count:

CustTable xCT;

int64 iCountRows;

;

Select COUNT(RecID) from xCT;

iCountRows = xCT.RecID;

Create:

static void Test_Insert(Args _args) { CustTable CustTable;

;

CustTable.AccountNum = "supposedAccount1";

CustTable.Name = "SupposedName1";

CustTable.insert();

info("Inserted");

}

Update:

static void Test_Update(Args _args) { SalesTable SalesTable;

;

ttsbegin;

while select forupdate SalesTable where SalesTable.CustAccount == "1102" { SalesTable.SalesName = "aaaaa";

SalesTable.update();

info("Updated Successfully");

} ttscommit;

} SalesTable SalesTable;

;

update_recordset SalesTable setting salesName = "Update RecordSet", DeliveryStreet = "New Address" where SalesTable.CustAccount == "1102 “;

info("Updated Successfully via RecordSet");

Delete:

static void Q16_Delete(Args _args) { CustTable CustTable;

;

ttsbegin;

select forupdate CustTable where CustTable.AccountNum == "supposedAccount1";

CustTable.delete();

info("Deleted");

ttscommit;

} CustTable CustTable;

;

while select forupdate CustTable where CustTable.AccountNum == "4018" delete_from CustTable where CustTable.AccountNum == "4018";

Transaction Integrity Checking:

 

It is important to ensure the integrity of all transactions within the system. When a transaction begins, to ensure data consistency, it must finish completely with predictable results.

 

The following keywords help in integrity checking:

  • ttsbegin – Indicates the beginning of the transaction.
  • ttscommit – Indicates the successful end of a transaction. This ensures the transaction performed as intended upon completion.
  • ttsabort – Used as an exception to abort and roll back a transaction to the state before the ttsbegin.

Queries:

A query is an application object in the AOT

A query performs the same function as the select statements, but is a better option as it allows for more flexible user interaction when defining which records are to be retrieved.

Queries Using X++:

Queries can also be created and manipulated using X++. There are a number of classes available that you can use to achieve this.

Two important classes when executing a query are:

Query():

The Query() class provides the framework for the query

QueryRun():

QueryRun() class starts this framework dynamically.

 

Queries Using X++:

static void Test_ViaXPlusPlus(Args _args)

{ Query query;

QueryBuildDataSource qbds;

QueryBuildRange qbr;

QueryRun queryrun;

CustTable CustTable;

;

query = new Query();

qbds = query.addDataSource(TableNum(CustTable));

qbr = qbds.addRange(FieldNum(CustTable,AccountNum));

qbr.value('1101');

qbds.addSortField(FieldNum(CustTable,AccountNum));

queryrun = new QueryRun(query);

while(queryrun.next()) { CustTable = queryrun.get(TableNum(CustTable));

Print CustTable.AccountNum + ": " + CustTable.Name;

} Pause;

}

Accessing data from Different Companies:

static void Q10_CrossCompanies1(Args _args) { Container ConComapnies = ['cee','ceu'];

CustTable CustTable;

;

while select crossCompany : ConComapnies CustTable { Print CustTable.Name;

} pause;

}