Ax2012 Enus Devii 04
-
Upload
safdar-hussain -
Category
Documents
-
view
272 -
download
7
Transcript of Ax2012 Enus Devii 04
-
8/11/2019 Ax2012 Enus Devii 04
1/26
Chapter 4: Accessing the Database
4-1
CHAPTER 4: ACCESSING THE DATABASE
Objectives
The objectives are:
Retrieve data from the database using a select statement.
Create, update and delete data in the database.
Use and build queries using kernel classes.
Introduction
This course will show how to develop modifications that interact with the
database. This functionality is used frequently so it is an important part of
learning how to develop using X++.
Scenario
Isaac, the Systems Developer, is writing a program that requires data to be read
from the database. Additionally the data must also be updated and written back to
the database. To do this, Isaac must use the selectstatement and queries so that
the data can be retrieved correctly and efficiently, while ensuring the data
maintains its integrity.
-
8/11/2019 Ax2012 Enus Devii 04
2/26
Development II in Microsoft DynamicsAX 2012
4-2
Retrieving Data
Microsoft DynamicsAX 2012 accesses data using select statements and queries.
This section focuses on select statements.
Table BuffersA table buffer stores complete records in a variable. A table buffer is declared
like a variable the table name is specified in the declaration. It is required when
you use select commands to retrieve records and is declared before the select
statement.
The following code shows a table buffer declaration:
CustTable custTable; //declares a table buffer for theCustTable
When a select statement result is assigned to a table buffer, the variable can be
considered a subset of data from that table depending on the criteria of the
search.
Select Statements
Select statements are used to retrieve data from a database. The pure select
statement returns records to a table buffer. The following example shows codethat selects a record from the Customer table where the customer account number
is 1103 and also prints the customer's address. Use the legal entity CEU.
CustTable custTable;
select custTable
where custTable.AccountNum == "1103";
print "This is the credit limit of Customer " +custTable.AccountNum + ": " + num2str(custTable.CreditMax,-1,-1,-1,-1);
pause;
-
8/11/2019 Ax2012 Enus Devii 04
3/26
-
8/11/2019 Ax2012 Enus Devii 04
4/26
Development II in Microsoft DynamicsAX 2012
4-4
Syntax
Category
Keyword Description
maxof Returns the maximum of the fields from the
rows given by the group by clause.
count Returns the number of fields from the rows
given by the group by clause.
Sorting
Options
order by Instructs the database to sort selected
records by fields in the order by list.
group by Instructs the database to group selected
records by fields in the order by list.
Direction asc Sorts the order by or group by clause in
ascending order.
desc Sorts the order by or group by clause in
descending order.
Index Clause index Sorts the selected records as defined by theindex.
index hint Gives the database a hint to use this index to
sort the selected records as defined by theindex. The database might ignore the hint.
Join Clause exists Returns a record only if the linked record
exists.
notexists Returns a record only if the linked record
does not exists.
outer Returns all records regardless of whether
linked records exist.
Examples Using Keywords
The following code shows examples of select statements. The comments describe
their use.
CustTable custTable; // Declares a table buffer forthe CustTable
// Returns only the first record from the custtable.
select firstOnly custTable;
// Returns a count of the number of customer accountnumbers in the
// table and prints the result to screen.
-
8/11/2019 Ax2012 Enus Devii 04
5/26
Chapter 4: Accessing the Database
4-5
select count(AccountNum) from custTable;
print custTable.AccountNum;
pause;
Field Lists
To perform a lookup on specific fields in a table, use a field list in the select
statement. For example, to return only the name of a customer whose account
number is '4000' use a field select statement.
CustTable custTable;
select name from CustTable
where CustTable.AccountNum =="4000";
Using a field list is a good practice because it can increase performance.
Using While Select
The while selectstatement loops through many records fulfilling specific criteria
in a table. Use this loop to perform statements on each record. The following
code shows an example of a while selectstatement. This code loops through the
customer table and prints the account number, name, and address of each record
in the table.
CustTable custTable;
while select accountNum, currency, creditMax from custTable
{
print custTable.AccountNum, " ", custTable.currency, "", custTable.creditMax;
pause;
}
-
8/11/2019 Ax2012 Enus Devii 04
6/26
Development II in Microsoft DynamicsAX 2012
4-6
Using Ranges in Select Statements
A rangecan be set for records being selected. This range is specified in the
whereclause of the select statement using relational operators. The following
example selects customers who have account numbers greater than 4005 and less
than 4017.
CustTable custTable;
while select custTable
where custTable.AccountNum > "4005"
&& custTable.AccountNum < "4017"
{
print custTable.AccountNum , " ",custTable.currency;
}
pause;
Sorting Options
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.
To use an index on a select statement, use the keyword indexfollowed by the
name of the index:
CustTable custTable;
while select custTable index AccountIdx
{
print custTable.AccountNum, " ", custTable.currency;
}
pause;
-
8/11/2019 Ax2012 Enus Devii 04
7/26
Chapter 4: Accessing the Database
4-7
The result of this search is in the order specified by the index. You can view
available indexes for a given table in the Application Object Tree (AOT).
Indexes should only be specified when the sequence of records is important and
that sequence matches the index exactly.
Order By
If an index does not exist on a table, you can create a temporary index using an
order byclause in the select statement. This lets you specify one or more table
fields to sort the result. By default, records are sorted in ascending order. To sort
in descending order, use the keyword descafter the order by field. The following
example shows how to use the order byclause in descending order.
CustTable custTable;
while select custTable order by AccountNum desc
{
print custTable.AccountNum, " ", custTable.currency;
}
pause;
Group By
Use the group byclause to group selected records by field. The following
example shows how to display the number of sales orders by customer group
from the Sales table.
SalesTable salesTable;
while select count(recId) from salesTable group byCustGroup
{
print salesTable.CustGroup,"",int642Str(salesTable.RecId);
}
pause;
The output would be a list of customer groups with the total number of sales
orders that exist for customers in that group. The count() function counts the total
number of records and places the result in the field specified in the brackets.
-
8/11/2019 Ax2012 Enus Devii 04
8/26
Development II in Microsoft DynamicsAX 2012
4-8
Joins
Use joins to link tables so they can return values together. By default, inner joins
are created. The following table describes the types of joins available:
Join Type Use to
inner Combine records into one table only when there are
matching values in a common field.
outer Combine records into one table even if there are no matching
values in the common field.
exists Combine records from one table whenever a value exists in a
common field in another table.
notexists Combine records from one table whenever a value in a
common field in another table does not exist.
Not only is there additional syntax needed to specify what table is being joined,
but the whereclause also has to be modified. Within the whereclause, the two
fields that relate the tables must be specified in addition to any other conditions
of the search. When using an inner or outer join the table buffers of both tables
are populated with the retrieved records. An exists or not exists join only
populates the first table.
Example: Using a Join
The join shown in the following code combines records from the Customer table
and the Customer transaction table to show all vouchers and dates for customer4000.
CustTable custTable;
CustTrans custTrans;
while select AccountNum from custTable
join custTrans
where (custTrans.AccountNum ==custTrans.AccountNum)
&& custTrans.AccountNum =="1103"
{
-
8/11/2019 Ax2012 Enus Devii 04
9/26
Chapter 4: Accessing the Database
4-9
print custTable.accountNum, " ",custTrans.Voucher,' ',date2str(custTrans.TransDate,213,1,4,2,4,2);
}
pause;
Cross-company Support
Microsoft Dynamics AX can have multiple companies in one data base. It is a
common requirement to retrieve data from many or all of these companies in a
function. This can be achieved in one select statement by using the
crossCompany keyword. If just the keyword is used, it will search all companies.
You can also specify a container that defines which companies to search.
container conCompanies = [ 'cee', 'ceu' ];custTable custTable;
while select crossCompany : conCompanies custTable
{
print custTable.accountNum;
}
pause;
-
8/11/2019 Ax2012 Enus Devii 04
10/26
Development II in Microsoft DynamicsAX 2012
4-10
Lab 4.1 - Retrieving Data
Scenario
Isaac has been asked to produce a list of customer account numbers and names
sorted by name.
Challenge Yourself!
Create a job that will retrieve all customer accounts and print the account number
and name. The list should be sorted by name. Note that the customer name is not
stored in the customer table; it is stored in the DirPartyTable, which is related to
the customer table through the CustTable.Party field and the
DirPartyTable.RecId field.
Step by Step
1. Open the AOT.2. Create a new job
3. Add the following code.
CustTable CustTable;
DirPartyTable DirPartyTable;
while select DirPartyTable order Name
join CustTable
where custTable.Party == dirPartyTable.RecId{
info(DirPartyTable.Name+', '+CustTable.AccountNum);}
-
8/11/2019 Ax2012 Enus Devii 04
11/26
Chapter 4: Accessing the Database
4-11
Data Manipulation
Data manipulation in X++ refers to interactively using SQL commands. These
commands include insert, updateand delete. These are required to modify the
data in the database. When values are assigned to a table buffer, the data is not
updated in the database.
Insert
To create new records in the database, use the insert() method on the table. The
data is first set on the table buffer by assigning values to fields. The data is then
committed to the database by calling the insert() method.
custTable custTable;
custTable.accountNum = "1234";
custTable.Currency = "USD";
custTable.insert();
Insert_Recordset
Insert_Recordset copies data from one or more tables in one call to the database.
This is much faster than selecting records individually and then inserting the new
records in to the table.
In the following example, Isaac has been asked to copy all Employee records to
the vendor table for the purposes of being able to pay expenses to the employee.Only specific information, such as name and address needs to be copied.
VendTable vendTable;
HcmWorker hcmWorker;
Insert_RecordSet VendTable (accountnum, Party)
select PersonnelNumber, person from HcmWorker;
-
8/11/2019 Ax2012 Enus Devii 04
12/26
Development II in Microsoft DynamicsAX 2012
4-12
Update
The Update command modifies existing data in a table with the contents of a
table buffer. The record is first retrieved from the database using a select
statement. The data is then modified by assigning the new values to the fields in
the table buffer. The new values are then committed to the database using the
update() method.
Before records can be updated, use select forUpdateto exclusively set a record
for update. This tells SQL that the record is to be updated and allows the database
to lock the record so that another user cannot modify it at the same time.
The following example updates the customer name on all sales orders for the
customer with the account number 2001
SalesTable salesTable;
ttsbegin;
while select forupdate salesTable
where salesTable.CustAccount =="2001"
{
salesTable.SalesName ="New Enterprises";
salesTable.update();
}
ttscommit;
NOTE: The use of ttsbegin and ttscommit. This indicates the start and end of a
transaction. This topic will be discussed at the end of this section.
-
8/11/2019 Ax2012 Enus Devii 04
13/26
Chapter 4: Accessing the Database
4-13
Update_Recordset
The update_recordset command allows the manipulation of many records in one
operation. This command speeds up database processing because of fewer calls
to the database. Review the update function using the update_recordset
command.
SalesTable salesTable;
update_recordset salesTable
setting salesName ="New Enterprises"
where salesTable.custAccount =="2001";
NOTE: You do not have to use the ttsbegin; or ttscommit; when you use the
update_recordset command, however it is recommended to consider using atransaction when making other updates to the database at the same time.
Delete
The deletecommand deletes a complete record from the database that meets the
condition of the select statement. The syntax of the delete command resembles
update and insert. This example deletes a row in the database with a customer
account of '2032'.
CustTable custTable;
ttsbegin;
Select forUpdate custTable
where custTable.accountnum =="2032";
custTable.delete();
ttscommit;
-
8/11/2019 Ax2012 Enus Devii 04
14/26
Development II in Microsoft DynamicsAX 2012
4-14
Delete_from
The delete_fromcommand removes multiple records from the database at one
time. Similar to the update_recordsetcommand, delete_fromconsolidates
many database calls into one operation, and increases database performance.
CustTable custTable;
delete_from custTable
where custTable.Currency == "ABC";
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. If the transaction terminates in the middle, the system shouldroll back to its state before the transaction began. Use the Transaction Tracking
System (tts) to help you ensure database integrity.
The following keywords help in integrity checking:
ttsbegin: Indicates the beginning of the transaction.
ttscommit: Indicates the successful end of a transaction. Thisensures the transaction performed as intended upon completion.
ttsabort: Used as an exception to abort and roll back a transaction tothe state before the ttsbegin.
Nested ttsbegins and ttscommits are ignored in that a lock is held until the last
ttscommit is reached. However the system does keep track of the tts level. Each
time a ttsbegin is called, the tts level increases by one. Every ttscommit decreases
the level by one.
-
8/11/2019 Ax2012 Enus Devii 04
15/26
Chapter 4: Accessing the Database
4-15
Lab 4.2 - Update
Scenario
The item table needs to be updated so that all items in the item group
"Television" have a purchase tolerance of 2 percent. Isaac has been asked to
write a job that will achieve this.
Challenge Yourself!
Write a job that will find all items in the Television item group, and set the price
tolerance group to 2 percent.
Step by Step
1. Open the AOT.
2. Create a new job.
3. Add the following code.
InventTable InventTable;
InventItemGroupItem inventItemGroupItem;
ttsbegin;
while select forupdate InventTable
exists join inventItemGroupItem
where inventItemGroupItem.ItemId == InventTable.ItemId
&& inventItemGroupItem.ItemGroupId == 'Television'
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
InventTable InventTable;
InventItemGroupItem inventItemGroupItem;
ttsbegin;
while select forupdate InventTable
-
8/11/2019 Ax2012 Enus Devii 04
16/26
Development II in Microsoft DynamicsAX 2012
4-16
exists join inventItemGroupItem
where inventItemGroupItem.ItemId == InventTable.ItemId
&& inventItemGroupItem.ItemGroupId == 'Television'
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
InventTable InventTable;
InventItemGroupItem inventItemGroupItem;
ttsbegin;
while select forupdate InventTable
exists join inventItemGroupItem
where inventItemGroupItem.ItemId == InventTable.ItemId
&& inventItemGroupItem.ItemGroupId == 'Television'
{
InventTable.ItemPriceToleranceGroupId ="2%";
InventTable.update();
}
ttscommit;
-
8/11/2019 Ax2012 Enus Devii 04
17/26
Chapter 4: Accessing the Database
4-17
Queries
A query is an application object in the AOT. A query performs the same function
as the select statements. It is a better option because it allows for more flexible
user interaction when defining which records are to be retrieved. Queries provide
more flexibility, especially when sorting and specifying ranges.
The following figure shows an expanded query in the AOT:
FIGURE 4.1 EXPANDED QUERY IN THE AOT
With this example, the CustTable query has three data sources, two of which
have defined sorting and ranges. This section discusses methods within queriesand how to construct a simple query using X++.
Executing a Query in 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()
QueryRun()
-
8/11/2019 Ax2012 Enus Devii 04
18/26
Development II in Microsoft DynamicsAX 2012
4-18
The Query()class does not "fetch" records, this is accomplished by using the
QueryRun()class. The Query()class provides the framework for the query
whereas the QueryRun()class starts this framework dynamically. The following
example creates and runs a simple query. The QueryStr() function validates that
the element of type Query called Cust exists in the AOT.
Query query = new Query (QueryStr(Cust));
// Use the query to build a queryRun object
QueryRun queryRun = new QueryRun (query);
// Traverse some records...
while (queryRun.next())
{
// ...
}
Building a Query in X++
Queries contain many important elements. These elements have been discussed
in earlier courses in the context of the AOT. This section discusses these
elements from within the context of X++. Elements include datasources, ranges,
and sort fields which build upon each other.
There are two more classes to note before building a query:
QueryBuildDataSource
QueryBuildRange
QueryBuildDataSource
Data sources are what queries are built upon. They are arranged in a hierarchy
and define the sequence in which records are fetched from tables assigned to the
data source. The following example adds a data source to a query using X++:
Query query = new Query();
QueryBuildDataSource qbds =
query.addDataSource(TableNum(SalesTable));
Notice that the data source is an object, but the query object, 'query', requires a
method to add this data source to the query and assign the SalesTable to the data
source.
-
8/11/2019 Ax2012 Enus Devii 04
19/26
Chapter 4: Accessing the Database
4-19
QueryBuildRange
A QueryBuildRangeobject is embedded within a data source of a query and
defines which records should be selected from the data source. A querybuild
range is built upon a QueryBuildDataSourceobject. The following example
uses the QueryBuildRange:
Query query = new Query();
QueryBuildDataSource qbds =query.addDataSource(TableNum(SalesTable));
QueryBuildRange qbr =qbds.addRange(FieldNum(SalesTable,CustAccount));
Example: Building a Query in X++
The following code demonstrates how a query is built from scratch in X++. This
query returns all sales orders, from the SalesTable, for customer '4008', sorted bythe SalesId.
Query query;QueryRun queryRun;QueryBuildDataSource qbds;QueryBuildRange qbr;SalesTable SalesTable;
query = new Query();
//this line attaches a table to the qbds data source objectqbds = query.addDataSource(TableNum (SalesTable));
//this line attaches a range to the 'SalesTable' //datasource, the range is the CustAccountqbr = qbds.addRange(FieldNum (SalesTable,CustAccount));
// The range is set to '2001'qbr.value ('2001');
// The query will sort by sales idqbds.addSortField (FieldNum(SalesTable,SalesId));
// The queryRun object is instantiated using the queryqueryRun = new QueryRun(query);
// The queryRun object loops through all records returnedwhile (queryRun.next()){
-
8/11/2019 Ax2012 Enus Devii 04
20/26
Development II in Microsoft DynamicsAX 2012
4-20
// The current record is assigned to the salesTablevariable
SalesTable = queryRun.get(tableNum(SalesTable));
print SalesTable.SalesId;}
pause;
-
8/11/2019 Ax2012 Enus Devii 04
21/26
Chapter 4: Accessing the Database
4-21
Lab 4.3 - Create a Query Using X++
Scenario
Isaac has been asked to provide a list of vendors of packaging materials.
Challenge Yourself!
Write a job that will produce a list of vendors. The job should build a query using
X++, and the query should have a range that limits the list to vendors in vendor
group "50", and sort by account number.
Add code that will allow the user to modify the query ranges at run time.
Step by Step
1. Open the AOT.
2. Create a new job.
3. Add the following code.
4. Press F5to save and run the code.
Query query;QueryRun queryRun;QueryBuildDataSource qbds;QueryBuildRange qbr;vendTable vendTable;
query = new Query();qbds = query.addDataSource(TableNum(VendTable));
qbr = qbds.addRange(FieldNum(VendTable,VendGroup));qbr.value('50');qbds.addSortField(FieldNum(VendTable,AccountNum));
queryRun = new QueryRun(query);
if (queryRun.prompt()){
while (queryRun.next()){
VendTable = queryRun.get(tableNum(VendTable));info(VendTable.Name());
}}
-
8/11/2019 Ax2012 Enus Devii 04
22/26
Development II in Microsoft DynamicsAX 2012
4-22
Summary
This course showed how to search for data using the select command and how to
create criteria to specify which records will be selected. This course also showed
how these records can be updated or deleted and how to insert new records.
Additionally, this course showed how to build a more user-friendly search using
queries to specify the search criteria.
-
8/11/2019 Ax2012 Enus Devii 04
23/26
Chapter 4: Accessing the Database
4-23
Test Your Knowledge
Test your knowledge with the following questions.
1. What is wrong with the following line of code? Rewrite it so that it compilescorrectly: select count(vendTable.AccountNum);
2.
3. What are three ways to sort data on a select statement?
4. What are the three keywords associated with the Transaction TrackingSystem and what is their significance?
-
8/11/2019 Ax2012 Enus Devii 04
24/26
Development II in Microsoft DynamicsAX 2012
4-24
Quick Interaction: Lessons Learned
Take a moment and write down three key points you have learned from this
chapter
1.
2.
3.
-
8/11/2019 Ax2012 Enus Devii 04
25/26
Chapter 4: Accessing the Database
4-25
Solutions
Test Your Knowledge
1. What is wrong with the following line of code? Rewrite it so that it compiles
correctly: select count(vendTable.AccountNum);
2.
MODEL ANSWER:
You are only supposed to specify a field name as a parameter to the keyword
COUNT and then add a from clause after this function. The following shows
the code written correctly:
Select COUNT(accountnum) from vt;
3. What are three ways to sort data on a select statement?
MODEL ANSWER:
a. Using existing indexes on the tablesb. The order by clause in a select statement that will return records in
ascending order by default and if specified, can return them in descending
order.
c. The group by clause in a select statement will group records by a selected
field.
4. What are the three keywords associated with the Transaction Tracking
System and what is their significance?
MODEL ANSWER:
ttsBegin: You mark the beginning of the transaction with ttsbegin.
ttsCommit: You mark the successful end of a transaction with a ttscommit.
This ensures the transaction is performed the way it is supposed to upon
completion.
ttsAbort: This can be used as an exception to make a transaction be aborted
and rolled back to its state before the ttsbegin. You insert a ttsAbort wherever
you want this to occur.
-
8/11/2019 Ax2012 Enus Devii 04
26/26
Development II in Microsoft DynamicsAX 2012