JDBC SQL DDL Syntax

Here are the SQL DDL commands we currently support:

CREATE TABLE

We provide a way to create a Salesforce "custom object" via the JDBC driver:

1
2
CREATE TABLE <tableName> ( <columnName> <dataType> [null | not null ]
[ WITH <withClause> ] ,... );

For example:

1
2
3
4
5
6
7
8
9
create table superhero__c (
name string(80),
birthdate__c date,
cape_colour__c picklist('red', 'blue' default, 'green') sorted,
identity__c masterrecord references(secretidentity__c) with (relationshipName 'secretId'),
group__c reference references(superherogroup__c) with (relationshipName 'secretGroup'),
my_formula__c string(80) with (formula "name & ' ' & identity__r.Name"),
secret__c encryptedstring(50) with (maskChar asterisk, maskType all),
commitment__c percent(3,0) with (inlineHelpText "Here is some help!"));

Data Types

We support the following datatypes via the CREATE and ALTER table commands.

Numeric datatypes

These data types support an optional "precision, scale" clause:

1
2
3
4
5
Currency[(<precision>, [<scale>])]
Percent[(<precision>, [<scale>])]
Double[(<precision>, [<scale>])]
Int[(<precision>)]
Decimal[(<precision>, [<scale>])]

String datatypes

These data types support an optional "length" clause:

1
2
3
String[(<length>)]
TextArea[(<length>)]
LongTextArea[(<length>)] with (visibleLines 3)

Picklist and Multipicklist datatype

[Multi]Picklist supports a list of valid values, a default indicator, and an optional "sorted" clause. Note that currently there is no support for "controlling field" in Picklist.

1
2
PickList(<value> [default] ... [sorted]
MultiPickList(<value> [default] ... [sorted]

Foreign key relationships

Foreign keys are defined via the "Reference" and "MasterRecord" data types:

1
2
3
Reference references (<referencedTable>) with (relationshipName <refName> [ , relationshipLabel '<labelName>'])

MasterRecord references (<referencedTable>) with (relationshipName <refName> [ , relationshipLabel '<labelName>'])

AutoNumber

The AutoNumber datatype, used with the built-in "Name" field, defaults to a format of {0000000000}, but this can be changed via the 'with' clause, eg:

AutoNumber with (displayFormat "{INV0000}")

Other datatypes

Use of 'with clause'

The CREATE command maps standard SQL values such as the the column name, data type, and field size to an internal Salesforce structure. The 'with' clause is really just a back-door to support unusual features associated with a column definition. The values listed in the 'with' clause are passed through to Salesforce unmodified, and values are case sensitive.

The "with" values are documented by Salesforce here. If you wanted, for example, to define "inline help text" for a field you would use:

...with (inlineHelpText "Here is some help!")

Some data types, like LongTextArea, Reference and MasterRecord actually require the use of the 'with' clause, as documented above. Failure to include the 'with' clause will cause Salesforce to generate an error that provides a pretty good clue about what you need to add.

For example:

create table wibble__c (description__c longTextArea(12000));

Will generate an error:

1
2
Error: wibble__c.description__c Must specify 'visibleLines' for
a CustomField of type LongTextArea on line 1 col 372

So add a "with" clause as appropriate, eg:

1
2
create table wibble__c(description__c longTextArea(12000)
with (visibleLines 3))

Formula fields are also handled via the "with" clause. For example:

my_formula__c string(80) with (formula "name & ' ' & identity__r.Name"),

Known Issues

There are a few things that might make you raise an eyebrow.

Unsupported Custom Object Properties

There is currently no way to define the following properties of Custom Object.

This simply hasn't been implemented. If there is a demand, it will be :-)

Silly Error Messages

You may see silly error messages, because Salesforce thinks you are a web browser. Eg: if you try to use a picklist field as part of a formula you will get the following HTML code embedded in the error message:

Picklist fields are only supported in certain functions. <a href="javascript:openPopupFocusEscapePounds('/HelpAndTrainingDoor?loc=HOME&qs=BLANK?loc=help&target=tips_on_building_formulas.htm#picklists_and_msps&section=Customizing', 'Help', 1024, 768, 'width=1024,height=768,resizable=yes,toolbar=yes,status=yes,scrollbars=yes,menubar=yes,directories=no,location=yes,dependant=no', false, false);">Tell me more</a> on line 1 col 1458

Inaccurate Line Numbers

Lines and columns reported in error messages have no relationship to the CREATE TABLE script you wrote.

Relationship Names can't be reused

If you create a 'relationship' column with name given name, you won't be able to reuse the name, even if you drop the table or column. This is because Salesforce doesn't really drop the data until later, just in case you change your mind, so you can't reuse the relationship name until the column is completely deleted.

ALTER TABLE

You can add OR MODIFY a column with:

ALTER TABLE ADD <columnName> <dataTypeDefinition>

and remove columns with:

ALTER TABLE DROP COLUMN <columnName>

DROP TABLE

You can remove a table from Salesforce with:

DROP TABLE [ IF EXISTS ]