CData Cloud offers access to Veeva Vault across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to Veeva Vault through CData Cloud.
CData Cloud allows you to standardize and configure connections to Veeva Vault as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to Veeva Vault in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to Veeva Vault and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Veeva Vault through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Veeva Vault by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
In order to connect to your Veeva Vault account, you will first need to specify the Url connection property to the host you see in the address bar after logging in to your account, ex. https://myvault.veevavault.com.
OpenID Connect with Azure AD is a connection type that goes through OAuth. Set the AuthScheme to AzureADOpenID and the OpenIDConnectProfileID connection property to the Id of your Open ID Connect profile, which can be found by navigating to Admin > Settings > OAuth 2.0 / OpenID Connect Profiles and expanding the details of your OpenID Connect Profile.
There are two authentication methods available for connecting to your Veeva Vault data source, Basic and OAuth 2.0 / OpenID Connect with the Azure AD Authentication Provider.
Set the AuthScheme to Basic and set the User and Password to your user login credentials.
By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
The CData Cloud models Veeva Vault objects as relational views. A Veeva Vault object has relationships to other objects; in the views, these relationships are expressed through foreign keys. The following sections show the static endpoints of the Veeva Vault API.
However, the rest of the views are dynamically determined, using the metadata exposed on the Veeva Vault API. All vault objects will be exposed by the CData Cloud. However, we only only list views in the help for types we can determine at design time all available columns. Examples of Veeva objects availabe with the CData Cloud but not displayed in the help include:
Veeva Vault Query (VQL) is a query language similar to SQL. It is available to use via the Veeva Vault API. When the query can be handled server-side,
the provider transforms SQL to VQL and sends it to Veeva Vault. Optimizing in this way decreases the execution time, since the filtering will be done
server-side.
Also, the provider supports QueryPassthrough, which means that you can write a VQL query and directly send it to Veeva Vault. This can be done by setting
QueryPassthrough connection property to true.
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
Name | Description |
AuditHistory | Retrieve complete audit history for a single document or for a single object record. |
DocumentRelationships | Returns all relationships per each document. |
DocumentRoles | Manually retrieve assigned roles on a document and the users and groups assigned to them. |
DocumentUserActions | Retrieve all available user actions on specific versions of multiple documents or binders. |
Lifecycles | Retrieve brief details for the available lifecycles. |
ObjectRelationships | Returns all relationships per each object. An ObjectName is required to query this view. |
ObjectRoles | Manually retrieve assigned roles on an object record and the users and groups assigned to them. |
ObjectTypes | Returns brief details of all types an object can be. |
ObjectWorkflows | Retrieve the workflows of an object or the workflows of an user. |
ObjectWorkflowTasks | Retrieve the workflows of an object or the workflows of an user. |
Retrieve complete audit history for a single document or for a single object record.
Either the DocumentId column or the ObjectName and ObjectRecordId columns must be specified in order to query this view.
The Cloud will use the Veeva Vault API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Cloud.
For example, the following queries are processed server-side:
SELECT * FROM AuditHistory WHERE DocumentId = '8' SELECT * FROM AuditHistory WHERE ObjectName = 'product__v' AND ObjectRecordId = '00P000000000601' SELECT * FROM AuditHistory WHERE DocumentId = '3258' AND StartDate = '2020-01-01T01:13:36' AND EndDate = '2020-01-06T01:3:32'
Name | Type | Description |
DocumentId | String | Identifier of the document. |
ObjectName | String | The object Name. |
ObjectRecordId | String | Identifier of the object record. |
Id [KEY] | String | Identifier of the audit record. |
Timestamp | Datetime | The time and date of the preformed action. |
UserName | String | The name of the user responsible for the preformed action. |
FullName | String | The full name of the user responsible for the preformed action. |
Action | String | The name of the action performed on the object record or on the document. |
Item | String | The type and name of the affected record or the number field value of the affected Document. |
FieldName | String | The name of the affected field. |
OldValue | String | The previous field value before the executed action. |
NewValue | String | The actual field value after the executed action. |
ObjectLabel | String | The label of the affected object. |
MigrationMode | Bool | Indicates that the object record was created using the X-VaultAPI-MigrationMode header with a specified state__v value. |
WorkflowName | String | The name of the workflow that preformed the action. |
TaskName | String | The name of the preformed workflow task. |
Verdict | String | The verdict of the workflow task action. |
Reason | String | The reason of the workflow task action verdict. |
Capacity | String | The approval capacity of the workflow that preformed the action. |
SignatureMeaning | String | The reason a signature was required for any manifested signature. |
ViewLicense | String | Returns a value of View-Based User only when the user is assigned that license type. Otherwise, returns an empty string. |
JobInstanceId | String | The Id of the Job Instance that preformed the action. |
Version | String | The version of the affected Document. |
DocumentUrl | String | The URL of the affected Document. |
EventDescription | String | Description of the action that occurred. |
Pseudo-column fields are used in the WHERE clause of SELECT statements and offer more granular control over the tuples that are returned from the data source.
Name | Type | Description |
StartDate | Datetime | Specify a start date to retrieve audit history. This date cannot be more than 30 days ago. IIf not stated, defaults to the vault's creation date. |
EndDate | Datetime | Specify an end date to retrieve audit history. This date cannot be more than 30 days ago. If not stated, defaults to today's date. |
Returns all relationships per each document.
DocumentId, MajorVersion and MinorVersion must be specified in order to query this view.
To find these values, you can execute the following query:
SELECT version_id FROM DocumentsAnd version_id values are of this pattern: {DocumentId}_{MajorVersion}_{MinorVersion}
SELECT * FROM DocumentRelationships WHERE SourceDocumentId = '122' AND MajorVersion = 1 AND MinorVersion = 0 SELECT * FROM DocumentRelationships WHERE SourceDocumentId = '101' AND MajorVersion = 0 AND MinorVersion = 1
Name | Type | Description |
Id [KEY] | Integer | The Id of the relationship. |
SourceDocumentId | Integer | Identifier of the source document. |
TargetDocumentId | Integer | Identifier of the target document. |
Type | String | Type of the relationship. |
CreatedDate | Datetime | The datetime of when the relationship was created. |
CreatedBy | Integer | Identifier of the user who created the relationship. |
MajorVersion | Integer | Major version of the source document. |
MinorVersion | Integer | Minor version of the source document. |
Manually retrieve assigned roles on a document and the users and groups assigned to them.
The Cloud will use the Veeva Vault API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM DocumentRoles WHERE DocumentId = '100' SELECT * FROM DocumentRoles WHERE DocumentId IN ('99', '98', '876', '873', '867', '1')It is recommended to specify DocumentId, otherwise the provider will make a request per each document, causing the execution of the query to take some time.
Name | Type | Description |
DocumentId [KEY] | String | Identifier of the document. |
Name [KEY] | String | Name of the role. |
Label | String | Label of the role. |
AssignedUsers | String | Comma-separated list of user Ids. |
AssignedGroups | String | Comma-separated list of group Ids. |
AvailableUsers | String | Comma-separated list of user Ids. |
AvailableGroups | String | Comma-separated list of group Ids. |
DefaultUsers | String | Comma-separated list of user Ids. |
DefaultGroups | String | Comma-separated list of group Ids. |
Retrieve all available user actions on specific versions of multiple documents or binders.
DocumentId, MajorVersion and MinorVersion must be specified in order to query this view.
To find these values, you can execute the following query:
SELECT version_id FROM DocumentsAnd version_id values are of this pattern: {DocumentId}_{MajorVersion}_{MinorVersion}
SELECT * FROM DocumentUserActions WHERE SourceDocumentId = '122' AND MajorVersion = 1 AND MinorVersion = 0 SELECT * FROM DocumentUserActions WHERE SourceDocumentId = '101' AND MajorVersion = 0 AND MinorVersion = 1
Name | Type | Description |
DocumentId | String | Name of the action. |
Name | String | Name of the action. |
Label | String | Label of the action. |
ActionType | String | Type of the action. |
Lifecycle | String | Name of the lifecycle. |
State | String | State before the action is initialized. |
MajorVersion | Integer | Major version of the source document. |
MinorVersion | Integer | Minor version of the source document. |
Retrieve brief details for the available lifecycles.
The Cloud will use the Veeva Vault API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Lifecycles WHERE lifecycle = 'draft_to_effective_lifecycle_supporting__c' SELECT * FROM Lifecycles WHERE Role = 'qa__vs'
Name | Type | Description |
Lifecycle | String | Name of the lifecycle. |
Role | String | Role of the user that can use the lifecycle. |
AllowedUsers | String | Comma-separated list of names of the allowed users. |
AllowedGroups | String | Comma-separated list of names of the allowed groups. |
AllowedDefaultUsers | String | Comma-separated list of names of the default allowed users. |
AllowedDefaultGroups | String | Comma-separated list of names of the default allowed groups. |
Returns all relationships per each object. An ObjectName is required to query this view.
Name | Type | Description |
RelationshipName | String | Name of the relationship. |
RelationshipLabel | String | Label of the relationship. |
RelationshipType | String | Type of the relationship. |
Field | String | Type of the relationship. |
RelatedObjectName | String | Name of the object in the relationship. |
RelatedObjectLabel | String | Label of the object in the relationship. |
ObjectName | String | Name of the object. |
Manually retrieve assigned roles on an object record and the users and groups assigned to them.
ObjectName and RecordId must be specified in order to query this view.
Note: In this provider vault objects are exposed as views. And the keys of these views corresponds to RecordId.
Some query examples:
SELECT * FROM ObjectRoles WHERE ObjectName = 'product__v' AND RecordId = '00P000000000201' SELECT * FROM ObjectRoles WHERE ObjectName = 'activity__v' AND RecordId = '0AC000000001D27'
Name | Type | Description |
RecordId [KEY] | String | Name of the role. |
ObjectName [KEY] | String | Name of the role. |
Name | String | Name of the role. |
Users | String | Comma-separated list of user Ids. |
Groups | String | Comma-separated list of group Ids. |
AssignmentType | String | Type of the assignment. |
Returns brief details of all types an object can be.
No available server-side filters for this view.
Name | Type | Description |
Name | String | The Id of the relationship. |
Object | String | Identifier of the source document. |
Label | String | Identifier of the target document. |
Active | Boolean | Type of the relationship. |
Retrieve the workflows of an object or the workflows of an user.
ObjectName and RecordId or UserId must be specified in order to query this view.
Note: In this provider vault objects are exposed as views. And the keys of these views corresponds to RecordId here.
The Cloud will use the Veeva Vault API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Cloud.
For example, the following queries are processed server-side:
Some query examples:
SELECT * FROM ObjectWorkflows WHERE UserId IN (SELECT id FROM Users) SELECT * FROM ObjectWorkflows WHERE UserId IN ('1069977', '115731') SELECT * FROM ObjectWorkflows WHERE UserId = '1069977' SELECT * FROM ObjectWorkflows WHERE UserId = '349101' AND Status = 'completed__v' SELECT * FROM ObjectWorkflows WHERE ObjectName = 'product__v' AND RecordId = '00P000000000201'
Name | Type | Description |
Id [KEY] | String | Identifier of the workflow. |
Label | String | Label of the workflow. |
Status | String | Status of the workflow. |
Initiator | String | Identifier of the user who initiated the workflow. |
StartedDate | Datetime | Datetime when the workflow started. |
DueDate | Datetime | Datetime when the workflow is due. |
CompletedDate | Datetime | Datetime when the workflow was completed. |
RecordId | String | Identifier of the record of the object. |
ObjectName | String | Name of the object. Required if UserId not specified. |
UserId | String | Identifier of the user. Required if ObjectName not specified. |
Retrieve the workflows of an object or the workflows of an user.
ObjectName and RecordId or AssigneeId must be specified in order to query this view.
Note: In this provider vault objects are exposed as views. And the keys of these views corresponds to RecordId.
The Cloud will use the Veeva Vault API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Cloud.
For example, the following queries are processed server-side:
Some query examples:
SELECT * FROM ObjectWorkflowTasks WHERE AssigneeId IN (SELECT id FROM Users) SELECT * FROM ObjectWorkflowTasks WHERE AssigneeId IN ('1069977', '115731') SELECT * FROM ObjectWorkflowTasks WHERE AssigneeId = '1069977' SELECT * FROM ObjectWorkflowTasks WHERE AssigneeId = '349101' AND Status = 'completed__v' SELECT * FROM ObjectWorkflowTasks WHERE ObjectName = 'product__v' AND RecordId = '00P000000000201'
Name | Type | Description |
Id [KEY] | String | Identifier of the workflow task. |
WorkflowId | String | Identifier of the workflow the task is in. |
Label | String | Label of the workflow task. |
Status | String | Status of the workflow task. |
Instructions | String | Instructions to follow. |
CreatedDate | Datetime | Datetime when the task was created. |
DueDate | Datetime | Datetime when the task is due. |
AssignedDate | Datetime | Datetime when the task was assigned. |
RecordId | String | Identifier of the record of the object. |
ObjectName | String | Name of the object. Required if UserId not specified. |
AssigneeId | String | Identifier of the user. Required if ObjectName not specified. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT operations with Veeva Vault.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Veeva Vault, along with an indication of whether the procedure succeeded or failed.
Name | Description |
CreateDocument | Create a single document. |
CreateDocumentVersion | Add a new draft version of an existing document. You can choose to either use the existing source file, or a new source file. These actions will increase the document's minor version number. This is analogous to using the Create Draft action in the UI. Note that not all documents are eligible for draft creation. For example, you cannot create a draft of a checked out document. |
UpdateDocument | Update editable field values on the latest version of a single document. |
Create a single document.
To find the API names of the Documents table, get the Description column for the table Documents:
SELECT Description FROM [sys_tablecolumns] WHERE TableName = 'Documents'
The Description column will contain the API names of the Documents table. Now, use these API names to update a document, for example:
EXEC CreateDocument File = 'C:/Users/Public/document1.docx', Type__v = 'Promotional Piece', Subtype__v = 'Advertisement'
Name | Type | Required | Description |
File | String | False | Optional. The filepath of the source document. For example, C:/Users/Public/Desktop/document.docx . The maximum allowed file size is 4GB. |
FileName | String | False | The file name including extension to be used for File or Content input. FileName is optional for File input, and required for Content input. |
* | String | False | In the stored procedure's parameters, add the API name of any field values that are required to create a document. |
Name | Type | Description |
ResponseStatus | String | Whether the document was created successfully. |
ResponseMessage | String | The message of the response. |
Id | String | The Id of the document. |
Add a new draft version of an existing document. You can choose to either use the existing source file, or a new source file. These actions will increase the document's minor version number. This is analogous to using the Create Draft action in the UI. Note that not all documents are eligible for draft creation. For example, you cannot create a draft of a checked out document.
EXEC CreateDocumentVersion File = 'C:/Users/Public/document2.docx', DocumentId = '514', CreateDraft = 'uploadedContent', Description = 'This is a description', suppressRendition = 'true'
Name | Type | Required | Description |
DocumentId | String | True | The document id field value. |
File | String | False | Optional. If createDraft=uploadedContent, use this parameter to include the new document source file. If your target document is a placeholder, use this parameter to upload a source file and create a new draft version of the document. |
FileName | String | False | The file name including extension to be used for File or Content input. FileName is optional for File input, and required for Content input. |
CreateDraft | String | False | latestContent - Create a new draft version from the existing document in the vault. This does not require uploading a file. This is analogous to the Copy file from current version option in the Create Draft UI. uploadedContent - Create a new draft version by uploading the document source file. This requires uploading a new source file with an additional file body parameter. The maximum allowed file size is 4GB. This is analogous to the Upload a new file option in the Create Draft UI.
The allowed values are latestContent, uploadedContent. The default value is latestContent. |
Description | String | False | Optional. Add a Version Description for the new draft version. Other users may view this description in the document's Version History. Maximum 1,500 characters. |
SuppressRendition | String | False | Set to true to suppress automatic generation of the viewable rendition. If omitted, defaults to false.
The default value is false. |
Name | Type | Description |
ResponseStatus | String | Whether the document was created successfully. |
ResponseMessage | String | The message of the response. |
MajorVersionNumber | String | The major version number of the document. |
MinorVersionNumber | String | The minor version number of the document. |
Update editable field values on the latest version of a single document.
To find the API names of the Documents table, get the Description column for the table Documents:
SELECT Description FROM [sys_tablecolumns] WHERE TableName = 'Documents'
The Description column will contain the API names of the Documents table. Now, use these API names to update a document, for example:
EXEC UpdateDocument DocumentId = '123', name__v = 'new name', product__v = 'new product'
Name | Type | Required | Description |
DocumentId | String | True | Required. The document id field value. |
* | String | False | In the stored procedure's parameters, add the API name of any editable field values that you wish to update. |
Name | Type | Description |
ResponseStatus | String | Whether the document was updated successfully. |
Id | String | The Id of the document. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Veeva Vault:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Documents table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Documents'
Name | Type | Description |
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the SampleProcedure stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SampleProcedure' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys.
The following query retrieves the primary key for the Documents table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Documents'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:veevavault:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the Cloud can offload to the data source.
See SQL Compliance for SQL syntax details.
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | Whether COUNT function is supported. | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
OJ_CAPABILITIES | The supported varieties of outer joins supported. | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
OUTER_JOINS | Whether outer joins are supported. | YES, NO |
SUBQUERIES | Whether subqueries are supported, and, if so, the degree of support. | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
DATASYNCVERSION | The CData Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The CData Data Sync category of this driver. | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Name | Type | Description |
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | The database-generated Id returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a Connection.
Property | Description |
AuthScheme | The scheme used for authentication. Accepted entries are Basic and AzureADOpenID. |
URL | The host you see in the Url after you log in in Veeva Vault. |
User | The Veeva Vault user account used to authenticate. |
Password | The password used to authenticate the user. |
OpenIDConnectProfileID | The ID of your OAuth2.0 / Open ID Connect profile. |
Property | Description |
APIVersion | The version of the Veeva Vault API used. |
Property | Description |
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
Verbosity | The verbosity level that determines the amount of detail included in the log file. |
Property | Description |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
Property | Description |
ColumnNamesLocale | Configure the language of the column names. Works only on the views that represent Vault Objects. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Pagesize | The maximum number of results to return per page from Veeva Vault. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
QueryPassthrough | Whether or not the provider will pass the query to Veeva Vault as-is. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseDisplayNames | If set to false, the provider will use api names for some operations. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
Property | Description |
AuthScheme | The scheme used for authentication. Accepted entries are Basic and AzureADOpenID. |
URL | The host you see in the Url after you log in in Veeva Vault. |
User | The Veeva Vault user account used to authenticate. |
Password | The password used to authenticate the user. |
OpenIDConnectProfileID | The ID of your OAuth2.0 / Open ID Connect profile. |
The scheme used for authentication. Accepted entries are Basic and AzureADOpenID.
string
"Auto"
The host you see in the Url after you log in in Veeva Vault.
string
""
The host you see in the Url after you log in in Veeva Vault.
The Veeva Vault user account used to authenticate.
string
""
Together with Password, this field is used to authenticate against the Veeva Vault server.
The password used to authenticate the user.
string
""
The User and Password are together used to authenticate with the server.
The ID of your OAuth2.0 / Open ID Connect profile.
string
""
The ID of your OAuth2.0 / Open ID Connect profile. This connection property is required only when using the AzureADOpenID AuthScheme.
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
Property | Description |
APIVersion | The version of the Veeva Vault API used. |
The version of the Veeva Vault API used.
string
"21.1"
The Veeva Vault API version used by default is '21.1'.
Versions 21.1 and later are supported.
This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.
Property | Description |
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
string
""
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
Property | Description |
Verbosity | The verbosity level that determines the amount of detail included in the log file. |
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
Property | Description |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
Property | Description |
ColumnNamesLocale | Configure the language of the column names. Works only on the views that represent Vault Objects. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Pagesize | The maximum number of results to return per page from Veeva Vault. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
QueryPassthrough | Whether or not the provider will pass the query to Veeva Vault as-is. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseDisplayNames | If set to false, the provider will use api names for some operations. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
Configure the language of the column names. Works only on the views that represent Vault Objects.
string
""
For the views that represent Vault Objects, use this property to configure the language of the column names. For example, ColumnNamesLocale=de will return the column names in German. Note, if you are using QueryPassThrough, this property won't work.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
int
-1
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
The maximum number of results to return per page from Veeva Vault.
int
1000
The Pagesize property affects the maximum number of results to return per page from Veeva Vault. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Whether or not the provider will pass the query to Veeva Vault as-is.
bool
false
Whether or not the Cloud will pass the query to Veeva Vault as-is. These queries should use proper VQL syntax.
Veeva Vault supports a set of queries that are not specified in the regular SQL-92 standard; to execute these queries simply set QueryPassthrough to true. This will pass the query directly to Veeva Vault without parsing it internally.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Cloud throws an exception.
If set to false, the provider will use api names for some operations.
bool
true
If set to false, the Cloud will use api names for some operations. This property is used for custom modules: The API name for custom modules is different from a custom module name in the Veeva Vault UI. For example, if you create a Potentials custom module in Veeva Vault, the Cloud will make the underlying requests to the API name; for example, CustomModule_1.
Set this property to false to use the api name in SQL queries.
Boolean determining if simple names should be used for tables and columns.
bool
false
Veeva Vault tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the Cloud easier to use with traditional database tools.
Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.