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
DowithforceDowithforce 

Passing arrgument to SOQL loop

Hello All,

 

I have to create a dynamic query in apex class method on the basis of its arguments,

 

For example (in short):

 

WebService static String gottheResult (String query)
{

for (Account a : [+query+] )
{
str+= a.id+'||'+a.name+'~||~';
}

 

  }

 

I am getting error as follows:

 

Compile Error: expecting right square bracket, found '+' at line

 

Here is the reference code:

 

String s = 'Acme';
for (Account a : [select id, name from account
                  where name like :(s+'%')]) {
    // Your code
}

 

 in above code s is passed , I think it is possible after where and : combination, please clear on this.

 

 

Can I used argument directly as above, or something else I need to do?

 

Thanks in advance.

 

 

 

 

JimRaeJimRae

Construct the querystring and execute the query first, then pass it into the for loop, like this:

 

List<Account> accountlist = new List<Account>();
String s = 'Acme';
//use the \ to escape the single quotes as needed to make your like syntax
String qry = 'select id, name from account where name like\'%'+s+'%\'';
system.debug('\n\n************************************');
system.debug('\n\n'+s);
system.debug('\n\n'+qry);

accountlist = Database.query(qry);
for (Account a : accountlist) {
    // Your code
}




 
DowithforceDowithforce

Hello JimRae,

 

Thanks a lot for reply.., :-)

 

my basic concern to use SOQL loop is the performace of query, my query

 

will return 500,000 record, is it feasible to use a list as below? I think list can't handled more than 1000

 

records .

 

Can I use SOQL loop in S-control with function returning string, but there is also limitations for string too.

Is there any tool provided by salesforce to measure a query performance?

 

 

 

 

List<Account> accountlist = new List<Account>();
String s = 'Acme';
//use the \ to escape the single quotes as needed to make your like syntax
String qry = 'select id, name from account where name like\'%'+s+'%\'';
system.debug('\n\n************************************');
system.debug('\n\n'+s);
system.debug('\n\n'+qry);

accountlist = Database.query(qry);
for (Account a : accountlist) {
    // Your code
}

 

 Thanks, please do reply!

Message Edited by Dowithforce on 08-19-2009 10:40 PM
DowithforceDowithforce
System log will help !!
Message Edited by Dowithforce on 08-20-2009 12:52 AM
DowithforceDowithforce

Hello all,

 

Following statement having limitation to fetch records.

 

accountlist = Database.query(qry);

 
JimRaeJimRae

Yes, if you are going to return that many records, you could hit other governor limits.  I think the max queryrows you can return is 10,000.

 

You could try modifying your code like this:

 

 

List<Account> accountlist = new List<Account>();
String s = 'Acme';
//use the \ to escape the single quotes as needed to make your like syntax
String qry = 'select id, name from account where name like\'%'+s+'%\'';
system.debug('\n\n************************************');
system.debug('\n\n'+s);
system.debug('\n\n'+qry);

for (Account a : Database.query(qry)) {
    // Your code
}
 
 This should  get you past the list size limit.  I am not aware of any tools specifically for measuring query performance, in general, a "like" query is going to be slower than any explicit query would be.
You could try adding some additonal where clause elements to restrict the dataset size, if you are able.  Things like RecordType (if you are using that) can greatly improve performance, if it reduces the possible max dataset size. 
 


DowithforceDowithforce
Hello JimRae,
 
Yes I tried this and really usefull. But if I have to return Account a values to my scontrol, how do I (something like array)? 
 
Thanks for your gr8 gr8 help! Please do reply!
 
 


JimRaeJimRae
Sorry I can't help with that one, haven't done much with scontrols, since they have been superceded byVF pages and apex, and no new scontrols will be created after January 2010.
If I had to guess, you would need to create a method that returned a list of accounts, as a result of your query and manipulation.  That method would be called against a javascript array, but I am not sure how to get the data elements out from there.
 
With VF pages, it is much more straightforward, but of course this depends on your specific need. 
 


DowithforceDowithforce
Hello JimRae,
 
list having a limitiation of records so not possible with that! 
 
It is ok JimRae ! , now I think I will manage the stuff. 
 
your help is really appreciated.
 
Thanks! 


DowithforceDowithforce
Hello JimRae,
 
 
I inserted leads about 10001, currently I am not able to insert more lead and getting error
 
storgae limit exceed. May reached to developer edition limit 20MB. 
 
 
Q1. How do I increase my limit? 
 
One more...
 
I am fetching records using SOQL LOOP and Database.query(query) combinatio as you suggested, but I
am getting error as Too many rows 10001. 
 
Q2. How I can all my query genrated records whatever it may be in nos.?
 
 
Thanks in advance! Please do reply. Its turn to more exciting and challenging now.
 
 
 
Thanks, 
 
 
 
 
 
 
 
 
 


SuperfellSuperfell
Call the regular web services API directly from your s-control, rather than having your s-control call apex to do the work. 


JimRaeJimRae
I think you have hit the limit (10000), as I mentioned before.
You will need to rearchitect your solution to handle the volume differently. This is a global limit, since SFDC is a multitenent  application all users on the system have these restrictions to prevent one user from using an unusually large amount of system resources.  You cannot increase your limit.
 
I would recommend you do some searching on these discussion boards for "large volume" to see if you can get some insights on how to accomplish your goals.  Otherwise, my only other suggestion would be to speak with support about it, and see if they have any suggestions. 
 


DowithforceDowithforce
Hello SimonF,
 
Thanks for reply, can you please tell me with example how I can do that?
 
Thanks in advance!
 
 


DowithforceDowithforce
Hello JimRae!
 
 
Thanks for suggestion, yes I am searching for solution.
 
 
Thanks 


SuperfellSuperfell
http://wiki.developerforce.com/index.php/Web_Services_API


DowithforceDowithforce

Hello Simon,
 
Thanks for reply, I have tried this too. For 11100 records it takes 3 minutes time approximatly which is more 
 
what I am expecting. Currently I am fetching in my PHP application using webservices API and need to get back
 
 records in my s-control. 
 
I want to fetch more records with webservice API but then it will be time consuming as per my current 
 
observation. 
 
But if webservice API is the solution for large data fetching then how salesforce application will be more 
 
powerful at its platform, there should be something in salesforce environment to handle the situation (for bulk  data).
 
Can you please explain me? Please do reply.
 
 
Thanks