Retrieving Salesforce Reports, Objects, and Metadata

The Simba Salesforce JDBC Connector makes Salesforce reports, objects, and metadata accessible by mapping them to virtual tables. You can query these tables to retrieve various data and metadata or retrieve objects for data replication.

Retrieving Reports

You can retrieve data or metadata from Salesforce reports by querying the virtual tables provided by the connector. The query must specify either the report ID, or the names of the report and the folder where it is stored.

To retrieve a report using an ID:

  1. To retrieve a list of IDs, report names, and folder names for the reports that are available through your connection, run the following query:

    2. select * from sf_report_list

  2. To retrieve data from a report, run the following query, where [ReportID] is the ID for the report:

    3. select * from sf_report_data where id = '[ReportID]'

  3. To retrieve column metadata from a report, run the following query, where [ReportID] is the ID for the report:

    4. select * from sf_report_columns_metadata where id = '[ReportID]'

To retrieve a report using a folder name and report name:

  1. To retrieve a list of report names, folder names, and IDs for the reports that are available through your connection, run the following query:

    2. select * from sf_report_list

  2. To retrieve data from a report that does not have an ID, run the following query, where [FolderName] is the name of the folder where the report is stored and [ReportName] is the name of the report:

    3. select * from sf_report_data where folder = '[FolderName]' and name = '[Name]'

  3. To retrieve column metadata from a report that does not have an ID, run the following query, where [FolderName] is the name of the folder where the report is stored and [ReportName] is the name of the report:

    4. select * from sf_report_columns_metadata folder = '[FolderName]' and name = '[Name]'

Retrieving Objects

You can retrieve objects for data replication or retrieve metadata from objects by querying the virtual tables provided by the connector.

Note:

For information about the data replication feature in Salesforce, see "Data Replication" in the SOAP API Developer Guide: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_guidelines_datareplication.htm.

To retrieve objects for data replication:

  • To retrieve a list of objects that have been updated or deleted during a specific timespan, run the appropriate query from the following table, where [TableName] is the name of a table in the object and [Timestamp] is the date and time specified using the format yyyy.mm.dd 24h:mm:ss:
    • Objects to Retrieve Query to Run

    Updated objects

    select * from sf_updated where table = '[TableName]' and startdate = '[Timestamp]' and enddate = '[Timestamp]'

    For example:

    select * from sf_updated where table = 'account' and startdate = '2016-02-01 00:00:01' and enddate = '2016-02-15 23:59:59'

    Deleted objects

    select * from sf_deleted where table = '[TableName]' and startdate = '[Timestamp]' and enddate = '[Timestamp]'

    For example:

    select * from sf_deleted where table = 'account' and startdate = '2016-02-01 00:00:01' and enddate = '2016-02-15 23:59:59'

To retrieve metadata from objects:

  • Depending on the metadata that you want to retrieve, run the appropriate query from the following table, where [TableName] is the name of a table in the object, and [ColumnName] is the name of a column in the object:
    • Metadata to Retrieve Query to Run

    Primary keys metadata

    select * from sf_primary_keys_metadata where table = '[TableName]'

    Exported keys metadata

    select * from sf_exported_keys_metadata where table = '[TableName]'

    Imported keys metadata

    select * from sf_imported_keys_metadata where table = '[TableName]'

    Column metadata

    To retrieve all the column metadata from a table, run the following query:

    select * from sf_column_metadata where table = '[TableName]'

    Or, to retrieve the metadata of a specific column, run the following query:

    select * from sf_column_metadata where table = '[TableName]' and column = '[ColumnName]'

Retrieving Connection Metadata

You can retrieve the server timestamp or information about the Salesforce user that is currently logged in by querying the virtual tables provided by the connector.

To retrieve the server timestamp:

  • Run the following query:

    • select * from sf_server

To retrieve information about the current user:

  • Run the following query:

    • select * from sf_current_user_info