ACCESSING THE DATABASE in Dynamics Ax 2012

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;
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s