while select picking up new records causing inf loop?

(0) Share

Report

Posted on by Community Member

Ive never quite seen this behavior before. First of all, this used to work fine and it suddenly stopped working. I have no idea how or why or what changed if anything.

We have two instances of a table, lets call them t1 and t2. We do a while select on t1 with a simple criterion, then inside the while select we set t2 = t1.data then insert t2. To my astonishment, on the next loop of the t1 while select, t1's recid == t2's old recid! So t2 again gets set to t1.data, then gets inserted, then t1 picks up the new record, and so on. Here is the code:

    ProdParmStartUp p1       = this.defaultParmBuffer();
    ProdParmStartUp p2;
    ProdQty         startUpQtyRemain;
    #OCCRetryCount


    if (this.parmId())
    {

        while select forUpdate p1
            order by Linenum
            where p1.ParmId == this.parmId()
            && p1.SplitQty > 0
        { 
                ttsBegin;

                //Set remaining quantity for each ProdParmStartup
                startUpQtyRemain    = p1.StartUpQty;

                //Update intial ProdParmStartup
                p1.StartUpQty = p1.SplitQty; 
                p1.update();
                startUpQtyRemain -= p1.SplitQty;

                //Create ProdParmStartup for remaining
                while(startUpQtyRemain >= p1.SplitQty) 
                {
                    p2.clear();
                    p2.data(p1);
                    p2.StartUpQty = p1.SplitQty;
                    p2.insert();

                    startUpQtyRemain -= p1.SplitQty;
                }

                //Create ProdParmStartup for the rest
                if(startUpQtyRemain < p1.SplitQty)
                {
                    p2.clear();
                    p2.data(p1);
                    p2.StartUpQty = startUpQtyRemain;
                    p2.insert();
                }

                ttsCommit;
        }

    }

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

This is considered to be a bad design, dont loop on a table that you are inserting record into as well.

You could work around by first looping through p1, insert it to a set storing the RecIds you want to update.

Then enumerate the set of RecIds on p1, and you could update p1 / insert p2 as required and should not loop. Instead of using data() you could just do buf2buf() to copy the values but not any record context.

Also please update the tags for your post to reflect the correct AX version if required, since current stands for AX 7.

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Steven

Use Buf2Buf global method instead of .Data(..)

The .Data(...) copy also system fields like recid, etc.

I don't know if this is the trick...

Give a try..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the feedback. We can change the approach fundamentally, but shouldnt then we still be worried about records being inserted from other processes? It was my impression that when you initiate a while select, you are building a cached dataset that is being iterated through, not that the query is being re-executed on each pass of the loop!

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

It messes with the SQL cursor and the AX AOS cache, I am afraid your impression is wrong.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I just tried switching over to buf2buf and that did not resolve the issue

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

So i just created a table and wrote the following job to try to recreate the scenario outside of my specific problem instance:

static void Job3(Args _args)

{

Table1 t1;

Table1 t2;

ttsBegin;

t1.insert();

ttsCommit;

while select t1 where t1.RecId

{

info(strFmt("%1", t1.RecId));

t2.data(t1);

ttsBegin;

t2.insert();

ttsCommit;

t2.clear();

}

}

but, unfortunately, this code behaves exactly as i would expect: at no point does the t1 loop find any instances of records inserted on t2. So presumably it must have something to do with the table configuration? here is the table config, i thought it might be the caching strategy so i tried a few other combinations but nothing fixed this problem.

Exportfile for AOT version 1.0 or later

Formatversion: 1

***Element: DBT

; Microsoft Dynamics AX Table : ProdParmStartUp unloaded

; --------------------------------------------------------------------------------

TABLEVERSION 1

TABLE #ProdParmStartUp

EnforceFKRelation 0

PROPERTIES

Name #ProdParmStartUp

Label #@SYS12531

TitleField1 #ProdId

TitleField2 #PostDate

ConfigurationKey #Prod

SecurityKey #ProdTables

CacheLookup #NotInTTS

CreateRecIdIndex #Yes

TableGroup #Worksheet

PrimaryIndex #NumProdNumIdx

ClusterIndex #NumProdNumIdx

DeveloperDocumentation #@SYS124169

Origin #{2C0F00FC-0000-1000-115C-6F4D05225096}

LegacyId #252

ENDPROPERTIES

ENDTABLE

***Element: END

Was this reply helpful? Yes No
Suggested answer

XB 1,875 on at

Like (0)

Report

I'm not sure to understand your problem but first set a code that works fine.

static void Job3(Args _args)

{

Table1 t1;

Table1 t2;

/* ttsBegin;

t1.insert(); <--What are you trying to insert? nothing!

ttsCommit;*/

while select t1

// where t1.RecId <--if you want all records you don't need this because all records have recId

{

info(strFmt("%1", t1.RecId));

ttsBegin;

t2.clear();

t2.data(t1);

t2.recId = 0;//It's better options buf2buf function

t2.insert();

ttsCommit;

}

}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Javier, yes exactly i was just trying to recreate a simple version of the scenario referenced above. In the job i wrote the table doesnt have any interesting data, so i just insert it. I then do the data copy just to demonstrate that the recid getting copied to the new instance of the table is not the problem. When you copy the data and then insert, a new recid gets assigned and overwritten on the old table object. I included a simple blank criterion just to demonstrate that it isnt the where clause that is breaking my code above.

Was this reply helpful? Yes No