SQLForce Saves Marketing Many Hours

Marketing had an interesting problem last week.

Marketing: We want to create a price book just like the "2012 Standard Price Book" but only include USD prices. Can we just clone the "2012 Standard Price Book" and have you fix it.

Any guesses about how much work it was to "fix" the cloned price book?  In sqlforce it took a single line:

SQLForce> DELETE FROM pricebookentry WHERE pricebook2.name='Distributor Price Book - 2012' and currencyIsoCode<>'USD';

Yes, using the DELETE statement is dangerous. The wrong WHERE clause (or no WHERE clause) can quickly destroy a lot of data. However, judiciously used, DELETE can save a lot of time.

Salesforce Rich Text Fields -- Problem Solved

I had an interesting problem this morning. An account rep had a small table in MS/Word that he wanted to pasted into a Salesforce rich text field.

Big Problem: Though the table was small all of the style crap added by MS/Word exceed the columns' 32,000 character limit for rich text fields. You think this would be an easy problem to solve?

Here is what I had to do:

First attempt: 
I saved the MS/Word as filtered HTML – too big.

Second attempt: 
I used CKEditor to create new html – too big.

Third attempt: 
I hand crafted the HTML, visualized it in Chrome, and then did a copy/paste from Chrome to SFDC – still too big. The SFDC gadget transformed my short HTML into something bigger than 32K. Odd behavior – SFDC sandbox worked; production SFDC did not. They must be updating the SFDC gadget in Winter’11.

 Final attempt:
 I wrote a one-line sqlforce UPDATE statement and patched the field. Finally, success.

• UPDATE Quote SET SpecialInstructions__c='....' WHERE id='00a88789a7s89af'

I love sqlforce. 

Upgrade to Summer'11

The SQLForce, CopyForce, and JavaForce updates to the Summer'11 protocol (22.0) have been posted to code.google. Other than support for a new SFDC column type and new proxy command line args there is nothing new to report.

I've been having problems getting CopyForce to work consistently thru proxy servers on Linux.
Are there any guru's reading that can throw me some same sforce connection code? On Windows it was easy to make the app use the system proxy and certificate store. I am having no such luck with Linux.

The next upgrade to CopyForce will dynamically alter the RDMBS schema when new columns are added (or existing columns are altered) in force.

Thanks for all of the downloads and feedback. Feedback is the only $$ for this job. I appreciate it a lot.

It's too hot to code. MySQL Anyone?

CopyForce (and CopyStorm -- the commercial variant) does not support MySQL (I have not needed it on a project). This morning I took a look at the effort....the effort looks small. In a couple of hours I was exporting Salesforce data.

A question: Is there enough MySQL interest to make it worth my effort? Please drop me a line if you would be interested in trying it out. If few people respond, I probably will not launch a MySQL version.

Since it is summer and time for outdoor fun, progress on a WAR that provides browser access to CopyForce data has slowed. Though the individual record pages and list of record pages are in place, I will need some concentrated time to bring it to beta state. Maybe this fall.

It's time to take a swim (it's hot outside).

Limits of SOQL -- SQLForce Solution

One aspect of SOQL that is a bit annoying is its restrictions of where column names can appear in a query. A query I have want to run a few times:

SELECT id FROM Account WHERE ShippingCountry != BillingCountry

Bummer....SOQL does not allow column names on the right side of an expression.

Recently a user reported a bug with SQLForce that is caused by the same SOQL limitation. He report that a SQLForce query like:

UPDATE Account SET ShippingCountry = BillingCountry WHERE ShippingCountry=null

set all ShippingCountry values to the string "BillingCountry." Why? Since SOQL does not have a native UPDATE statement, SQLForce simulates it using a SELECT followed by an Update() call. Since SQLForce knows that SOQL does not allow column names on the right, it assumes that the user forgot to quote a string in the UPDATE statement.

How can this problem be approached in a couple of lines of code. The answer for me is with the SQLForce Jython module. I'll post a typical solution tomorrow.

Help: Delete A Lot of Leads

Marketing: Please delete all leads with status of "Archive"

