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.