COURSE: 80312
DEVELOPMENT III
Last Revision: August 2011
This courseware is provided “as-is”. Information and views expressed in this courseware, including URL and other Internet Web site references, may change without notice.
Unless otherwise noted, the examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This courseware does not provide you with any legal rights to any intellectual property in any Microsoft product. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this courseware may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means or for any purpose, without the express written permission of Microsoft Corporation.
Copyright © 2011 Microsoft Corporation. All rights reserved.
Microsoft®,MicrosoftDynamics®, Microsoft® PowerPoint®,Microsoft® SQL Server® data management software and Microsoft Dynamics® AX are trademarks of the Microsoft group of companies. All other trademarks are
property of their respective owners.
Introduction 0-1
Welcome ... 0-1 Microsoft Dynamics Courseware Contents ... 0-2 Documentation Conventions ... 0-3 Student Objectives ... 0-4Chapter 1: X++ Unit Test Framework
1-1
Objectives ... 1-1 Introduction ... 1-1 Creating Test Cases ... 1-2 Adding Methods to Test Cases ... 1-3 Running Test Cases ... 1-5 Build Test Projects and Suites ... 1-7 Lab 1.1 - Create a Test Case ... 1-11 Summary ... 1-13 Test Your Knowledge ... 1-14 Quick Interaction: Lessons Learned ... 1-16 Solutions ... 1-17
Chapter 2: Working with Data
2-1
Objectives ... 2-1 Introduction ... 2-1 While Select ... 2-2 Query ... 2-9 Lab 2.1 - Fetching Data ... 2-12 Lab 2.2 - Converting Queries ... 2-15 Caching ... 2-17 Locking ... 2-23 Lab 2.3 - Reducing Locking ... 2-25 Temporary Tables ... 2-27 Lab 2.4 - Temporary Tables ... 2-28 InitFrom ... 2-31 Parm Tables ... 2-31 Date Effectiveness ... 2-32 Computed Columns in Views ... 2-34 Data Integration ... 2-36 Lab 2.5 - Integrating External Data... 2-44 Summary ... 2-46 Test Your Knowledge ... 2-47 Quick Interaction: Lessons Learned ... 2-50 Solutions ... 2-51
Chapter 3: Classes
3-1
Objectives ... 3-1 Introduction ... 3-1 Collection Classes ... 3-2 Lab 3.1 - Create a Map ... 3-12 Application Object Classes ... 3-14 Lab 3.2 - Create a Query From Code ... 3-19
Application Substituted Kernel Classes ... 3-21 Lab 3.3 - Create a Global method ... 3-24 RunBase Framework ... 3-25 Lab 3.4 - Make a RunBase Class ... 3-37 Args Object ... 3-39 Lab 3.5 - Using Args ... 3-42 Summary ... 3-44 Test Your Knowledge ... 3-45 Quick Interaction: Lessons Learned ... 3-47 Solutions ... 3-48
Chapter 4: Forms
4-1
Objectives ... 4-1 Introduction ... 4-1 Architecture ... 4-2 Data Sources ... 4-6 Lab 4.1: Create a form ... 4-16 Form Controls ... 4-18 Lab 4.2 - Use Unbound Controls ... 4-22 Form Methods ... 4-23 Lab 4.3 - Initialize a Form ... 4-25 Placement of Code ... 4-26 Additional Controls ... 4-26 Lab 4.4 - Add a window control ... 4-35 Summary ... 4-37 Test Your Knowledge ... 4-38 Quick Interaction: Lessons Learned ... 4-39 Solutions ... 4-40Chapter 5: Visual Studio Integration
5-1
Objectives ... 5-1 Introduction ... 5-1 Application Explorer ... 5-2 Visual Studio Projects ... 5-3 Managed code projects ... 5-3 Deploying Managed Code ... 5-6 Visual Studio Debugging Experience for X++ ... 5-7 Lab 5.1 - Create a Managed Code Project ... 5-9 Lab 5.2 - Create an Event Handler in Managed Code ... 5-10 Summary ... 5-11 Test Your Knowledge ... 5-12 Quick Interaction: Lessons Learned ... 5-14 Solutions ... 5-15
Chapter 6: Workflow
6-1
Objectives ... 6-1 Introduction ... 6-1
Create a Workflow Type ... 6-4 Enable Workflow on a Form ... 6-5 Create a Workflow Approval ... 6-8 Create Event Handlers ... 6-9 Author a Workflow ... 6-12 Lab 6.1 - Add Another Condition to the Submit Action ... 6-15 Lab 6.2 - Enable Resubmit ... 6-17 Summary ... 6-20 Test Your Knowledge ... 6-21 Quick Interaction: Lessons Learned ... 6-22 Solutions ... 6-23
INTRODUCTION
Welcome
We know training is a vital component of retaining the value of your Microsoft Dynamics® AX 2012. investment. Our quality training from industry experts
keeps you up-to-date on your solution and helps you develop the skills necessary for fully maximizing the value of your solution. Whether you choose Online Training, Classroom Training, or Training Materials; there is a type of training to meet everyone's needs. Choose the training type that best suits you so you can stay ahead of the competition.
Online Training
Online Training delivers convenient, in-depth training to you in the comfort of your own home or office. Online training provides immediate access to training 24 hours-a-day. It is perfect for the customer who does not have the time or budget to travel. Our newest online training option, eCourses, combine the efficiency of online training with the in-depth product coverage of classroom training, with at least two weeks to complete each course.
Classroom Training
Classroom Training provides serious, in-depth learning through hands-on interaction. From demonstrations to presentations to classroom activities, you receive hands-on experience with instruction from our certified staff of experts. Regularly scheduled throughout North America, you can be sure you will find a class convenient for you.
Training Materials
Training Materials enable you to learn at your own pace, on your own time with information-packed training manuals. Our wide variety of training manuals feature an abundance of tips, tricks, and insights you can refer to again and again:
Microsoft Dynamics Courseware
The Microsoft Dynamics Courseware consists of detailed training manuals, designed from a training perspective. These manuals include advanced topics as well as training objectives, exercises, interactions and quizzes.
Look for a complete list of manuals available for purchase on the Microsoft Dynamics website: www.microsoft.com/Dynamics.
Microsoft Dynamics Courseware Contents
Test Your Skills
Within the Microsoft Dynamics Training Materials you find a variety of different exercises. These exercises are offered in three levels to accommodate the variety of knowledge and expertise of each student. We suggest you try the level three exercises first, if you need help completing the task look to the information in the level two exercises. If you need further assistance each step of the task is outlined in the level one exercise.
Challenge Yourself!
Level 3 exercises are the most challenging. These exercises are designed for the experienced student who requires little instruction to complete the required task.
Need a Little Help?
Level 2 exercises are designed to challenge students, while providing some assistance. These exercises do not provide step by step instructions, however, do provide you with helpful hints and more information to complete the exercise.
Step by Step
Level 1 exercises are geared towards new users who require detailed instructions and explanations to complete the exercise. Level 1 exercises guide you through the task, step by step, including navigation.
Quick Interaction: Lessons Learned
At the end of each chapter within the Microsoft Dynamics Training Material, you find a Quick Interaction: Lessons Learned page. This interaction is designed to provide the student with a moment to reflect on the material they have learned. By outlining three key points from the chapter, the student is maximizing knowledge retention, and providing themselves with an excellent resource for reviewing key points after class.
Documentation Conventions
The following conventions and icons are used throughout this documentation to help you quickly and effectively navigate through the information.
CAUTION: Cautions are found throughout the training manual and are preceded by the word CAUTION in bold. Cautions are used to remind you of a specific result of a specific action which may be undesirable.
HINT: Hints are found throughout the training manual and are preceded by the word HINT in bold. Hints are used to suggest time-saving features or alternative methods for accomplishing a specific task.
NOTE: Notes are found throughout the training manual and are preceded by the word NOTE in bold. Notes are used to provide information which, while not critical, may be valuable to an end user.
BEYOND THE BASICS: Advanced information found throughout the training manual is preceded by the words BEYOND THE BASICS in bold. Beyond the Basics provides additional detail, outside of standard functionality, that may help you to more optimally use the application.
EXAMPLE: Examples are found throughout the training manual and are preceded by the word EXAMPLE in bold. Examples bring to light business scenarios that may better explain how an application can be used to address a business problem.
Student Objectives
What do you hope to learn by participating in this course? List three main objectives below.
1.
2.
CHAPTER 1: X++ UNIT TEST FRAMEWORK
Objectives
The objectives are:
• Create a test case.
• Add methods to a test case. • Run a test case.
• Build a test project and suite. • Isolate test cases appropriately.
Introduction
The X++ Unit Test framework allows for unit tests to be created along with the code they are designed to test.
A unit test is code that verifies that some specific application code has been implemented correctly. If you adhere to the principles of test-driven
development, it is best for the developer who is writing the application code to write the unit tests either before or during development.
Scenario
Isaac, the systems developer, is about to start a development project based on some written specifications. Before he begins writing code, he has been asked to create a Unit Test suite, to test the project during its lifecycle.
Creating Test Cases
A unit test, in the context of the Unit Test framework, includes test cases, how test cases are staged with data, and the organization of test cases. A test case is a class that extends the SysTestCase class. You can add test methods to test each requirement of the feature code.
The following example is of how to create a test case. In this example you will be testing an existing system class, so the results should be successful. You will test the SysDictTable class, to make sure its methods to return the table name and group for a given table name, are correct. The following is the test case class declaration.
[SysTestTargetAttribute('SysDictTable', UtilElementType::Class)]
class SysDictTableTest extends SysTestCase {
SysDictTable sysDictTable; }
Note the attribute used on the class. The SysTestTargetAttribute attribute is attached to the test case to specify which element is being tested. In this case, it is the SysDictTable class being tested. There are a number of predefined attributes available in the Unit Test framework:
Attribute Description Applied to
SysTestMethodAttribute Indicates that a method is a unit test.
Method SysTestCheckInAttribute Indicates the test is a
check-in unit test. A check-in test is run when checking in code to a version control system to ensure a proper level of quality.
Method or Class
SysTestNonCheckInAttribute Indicates the test is not a check-in test.
Attribute Description Applied to
SysTestTargetAttribute Indicates the application object that is being tested by the case. This attribute takes two parameters: Name of element, and UtilElementType value.
Class
SysTestInactiveTestAttribute Indicates the class or method is inactive.
Method
Another variable has been created in the ClassDeclaration, of the type of the class being tested.
Adding Methods to Test Cases
The next step in creating a test case, is to add some methods to the test case class. Each method should assert that one of the known requirements is true.
There are two new methods added in the following example: one to assert that the table's name is as expected, and the other to assert that the table's group value is as expected.
[SysTestMethodAttribute] public void testTableName() {
// Verify that the tables name is set correctly. this.assertEquals("CustTable", sysDictTable.name()); }
[SysTestMethodAttribute] public void testTableGroup() {
// Verify that the tables group is set correctly. this.assertEquals(TableGroup::Main,
sysDictTable.tableGroup()); }
A test method will typically contain several assertions that are required to hold true, for the test to be successful.
Note the use of the assertEquals method. This method, and other assert methods, are available as part of the SysTestCase framework. These methods also have an optional string parameter called _message, to specify the message that would go into the infolog, if the assertion fails. The following is a list of available methods:
Method Description
assertEquals Tests if two values are equal. assertNotEqual Tests if two values are different.
Method Description
assertTrue Tests if the value is true. assertFalse Tests if the value is false. assertNull Tests if the value is null. assertNotNull Tests if the value is not null.
assertSame Tests if the objects referenced are the same. assertNotSame Tests if the objects referenced are not the same. assertRealEquals Tests if real values differ by the amount specified in
the delta. assertExpectedInfolog
Message
Tests for an infolog message that is expected. fail Enables a developer to create custom validation
logic and then call the fail method to integrate with the Unit Test framework.
Setup Method
The next step is to override the setUp method of the SysTestClass. This will tell the Unit Test framework what parameters need to be setup before the test is run. In this case, you need to instantiate the SysDictTable object using the table name.
public void setUp() {
sysDictTable = new SysDictTable(TableNum(CustTable)); super();
}
The setUp method can also be used to insert or update any data necessary for the test.
TearDown Method
At the completion of a test run, it may be necessary to "undo" any data created in the setUp method. This can be done in the tearDown method, which is called at the end of the test. The tearDown method can be overridden on any class extending SysTestCase.
Running Test Cases
When the test class has methods added to it, and appropriate setUp and
tearDown methods, it can be run.
To run the test case, right click the SysDictTableTest class, click Add-Ins and then Run tests. The Unit Test toolbar will appear, and the test case will be added to the list of tests. The test will automatically be run, and the results displayed in the toolbar, and in the infolog (if it failed).
FIGURE 1.1 UNIT TEST TOOLBAR
The SysDictTableTest test should pass on the first run. Try changing the
expected values in the test* methods, or the table id used in the setUp method, to emulate a test that fails, then run the test again. To run the test again, click the
Run button on the Unit Test toolbar.
If the test fails, click the Details button to see more details of the results.
Reviewing Test Results
To review test results, the Test jobs form can be opened from the Tools menu (Tools > Unit test > Test jobs).
The Test jobs form lists all previous runs of tests, and displays information about the test.
Click the Tests button on the Test jobs form, to open another form where test information is available at the method level.
Listeners
Test results can be displayed in various formats, to suit the requirement. There are several standard listeners that capture test results and format their results in different ways. These can be configured on the Unit Test Parameters window, as seen in the figure below.
FIGURE 1.4 LISTENER CONFIGURATION
Standard listeners include: Database, Infolog, Infolog (result only), Message window, Print window, Progress bar, Text file, and XML file.
NOTE: It is also possible to create new listeners, by extending the
SysTestListener interface. This can be explored further on MSDN at this location
http://go.microsoft.com/fwlink/?LinkID=225151&clcid=0x409,by following the "How to: Display Test Case Results" topic in the "Unit Test Framework" section.
Build Test Projects and Suites
Test classes can be organized in two ways: • Test projects
• Test suites
Test Projects
Test projects are groupings of test classes and appear in the Development
Use the following procedure to create a new Test project: 1. Open the Project tree.
2. Right-click either the Private or Shared node, point to New, and then click Test project. This creates a project with a test suite. 3. Open the AOT.
4. In the AOT, select one or more test classes to include in the test project. Drag them to the TestSuite node in the Test project.
FIGURE 1.5 TEST PROJECT
Once test classes have been added to a Test project, the entire collection of tests can be run at the same time, by right-clicking the root node of the project, and clicking Run.
Test Suites
Collections of tests can also be created with X++ code. These collections extend the SysTestSuite class, and are referred to as Suites.
Use the following procedure to create a test suite class: 1. In the AOT, create a new Class.
2. Open the new class in the Code Editor.
3. Edit the ClassDeclaration so that the class extends the SysTestSuite class.
4. Override the new method on the class.
5. In the new method, call the add method to add test cases to the suite, as shown.
public void new() {
// Create an instance of a test suite. SysTestSuite suiteDictTableTest = new
this.add(suiteDictTableTest); }
The setUp and tearDown methods can also be used in the scope of a Test Suite. Both methods are available to override on any class extending the SysTestSuite class. In this way, the setting up of data and variables can be done before the entire suite of tests is run.
Isolation
The isolation level of a test case varies based on the changes that the test case will make to the data. Each test case could have different needs for isolation based on what data it will change. The Unit Test framework provides four test suite base classes that provide different levels of isolation. The following table describes the test suites by the level of isolation that they provide.
Test suite class Description
SysTestSuite Default test suite. No isolation. SysTestSuiteCompanyIsolate
Class
Constructs an empty company account for each test class. All test methods on the class are run within the company, then it is deleted.
SysTestSuiteCompanyIsolate Method
Constructs an empty company account for each test method, and then deletes it at the end of the method.
SysTestSuiteTTS Wraps each test method in a transaction. After the method is complete, the transaction is aborted.
Note: Tests that need to commit data will not work in this suite. Also, the
ParmExceptionExpected exception is not supported in this suite.
SysTestSuiteCompIsolate ClassWithTts
This is a combination of
SysTestSuiteCompanyIsolateClass and SysTestSuiteTTS.
To apply a specific test suite to a test class, override the createSuite method on the test class, and return an object of the type of the suite required.
The following example demonstrates how a test suite can be applied to a test class.
class SysTestSuite createSuite() {
// Isolation level: construct a company account for the entire test class.
return new SysTestSuiteCompanyIsolateClass(this); }
Lab 1.1 - Create a Test Case
During this lab, you will create a test case and add it to a test suite.
Scenario
Isaac is about to start a development project based on some written
specifications. Before he begins writing code, he has been asked to create a Unit Test suite, to test the project during its lifecycle.
Challenge Yourself!
Create a test case class, and add a test method to it. The test case should check that when an address record's Zip code is changed, the correct City is
automatically updated. Once complete, create a test suite class that enforces TTS isolation, and link the test case class to it. Finally, run the test.
Step by Step
1. Open the AOT. 2. Create a new class.
3. Rename the class to MyTestSuite.
4. Edit the ClassDeclaration so that it extends the SysTestCase class, and add a variable logisticsPostalAddress of type
LogisticsPostalAddress.
5. Add a public method called testCity.
6. Add a line of code to the method: this.assertEquals("New York",
logisticsPostalAddress.City);
7. Override the setUp method on the class. It should look like the following code.
public void setUp() {
ttsBegin;
select firstonly forupdate logisticsPostalAddress where logisticsPostalAddress.City != "New York"
&& logisticsPostalAddress.CountryRegionId == "USA"; logisticsPostalAddress.validTimeStateUpdateMode(ValidTimeSt ateUpdate::EffectiveBased); logisticsPostalAddress.ZipCode = "10001"; logisticsPostalAddress.ZipCodeRecId = 0; logisticsPostalAddress.LogisticsPostalAddressMap::modifiedF ieldZipCode();
logisticsPostalAddress.update(); ttsCommit;
super(); }
8. Create a new class.
9. Name the class MyTestSuiteTTS.
10. Edit the ClassDeclaration so that it extends SysTestSuiteTTS. 11. Go back to the MyTestSuite class, and override its createSuite
method.
12. Change the method to read: return new MyTestSuiteTTS(this); 13. Run the test class.
Summary
This lesson explains how to use the Unit Test framework to test X++ code during the development lifecycle.
The concepts of test cases, assertions, projects, suites and isolation are
Test Your Knowledge
Test your knowledge with the following questions. 1. What class does a test class need to extend?
3. Where can the Test jobs form be opened from?
Quick Interaction: Lessons Learned
Take a moment and write down three key points you have learned from this chapter
1.
2.
Solutions
Test Your Knowledge
1. What class does a test class need to extend? MODEL ANSWER:
SysTestCase
2. What is the setUp method on the SysTestCase class used for? MODEL ANSWER:
It is used to prepare variables and data that are required for the test. 3. Where can the Test jobs form be opened from?
MODEL ANSWER:
Tools > Unit test > Test jobs.
4. What is meant by "isolation" in the context of the Unit Test framework? MODEL ANSWER:
Isolation refers to the scope in which company data should be created and destroyed during a test run.
CHAPTER 2: WORKING WITH DATA
Objectives
The objectives are:
• Program optimal database access using a "while select" statement. • Program optimal database access using queries.
• Describe the caching mechanisms in Microsoft Dynamics® AX.
• Prevent and resolve database locking.
• Use temporary tables in classes, forms, and reports. • List the reasons for using InitFrom<tablename> methods. • Use ParmId and ParmTables.
• Discuss date effectiveness and describe how to build date effective forms.
• Add a computed column to a view.
• Employ the various techniques available for integrating external data with Microsoft Dynamics AX.
Introduction
A Microsoft Dynamics AX application processes large amounts of data. Most functions involve sending data between the client and Application Object Server (AOS) and between the AOS and database server. It is important to use the correct approach to database access when developing in Microsoft Dynamics AX. Almost every performance bottleneck is associated with database traffic.
While Select
This section describes the different qualifiers and options that can be used in the
select statement, to achieve optimal database access performance.
The complete syntax for the select statement is as follows.
[while] select [reverse] [firstfast]
[firstonly] [firstOnly10] [firstOnly100] [firstOnly1000] [forupdate] [nofetch] [crosscompany]
[forcelitterals | forceplaceholders] [forcenestedloop] [forceselectorder]
[repeatableRead] [validTimeState] [ * | <fieldlist> from] <tablebuffer> [ index [hint] <indexname> ]
[ group by {<field>} ]
[ order by {<field> [asc][desc]} ] [ where <expression> ]
[ outer | exists | notexists ] join [reverse] [ * | <fieldlist> from] <tablebuffer>
[ index <indexname> ]
[sum] [avg] [minof] [maxof] [count] [ group by {<field>} ]
[ order by {<field> [asc][desc]} ] [ where <expression> ]
]
<fieldlist> ::= <field> | <fieldlist> , <field> <field> ::= fieldname | <function>(<field>)
General Optimization
To optimize general performance, the following tools and keywords may be used.
Fieldlist
One way to optimize communication with the database is to specify which fields are returned. For example, for a table with 40 fields, reading the information from only four fields will reduce the amount of data sent from the database server by up to 90 percent
.
The following illustrates using a field list.
while select amountMST from ledgerTrans {
amountMST += ledgerTrans.amountMST; }
NOTE: Use this optimization with care. If the record returned from the database
is subsequently passed as a parameter to other methods, that method may have been written on the assumption that all fields are set. Only use field lists when controlling access to the information locally.
Aggregation
To obtain a sum of records, consider instructing the database to calculate the sum and only return the result, as an alternative to reading all the records and making the aggregation yourself. To receive a sum specified for one or more fields in the table, combine the aggregation with a group by clause. The following
aggregation clauses are available.
Aggregation clause Description
sum Returns the sum of the values in a field. avg Returns the average of the values in a field. maxof Returns the maximum of the values in a field. minof Returns the minimum of the values in a field.
count Returns the number of records that satisfy the statement. The following illustrates using aggregate functions:
select sum(qty) from inventTrans; qty = inventTrans.amountMST;
select count(recId) from inventTrans; countInventTrans = ledgerTrans.recId;
Join
To read records in a main table and then process related records in a transaction table for each main table record, one solution is to make one "while select" statement which loops over the records in the main table and another nested "while select" statement which loops over the transaction records related to the current record in the main table. The following is an example of a nested "while select" statement
while select InventTable {
while select InventTrans
where InventTrans.itemId == inventTable.itemId {
qty += inventTrans.qty; }
}
To process 500 records in the main table, this approach would have 501 SQL statements executed on the database.
Alternatively, making a single "while select" statement with a join clause reduces the number of SQL statements to just 1.
The following example illustrates using a join clause (and fieldlists for extra performance).
while select recId from inventTable join qty from inventTrans
where inventTrans.itemId == inventTable.itemId {
qty += inventTrans.qty; }
ForceLiterals
ForceLiterals instructs the kernel to reveal the actual values used in the "where" clauses to the database server at the time of optimization. This is the default behavior in all "join" statements involving more than one table from the following table groups:
• Miscellaneous • Main
• Transaction • Worksheet Header • Worksheet Line
The advantage of using this keyword is that the server now gets all information needed to calculate the optimal access plan for a statement. The disadvantage is that the access plan cannot be reused with other search values and that the optimization may use more CPU resources on the database server. High frequency queries should not use literals.
The following X++ statement is an example of how to use this keyword.
static void DemoForceLiterals() {
InventTrans inventTrans; ;
while select forceliterals inventTrans order by itemId
where inventTrans.DatePhysical >= mkdate(21,12,2012) {
} }
It is not possible to determine whether an index on itemId or an index on
DatePhysical should be used without considering the actual value of 21\12\2012. Therefore, the keyword should be used as shown in the previous code sample.
ForcePlaceholders
ForcePlaceholders instructs the kernel not to reveal the actual values used in where clauses to the database server at the time of optimization. This is the default in all non-join statements. The advantage of using this keyword is that the kernel can reuse the access plan for other similar statements with other search values. The disadvantage is that the access plan is computed without considering that data distribution might not be even, or that the access plan is an "on average" access plan.
The following X++ statement is an example of when to use this keyword.
static void DemoForcePlaceholders() {
SalesTable salesTable; SalesLine salesLine; ;
while select forcePlaceholders salesLine join salesTable
where salesTable.SalesId == salesLine.SalesId && salesTable.SalesId == '10'
{ } }
In the previous code example, the database server automatically chooses to search the SalesTable using an index on salesId. The database server uses the fact that the salesId column is a unique field and does not need the actual search value to compute the optimal access plan.
FirstFast
FirstFast instructs the SQL-database to prioritize fetching the first few rows fast over fetching the complete result set. This also means that the SQL-database might select an index fitting the order by clause over an index fitting the "where" clause. The FirstFast hint is automatically issued from all forms, but is rarely used directly from X++.
Firstonly
When Microsoft Dynamics AX fetches data from the database, it transfers a package of records in each fetch. This is called read-ahead caching and is performed to minimize calls to the database. If it is known that only one record will be fetched, you can disable the read-ahead caching with this qualifier.
NOTE: It is best practice to use this in the "find" methods on the tables.
The following example illustrates the use of FirstOnly.
static CustTable findCustTable(CustAccount _custAccount) {
CustTable custTable; ;
select firstonly custTable
where custTable.AccountNum == _custAccount;
return custTable; }
NOTE: It is important that "find" methods are designed to be executed on the
tier where the method is called. This is the default behavior on static table methods. If the method is bound on the server it will always require a round-trip to the AOS even if the data was cached on the client.
Access Plan Repair
The following keywords are categorized as access plan repair keywords and should not be used unless required to fix specific performance problems.
An incorrectly used index hint can affect performance, so index hints should only be applied to SQL statements that do not have dynamic where or order by clauses and where the effect of the hint can be verified.
Microsoft Dynamics AX automatically removes index hints referring to a disabled index.
By default index hints are disabled on the AOS.
ForceSelectOrder
This keyword forces the database server to access the tables in a join in the given order. If two tables are joined the first table in the statement is always accessed first. This keyword is frequently combined with the forceNestedLoop keyword. One situation where it can be interesting to force a select order is when you use
index hint on a join. The following construction is an example of the ForceSelectOrder.
static void DemoForceSelectOrder() {
InventTrans inventTrans; InventDim inventDim; ;
while select inventTrans index hint ItemIdx
where inventTrans.ItemId == 'X' join inventDim
where inventDim.inventDimId == inventTrans.inventDimId
&& inventDim.inventBatchId == 'Y' {
} }
Give the database a hint on using the index ItemIdx on the table InventTrans. This works well if the database searches this table first. But if the database, with the help of the generated statistics, starts with the table InventDim and then finds records in InventTrans for each occurrence of InventDimId, the use of the index ItemIdx may not be an appropriate approach. To hint indexes in a join, consider specifying forceSelectOrder, as shown in the following example.
static void DemoForceSelectOrder() {
InventTrans inventTrans; InventDim inventDim; ;
while select forceSelectOrder inventTrans index hint ItemIdx
where inventTrans.ItemId == 'X' join inventDim
where inventDim.inventDimId == inventTrans.inventDimId
&& inventDim.inventBatchId == 'Y' {
} }
ForceNestedLoops
This keyword forces the database server to use a nested-loop algorithm to process a given SQL statement that contains a join. This means that a record from the first table is fetched before trying to fetch any records from the second table. Generally other join algorithms like hash-joins, merge-joins, and others are also considered. This keyword is frequently combined with the forceSelectOrder keyword.
Review the previous example with the tables InventTrans and InventDim. You could risk that the database finds all InventTrans records by the index ItemIdx and all the InventDim records by the BatchId. (If you hint the index DimIdIdx this will be used for this search.) The two collections of records are hashed together. For the database to find the inventTrans and then the inventDim for each inventTrans, specify forceNestedLoops, as shown in the following example.
static void DemoForceSelectOrder() {
InventTrans inventTrans; InventDim inventDim; ;
while select forceSelectOrder forceNestedLoop inventTrans
index hint ItemIdx
where inventTrans.ItemId == 'X' join inventDim
where inventDim.inventDimId == inventTrans.inventDimId
&& inventDim.inventBatchId == 'Y' {
} }
Cross Company
By default, select statements in X++ adhere to the DataAreaId structure of the application. For example, if there are two company accounts, 001 and 002, a select statement run in company 002 will only return records stamped with dataAreaId equal to 002. The crossCompany keyword allows the statement to fetch records regardless of the dataAreaId. The following example shows a statement that will count all customer records from all company accounts.
select crossCompany count(recId) from custTable; countCustTable = custTable.recId;
You also have the option of adding a container variable of company identifiers immediately after the crosscompany keyword (separated by a colon). The
container restricts the selected rows to those with a dataAreaId that match a value in the container. The following example illustrates the use of the crossCompany keyword.
CustTable custTable;
container conCompanies = [ '001', '002', 'dat' ]; ;
while select crosscompany : conCompanies * from custTable order by dataAreaId
{
//do something }
Query
A query is an object-oriented interface to the SQL database. A query is composed of objects from different classes.
Various objects are available to manipulate a query object from the AOT. Use the queryRun object to execute the query and fetch data.
The query object is the definition master. It has its own properties and has one or more related data sources.
The queryBuildDataSource defines access to a single table in the query. If one data source is added below another data source, they form a join between the two tables.
The queryBuildFieldList object defines which fields to fetch from the database. The default is a dynamic field list that is equal to a "select * from …". Each data source has only one queryBuildFieldList object which contains information about all selected fields. You can also specify aggregate functions like sum,
The queryBuildRange object contains a limitation of the query on a single field. The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than the queryBuildRange object and filters the parent table based on the child table results.
The queryBuildDynaLink objects can only exist on the outer data source of a query. The objects contain information about a relation to an external record. When the query is run, this information is converted to additional entries in the "where" section of the SQL statement. The function is used by forms when two data sources are synchronized. The subordinate data source contains DynaLink(s) to the master data source. The function is used even if the two data sources are placed in two different forms, but are synchronized.
The queryBuildLink objects can only exist on inner data sources. The objects specify the relation between the two tables in the join.
The following is a sample of code using some of these QueryBuild objects, to build a query and loop through it:
Query q; QueryRun qr; QueryBuildDatasource qbds1, qbds2; QueryBuildRange qbr; ; q = new Query(); qbds1 = q.addDataSource(tablenum(InventTable)); qbds2 = qbds1.addDataSource(tablenum(InventTrans)); qbds2.relations(TRUE); //this enforces a relationship between this datasource and its parent. Relationships defined in the Data Dictionary are used by default. qbr = qbds1.addRange(fieldnum(InventTable, ItemId)); qbr.value(SysQuery::value("1706")); //SysQuery object provides various static methods to assist in defining Query criteria. The SysQuery::value() method should always be used when defining a singular value for a range.
qr = new QueryRun(q); while(qr.next()) { //do something }
You can build a query in the AOT using MorphX or as shown in the previous topic, by dynamically creating the query by X++ code. Both approaches are used in the standard application. One advantage of making the query dynamic is that it is not public in the AOT and is protected against unintentional AOT changes. Alternatively, an advantage of creating the query in the AOT is that it can be
Forms and reports use queries for fetching data. These queries are not public below the Query-node in the AOT. Forms generate the queries dynamically from the setting of the data source, whereas Reports have an integrated query-node. In both cases, you can edit the query for modification.
There are alternative ways of specifying existing objects in a query. Best practice is to use the intrinsic functions which use table and field ID as arguments
Lab 2.1 - Fetching Data
Scenario
As part of a larger modification, you need to develop code which queries the database for specific records. Use AOT queries, "while select" statements, and query objects to develop the most efficient data fetching code.
Challenge Yourself!
Make the following query "Count the number of customers from all companies, limited to a specific currency." For this example, make USD the limiting value.
Perform the following actions:
1. Create a job which makes the above select using "while select". The currency to use can be defined in the code.
2. Create a new query in the AOT. Prompt the user for the currency to use when the query is run.
3. Create a job which executes the query defined in step 2.
4. Create a new job which builds the same query dynamically using the query classes. Prompt the user for the currency to use when the query is run.
5. Verify that the three implementations return the same data.
Step by Step
1. The following shows query made in a job using "while select".
static void SelectCustomerJob(Args _args) {
CustTable custTable; Counter recordsFound; ;
while select crosscompany custTable where custTable.Currency =="USD" {
recordsFound++; }
info(strFmt("Customers found: %1", recordsFound)); }
2. The following shows a query built in AOT:
FIGURE 2.1
3. The following shows the job that executes query from step 2.
static void SelectCustomerRunQuery(Args _args) {
QueryRun queryRun; Counter recordsFound; ;
queryRun = new QueryRun(queryStr(CustTableByCurrency)); if (queryRun.prompt()) { while (queryRun.next()) { recordsFound++; } }
info(strFmt("Customers found: %1", recordsFound)); }
4. The following shows the job that dynamically builds the query
static void SelectCustomerQuery(Args _args) { Query query; QueryBuildDataSource queryBuildDatasource; QueryFilter queryFilter; QueryRun queryRun; Counter recordsFound; ;
query = new Query();
query.allowCrossCompany(TRUE);
queryBuildDataSource = query.addDataSource(tableNum( CustTable));
queryFilter =
identifierStr(Currency));
queryRun = new QueryRun(query); if (queryRun.prompt()) { while (queryRun.next()) { recordsFound++; } }
info(strFmt("Customers found: %1", recordsFound)); }
Lab 2.2 - Converting Queries
Scenario
Your development manager has asked that you re-write all your queries using Query objects instead of "while select" statements.
Make a job that dynamically builds a query, using query objects, with the same structure as the following statement.
inventTrans inventTrans; inventDim inventDim;
while select sum(qty) from inventTrans where inventTrans.ItemId =="1706" join inventDim group by inventBatchId where inventDim.InventDimId == inventTrans.InventDimId { info(strFmt("Batch %1, qty %2", inventDim.inventBatchId, inventTrans.qty)); }
Enable the user to enter the item id when the query is run.
Perform the following actions:
1. Create a new job.
2. Use the Query, QueryBuildDataSource classes, and more, to create a query dynamically.
3. Create another job that uses "while select" as shown.
4. Verify that the two implementations return the same data, using item number 1706.
Step by Step
1. In the AOT, create a new job. 2. Copy the code above in to the job. 3. Press F5 to run the code
4. Note the result.
5. Create another new job. 6. Add the following code.
static void InventTransBuildQuery(Args _args) { Query query; QueryBuildDataSource queryBuildDataSource; QueryBuildRange queryBuildRange; QueryRun queryRun; Qty total; InventTrans inventTrans; inventDim inventDim;
query = new Query(); queryBuildDataSource = query.addDataSource(tableNum(InventTrans)); queryBuildDataSource.addSelectionField(fieldNum(InventTrans ,Qty),SelectionField::Sum); queryBuildRange = queryBuildDataSource.addRange(fieldNum(InventTrans, ItemId)); queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(InventDim)); queryBuildDataSource.addGroupByField(fieldNum(InventDim, InventBatchId)); queryBuildDataSource.relations(true); queryRun = new QueryRun(query);
if (queryRun.prompt()) { while (queryRun.next()) { inventTrans = queryRun.get(tableNum(InventTrans)); inventDim = queryRun.get(tableNum(inventDim)); info(strFmt("Batch %1, qty %2", inventDim.inventBatchId, inventTrans.qty)); } } }
7. Press F5 to run the job.
8. In the prompt, enter 1706 in the Item number field 9. Note the result.
Caching
The previous sections have discussed how to optimize communication with the database by structuring SQL statements that make the database access data in the correct way. But the most optimal strategy is to have no communication with the database or to minimize the number of communications with the database server. The concept of caching is to remember the information retrieved from the database and use this memory when the same data is needed at a later time. However, this strategy has one large drawback, if the remembered information is no longer valid, this could compromise the consistency of the database, as the updates will be made based on invalid data.
This section covers data caching done by Microsoft Dynamics AX AOS and/or client. Microsoft Dynamics AX supports the following kinds of caching:
• Read-ahead caching • Single-record caching • Entire table caching • Record view caching • Display method caching • Global object cache • Date effective join cache • Unique index cache extension • Cache lookup for table groups
The remainder of this section explains the first five above mentioned data catching.
Read-ahead Caching
The SQL interface in Microsoft Dynamics AX provides read-ahead caching and uses a buffer to pre-fetch rows. The read-ahead adapts to the queries executed. As an example, a query to show rows in a form pre-fetches in chunks that correspond to the number of rows currently visible in the form, whereas a multi-row select with the FirstOnly keyword will not pre-fetch.
Single-Record Caching
Microsoft Dynamics AX offers record caching. Rows selected through a cache-key are cached and successive look-ups specifying the cache-cache-key are then served from the record cache. The record-cache holds the rows that match frequently issued single-row queries.
The cache-key is the Primary Key on the cached table. If a Primary Key does not exist, the first unique index is used.
Because only single-row queries go into the cache, the cache is small with a default value of 100 rows on the client and 2000 rows on the AOS.
Four single record cache settings are used with tables:
CacheLookup Result
None No data is cached or retrieved from cache for this table. No caching should be used for tables that are heavily updated or where it is unacceptable to read outdated data.
NotInTTS All successful caching key selects are cached. When in TTS (after ttsbegin), the record is read once from the database and subsequently from cache. The record is select-locked when read in TTS which ensures that the record cached is not updated as long as the TTS is active.
A typical example of its use is the CustTable in the standard application. It is acceptable to read outdated data from cache outside TTS, but when data is used for validation or creating references it is ensured that it is real-time data.
Found All successful caching key selects are cached. All caching key selects are returned from cache if the record exists there. A select forupdate in TTS forces reading from the database and replaces the record in the cache. This is typically used for static tables like ZipCodes where the normal case is that the record exists.
FoundAndEmpty All selects on caching keys are cached, even selects not returning data.
All caching key selects are returned from caching if the record exists there or the record is marked as non-existing in the cache.
An example of this kind of caching is found in the
InventTxt table in the standard application. For this
table it is a normal case that no record exists. Therefore, in this case it is of great value to register the non-existing keys.
Important features about single-record caching are as follows:
• The caching only works when selecting exactly one record, with a distinct where on the primary key.
• The cache will not support fetch of multiple records in a query or a "while select".
The cache on the client is not updated with changes done from other clients. This means that one client can cache and use invalid information.
The single record caching is selected by the CacheLookup property setting on the table.
You can use the AOT find tool to list some examples of this setting.
Entire Table Caching
The entire table caching loads all the records in the table the first time one record is fetched. There is no limitation on the number of records cached.
An individual cache is maintained for each data area in Microsoft Dynamics AX. The AOS holds the cache. This is shared between all clients connected to the AOS. The client supplements this AOS caching with a FoundAndEmpty single record caching if possible.
The entire table caching is not used when making a join with other tables which is not entire table cached.
The entire table caching is selected by the CacheLookup property setting on the table. The individual users can disable the caching clicking Tools > Options >
Preload. This form also provides an overview of which tables are configured by
using entire table caching.
FIGURE 2.2 PRELOAD TABLES
Record View Caching
Use record view caching to cache the records from a select statement (a result set). Result set caching is made available through the RecordViewCache class. The cache is instantiated using a select with the nofetch option. If the
instantiating select is a join, a temporary table or is instantiated without the
nofetch option, the cache is not instantiated and an error is reported. The select
The following example illustrates the instantiating of a record view cache:
SalesLine salesLine;
RecordViewCache salesLineCache; ;
select noFetch salesLine
where salesLine.SalesId =="100";
salesLineCache = new recordViewCache(salesLine);
The cache is not shared between Microsoft Dynamics AX clients, and is deactivated as soon as the cache object goes out of scope or is destroyed. The cache is used when a select is from the same table, not a join and at least matches the where clause that the cache was instantiated with.
If the table is not set up for Optimistic concurrency then using the forupdate option on the select within TTS results in database locks on all the rows that are put into the cache. A subsequent select forupdate within the same TTS is selected from the cache and not from the database as the row is already locked. Updates and deletes that match the instantiating where clause simultaneously maintain the database and the cache. Updates and deletes are not in the above category do not affect the result-set.
The reread method retrieves data from the database and updates the copy data. The cache can be instantiated using a select without a where clause. This effectively creates a copy of the table in memory and functions like a personal table copy.
The cache is useful for caching tables that are not of static nature, or that contain so many records that the other caching methods would be impractical.
Because of concurrency issues, the forupdate keyword on the instantiating X++
select should only be used when all the records in the result set will be updated.
Otherwise, it is a better strategy to select forupdate the records that are updated or use Optimistic concurrency on the tables.
Do not use field-lists unless you are certain that the omitted fields will never be used. There is no warning or error when selecting fields from the cache that were not included in the select that instantiated the result set.
Inserts normally do not affect the cache even when the record would match the
where clause defining the result-set.
When you use this form of caching, consider the memory consumption, as there is no internal limitation to the memory allocated for a single table. Different application behavior, with regard to exploiting caching when it is running on server versus client, could be an option.
One example of using record view caching is the class InventMovement. This holds information about inventory transactions related to one SalesLine, PurchLine and more. The cache is defined as a RecordViewCache object in \Classes\InventMovement\classDeclaration.
The cache is initialized by the method
\Data\Dictionary\Tables\InventTrans\Methods\viewCacheInventTransOrigin
public static RecordViewCache
viewCacheInventTransOrigin(InventTransOriginId _inventTransOriginId, boolean _forupdate = false) {
InventTrans inventTrans; ;
inventTrans.selectForUpdate(_forupdate); select nofetch inventTrans where
inventTrans.InventTransOrigin == _inventTransOriginId; return new RecordViewCache(inventTrans);
}
According to the cross reference, this method is called from \Classes\InventMovement\viewCacheInventTransId. void viewCacheInventTransOrigin() { if (viewCacheInventTrans) return; viewCacheInventTrans = null; viewCacheInventTrans = InventTrans::viewCacheInventTransOrigin(this.InventTransOri ginId(),true); }
Display Method Caching
Caching display methods improves the performance of display functions if they are calculated on the AOS and improves performance when records are
transferred from the server to the client.
Only methods that are explicitly added to the cache are affected by the new caching mechanism. To sign up a method for caching, the method
cacheAddMethod on the form data source should be called after super() in the init method of the data source.
The call to cacheAddMethod also determines how frequently the cached display method value is updated. The value is filled in upon fetching data from the back-end, and it is refreshed when reread is called on the data source. Additionally, by default, the display method values are also updated when the record is written to the database. But that can be changed using the _updateOnWrite parameter in the cacheAddMethod.
Only display methods that are of display type can be cached. Edit methods cannot be cached. Only display methods placed on the table, can be cached. One example of the initialization is found in \Forms\PurchTable\Data Sources\PurchLine\Methods\init. purchLine_ds.cacheAddMethod(tablemethodstr(PurchLine, receivedInTotal)); purchLine_ds.cacheAddMethod(tablemethodstr(PurchLine, invoicedInTotal)); purchLine_ds.cacheAddMethod(tablemethodstr(PurchLine, itemName)); purchLine_ds.cacheAddMethod(tablemethodstr(PurchLine, displayBudgetCheckResult)); purchLine_ds.cacheAddMethod(tablemethodstr(PurchLine, calcPendingQtyPurchDisplay));
Display methods not based on cached data, are candidates for display method caching.
Locking
So far you have seen that the database server can access data faster when the number of requests to the database is minimized, by using efficient queries and caching.
Another issue is if multiple users want to lock the same record at the same time. Only one user can lock the record, and the rest wait in a queue until the record is released. This has a substantial effect on the time used to perform a function. Locks are typically done in transactions where more than one record is locked at a time. The following example illustrates two different processes which lock different items:
Process 1 Process 2
Item C - Locked -
Item A - Locked -
Item B - Locked Item G - Locked Item G - Waiting for lock from
Process 2
Item A - Waiting for lock from Process 1
This situation is called a dead lock; one of the transactions must be aborted to resolve the issue.
Locking is the biggest issue with scalability, which means that many users are working with the same functions and data.
To reduce locking, consider using Optimistic Concurrency Control (OCC). When the risk of overlapping transactions modifying the same data is small, optimistic locking can increase concurrency and thus transaction throughput. Enable OCC for a table by setting the property OccEnabled to Yes.
NOTE: Only a few tables in the standard application do not use Optimistic
Concurrency Control.
Strategies to use to avoid or reduce the locking problem are as follows: • Keep the transactions as short in time as possible. Do not
compromise the integrity of the data. • Try to avoid locking central resources.
• Try to lock central resources in the same order. This avoids the dead lock situation discussed previously. This serializes the process, which means that only one user can perform this at a time. • Never implement dialog with the user inside a transaction.
Locking is held for the time it takes to finish the process. If the process has poor performance because of missing optimization of AOS and database
Lab 2.3 - Reducing Locking
Scenario
Some users have been reporting performance issues and deadlock messages, when using some of your modifications. You have been given the task of optimizing your code, to reduce any locking.
Challenge Yourself!
Modify the following job to reduce locking:
InventTable inventTable; VendTable vendTable; ;
ttsbegin;
while select forupdate inventTable { if (VendTable::find(inventTable.PrimaryVendorId).Blocked == CustVendorBlocked::All) { inventTable.PrimaryVendorId =""; inventTable.update(); } } ttscommit;
Perform the following actions:
1. Create a new job with the above contents. 2. Try to reduce the locking.
3. Try to redesign the code to obtain a better performance.
Step by Step
Solution 1: A new InventTable table variable is declared. Updates are done
against this variable, so that you do not need to lock the whole InventTable forupdate.
static void Locking_Solution_1(Args _args) { InventTable inventTable; InventTable inventTableUpdate; VendTable vendTable; ; ttsbegin; // solution #1
while select inventTable { if (VendTable::find(InventTable.PrimaryVendorId).Blocked == CustVendorBlocked::All) { inventTableUpdate = InventTable::find(inventTable.ItemId, true); inventTableUpdate.PrimaryVendorId =""; inventTableUpdate.update(); } } ttscommit; }
Solution 2: If and find on VendTable are replaced with exist join on VendTable.
static void Locking_Solution_2(Args _args) { InventTable inventTable; VendTable vendTable; ; ttsbegin; // solution #2
while select forupdate inventTable exists join vendTable
where vendTable.AccountNum == inventTable.PrimaryVendorId && vendTable.Blocked == CustVendorBlocked::All { inventTable.PrimaryVendorId =""; inventTable.update(); } ttscommit; }
Temporary Tables
A temporary table is defined in the AOT as a normal table, but with the
TableType property set to either InMemory or TempDB. Data is not persisted in a temporary table, and only remains while the table is in scope. If TableType is set to InMemory, the data is stored in memory or in a temporary file if memory is full. TempDb means it is stored in a table in SQL, and can be joined to regular tables at the database tier.
A typical example is a class which initializes a temporary table and inserts some records, which should be shown in a form. The class has a variable _tmpTable which holds the data and the form has a data source tmpTable_DS which should show the same data. This is solved by using the method
tmptable_DS.setTmpData(_tmpTable). This disregards the individual file
allocated to tmptable_DS. Now, both _tmpTable and tmptable_DS will access the same pool of data, as if they were normal table variables. The allocated file now shared between _tmpTable and tmptable_DS is deleted once both variables have gone out of scope.
Instead of making a dedicated definition of a temporary table in the AOT, consider making a temporary instance of a database table (in other words, a non-temporary table which is part of the SQL database). Do this using the .setTmp() method before accessing the variable. Be careful with this option, as you can activate other methods which act as if it is real database data and cause
subsequent updates in other tables. See the following example, which copies all customers from Australia to a temporary table.
static void CopyPersistedTableToTemp(Args _args) { CustTable custTable; CustTable tmpCustTable; ; tmpCustTable.setTmp();
while select custTable
where custTable.CountryRegionId == "AU" {
tmpCustTable.data(custTable.data()); tmpCustTable.doInsert();
} }
Lab 2.4 - Temporary Tables
Scenario
As part of a larger modification, you need to implement some temporary tables, to achieve the required outcome.
Challenge Yourself!
Make a job which works with two individual instances of temporary data (for example, TmpSum).
Perform the following steps:
1. Make a job with two variables A and B of type TmpSum. 2. Insert some records in variable A. Does this have an effect on the
contents of variable B?
3. Copy all records from A to B. Verify that the contents of the two tables are equal.
4. Delete one record from A. Does this have an effect on the contents of variable B?
5. Call the B.setTempData(A) method. Repeat step 2 and 4.
Step by Step
If you declare two temporary table variables A and B, inserts, updates, and deletes on table A do not have impact on B. This only happens if the method variableB.setTempData(variableA) is set before updating.
1. Open the AOT create a new job. 2. Copy the following code in to the job.
3. Read through the code carefully, and try to understand what each line is doing and what the result will be.
4. Press F5 to run the job. Verify the results are what you expected.
static void Temporary(Args _args) {
TmpSum a; TmpSum b;
Counter counter;
void countRecords(Tmpsum _tmptable, str _step, str _name)
{
select count(RecId) from _tmptable;
// Insert records in 'a'
for (counter = 1; counter<= 1000; counter++) {
a.Key = num2Str(counter,1,0,0,0); a.Balance01 = counter;
a.insert(); }
// Verify that inserts in 'a' do not affect 'b' countRecords(a,"2","a");
countRecords(b,"2","b");
// Copy all records from 'a' to 'b' b.skipDataMethods(true);
insert_recordSet b (Key, Balance01) select Key, Balance01 from a; countRecords(a,"3","a");
countRecords(b,"3","b"); // Delete one record from 'a' select forupdate a;
a.delete();
/* Verify that the contents of the two tables are not equal,
delete from ¢a¢ do not effect b */ countRecords(a, "4","a");
countRecords(b, "4","b"); // Call setTmpData method b.setTmpData(a);
// Insert 10 new records in ¢a¢
for (counter = 1001; counter<= 1010; counter++) {
a.Key = num2Str(counter,1,0,0,0); a.Balance01 = counter;
a.insert(); }
/* Verify that the contents of the two tabels are equal,
inserts in 'a' effect 'b' */ countRecords(a,"5a","a"); countRecords(b,"5b","b"); // Delete one record from ¢a¢ select forupdate a;
/* Verify that the contents of the two tabels are equal,
delete from 'a' effect 'b' */ countRecords(a,"5b","a"); countRecords(b,"5b","b"); ttscommit;
