Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

Building and using QueryRun in X++

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

I have a need to be able to dynamically build a query in X++. I can do so and make it work. My question is:

Is there any way, using the Query classes, to get the entire result set in 1 buffer. I understand the:

While(queryRun.Next())
{
    common = queryRun.get(tableName2Id('InventTable'));
}

but this just does it record by record. I am wanting the entire result table at once. Any ideas? I have been searching for a long time.

Thanks

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Building and using QueryRun in X++

    Thanks for that I actually was just using semi psudocode,  but I didn't know that you could just feed it without quotes, I thought you would have to use identifierstr () for that, good to know.

    I really just wanted to know if it could be done, as I said I am experimenting, I do know it's not favorable but I really was just originally asking if the ability to retrieve a table buffer from a dynamic query with all of the results was possible.  It didn't make sense to me that it was not being that you can do it through many other means, outside of x++.

    Thank you for all of the extra useful info!

  • Suggested answer
    Joris dG Profile Picture
    Joris dG 17,775 on at
    RE: Building and using QueryRun in X++

    I'm not sure what you're doing.

    while (queryRun.next())

    {

       c = queryRun.get(...);

    }

    is EXACTLY the same as

    if(queryrun.next())

    {

       c = queryRun.get(...);

    }

    while(c.RecId != 0)

    {

       next c;

    }

    You seem to indicate that your plan is to return the c variable (buffer), so a calling method can access all the records using next. There's very few scenarios where that would be good practice. Your calling method should either accept the queryRun, or some other collection type that holds the records you've iterated from the queryrun. Use List, Set, Container or RecordSortedList/RecordLinkList to return from this method. There's plenty of reasons beyond good coding style to do this, performance and open cursors on the server being one of them.

    Also, please do not use quotes inside things like TableName2Id, it defeats at least part of the purpose of using that precompiler instruction.

    If you use tableName2Id(InventTable) instead of tableName2Id('InventTable') the compiler will actually (at compile time instead of run time) check if that table actually exists in the AOT, which is of course a best practice.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Building and using QueryRun in X++

    yes I know, but the original question was about getting an entire set of data into a buffer from a dynamic query inside x++.  My intention was to show that the queryRun.get() can infact get all of the data returned from the query class.  As stated in the original question I understood that queryRun.next could do it record by record but no one seemed to tell me/know that you could get the entire set into the buffer.  This way I can pass the entire set between my classes instead of having to do it 1 record at a time.

    Thanks!

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,371 Most Valuable Professional on at
    RE: Building and using QueryRun in X++

    You can use the same approach with any buffer, e.g. you could replace while selects with select + next statements in a loop. (But just because you can doesn't mean you should.)

    A buffer is basically a reference to a cursor in database; it doesn't mean that the whole record set gets loaded to memory.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Building and using QueryRun in X++

    I figured this out AND THERE IS A WAY!!! :)  as long as you do 1 queryRun.next()

       Common c;

       if(queryRun.next())

       {

           c = queryRun.get(tableName);

       }

       while(c)

       {

           ...;

          next c;

       }

    And there you have it.  Query run does return a full set of records, but they hide it through the iteration aspect of the .next() property.

    Thanks for the responses guys.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,371 Most Valuable Professional on at
    RE: Building and using QueryRun in X++

    You already have "a table buffer containing all of those records" - the source table in database. You still haven't explained why would you copy the data to another table. Instead of copying data and passing them around, pass a reference to the query and execute it when needed.

    Nevertheless if you want to copy data anyway and you're using AX 2012 R3, you could benefit from Query::insert_recordset() (explained in Improving SSRS Report Performance using new R3 features - Part 6).

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Building and using QueryRun in X++

    Oh yes I apologize.  The need for these to be in memory tables is not there.  At the time of the original post I figured they may meet my needs but I was just experimenting.  I had never used in memory tables before and thought since they don't affect the original table, I may be able to insert without changing the recIds.

    I need the original recIds to perform business logic on them using ax classes just as you would for a table.find() method passing it a table record/buffer.  I really just need the original table records.  My want is this:

    Building a dynamic query inside of ax using parameters that I feed through a service from an outside program and the creation of a table buffer containing all of those records.  

    It doesn't sound like this is a possibility so I will just have to redesign what I am doing.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,642 Super User 2024 Season 2 on at
    RE: Building and using QueryRun in X++

    I understand this, but what would be the purpose of the original record id? You have not mentioned that part in your latest answer.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Building and using QueryRun in X++

    Yes I am.  I want to be able to be able to create a result set from parameters that I am feeding through a custom ax service.  I am more or less just toying around with some ideas but I want to be able to create a common table buffer with all of the records I need for the table that I specify so I can pass it to other classes.  

    And while I agree that AX does work, and works well, it surprises me that the Query Run class, while it obviously could access the entire data set at once, only allows for record by record traversal.  You learn something new everyday I suppose :)

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,642 Super User 2024 Season 2 on at
    RE: Building and using QueryRun in X++

    Hi,

    [quote user="brilliancenp"]

    Well I am doing this in memory table as a common type because we will be doing this with many tables.[/quote]

    What are you trying to achieve and in which way? Microsoft Dynamics AX has a lot of coding and apparently they managed to create an application which works. Probably you are inventing something new?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans