Ax2012 Enus Devii 04

download Ax2012 Enus Devii 04

of 26

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