CODEWITHSUNDEEP
axaptax++
theBrilliance
theBrilliance

Reputation: 61

Building and using QueryRun in x++

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

Upvotes: 2

Views: 7571

Answers (3)

theBrilliance
theBrilliance

Reputation: 61

Actually, this can be done. I figured it out today. The problem is that queryRun.next() makes you think that is the only way to iterate... Actually if you do this:

Common c;
if(queryRun.next())
{
   c = queryRun.get(tableId);
}

while(c)
{
   ...;
   next c;
}

you can see that queryRun.get() does get the entire set of records. So as long as you use .next() once, the get method will actually get every record returned from the query in a buffer.

Hope this is helpful to you guys!

Upvotes: 2

Geoffrey DELMEE
Geoffrey DELMEE

Reputation: 782

As 10p said, it can't be done with queryRun or anything else in AX except direct SQL. But even with direct SQL, you will have to iterate a resultset which is no table buffer. It's heavy to handle and considered as security issue.

Using table buffer, you will never work with a resultset. It's really important because you have object-relational mapping with AX tables. So you can manage 1 record at a time in it.

If you want to manipulate a whole set of data, you have to go to bulk operations (insert_recorset, update_recordset, delete_from) but you will never have the whole data in AX. It will drive the SQL command at SQL engine level.

Upvotes: 1

10p
10p

Reputation: 6706

The buffer cannot have more than 1 record at a time.

What you can do is use insert_recordset to copy data from one or more tables directly into one resulting destination table on a single server trip.

Example:

InventTable inventTable;
MyInventTable myInventTable;
;

insert_recordset myInventTable (ItemId, Alias)
    select ItemId, NameAlias from inventTable
        where inventTable.ItemType == ItemType::Service;

It cannot be done with QueryRun.

Upvotes: 0

Related Questions