1

I've read thread from 2005 and people said SOQL does not support string concatenation.

Though wondering if it is supported and someone has done this.

I'm trying to concat but no luck :(

Below is APEX code trying to find record with specified email.

String myEmail = 'my@email.com';
String foo = 'SELECT emailTo__c, source__c FROM EmailLog__c 
              WHERE source__c = \'' +
              myEmail + '\';

Database.query(foo)

Even though the record is indeed in the database, it does not query anything. Debug shows "row(0)" which means empty is returned.

Am I doing concat wrong way?

UPDATE

I just found a way not have to add single quote. Just needed to apply same colon variable even for String that has query.

String foo = DateTime.newInstance(......);

String bar = 'SELECT id FROM SomeObject__c WHERE createdOn__c = :foo';

List<SomeObject__c> result = Database.query(bar);

System.debug(result);

This works too and is necessary if WHERE clause contains DateTime since DateTime cannot be surrounded with single quotes.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Meow
  • 18,371
  • 52
  • 136
  • 180

1 Answers1

4

Why do you use Database.query()? Stuff will be much simpler and faster if you'll use normal queries in brackets

[SELECT emailTo__c, source__c FROM EmailLog__c WHERE source__c = :myEmail]

Not to mention that parameter binding instead of string concatenation means no need to worry about SQL injections etc.. Please consider getting used to these queries in brackets, they look weird in beginnign but will save your butt many times (mistyped field names etc).

As for actual concatenation - it works like you described it, I'm just unsure about the need to escape apostrophes. Binding the variables is safest way to go.

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_soql.htm http://www.salesforce.com/us/developer/docs/api/index_Left.htm#CSHID=sforce_api_calls_soql.htm|StartTopic=Content%2Fsforce_api_calls_soql.htm|SkinName=webhelp

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • One reason is because I have 3 fields and if a user specifies any, those fields (i.e table column) are used to construct query. For instance if the user input 'firstname' and 'lastname' field the query would look like SELECT all FROM fooTable WHERE firstname = $userinput AND lastname = $userinput; – Meow Nov 16 '10 at 04:29
  • In such case, I can only concat 'firstname = $userinput AND' etc any alternative??? – Meow Nov 16 '10 at 04:30
  • 1
    Sure you can bind multiple variables `[SELECT Id FROM EmailLog__c WHERE column1 = :input1 AND colum2 = input2]`... But the fancier logic you'll put, the less readable query will be... So yeah, in your case dynamic query looked like good choice. But still I have another option for you: full-text search. Search documentation for SOSL. http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/langCon_apex_SOQL.htm?SearchType=Stem and my answer http://stackoverflow.com/questions/3122038/how/3139653#3139653 (part about controller). – eyescream Nov 16 '10 at 06:39