The CData JDBC Driver for Smartsheet models the Smartsheet data as an easy-to-use SQL database.

Tables

The data model of the driver is dynamic. This means that, when you connect using the driver, any changes you make in the Smartsheet UI, such as adding a new table, adding new columns, or changing a column's data type, are automatically included in the schema of the driver.

However, some tables and views, such as workspaces and folders, are static. These are defined in schema files, which are simple, text-based configuration files. The names of the static tables and views are prefixed with Info_.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including managing sheets and attachments, and retrieving OAuth credentials.

API Restrictions

The driver uses the Smartsheet API to process supported filters. The driver processes other filters client-side within the driver.

Hierarchy Navigation

By Hierarchy Navigation, we refer to recursively exploring the Smartsheet hierarchy (tree) of objects through the API by doing the following for each workspace (unless the RootFolderId pseudo-column is used).

• Fetching object metadata for objects located in the workspace's root.
• Fetching object metadata for objects located in the folders of the workspace's root.
• Fetching object metadata for objects located in the subfolders of those folders.
• Fetching object metadata for objects for all subfolders, until reaching folders that have no more folder objects.

For the following entities:

• Info_Sheets
• Info_Reports
• Info_Dashboards

the driver will navigate the hierarchy when the following conditions are met:

1. The FolderId or the WorkspaceId columns are explicitly specified in the projection as in the query example below:
   

   SELECT Id, Name, FolderId FROM Info_Sheets;

2. UseLegacyAPI is disabled.

For the following entities:

• Info_Folders
• Info_Templates

the driver will navigate the hierarchy simply when UseLegacyAPI is disabled.

Navigating the hierarchy is very costly in terms of performance because the driver has to make many API calls, and that's why the driver tries avoiding it where possible for the entities mentioned above only. For SELECT * queries such as the following:

SELECT * FROM Info_Sheets;

the driver avoids navigating the hierarchy, but as a side effect the FolderId and WorkspaceId columns are reported with NULL values in every record.

For Info_Objects, the driver will always navigate the hierarchy.