Apex Code Development You need to sign in to do that
Don't have an account?
dhetzer Explicitly close client-side cursor?
Help!
I can't seem to get the QueryMore command to work if i have nested queries happening within a loop.
I receive an error message of INVALID_QUERY_LOCATOR when i call the QueryResult.QueryMore command. I suspect it's because i am opening multiple client-side cursors, and the outermost QueryLocator is being closed.
Question is, how can i explicitly close such a cursor? I've tried "MyQueryResult = Nothing", but it didn't seem to make a difference.
thanks in advance
david hetzer
InScope Corporation (formerly HR Technologies)
Hi David,
Hmm.... I'll have to check the rules on multiple server side cursors. I'm sure the behavior you are experiencing is true. The info that I get from our developers is that you can have 3 server cursors open at any one time. A query that does not return more than your current batch size does not use up a cursor. Also, a cursor will time out after 15 minutes (use it up within 15 minutes or loose it). You cannot "close" a cursor. Cursor use is fifo. If you need to nest your processing, you should make use of the retrieve call as much as possible for obtaining up related records.
Hopefully, this provides enough info for you to find a way to deal. Addendum to the documentation will include this info.
Cheers
Hi Dave,
I�ve also be having the INVALID_QUERY_LOCATOR problem.
I have a nested loop like so , for clarity (I hope) I�ve put it in pseudo code:
getAllAccounts(); //SF Query ALL Accounts
while (not getAllAccounts.done)
{
foreach (Account in AllAccounts)
{
GetInDateContracts(); //SF Query In Date Contracts for Account
If (InDateContracts = 0) //i.e. Account does NOT have any valid contracts
{
getAllContactsforAccount() //SF Query Contacts for the Account
while (not getAllContactsforAccount.done)
{
foreach (Contact in AllContactsforAccount)
{
...Write Contact Info to Dataset....
}
If (not getAllContactsforAccount.done)
{
getAllContactsforAccount.queryMore
}
}
}
}
If (not getAllAccounts.done)
{
getAllAccounts.queryMore
}
}
I think this gives the general idea of how it's constructed but without the detail.
Anyway, if I run this with the batchsize set to 10, I get the INVALID_QUERY_LOCATOR error on the getAllAccounts.queryMore after 150 accounts have been processes i.e. 15 iterations of the outer loop.
If I set the batchsize to 100, I get the error after 1 iteration i.e. 100 records.
(I don't seem to ever get the error on the getAllContactsforAccount.queryMore)
However if I set the batchsize to 1, it processes all the accounts (nearly 1500) without any error.
The first two options fail before 15 minutes have elapsed, the final working option takes much longer than 15 minutes to process completely.
When you say "use it up within 15 minutes or loose it" do you mean "access" the cursor every 15 minutes or lose it, or do you mean use all the cursors contents within 15 minutes or lose it?
As you can see from the code I don't have the option of using the retrieve call, since I don't have the Id's of any of the objects I need to retrieve.
Any Ideas? Do I somehow open more than 3 cursors?
I'm using c#.NET, VS.NET 2003, and API Version 2.0
Cheers
Nick
Hi Nick,
The inner query will never fail (assuming that there is not another query that is not indicated by the psuedo code). This is because of the fifo nature of the cursor handling. It would seem, if the rules I stated previously hold true, that if you do nest more than 3 queries within the outer loop that you would lose the cursor for the outer loop. That does not appear to be the case in your code.
The 15 minutes is static.
I think to work this out, you should insert some debug code that allows you to determine how many cursors get opened and when (maybe a stack).
I will try to reproduce this behavior (so far I have not been able to), and if I can we can proceed as a bug.