4

I'm writing an API that converts actions performed by a non-technical user into Salesforce.com SOQL 'SELECT', 'UPSERT', and 'DELETE' statements. Is there any resource, library, etc. out there that could validate the syntax of the generated SOQL? I'm the only one at my company with any experience with SOQL, so I'd love to place it into a set of automated tests so that other developers enhancing (or fixing) the SOQL generation algorithm know if it's still functioning properly.

I know one solution here is to just make these integration tests. However, I'd rather avoid that for three reasons:

  1. I'd need to maintain another Salesforce.com account just for tests so we don't go over our API request cap.
  2. We'll end up chasing false positives whenever there are connectivity issues with Salesforce.com.
  3. Those other developers without experience will potentially need to figure out how to clean up the test Salesforce.com instance after DML operation test failures (which really means I'll need to clean up the instance whenever this occurs).
Technetium
  • 5,902
  • 2
  • 43
  • 54

4 Answers4

2

I found another way to do this.

Salesforce.com posted their SOQL notation in Backus-Noir Form (BNF) here: http://www.salesforce.com/us/developer/docs/api90/Content/sforce_api_calls_soql_bnf_notation.htm

This means you can use a BNF-aware language recognition tool to parse the SOQL. One of the most common tools, ANTLR, does this and is free. Following the ANTLR example, pass the SOQL grammar into its grammar compiler to get a Lexer and a Parser in your desired language (C#, Java, Python, etc.). Then you can pass the actual SOQL statements you want to validate into the Lexer, and then your Lexer tokens into your Parser, to break apart the SOQL statements. If your Lexer or Parser fails, you have invalid SOQL.

Technetium
  • 5,902
  • 2
  • 43
  • 54
2

You might solve your problem by using the SoqlBuilder library. It generates SOQL for you and is capable of producing SOQL statements that would be quite error prone to create manually. The syntax is straight forward and I've used it extensively with very few issues.

Samuel Liew
  • 76,741
  • 107
  • 159
  • 260
barelyknown
  • 5,510
  • 3
  • 34
  • 46
  • Unfortunately I'm not using Apex (*shakes first at C#*) so I can't use this solution, but I think this is the right answer to my question. Rather than use a tool that validates SOQL after the fact, this library will only let you build valid SOQL. While you can't use new SOQL procedures until the library has been updated to be aware of them, that limitation would exist in a strict offline validator as well. – Technetium Feb 16 '12 at 17:54
1

I can't think of a way to do this from outside of Salesforce (and even in Apex I've only got one idea right now that may not work), but I can think of two suggestions that may be of help:

  1. Validate queries by running them, but do them in batches using a custom web service. i.e. write a web service in Apex that can accept up to 100 query strings at once, have it run them and return the results. This would drastically reduce the number of API calls but of course it won't work if you're expecting a trial-and-error type setup in the UI.

  2. Use the metadata API to pull down information on all objects and their fields, and use those to validate that at least the fields in the query are correct. Validating other query syntax should be relatively straight forward, though conditionals may get a little tricky.

Matt Lacey
  • 8,227
  • 35
  • 58
  • Either way, we're talking about integration tests here unfortunately. Your first suggestion definitely solves my first concern. As for the second suggestion, I'm less concerned about column name validity since the columns will change from account to account -- it's the syntax that matters. – Technetium Jan 30 '12 at 18:51
  • Yeah I think the only way forward will be to write your own parser and validator (or see if somebody else has done that). – Matt Lacey Jan 30 '12 at 23:42
0

You can make use of the salesforce develop nuget packages that leverages SOAP API

Nicole Phillips
  • 753
  • 1
  • 18
  • 41