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:
Share
ReportWhile(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
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!
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.
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!
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.
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.
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).
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.
I understand this, but what would be the purpose of the original record id? You have not mentioned that part in your latest answer.
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 :)
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?
#1
André Arnaud de Cal...
291,642
Super User 2024 Season 2
#2
Martin Dráb
230,371
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
