Multi-line Value Handling with the SQLForce Jython Module

An engineering student, Caleb Smith -- MST (the old Univ of MO, Rolla) found an interesting problem this week where the SQLForce jython module failed. He was searching for errors in the Salesforce Contact table where the complete address was entered in MailingStreet (rather than using the MailingCity, MailingState, etc. fields).

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[1] ):
        reportTheError( row[0], row[1] )

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[1] as multi-line strings.

Underneath the Hood

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.