Yes, we could this 200 records a time using the Salesfoce mass delete tool. Yuch!
Or, we could do it in one line of SQLForce.

DELETE FROM Lead WHERE status='Archive';

Two seconds of work, 20 seconds waiting for Salesforce, and the 6000 records are history.

A Viewer for CopyForce?

It's been a good month for CopyForce downloads. Thanks to the people who have sent supportive emails. The next update will be sometime in July since other than a few issues with connection strings and one issue with TOAD (not displaying multi-byte characters) nothing significant has come up.

In my spare time I am working on a Apache/Tomcat war that provides read only access to any database created by CopyForcePro. Out of the box pages can be jsp or XML mark-up. The resulting screens are similar enough to what people are accustom to (ditto the behavior) that I doubt that extra user training will be necessary. Other than cost savings, other benefits include speed (loads faster), and the ability to create layouts using just markup that are not possible with force (example: When viewing a product, show a related list of all accounts that own the product -- just using markup). I am looking for volunteers to give it a try -- the only requirements Apache/Tomcat and a database created with CopyForcePro

It's a huge family activity month for me in May (and June) so my responsiveness may lag a bit (2 graduations, a big anniversary, two birthdays, and a long family trip.

Thanks for your support.

CopyForce Update -- Full UTF-8 Support

Thanks for the high level of activity this month. It was another record for downloads.

This month full UTF-8 support is added for all target databases. In addition, the jars have been tested against Spring '11.

The only type of defect seen this month was with connection strings. JDBC connection strings can be just plain tricky. Drop me a line if you have a problem. I have tested against Oracle 9 thru 10 and SQL Server 2005 and forward.

After a week of lovely weather we dived back into snow this morning. What a bummer. I had hoped to ride my bike this weekend.

Bad CopyForce jars posted....just replaced.

Thanks to Amarnath Venkataswamy I discovered that the last CopyForce downloads I posted to google code were bad. The short reason -- I fat fingered a thread.join() line in the CopyForce.main() method. Bummer! My unit tests call nearly everything at 98% coverage EXCEPT the main().

If you are one of the ~35 people that downloaded it last week, all versions of CopyForce will simply terminate with no error message.

I Love sqlforce

This morning the head of our consulting division decided to change the stage names used by opportunities. Before taking this step, I wanted to take a look at what stage names have actually been used by sales staff.

If I was working with a regular SQL database, I would simply run:

SELECT DISTINCT stageName Opportunity

How can I run the same query on a Force database?  Easy....start sqlforce and enter:

SELECT DISTINCT stageName from Opportunity

Case closed.

Updates to CopyForce

Monthly updates to SqlForce, CopyForce, etc. were posted on http://code.google.com/p/sqlforce/ this morning.
The two issues reported this month are fixed in the updates:

• encrypted fields now work.
• GMT DateTime conversion is now correct.

It has been a healthy month for downloads. CopyForceSQLServer is the most popular download but CopyForceOracle is gaining ground.

We are nearly ready to launch a commercial version of the code -- CopyForcePro.  The primary advantages of the commercial version  will be usability/accessability, support, and secure packaging for production environments. The core codes will stay open source under the EPL.

For operational use, I am finding it much easier to load a single configuration file and immediately restore my setup:

Please drop me a line at  gsmithfarmer@gmail.com. The occasional "it's working well for us" is always good news (as is any defect report).

Copy Salesforce to a SQL/Server Financal System.

We wanted to have a copy of our Salesforce price books available on our financial system but did not want to write custom code to do it.  We did the schema creation and copy in 5 minutes using CopyForceSqlServer (4 minutes development + 1 minute execution time).

How simple can it get!


SET SFDC=production
SET SQLSERVER=//localhost;databaseName=MyCompanyTools;username=salesforce;password=miracle;


java -jar CopyForceSqlServer.jar -salesforce %SFDC% -sqlserver "%SQLSERVER%" -include product2,pricebook2,pricebookentry -schema -querybatch 1000

Later we will set this up as a CRON job to keep our financial system up to date automatically.