- Set the "QueryOptions" parameter in the binding header.
It looks like CopyForceOracle will be ready to post on Monday. It is passing of the standard CopyForce unit tests and copied my large test database with flying colors.
UPDATE PriceBookEntry SET useStandardPrice=false WHERE useStandardPrice=true AND pricebook2.isStandard=false
The tool, CopyForce, is based on the same libraries we use everyday (via jython) to perform routine DBA work on Salesforce (when Apex is just too hard). The base project, SQlForce, can be found at SQLForce
CopyForce is fairly simple to use:
My motivation in creating CopyForce was a desire for an open-source was to create a snapshot of salesforce for backup purposes.
If it sounds interesting drop me a line.
Fortunately, the jython SQLForce module made this problem easy to solve. Here is the complete script:
import SQLForce def getAccountId( session, name ): records = session.selectRecords( "SELECT id FROM account where name='" + name + "'") if 1 == len(records): return records.id return None if __name__ == '__main__': session = SQLForce.Session("sandbox") srcAccountId = getAccountId( session, "Biota Scientific Management Pty Ltd") destAccountId = getAccountId( session, "Biota Structural Biology Laboratory") print "Src Account", srcAccountId print "Dest Account", destAccountId for att in session.selectRecords( "SELECT id, body, contentType, isPrivate, name FROM attachment WHERE parentId='" + srcAccountId + "'"): session.insert( "attachment", ["body", "contentType", "isPrivate", "name", "parentId"], [[att.body, att.contentType, att.isPrivate, att.name, destAccountId]]) session.delete( "attachment", [att.id] )
Yeah, I should have cached both the updates and deletes into a python array and made two calls to Salesforce instead of two per attachment. What can I say -- I was lazy.
session = SQLForce.Session("sandbox") for row in session.select( "SELECT id, FirstName, LastName FROM Contact LIMIT 3"): print "First Name: " + row print "Last Name: " + rowThis works OK as long as each row is referenced in close proximity to the session.select() and not many columns are being selected.
When selecting a lot of columns, I found this technique to be error prone...so I introduced a form of select, session.selectRecords() that returns jython classes where the class attributes are set to the name of each column. Example:
session = SQLForce.Session("sandbox") for row in session.selectRecords( "SELECT id, name, account.name FROM Contact LIMIT 3"): print "Name: " + row.name print "Account: " + row.account.nameThe only trick to to use the same column name referenced in the SELECT statement. Naturally, a session.selectRecords2(sql, callback) form is also supplied.
That's it..a small change that makes code more readable.
His code was something like:
import SQLForce session = SQLForce.Session("Sandbox") for row in session.select( "SELECT id, MailingStreet FROM Contact WHERE MailingStreet!=null"): if isStreetInvalid( row ): reportTheError( row, row )
Looks simple enough -- but it failed because the jython code was assuming NO EMBEDDED NEW LINES in any of the returned columns. Multi-line addresses were processed as additional rows.
If run against SQLForce 1.0.3 (or above) Caleb's loop runs like he expected originally. Multi-line addresses are stored in row as multi-line strings.
Underneath the hood, the Jython Session.select() was changed to always replace the SELECT that occurs at the beginning of the SOQL with a SELECTX. SELECTX works just like the SELECT command except that the results are returned as simple XML. Jython parses the XML using SAX to build each row. Simple!
Thanks for the bug Caleb.
Finding the products was easy. I wanted all products where:
Here is the script:
import SQLForce session = SQLForce.Session("sandbox") for product in session.select("SELECT id,name, division__c FROM Product2 ORDER BY name asc"): nOps = session.select("SELECT COUNT() FROM OpportunityLineItem WHERE PriceBookEntry.product2Id ='" + product+ "'") nAssets = session.select("SELECT COUNT() FROM Asset WHERE product2Id='" + product+ "'") pricebooks =  for pb in session.select("SELECT DISTINCT Pricebook2Id FROM PricebookEntry WHERE Product2Id='" + product+ "'"): pricebooks.append(pb) if "0"==nOps and "0"==nAssets: notUsed = [product,product,product, nOps, nAssets, "\t".join(pricebooks) ] print "\t".join(notUsed)
I put the results of the script into an Excel worksheet so marketing can look for mistakes. Later I will modify the script to delete the records I found.
We just started using Salesforce forecasting subsystem and needed to initialize Opportunity.ForecastCategory based on two Opportunity custom fields. Hundreds of records had to be updated -- doing it by hand was not a good option.
The solution...the following short SQL Force script:
import SQLForce session = SQLForce.Session("sandbox") nUpdated = 0 ## ## Case where ForecastCategory should be Best Case ## for record in session.select("""SELECT id, forecastCategory FROM Opportunity WHERE (forecastCategory='Commit' OR forecastCategory='Pipeline') AND isClosed=false AND include_in_estimate__c=false AND include_in_upside__c=true AND license_type__c!=null AND closeDate > LAST_YEAR """): nUpdated += 1 print nUpdated session.runCommands("UPDATE Opportunity SET forecastCategoryName='Best Case' WHERE id='" + record + "'" )
There were a couple of addition loops in the code for setting other Opportunity.ForecastCategory values. Looks useful? See SQLForce Project on code.google.