SalesForceJdbc Driver
Introduction
The SalesforceJdbc driver allows reading objects from Salesforce via JDBC. Using SQL queries, data from a Salesforce object can be extracted and processed just like from a database table.
- Schema: empty, public or system
- Table: With empty schema or public, the name of a Salesforce object. With schema system, the name of a system table
- Columns: The respective columns of the object or the system tables
With the following query, for example, the first 10 records of some columns of the Account object can be read using the driver:
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
FILTER limit 10
SQL Syntax
Deviating from the SQL syntax presented under SQL Parser, there are the following deviations/additions:
- The FILES() functionality is not supported. Thus, the pseudo-columns DIRECTORY and FILENAME are also not supported.
- The keywords COLUMNS, HEADLINE, SEPARATED BY, QUOTED BY and ENCODING are not supported.
- The FILTER keyword is supported
When using the FILTER keyword, any code directly following the FROM section is appended to the generated Salesforce SOQL query.
The FILTER section is currently not validated.
This enables preselection in Salesforce before the WHERE condition is evaluated in the datasqill JDBC SQL.
Examples for FILTER:
Limit to 10 rows:
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
FILTER LIMIT 10
Effective query to Salesforce via SOQL:
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
LIMIT 10
Filter for all accounts starting with a/A:
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
FILTER WHERE name LIKE 'a%'
Effective query to Salesforce via SOQL:
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
WHERE name LIKE 'a%'
SOQL LIKE does not distinguish between uppercase and lowercase.
If instead of using the FILTER condition the following SQL were used
SELECT id
, name
, masterrecordid
, isdeleted
, lastactivitydate
, lastvieweddate
FROM public.account
WHERE LOWER(name) LIKE 'a%'
then the same result would be produced, but all data from the account would be read first and only then filtered by the datasqill JDBC SQL.
System Tables
The driver supports system tables. These allow loading metadata about Salesforce objects, columns, primary keys and column value enumerations. The system tables are always under the schema system.
The metadata of the system tables themselves is not returned when querying the system tables or the JDBC metadata methods getTables, getColumns and getPrimaryKeys.
Column values for catalog, schema, table, column identifiers, etc. are always in lowercase.
The following system tables exist:
| Schema | Table Name | Description |
|---|---|---|
| system | table_list | A list of all objects in Salesforce |
| system | column_list | A list of all columns of all objects in Salesforce |
| system | primary_key_list | A list of all primary key columns of all objects in Salesforce |
| system | picklist_list | A list of all possible enumerations of all columns of all objects in Salesforce |
Table List
Here the metadata of all available objects in Salesforce is provided.
The set of visible objects depends on the permissions that the technical user possesses with which the connection is established. This applies to all other system tables as well.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | public | The object schema is always public |
| table_name | VARCHAR(100) | Object name | Name of the object in Salesforce |
| table_type | VARCHAR(100) | TABLE | Always has the value TABLE |
| remarks | VARCHAR(4000) | Description | A description of the Salesforce object |
| type_cat | VARCHAR(100) | NULL | Not supported. Always NULL |
| type_schem | VARCHAR(100) | NULL | Not supported. Always NULL |
| type_name | VARCHAR(100) | NULL | Not supported. Always NULL |
| self_referencing_col_name | VARCHAR(100) | NULL | Not supported. Always NULL |
| ref_generation | VARCHAR(20) | NULL | Not supported. Always NULL |
The same ResultSet is returned when calling getTables via the JDBC interface java.sql.DatabaseMetaData.
Column List
Here the metadata of all columns of all available objects in Salesforce is provided.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | public | The object schema is always public |
| table_name | VARCHAR(100) | Object name | Name of the object in Salesforce |
| column_name | VARCHAR(100) | Column name | Name of the column in Salesforce |
| data_type | Integer | JDBC data type | The JDBC data type (see java.sql.JDBCType) |
| type_name | VARCHAR(100) | Data type | The name of the data type (see below) |
| column_size | Integer | Length | The length of the column |
| buffer_length | Integer | 0 | No longer supported by JDBC. Always 0 |
| decimal_digits | Integer | Decimal places | Only for data types with decimal places. Otherwise NULL |
| num_prec_radix | Integer | 10 | Always 10 |
| nullable | Integer | 2 | Stands for unknown (columnNullableUnknown) |
| remarks | VARCHAR(4000) | Comment | Column comment |
| column_def | VARCHAR(4000) | NULL | Default value (not supported) |
| sql_data_type | Integer | NULL | not supported |
| sql_datetime_sub | Integer | NULL | not supported |
| char_octet_length | Integer | NULL | not supported |
| ordinal_position | Integer | Position | Column number starting with 1 |
| is_nullable | VARCHAR(18) | empty string | not supported |
| scope_schema | VARCHAR(100) | empty string | not supported |
| scope_table | VARCHAR(100) | empty string | not supported |
| source_data_type | Integer | NULL | not supported |
| is_autoincrement | VARCHAR(10) | empty string | not supported |
| is_generatedcolumn | VARCHAR(10) | empty string | not supported |
Data types used:
| Name | Length | Remark |
|---|---|---|
| BIGINT | 0 | Integer. Can also be used as Integer data type |
| BOOLEAN | 0 | Boolean value true / false |
| DATE | 0 | Date |
| DECIMAL | as specified | Number with integer and decimal places. The number of decimal places is in decimal_digits |
| TIMESTAMP | 0 | Timestamp. The timestamp is always in the native timezone of Salesforce |
| VARCHAR | as specified | Character string. Can (empirically) accept up to 3,145,728 characters |
The same ResultSet is returned when calling getColumns via the JDBC interface java.sql.DatabaseMetaData.
Primary Key List
Here the metadata of all primary key columns of all objects in Salesforce is provided.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | public | The object schema is always public |
| table_name | VARCHAR(100) | Object name | Name of the object in Salesforce |
| column_name | VARCHAR(100) | Column name | Name of the primary key column |
| key_seq | Integer | Position | Position in the primary key starting with 1 |
| pk_name | VARCHAR(100) | = pk_$table_name | Name of the primary key. Automatically generated |
The same ResultSet is returned when calling getPrimaryKeys via the JDBC interface java.sql.DatabaseMetaData.
Column Enumerations List
Here the metadata of all possible enumerations of all columns of all objects in Salesforce is provided.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| schema_name | VARCHAR(100) | public | The object schema is always public |
| table_name | VARCHAR(100) | Object name | Name of the object in Salesforce |
| column_name | VARCHAR(100) | Column name | Name of the column for which this enumeration applies |
| active | BOOLEAN | TRUE | Is this element active? Salesforce returns only active elements |
| default_value | BOOLEAN | IsDefault | Indicates whether this element is the default value. Can only be TRUE once per column |
| label | VARCHAR(200) | Name | Display name of the enumeration |
| valid_for | VARCHAR(100) | Usage | Bit string that indicates usage information in Salesforce |
| value | VARCHAR(8000) | Value | Value of the enumeration |
Currently the label is only in English.
Legacy Mode
For backward-compatible use of the Salesforce driver, there is a switch "NullToEmptyStrings". This can be set to operate the Salesforce driver in a mode where instead of typed data only VARCHAR columns are returned.
To do this, the switch is added in the keyfile under dboptionlist:
SQTS_DB_44 jdbc:salesforce:https://myinstance.salesforce.com/services||dboptionlist={client_id=AAAAA,client_secret=XXXXX,NullToEmptyStrings=true}|
This makes the driver behave slightly differently with regard to the returned data:
- Boolean and Double data are returned as VARCHAR
- Null values are converted to empty strings.