RSS

Tag Archives: Database

Insert_recordset, Update_recordset, and delete_from single transaction command.

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordset, update_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.

Lets discuss about these commands one by one.

 

  • Insert_recordset

    A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.

    The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.

    static void Insert_RecordsetInsert(Args _args)

    {

    CarTable carTable;

    CarTable carTableTmp;

     

    /* Set the carTableTmp variable to be a temporary table.

    This means that its contents are only store in memory

    not in the database.

    */

    carTableTmp.setTmp();

    // Insert 3 records into the temporary table.

    carTableTmp.CarId = “200”;

    carTableTmp.CarBrand = “MG”;

    carTableTmp.insert();

    carTableTmp.CarId = “300”;

    carTableTmp.CarBrand = “SAAB”;

    carTableTmp.insert();

    carTableTmp.CarId = “400”;

    carTableTmp.CarBrand = “Ferrari”;

    carTableTmp.insert();

    /* Copy the contents from the fields carId and carBrand

    in the temporary table to the corresponding fields in

    the table variable called carTable and insert the chunk

    in one database operation.

    */

    Insert_Recordset carTable (carId, carBrand)

    select carId, carBrand from carTableTmp;

    }

    The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.

    static void Insert_RecordsetCopy(Args _args)

    {

    InventColor inventColor;

    InventTable inventTable;

    This material is copyright and is licensed for the sole use by ALESSANDRO CAROLLO on 18th December

    Chapter 6

    [ 169 ]

    InventColorId defaultColor = “B”;

    Name defaultColorName = “Blue”;

    ;

    insert_recordset inventColor (ItemId, InventColorId, Name)

    select itemId, defaultColor, defaultColorName

    from inventTable;

    }

    The field list inside the parentheses points to fields in the InventColor table.

    The fields in the selected or joined tables are used to fill values into the fields in

    the field list.

     

  • Update_recordset

    The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.

    The syntax for the update_recordset operator can be seen in the next example:

    static void Update_RecordsetExmple(Args _args)

    {

    CarTable carTable;

    ;

    info(“BEFORE UPDATE”);

    while select carTable

    where carTable.ModelYear == 2007

    {

    info(strfmt(“CarId %1 has run %2 miles”,

    carTable.CarId, carTable.Mileage));

    }

    update_recordset carTable setting Mileage = carTable.Mileage + 1000

    where carTable.ModelYear == 2007;

    info(“AFTER UPDATE”);

    while select carTable

    where carTable.ModelYear == 2007

    {

    info(strfmt(“CarId %1 has now run %2 miles”,

    carTable.CarId, carTable.Mileage));

    }

    }

     

    When this Job is executed it will print the following messages to the Infolog:

    Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.

     

  • Delete_from

    As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:

     

    static void Delete_FromExample(Args _args)

    {

    CarTable carTable;

     

    delete_from carTable

    where carTable.Mileage == 0;

    }

     

    Thanks for reading the post, any comments or questions are welcomed. Keep visiting the blog.

Advertisements
 
Leave a comment

Posted by on November 8, 2010 in AX, Dynamics, SQL, Tables, X++

 

Tags: , , ,

Playing with data

In this post, I am going to discuss about how the data can be retrieved, created, updated or deleted using data access and manipulating functionality provided by Dynamics AX framework. In DAX, developer doesn’t need to worry about opening the database connection, make transaction or fetching data, and close the connection. Rather doing these steps DAX framework requires only the table buffer variable to make transaction or manipulating data. What is table buffer? Hmmm, a table buffer is a variable of any Table which stores complete records, for example you can access any field of the table using that variable. Let’s take a simple example. In the Table node of the AOT, we have a table called “CustTable”, now we can declare a table buffer as,

 
 

CustTable custTable; ///table buffer variable of table type “CustTable”

 
 

A table buffer must be declared before using the select statement to retrieve data. The select statement is based on the SQL standard and quite similar to SQL server query statement except the fact that joining is little different for example there is no “ON” keyword in X++. The developer having experience in LINQ won’t have a problem in understanding the working of data access implementation in DAX. See the below simple job and its result.

 
 

static void Select_Statement(Args _args){

CustTable custTable;

select firstOnly custTable;

print custTable.AccountNum + ” ” + custTable.CustGroup;

pause;

}

