function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
fourfourfunfourfourfun 

database.query - how to execute?

Trying to do a simple swap to replace an SOQL statement in my Apex with something that is generated from a string.

Here is what I'm looking at:

String AccountSOQLString = 'Select ownerid from account where ownerid=:search.ownerid'
//		List<Account> ownerAccs = Database.query(AccountSOQLString);

The above does not work.

		List<Account> ownerAccs = [Select ownerid from account where ownerid=:search.ownerid];

This does work.

The aim is to eventually have the string built up with a series of IF statements (which in itself is another sticking point it seems).

I'll start with the current sticking point though, any help?


kiranmutturukiranmutturu
dynamic SOQL can’t use bind variable fields in the query string

String f1 = search.ownerid;
List<Account> ownerAccs = Database.query('Select ownerid from account where ownerid=' + f1);
fourfourfunfourfourfun
Doesn't appear to be working, page pops this error:

Only variable references are allowed in dynamic SOQL/SOSL
kiranmutturukiranmutturu
try this ..earlier one should also work

List<Account> ownerAccs = Database.query('Select ownerid from account where ownerid = :f1');

also see the debug of the constructed soql before it got queried...
praveen murugesanpraveen murugesan
Hi,

Can you pls post your full code.?

then only I will know where you getting search.ownerid etc

Thanks.
fourfourfunfourfourfun
Ok, here is the code:

public PageReference searchCon()
    {
		
		String AccountSOQLString = search.ownerid;
		List<Account> ownerAccs = Database.query('Select ownerid from account where ownerid = :'+AccountSOQLString);
//		List<Account> ownerAccs = [Select ownerid from account where ownerid=:search.ownerid];
		// Prepare a list to add IDs to
		List<Id> AccIds = new List<Id>();
		// Populating IDs to list
		for(Account acc : ownerAccs)
		{
			AccIds.add(acc.ownerId);
		}
		
		// Query list of already added attendees to remove from search
		List<PCM_Activity_Attendee__c> AddedAttendees = [select contact__c from PCM_Activity_Attendee__c where PCM_Activity__c=:stdCtrl.getId()];
		// Prepare a list to add IDs to
		List<Id> ConIDs = new List<Id>();
		// Populating IDs to list
		for(PCM_Activity_Attendee__c accatt : AddedAttendees)
		{
			ConIds.add(accatt.contact__c);
		}

 		   allcontacts=[select id, firstname, lastname, Contact.PCM_Contact_Type__c , account.name, account.ownerid, account.shippingpostalcode, account.target_account__c 
                           from contact 
                           where
                           Id not in :ConIDs
                           and lastname like :'%' + search.lastname + '%'
                           and account.name like :'%' + search.department + '%'           
                           and account.shippingpostalcode like :'%' + search.MailingPostalCode + '%'
                           and account.ownerid in :AccIds
                           and Account.RecordTypeId = '01230000000Y77V'
                           order by account.name asc, lastname
                           limit 50];
        return null;
    }

The owner ID is from here:

public pcmTimeControllerExtension(ApexPages.StandardController std) {
      stdCtrl=std;          
      pcmAct = (PCM_Time_Record__c)stdCtrl.getRecord();
      search = new contact(ownerid = UserInfo.getUserId());
      setupAttendees();
      setupAllCon();
    }

It's fine until I try to use dynamic SOQL.

Dynamic debug looks like this:

Select ownerid from account where ownerid=:005a0000007i3yCAAQ

Static looks like this:

select ownerid from account where ownerid = :tmpVar1
Yohann ChevreuxYohann Chevreux
Hi,

I think you should remove ':' from your string query.
List<Account> ownerAccs = Database.query('Select ownerid from account where ownerid = '+AccountSOQLString);
Enrico VarrialeEnrico Varriale
Ciao!
I got same error message , the problem are missing  ' '  for the id
Just do like this:
String query = 'SELECT Name FROM '+ObjectType+' WHERE ID =\'' + RecordID + '\' ';

Enjoyy!!