You need to sign in to do that
Don't have an account?
DateTime formatting with SOQL in batch apex
I have a database.batchable class I've written, and can't seem to get the start() to execute correctly. I suspect it has something to do with the query itself.
I have a custom setting that is keeping track of the last time a given scheduled apex job has run. It's a simple datetime field. I want to use that in a query like this:
SELECT ID, SomeField FROM SomeObject WHERE SomeField IN ('This, That') AND LastModifiedDate > 2010-12-29T21:17:00
Even though there's lots of documentation that seems to indicate this is the proper formatting, I'm getting "no viable alternative at character ' '" errors. In Eclipse, it rejects the colons, so I'm assuming this is the problem.
So I have two questions.
Question 1: What is the correct way to format dates for querying? Does it always require an assignment to the variable, so the query is rewritten as follows:
SELECT ID, SomeField FROM SomeObject WHERE SomeField IN ('This, That') AND LastModifiedDate> :someDateVariable
Question 2: If I do have to use a variable assignment within the query, and I make that variable part of the class and then assign the value in the calling routine, will the batch recognize that variable when executing the start()?
Scott,
You could declare the substituted variable in the class and assign it from calling routine. Else, you could declare a string variable and construct the query from the calling routine.
class batchapex {
public string query;
start(){
Database.getQueryLocator(query);
}
}
calling routine:
batchapex mybatch = new batchapex();
mybatch.query = 'select field from object where date =' + mydate;
database.executebatch(mybatch);
How do you track the last run of your job using custom setting ? This will help me.
Hey
It's a small syntax issue:
IN ('This, That') // You're missing some inverted commas here i.e.
IN ('This', 'That') // You need those guys in red
Cheers,
Wes
Wes, that's just a typo in my simplified sample code. The issue relates to the date formatting.
You don't need to use a dynamic query, I'm not sure if this is another typo but you've left the 'Z' out too:
Nine, can you be more explicit in discussing the date variable formatting? I believe that's precisely what I'm doing. The actual code looks like this:
FYI, the way I'm tracking the last run is simply by creating a custom setting with nothing more than a date field as its content. I then update the date field at the beginning of my run, so it always remembers the last time it ran. Retrieving that date is the first statement after instantiationl, in the above code.
Wes,I did forget the "Z". I just re-ran with that formatting in place and got the "attempt to deference a null object" error, but at least it ran.
Unfortunately, I don't see where that error is being generated. The total contents of my debug log are:
I have a debug statement as the first line in my execute(), and it never shows up. It appears that the start() is executing correctly (there's no error in the debug log, above), and it shows rows retrieved. How can I determine the source of the error? What's happening between the start() being fired and the first line of the execute() that could generate an error, but write nothing to the debug log?
Getting somewhere then!
I wouldn't assume that it's not hitting the first line of the execute method, the debugger might have some issue or bug. What happens if you comment out EVERYTHING in the execute and finish method? Does it still fail?
If it does try moving the Database.QueryLocator out of the return statement i.e. assign it to a variable, system.debug on that and return the var. I've had issues debugging problems when the issue is with the returned var/method-call.
Wes
OMG, down the rabbit hole...
Thanks to your finding my missing "Z", I've been able to get the formatting correct. The subsequent issue was the "de-reference a null" error. After much searching, I found posts that describe an issue related to the use of ALL ROWS. When I remove ALL ROWS from my query, the whole thing works perfectly.
Unfortunately, I *can't* remove ALL ROWS. I have to look at all records, including any deleted, for the given period. My understanding is that there's no way to do that other than by using ALL ROWS and then filtering down to the value of IsDeleted if you're looking only for the deleted ones.
Any ideas for getting around this? How can I query for all records, including deleted ones, when ALL ROWS seems to be the source of the error?
Wes, I think our posts passed each other. Can you read my last post (#9, I think), and provide some thoughts? I've been able to confirm that the problem is with ALL ROWS in a batch apex environment.
Question though in all of this... why are you using DateTime.format()? The variable substitution mechanism of dynamic apex should automatically format the date/time into the query's expected format. Just use the field normally:
I tried something just like this on my developer org without any issue. Try using normal variable substitution and see if that clears up the null-reference error. Also, check to make sure lastRun is not null and lastRun.lastRun__c is not null (either of those would be bad).
Agreed; that's what I ended up using. Essentially, I was chasing a ghost. I couldn't determine where the actual processing error was and began to question some of the basics of how I was configuring the call itself.
The end result here is a (now) acknowledged bug in SFDC: you cannot query deleted records (i.e., ALL ROWS) in a batch apex environment. I don't know if it's been fixed yet (they just acknowledged it to me about two weeks ago).
That is the wonderful thing about Salesforce errors. Sometimes you just happen to run into one and and assume its your own fault. Sometimes the system just isn't perfect yet. Make sure you keep us posted about the ALL ROWS/Batch Apex glitch. I'll try it out myself and see if I can replicate it.