And the result after running this job is, 
 


 

Now lets analyze the job, firstly a table buffer of table CustTable is declared, and in the 2nd line a select statement is written with the keyword “firstonly” which retrieves only one record based on the primary index defined on the Table. The “firstonly” keyword called the find option in DAX. The other find option keywords are “forUpdate”, “noFetch”, “firstFast”, and “reverse”. You can also define multiple find option keyword within a single select statement. A field list can also be provided with the select statement with the keyword “from”, for example,  
 

select firstoOnly AccountNum, CustGroup from custTable;  
 

Note: its now the BP error if not define the field list in the select statement in the AX 6.0 version.

Can we fetch multiple records? Yes, we can fetch multiple records using the “while” keyword with the select statement. The while loop will continue until all the records is fetched based on the condition in the where clause of the select statement. If now condition is defined, the loop will execute till the last record. 
 

See and observe the output of the following job. 
 

static void Select_Statement(Args _args){CustTable custTable;

while select AccountNum, custGroup from custTable

where custTable.CustGroup == “40”

{

print custTable.AccountNum + ” ” + custTable.CustGroup;

}

pause;

}

 
 

Aggregate functions like sum, count, avg etc. can be used in the select statement like,  
 

select count(AccountNum) from custTablegroup by custTable.CustGroupwhere custTable.CustGroup == “40”;

print custTAble.AccountNum;

 

Lets talk about “joins” in DAX, how we can join multiple tables in the select statement. See the below simple example of join, which joins custTable with custGroup table. 
 

static void Select_Join_Statement(Args _args){CustTable custTable;

CustGroup custGroup; 
 

while select * from custTable

join * from custGroup

where custTable.CustGroup == custGroup.CustGroup

&& custGroup.Name == “Wholesalers”

{

print custTable.AccountNum + ” ” + custTable.Currency + ” ” + custGroup.PaymTermId;

}

pause;

}

Instead of “On” keyword “where” keyword is used to join the same field of the tables that needs to be joined. The Join can be of type “Inner”, “outer”, “exists”, and “not exists”. There is no inner keyword so if the keyword join is written without the other keyword is referred to as inner join. See the below explanation of join types,

Join Type Explanation
join Fetches the records that matches on both the table. It is the inner join.
outer join Fetches the records whether or not records matches on both the table.
exists join Fetches record from the table define before the exists keyword that matches with the records in the 2nd table. No records will be fetched from thesecondary table using exists join.
notexists join Opposite of exists join. Will fetch records from the primary table, where norecords in the secondary table match the join expression.

 

Try and observe the join type result by creating a job. 

Now lets talk about insert, update, and delete records from the table. These will also be done by using the table buffer. In the table buffer, we have methods, of insert, update , and delete. To insert a record into table, declare table buffer, set values of the fields and call insert method of that table buffer. For example, 

static void Insert_Job(Args _args){

CustTable custTable;

custTable.AccountNum = “4444”;

custTable.Currency = “USD”;

custTable.insert();

}

Similarly, to update the record, update method will be called, but first records need to be fetched using the select statement with the keyword “forupdate”. 
 

static void Update_Job(Args _args){

CustTable custTable;

select forupdate * from custTable where custTable.AccountNum == “4444”; 

custTable.Currency = “EUR”;

custTable.update();

}

You can also update the record using the statement “update_recordset

update_recordset custTablesetting currency = “EUR”where custTable.AccountNum ==”4444″;

 

Now to delete the record, you can delete the record in two ways, either fetch the record using the forupdate keyoword and call the delete method of table buffer or using the delete_from statement.

static void Delete_Job(Args _args){

CustTable custTable;

select
forupdate * from custTable where custTable.AccountNum == “4444”;

custTable.delete();

}

Or using delete_from like,

delete_from custTablewhere custTable.AccountNum ==”4444″;

Inserting, updating, and deleting records are placed under the ttsbegin; and ttscommit; block. These keywords are usually placed to make sure that the transactions to database have been done successfully. If any failure occurs during the transaction then the state will be roll back to position before the ttsbegin keyword. ttsabort; keyword is also used when any exception occurs and transaction needs to be aborted.

 Thanks for reading.

 
2 Comments

Posted by on April 16, 2010 in Basics

 

Tags: , , , ,