function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Scott GraboScott Grabo 

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()?

 

 

 

 

MohandaasMohandaas

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.

WesNolte__cWesNolte__c

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

Scott GraboScott Grabo

Wes, that's just a typo in my simplified sample code. The issue relates to the date formatting.

WesNolte__cWesNolte__c

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:

 

SELECT  ID, SomeField
  FROM  SomeObject
  WHERE SomeField IN ('This, That') AND
        LastModifiedDate > 2010-12-29T21:17:00Z
Scott GraboScott Grabo

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:

 

 

// CALLING ROUTINE
// Instantiate the class
AccountTeamBatch ATB = new AccountTeamBatch();

// Capture the last run date
ScheduledApex__c lastRun = [SELECT LastRun__c
                              FROM ScheduledApex__c 
                             WHERE Name = 'ApexRun'];

// Assign the value to a variable in the class
ATB.lastRunDT = lastRun.LastRun__c;

// Create the query string. I've tried it two ways. 

// Approach 1: define a query string using the formatted date. 
// This is what all the documentation says is the right way to 
// query with a datetime string. Unfortunately, it results in 
// the error "no viable alternative at character ' '", pointing
// to the ":" as the culprit.
ATB.query = 'SELECT ID, Account__c, IsDeleted ' + 
            'FROM AccountTeam__c ' +  
            'WHERE Role__c IN ('Exec (MDM)\', \'Infra Exec\', \'CT\') AND ' + 
                  'LastModifiedDate > ' + lastRun.lastRun__c.format('yyyy-MM-dd\'T\'HH:mm:ss') + ' ' +
                  'ALL ROWS';
// query variable ends up looking like this:
// SELECT ID, Account__c, IsDeleted FROM AccountTeam__c WHERE Role__c IN ('Exec (MDM)', 'Infra Exec', 'CT') AND LastModifiedDate > 2010-12-29T21:17:00 ALL ROWS

// Approach 2: same definition, but use variable substitution
// This results in an "Attempt to de-reference a null object" error
ATB.query = 'SELECT ID, Account__c, IsDeleted ' + 
            'FROM AccountTeam__c ' +  
            'WHERE Role__c IN ('Exec (MDM)\', \'Infra Exec\', \'CT\') AND ' + 
                  'LastModifiedDate > :lastRunDT ' + 
                  'ALL ROWS';

// CLASS DEFINITION
global class AccountTeamBatch implements Database.Batchable<sObject>
{
	public datetime lastRunDT;
	public String query;

	global database.querylocator start(Database.BatchableContext BC) 
	{

		// It never seems to hit this debug statement. At least
		// it never shows up in a log, and the query appears not
		// to run.
		system.debug('start: ' + query);
    
		return Database.getQueryLocator(query);
	}
	
	global void execute(Database.BatchableContext BC, List<sObject> scope) 
	{

		// Do some stuff; never gets here as best I can tell
	}

	global void finish(Database.BatchableContext BC) 
	{
	}
}

 

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.

 

Scott GraboScott Grabo

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:

 

 

19.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
07:40:40.936|EXECUTION_STARTED
07:40:40.936|CODE_UNIT_STARTED|[EXTERNAL]|01pQ00000009Hl9|AccountTeamBatch
07:40:40.939|METHOD_ENTRY|[25]|System.debug(ANY)
07:40:40.939|USER_DEBUG|[25]|DEBUG|start: SELECT	ID, Account__c, IsDeleted FROM	AccountTeam__c WHERE	Role__c IN ('Exec (MDM)', 'Infra Exec', 'CT') AND LastModifiedDate > 2010-12-29T21:17:00Z ALL ROWS
07:40:40.939|METHOD_EXIT|[25]|System.debug(ANY)
07:40:40.939|METHOD_ENTRY|[26]|Database.getQueryLocator(String)
07:40:40.940|SOQL_EXECUTE_BEGIN|[26]|Aggregations:0|SELECT	ID, Account__c, IsDeleted FROM	AccountTeam__c 
07:40:40.992|SOQL_EXECUTE_END|[26]|Rows:3
07:40:40.992|METHOD_EXIT|[26]|Database.getQueryLocator(String)

 

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?

 

 

WesNolte__cWesNolte__c

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

Scott GraboScott Grabo

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?

Scott GraboScott Grabo

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.

sfdcfoxsfdcfox

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:

 

 

'SELECT ID, Account__c, IsDeleted ' + 
            'FROM AccountTeam__c ' +  
            'WHERE Role__c IN ('Exec (MDM)\', \'Infra Exec\', \'CT\') AND ' + 
                  'LastModifiedDate > :lastRun.lastRun__c' + ' ' +
                  'ALL ROWS';

 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).

 

Scott GraboScott Grabo

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).

sfdcfoxsfdcfox

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.