CData Cloud offers access to Google Calendar 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 Google Calendar through CData Cloud.
CData Cloud allows you to standardize and configure connections to Google Calendar as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to Google Calendar 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 Google Calendar and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Google Calendar through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Google Calendar 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.
The Cloud supports using user accounts and GCP instance accounts for authentication.
The following sections discuss the available authentication schemes for Google Calendar:
AuthScheme must be set to OAuth in all user account flows.
Get an OAuth Access Token
Set the following connection properties to obtain the OAuthAccessToken:
Then call stored procedures to complete the OAuth exchange:
Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.
Automatic Refresh of the OAuth Access Token
To have the driver automatically refresh the OAuth access token, set the following on the first data connection:
Manual Refresh of the OAuth Access Token
The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.
Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:
Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.
Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.
Option 1: Obtain and Exchange a Verifier Code
To obtain a verifier code, you must authenticate at the OAuth authorization URL.
Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
After the OAuth settings file is generated, you need to re-set the following properties to connect:
Option 2: Transfer OAuth Settings
Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.
After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.
Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.
On the headless machine, set the following connection properties to connect to data:
When running on a GCP virtual machine, the Cloud can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.
These permissions are defined by access scopes, which determine what data your application can access and what actions it can perform.
This topic provides information about the required access scopes and endpoint domains for the Google Calendar Cloud.
Scopes are a way to limit an application's access to a user's data. They define the specific actions that an application can perform on behalf of the user.
For example, a read-only scope might allow an application to view data, while a full access scope might allow it to modify data.
| Scope | Description |
| googleapis.com/auth/calendar | While Google Calendar provides read-only scopes, the driver only surfaces the full one. This scope is required for full access to read and write permissions. |
| googleapis.com/auth/calendar.events | This scope is required for read-only access. |
Endpoint domains are the specific URLs that the application needs to communicate with in order to authenticate, retrieve records, and perform other essential operations.
Allowlisting these domains ensures that the network traffic between your application and the API is not blocked by firewalls or security settings.
Note: Most users do not need to make any special configurations. Allowlisting is typically only necessary for environments with strict security measures, such as restricted outbound network traffic.
| Domain | Always Required | Description |
| accounts.google.com | TRUE | The subdomain used for OAuth. |
| googleapis.com | TRUE | The domain used for accessing various services. |
(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Google Calendar".)
However, you must create a custom OAuth application to connect to Google Calendar via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.
Custom OAuth applications are useful if you want to:
The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).
For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)
Do the following:
Note: The client secret remains accessible from from the Google Cloud Console.
To create a new service account:
To complete the service account flow, generate a private key in the Google Cloud Console. In the service account flow, the driver exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The driver will have the same permissions granted to the service account.
By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.
To specify another certificate, see the SSLServerCert connection property.
To authenticate to an HTTP proxy, set the following:
Set the following properties:
The CData Cloud models Google Calendar APIs as relational tables, views, and stored procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
The Cloud dynamically retrieves table definitions for the calendars accessible to your account. Tables describes the columns for a sample calendar as well as the Calendars table, which can be used to list, create, modify, and delete calendars.
Views offer additional metadata information from Google Calendar to surface other aspects of a calendar.
Stored Procedures are function-like interfaces to the data source.
When working with events and their related entities through: EventsAttachments, AllCalendars, or dynamic event tables, only single events and recurring events (with their exceptions) are included by default, but not recurring event instances. To change this behavior, you can use EventsScope.
Note: Exceptions are recurring event instances that differ from their parent recurring event in any way.
Note: Single events are non-recurring events.
When working with events and their related entities through: EventsAttachments, AllCalendars, or dynamic event tables, deleted/canceled events are not included by default. To change this behavior, you can enable ShowDeleted or use the pseudo-column in the corresponding table/view. In that case, events with the status 'canceled' are included in the results. The value specified in the pseudo-column takes higher precedence than the value specified in ShowDeleted.
The Cloud models the data in Google Calendar as a list of tables in a relational database that can be queried using standard SQL statements.
| Name | Description |
| AccessControlRules | Create, update, delete, and query access control rules to calendars' resources. |
| AllCalendars | Create, update, delete, and query all calendar events in your Google Account. |
| Calendars | Create, update, delete, and query calendars in Google. |
| MyCalendar | Create, update, delete, and query events of the calendar. |
Create, update, delete, and query access control rules to calendars' resources.
To query access control rules of all calendars you can execute a query as below.
SELECT * FROM [AccessControlRules]
The CalendarId, and Id and CalendarId (both specified at the same time) columns support server side filtering only for the = operator.
To query the access control rules of a certain calendar you will need to specify the CalendarId as a WHERE clause criterion.
SELECT * FROM [AccessControlRules] WHERE CalendarId = '[email protected]'
To query a specific access control rule of a certain calendar you will need to specify the CalendarId and Id as WHERE clause criteria.
SELECT * FROM [AccessControlRules] WHERE CalendarId = '[email protected]' AND Id = 'domain:cdatatest.page'
To add a new access control rule to a specific calendar, for example to share the calendar with a user, group or domain, or to make the calendar publicly available (ScopeType='default'), you will need to at least specify the CalendarId, Role, ScopeType and optionally the ScopeValue (required if you haven't specified the default ScopeType).
For example, to share a calendar with a specific user you can execute a query as below.
INSERT INTO [AccessControlRules] (CalendarId, Role, ScopeType, ScopeValue) VALUES ('[email protected]', 'writer', 'user', '[email protected]')
For example, to share a calendar with a specific domain you can execute a query as below.
INSERT INTO [AccessControlRules] (calendarId, Role, ScopeType, ScopeValue, SendNotification) VALUES ('[email protected]', 'writer', 'domain', 'cdatatest.page', 'true')
Note: Sharing a calendar with a user no longer automatically inserts the calendar into their CalendarList. If the user should be able to perform CRUD operations on the shared calendar, they will need to execute an INSERT statement by specifying at least the existing CalendarId in the statement. For more information, refer to the Calendars table section.
To update an access control rule of a specific calendar, you will need to specify the CalendarId along with the Id in the WHERE clause criteria.
UPDATE [AccessControlRules] SET Role = 'writer', ScopeType = 'user' WHERE Id = 'user:[email protected]' AND CalendarId = '[email protected]'
To delete an access control rule from a specific calendar, you will need to specify the CalendarId along with the Id in the WHERE clause criteria.
DELETE FROM [AccessControlRules] WHERE Id = 'user:[email protected]' AND CalendarId = '[email protected]'
| Name | Type | ReadOnly | Description |
| CalendarId [KEY] | String | False |
The calendar identifier. |
| Id [KEY] | String | True |
The identifier of the ACL rule. |
| Etag | String | True |
The ETag of the resource. |
| Kind | String | True |
The type of the resource. |
| Role | String | False |
The role assigned to the scope. Possible values are: none, freeBusyReader, reader, writer and owner. |
| ScopeType | String | False |
The type of the scope. Possible values are: default, user, group and domain. |
| ScopeValue | String | False |
The email address of a user or group, or the name of a domain, depending on the scope type. Not applicable for type 'default'. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| SendNotification | String |
This value sets whether or not to send notifications about the calendar sharing change. The default value is True. |
Create, update, delete, and query all calendar events in your Google Account.
This is an example on how all calendar events in your account are exposed in a single table.
Query events of all calendars.
SELECT * FROM [AllCalendars]
Create a new event in a certain calendar. At least StartDateTime, EndDateTime and CalendarId must be specified.
INSERT INTO [AllCalendars] (Summary, Description, StartDateTime, EndDateTime, CalendarId) VALUES ('Great Event', 'Description for event', '8/27/2017', '8/28/2017', '[email protected]')
You can create an All-Day event by setting the StartDate and EndDate columns to the actual start and end dates of the event.
INSERT INTO [AllCalendars] (Summary, Description, StartDate, EndDate, CalendarId) VALUES ('Google Cal Test Event 1', 'Example All Day Event', '2017-01-01', '2017-01-09', '[email protected]')
You can create a Time Limited event by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event.
INSERT INTO [AllCalendars] (Summary, Description, StartDateTime, EndDateTime, CalendarId) VALUES ('Google Cal Test Event 2', 'Example Time Limited Event', '2017-09-05T22:13:03', '2017-09-11T13:24:10', '[email protected]')
Update details of a specific event. At least the Id of the event being updated must be specified.
UPDATE AllCalendars SET Summary = 'Test Event' WHERE id = '6bjelf33p0al4d8ei5ft5ghqjs' AND CalendarId = '[email protected]'
You can update a Time Limited event to an All-Day one by setting the StartDate and EndDate columns to the actual start and end dates of the event, and the StartDateTime and EndDateTime columns to Null-s.
UPDATE [AllCalendars] SET StartDate = '2017-09-05', EndDate = '2017-09-11', StartDateTime = NULL, EndDateTime = NULL WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'
You can update an All-Day event to a Time Limited one by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event, and the StartDate and EndDate columns to Null-s.
UPDATE [AllCalendars] SET StartDate = NULL, EndDate = NULL, StartDateTime = '2017-09-05T22:13:03', EndDateTime = '2017-09-11T13:24:10' WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'
Delete an event from AllCalendars by specifying at least its Id.
DELETE FROM [AllCalendars] WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'
When you query from AllCalendars table, the events will not be ordered by the StartDate, but rather by the CalendarId. You can order the calendars by either the StartDate or StartDateTime column, depending if the event is an AllDayEvent or not. Alternatively, you can order both Event types using the example query below.
SELECT CalendarId, Id, Summary,
CASE
WHEN StartDateTime IS NULL THEN startDate
ELSE StartDateTime
END AS EventDate
FROM AllCalendars ORDER BY EventDate ASC
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The Id of the event. |
| CalendarId [KEY] | String | True |
Calendar Id. |
| Summary | String | False |
The title of the event. |
| Description | String | False |
The description of the event. |
| Location | String | False |
The geographic location of the event as free-form text. |
| AllDayEvent | Boolean | True |
This value indicates whether or not the event is an all-day event. |
| StartDate | Date | False |
The start date if this is an all-day event. |
| StartDateTime | Datetime | False |
The (inclusive) start time of the event. For a recurring event, this value is the start time of the first instance. |
| StartDateTimeZone | String | False |
The time zone in which the start date time is specified. |
| EndDate | Datet | False |
The end date if this is an all-day event. |
| EndDateTime | Datetime | False |
The (exclusive) end time of the event. For a recurring event, this value is the end time of the first instance. |
| EndDateTimeZone | String | False |
The time zone in which the end date time is specified. |
| OriginalStartTimeDateTime | Datetime | False |
For an instance of a recurring event, this value is the time when the event would start according to the recurrence data in the recurring event identified by RecurringEventId. |
| SendNotification | Boolean | False |
This value sets whether to send a notification when performing an insert or update. |
| Kind | String | True |
The type of the resource, returned in the format calendar#event. |
| ETag | String | True |
The ETag of the resource. |
| Status | String | False |
The status of the event. |
| HTML_Link | String | True |
The absolute link to the event in the Google Calendar Web UI. |
| Created | Datetime | True |
The creation time of the event. |
| Updated | Datetime | True |
The latest modification time of the event. |
| ColorId | Integer | False |
The color of the event. This value is an Id referring to an entry in the event section of the colors definitions. |
| CreatorEmail | String | True |
The creator's email address, if available. |
| CreatorDisplayName | String | True |
The creator's name, if available. |
| OrganizerEmail | String | False |
The organizer's email address, if available. |
| OrganizerDisplayName | String | False |
The organizer's name, if available. |
| Recurrences | String | False |
A pipe-separated list of RRULE, EXRULE, RDATE, and EXDATE lines for a recurring event. This field is omitted for single events or instances of recurring events. OriginalStartTimeDateTime must be set in order to modify this value. |
| RecurringEventId | String | True |
For an instance of a recurring event, this value is the event Id of the recurring event itself. |
| Transparency | String | False |
This value sets whether the event blocks time on the calendar. If set to transparent, the event does not block time on the calendar. If set to opaque, the event blocks time; this is the default value. |
| Visibility | String | False |
The visibility of the event. |
| ICalUid | String | True |
The event Id in the iCalendar format. |
| Sequence | String | False |
The sequence number as per iCalendar. |
| AttendeesEmails | String | False |
A comma-separated list of attendee's email addresses, if available. |
| AttendeesDisplayNames | String | False |
A comma-separated list of attendee's names, if available. |
| AttendeesOmitted | Boolean | True |
This field sets whether attendees have been omitted from the event's representation. When updating an event, this field can be used to update only the participant's response. When retrieving an event, the attendees that are returned are restricted to only the participant by the MaxAttendees query parameter. |
| ExtendedPropertiesPrivateKey | String | False |
This field contains properties that are private to the copy of the event that appears on the calendar. |
| ExtendedPropertiesPrivateValue | String | False |
This field contains properties that are private to the copy of the event that appears on the calendar. |
| ExtendedPropertiesSharedKey | String | False |
This field contains properties that are shared between copies of the event on other attendees' calendars. |
| ExtendedPropertiesSharedValue | String | False |
This field contains properties that are shared between copies of the event on other attendees' calendars. |
| GuestsCanInviteOthers | Boolean | False |
This value sets whether attendees other than the organizer can invite others to the event. |
| GuestsCanSeeOtherGuests | Boolean | False |
This value sets whether attendees other than the organizer can see who the event's attendees are. |
| GuestsCanModify | Boolean | False |
Whether attendees other than the organizer can modify the event. |
| PrivateCopy | Boolean | True |
This value sets whether this is a private event copy where changes are not shared with other copies on other calendars. |
| RemindersUseDefault | Boolean | False |
This value sets whether the default reminders of the calendar apply to the event. |
| ReminderOverrideMethods | String | False |
A comma-separated list of the methods used by the reminder. The possible values are EMAIL, SMS, and POPUP. |
| ConferenceId | String | False |
The ID of the conference. Can be used by developers to keep track of conferences, should not be displayed to users. |
| ConferenceNotes | String | False |
Additional notes (such as instructions from the domain administrator, legal notices) to display to the user. Can contain HTML. The maximum length is 2048 characters. |
| ConferenceSignature | String | False |
The signature of the conference data. Generated on server side. Must be preserved while copying the conference data between events, otherwise the conference data will not be copied. |
| ConferenceSolutionName | String | False |
The user-visible name of this solution. Not localized. |
| ConferenceSolutionKeyType | String | False |
The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers. |
| ConferenceSolutionIconUri | String | False |
The user-visible icon for this solution. |
| ConferenceRequestId | String | False |
The client-generated unique ID for this request. Clients should regenerate this ID for every new request. If an ID provided is the same as for the previous request, the request is ignored. |
| ConferenceRequestKey | String | False |
The conference solution, such as Hangouts or Hangouts Meet. |
| ConferenceRequestKeyType | String | False |
The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers. |
| ConferenceRequestStatus | String | False |
The status of the conference create request. |
| ConferenceRequestStatusCode | String | True |
The current status of the conference create request. The possible values are: (1) |
| ConferenceEntryPointsAggregate | String | False |
Information about individual conference entry points, such as URLs or phone numbers. All of them must belong to the same conference. |
| ReminderOverrideMinutes | String | False |
A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger. |
| EventType | String | False |
Specific type of the event. Read-only. Possible values are: 'default' - A regular event or not further specified. 'outOfOffice' - An out-of-office event. 'focusTime' - A focus-time event. 'workingLocation' - A working-location event. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| SearchTerms | String |
Free text search terms to find events that match these terms in any field, except for extended properties. |
| ShowDeleted | Boolean |
Whether to list cancelled events. |
Create, update, delete, and query calendars in Google.
When selecting calendars no fields are required. In addition an Id can be specified for filtering the result. For example:
SELECT Id, Summary, Description FROM Calendars
To insert a new calendar, issue an INSERT statement and specify a value for at least the Summary column. For example:
INSERT INTO Calendars (Summary) VALUES ('My Custom Calendar')
To insert an existing calendar, a shared one or a global/domain public one, into the user's calendars list, you will need to execute an INSERT statement by specifying at least the existing CalendarId in the statement.
For example, to add a shared calendar from a specific user, you can execute a query as below.
INSERT INTO Calendars (Id) VALUES ('[email protected]')
For example, to add a public calendar, you can execute a query as below.
INSERT INTO Calendars (id, SummaryOverride) VALUES ('en.usa#[email protected]', 'Official Holidays in the US')
To update a calendar, the Id column must be specified. Only the Description, Location, Summary, and Timezone columns are updateable. For example:
UPDATE Calendars SET Description = 'Updated Description' WHERE Id = '8ba774m3anenroqcepfi7ka6ok'
Delete a calendar by specifying its Id. For example:
DELETE FROM Calendars WHERE Id = '123456789'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The calendar identifier. |
| Summary | String | False |
The title of the calendar. |
| Description | String | False |
The description of the calendar. |
| Kind | String | True |
The type of the resource. |
| ETag | String | True |
The ETag of the resource. |
| Location | String | False |
The geographic location of the calendar as free-form text. |
| Timezone | String | False |
The time zone of the calendar. |
| SummaryOverride | String | False |
The summary that the authenticated user has set for the calendar. |
| ColorId | Integer | False |
The color of the calendar. This is an Id referring to an entry in the 'calendar' section of the colors definition. |
| Hidden | Boolean | False |
This field sets whether the calendar has been hidden from the list. |
| Selected | Boolean | False |
This field sets whether the calendar content shows up in the calendar UI. |
| AccessRole | String | True |
The effective access role that the authenticated user has on the calendar. |
| ReminderMethods | String | False |
A semicolon-separated list of the methods used by the reminder. Possible values are: EMAIL, SMS, and POPUP. |
| ReminderMinutes | String | False |
A semicolon-separated list of minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| ShowHidden | Boolean |
This value sets whether to show hidden calendars. The default value is False. |
Create, update, delete, and query events of the calendar.
This is an example on how a calendar is exposed as table.
Query events of the specified calendar.
SELECT * FROM [MyCalendar]
Create a new event in the calendar. At least StartDateTime and EndDateTime must be specified.
INSERT INTO [MyCalendar] (Summary, Description, StartDateTime, EndDateTime) VALUES ('Great Event', 'Description for event', '8/27/2017', '8/28/2017')
You can create an All-Day event by setting the StartDate and EndDate columns to the actual start and end dates of the event.
INSERT INTO [MyCalendar] (Summary, Description, StartDate, EndDate) VALUES ('Google Cal Test Event 1', 'Example All Day Event', '2017-01-01', '2017-01-09')
You can create a Time Limited event by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event.
INSERT INTO [MyCalendar] (Summary, Description, StartDateTime, EndDateTime) VALUES ('Google Cal Test Event 2', 'Example Time Limited Event', '2017-09-05T22:13:03', '2017-09-11T13:24:10')
When inserting a new event, multiple values can be specified for AttendeesEmails and AttendeesDisplayNames.
INSERT INTO [MyCalendar] (StartDateTime, EndDateTime, AttendeesEmails) VALUES ('2017-03-15 15:00', '2017-03-15 20:00', '[email protected],[email protected]')
Update details of a specific event. The Id of the event being updated must be specified.
UPDATE [MyCalendar] SET Summary = 'Updated Summary 2' WHERE Id = '8ba774m3anenroqcepfi7ka6ok'
You can update a Time Limited event to an All-Day one by setting the StartDate and EndDate columns to the actual start and end dates of the event, and the StartDateTime and EndDateTime columns to Null-s.
UPDATE [MyCalendar] SET StartDate = '2017-09-05', EndDate = '2017-09-11', StartDateTime = NULL, EndDateTime = NULL WHERE Id = '8ba774m3anenroqcepfi7ka6ok'
You can update an All-Day event to a Time Limited one by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event, and the StartDate and EndDate columns to Null-s.
UPDATE [MyCalendar] SET StartDate = NULL, EndDate = NULL, StartDateTime = '2017-09-05T22:13:03', EndDateTime = '2017-09-11T13:24:10' WHERE Id = '8ba774m3anenroqcepfi7ka6ok'
Delete an event by specifying its Id.
DELETE FROM [MyCalendar] WHERE Id = '8ba774m3anenroqcepfi7ka6ok'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The Id of the event. |
| CalendarId | String | False |
Calendar Id. This can be used to override the Calendar Id for which you are querying events. |
| Summary | String | False |
The title of the event. |
| Description | String | False |
The description of the event. |
| Location | String | False |
The geographic location of the event as free-form text. |
| AllDayEvent | Boolean | True |
This value indicates whether or not the event is an all-day event. |
| StartDate | Date | False |
The start date if this is an all-day event. |
| StartDateTime | Datetime | False |
The (inclusive) start time of the event. For a recurring event, this value is the start time of the first instance. |
| StartDateTimeZone | String | False |
The time zone in which the start date time is specified. |
| EndDate | Date | False |
The end date if this is an all-day event. |
| EndDateTime | Datetime | False |
The (exclusive) end time of the event. For a recurring event, this value is the end time of the first instance. |
| EndDateTimeZone | String | False |
The time zone in which the end date time is specified. |
| OriginalStartTimeDateTime | Datetime | False |
For an instance of a recurring event, this value is the time when the event would start according to the recurrence data in the recurring event identified by RecurringEventId. |
| SendNotification | Boolean | False |
This value sets whether to send a notification when performing an insert or update. |
| Kind | String | True |
The type of the resource, returned in the format calendar#event. |
| ETag | String | True |
The ETag of the resource. |
| Status | String | False |
The status of the event. |
| HTML_Link | String | True |
The absolute link to the event in the Google Calendar Web UI. |
| Locked | Boolean | True |
Whether this is a locked event copy where no changes can be made to the main event fields |
| SourceTitle | String | False |
Title of the source; for example a title of a web page or an email subject. |
| SourceURL | String | False |
URL of the source pointing to a resource. The URL scheme must be HTTP or HTTPS. |
| HangoutLink | String | True |
An absolute link to the Google+ hangout associated with this event, if there is any. |
| Created | Datetime | True |
The creation time of the event. |
| Updated | Datetime | True |
The latest modification time of the event. |
| ColorId | Integer | False |
The color of the event. This value is an Id referring to an entry in the event section of the colors definitions. |
| CreatorEmail | String | True |
The creator's email address, if available. |
| CreatorDisplayName | String | True |
The creator's name, if available. |
| OrganizerEmail | String | True |
The organizer's email address, if available. |
| OrganizerDisplayName | String | True |
The organizer's name, if available. |
| Recurrences | String | False |
A pipe-separated list of RRULE, EXRULE, RDATE, and EXDATE lines for a recurring event. This field is omitted for single events or instances of recurring events. OriginalStartTimeDateTime must be set in order to modify this value. |
| RecurringEventId | String | True |
For an instance of a recurring event, this value is the event Id of the recurring event itself. |
| Transparency | String | False |
This value sets whether the event blocks time on the calendar. If set to transparent, the event does not block time on the calendar. If set to opaque, the event blocks time; this is the default value. |
| Visibility | String | False |
The visibility of the event. |
| ICalUid | String | True |
The event Id in the iCalendar format. |
| Sequence | String | False |
The sequence number as per iCalendar. |
| AttendeesEmails | String | False |
A comma-separated list of attendee's email addresses, if available. |
| AttendeesDisplayNames | String | False |
A comma-separated list of attendee's names, if available. |
| AttendeesResponseStatus | String | False |
A comma-separated list of attendee's response status, if available. |
| AttendeesOmitted | Boolean | True |
This field sets whether attendees have been omitted from the event's representation. When updating an event, this field can be used to update only the participant's response. When retrieving an event, the attendees that are returned are restricted to only the participant by the MaxAttendees query parameter. |
| ExtendedPropertiesPrivateKey | String | False |
This field contains properties that are private to the copy of the event that appears on the calendar. |
| ExtendedPropertiesPrivateValue | String | False |
This field contains properties that are private to the copy of the event that appears on the calendar. |
| ExtendedPropertiesSharedKey | String | False |
This field contains properties that are shared between copies of the event on other attendees' calendars. |
| ExtendedPropertiesSharedValue | String | False |
This field contains properties that are shared between copies of the event on other attendees' calendars. |
| GuestsCanInviteOthers | Boolean | False |
This value sets whether attendees other than the organizer can invite others to the event. |
| GuestsCanSeeOtherGuests | Boolean | False |
This value sets whether attendees other than the organizer can see who the event's attendees are. |
| GuestsCanModify | Boolean | False |
Whether attendees other than the organizer can modify the event. |
| PrivateCopy | Boolean | True |
This value sets whether this is a private event copy where changes are not shared with other copies on other calendars. |
| RemindersUseDefault | Boolean | False |
This value sets whether the default reminders of the calendar apply to the event. |
| ReminderOverrideMethods | String | False |
A comma-separated list of the methods used by the reminder. The possible values are EMAIL, SMS, and POPUP. |
| ReminderOverrideMinutes | String | False |
A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger. |
| ConferenceId | String | False |
The ID of the conference. Can be used by developers to keep track of conferences, should not be displayed to users. |
| ConferenceNotes | String | False |
Additional notes (such as instructions from the domain administrator, legal notices) to display to the user. Can contain HTML. The maximum length is 2048 characters. |
| ConferenceSignature | String | False |
The signature of the conference data. Generated on server side. Must be preserved while copying the conference data between events, otherwise the conference data will not be copied. |
| ConferenceSolutionName | String | False |
The user-visible name of this solution. Not localized. |
| ConferenceSolutionKeyType | String | False |
The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers. |
| ConferenceSolutionIconUri | String | False |
The user-visible icon for this solution. |
| ConferenceRequestId | String | False |
The client-generated unique ID for this request. Clients should regenerate this ID for every new request. If an ID provided is the same as for the previous request, the request is ignored. |
| ConferenceRequestKey | String | False |
The conference solution, such as Hangouts or Hangouts Meet. |
| ConferenceRequestKeyType | String | False |
The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers. |
| ConferenceRequestStatus | String | False |
The status of the conference create request. |
| ConferenceRequestStatusCode | String | True |
The current status of the conference create request. The possible values are: (1) |
| ConferenceEntryPointsAggregate | String | False |
Information about individual conference entry points, such as URLs or phone numbers. All of them must belong to the same conference. |
| EventType | String | False |
Specific type of the event. Read-only. Possible values are: 'default' - A regular event or not further specified. 'outOfOffice' - An out-of-office event. 'focusTime' - A focus-time event. 'workingLocation' - A working-location event. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| SearchTerms | String |
Free text search terms to find events that match these terms in any field, except for extended properties. |
| ShowDeleted | Boolean |
Whether to list canceled events. |
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 |
| Colors | Query colors for Google calendars and events. |
| EventsAttachments | Query the attachments metadata from the events in your Google calendar. |
Query colors for Google calendars and events.
Returns the color definitions for calendars and events.
SELECT * FROM Colors
| Name | Type | Description |
| Id [KEY] | String | The unique color identifier composed of the color type and Id separated by the pipe character. |
| EntityType [KEY] | String | The type of the entity the color is specified for. It can be either Calendar or Event |
| ColorBackground | String | The background color for the calendar color. |
| ColorForeground | String | The foreground color for the calendar color. |
| Updated | Datetime | The last time the list of colors was updated. |
Query the attachments metadata from the events in your Google calendar.
To query attachments metadata of all events in a certain calendar, you must specify the CalendarId the WHERE clause.
SELECT * FROM EventsAttachments WHERE CalendarId = 'df5bf39459e666e1f6835a287923a083f83bfbe21113051b2759a9f2fa40a7c9@group.calendar.google.com'
To query attachments metadata for only a specific recurring event in a certain calendar, you must specify the CalendarId and the ParentEventId (the recurring event's Id) in the WHERE clause.
SELECT * FROM EventsAttachments WHERE CalendarId = 'df5bf39459e666e1f6835a287923a083f83bfbe21113051b2759a9f2fa40a7c9@group.calendar.google.com' AND ParentEventId = '4fbitolj1eb2ilbspcm2l5jcvc'
To query attachments metadata of all events in multiple calendars, you must specify their CalendarIds in the IN clause.
SELECT * FROM EventsAttachments WHERE CalendarId IN ('df5bf39459e666e1f6835a287923a083f83bfbe21113051b2759a9f2fa40a7c9@group.calendar.google.com', '8aab717ee7e6fca501e1e178e590f3ac4ac0c2660361fdff47b8c341eb379559@group.calendar.google.com')
| Name | Type | Description |
| CalendarId | String | The calendar identifier. |
| EventId [KEY] | String | The event identifier. |
| ParentEventId | String | The event instance identifier of a recurring event instance. |
| EventStatus | String | The status of the event. |
| FileId | String | The Google Drive file identifier of the attachment, if the attachment is located in a GoogleDrive. |
| FileURL [KEY] | String | The URL link to the attachment. |
| Title | String | The name of the attachment. |
| MimeType | String | The internet media type (MIME type) of the attachment. |
| IconLink | String | The URL link to the attachment's icon. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| ShowDeleted | Boolean | Indicates whether to list cancelled events. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Google Calendar.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Google Calendar, along with an indication of whether the procedure succeeded or failed.
| Name | Description |
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 Google Calendar:
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 MyCalendar table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='MyCalendar'
| 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 RefreshOAuthAccessToken stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' 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 MyCalendar table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='MyCalendar'
| 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.
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. |
Describes the available system information.
The following query retrieves all columns:
SELECT * FROM sys_information
| Name | Type | Description |
| Product | String | The name of the product. |
| Version | String | The version number of the product. |
| Datasource | String | The name of the datasource the product connects to. |
| NodeId | String | The unique identifier of the machine where the product is installed. |
| HelpURL | String | The URL to the product's help documentation. |
| License | String | The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.) |
| Location | String | The file path location where the product's library is stored. |
| Environment | String | The version of the environment or rumtine the product is currently running under. |
| DataSyncVersion | String | The tier of CData Sync required to use this connector. |
| DataSyncCategory | String | The category of CData Sync functionality (e.g., Source, Destination). |
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 type of authentication to use when connecting to Google Calendar. |
| Property | Description |
| EventsScope | Specifies the event types to expose in the Data Model. |
| ShowDeleted | Whether to include deleted events when listing events. |
| SupportsAllDrives | Determines whether to retrieve items from shared Drives. |
| Property | Description |
| OAuthClientId | Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server. |
| OAuthClientSecret | Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server. |
| DelegatedServiceAccounts | A space-delimited list of service account emails for delegated requests. |
| RequestingServiceAccount | A service account email to make a delegated request. |
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate. |
| OAuthJWTIssuer | The issuer of the Java Web Token. |
| OAuthJWTSubject | The user subject for which the application is requesting delegated access. |
| Property | Description |
| SSLServerCert | Specifies the certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| Verbosity | Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5. |
| Property | Description |
| BrowsableSchemas | Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC . |
| Property | Description |
| AWSWorkloadIdentityConfig | Configuration properties to provide when using Workload Identity Federation via AWS. |
| MaxRows | Specifies the maximum rows returned for queries without aggregation or GROUP BY. |
| PseudoColumns | Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property. |
| Timeout | Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
| WorkloadPoolId | The ID of your Workload Identity Federation pool. |
| WorkloadProjectId | The ID of the Google Cloud project that hosts your Workload Identity Federation pool. |
| WorkloadProviderId | The ID of your Workload Identity Federation pool provider. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to Google Calendar. |
The type of authentication to use when connecting to Google Calendar.
string
"OAuth"
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
| Property | Description |
| EventsScope | Specifies the event types to expose in the Data Model. |
| ShowDeleted | Whether to include deleted events when listing events. |
| SupportsAllDrives | Determines whether to retrieve items from shared Drives. |
Specifies the event types to expose in the Data Model.
string
"UNIQUE"
| UNIQUE | Includes single events and recurring events (with their exceptions), but not recurring event instances. |
| SINGLE | Includes single events and recurring events, but not recurring event instances and their exceptions. |
| ALL | Includes single events and all recurring event instances (but not their underlying parent events). |
Whether to include deleted events when listing events.
bool
false
When set to True, events with the status 'canceled' are included in query results. Otherwise, they are omitted.
Determines whether to retrieve items from shared Drives.
bool
false
If you set this property to 'true', you can download attachments from any Drive.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthClientId | Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server. |
| OAuthClientSecret | Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server. |
| DelegatedServiceAccounts | A space-delimited list of service account emails for delegated requests. |
| RequestingServiceAccount | A service account email to make a delegated request. |
Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
string
""
OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.
Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
string
""
OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.
A space-delimited list of service account emails for delegated requests.
string
""
The service account emails must be specified in a space-delimited list.
Each service account must be granted the roles/iam.serviceAccountTokenCreator role on its next service account in the chain.
The last service account in the chain must be granted the roles/iam.serviceAccountTokenCreator role on the requesting service account. The requesting service account is the one specified in the RequestingServiceAccount property.
Note that for delegated requests, the requesting service account must have the permission iam.serviceAccounts.getAccessToken, which can also be granted through the serviceAccountTokenCreator role.
A service account email to make a delegated request.
string
""
The service account email of the account for which the credentials are requested in a delegated request. With the list of delegated service accounts in DelegatedServiceAccounts, this property is used to make a delegated request.
You must have the IAM permission iam.serviceAccounts.getAccessToken on this service account.
This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate. |
| OAuthJWTIssuer | The issuer of the Java Web Token. |
| OAuthJWTSubject | The user subject for which the application is requesting delegated access. |
The JWT Certificate store.
string
""
The name of the certificate store for the client certificate.
The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.
OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| MY | A certificate store holding personal certificates with their associated private keys. |
| CA | Certifying authority certificates. |
| ROOT | Root certificates. |
| SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).
The type of key store containing the JWT Certificate.
string
"PEMKEY_BLOB"
This property can take one of the following values:
| USER | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
| BCFKSFILE | The certificate store is the name of a file that contains an Bouncy Castle keystore. |
| BCFKSBLOB | The certificate store is a string (base-64-encoded) that contains a Bouncy Castle keystore. |
| GOOGLEJSON | The certificate store is the name of a JSON file containing the service account information. Only valid when connecting to a Google service. |
| GOOGLEJSONBLOB | The certificate store is a string that contains the service account JSON. Only valid when connecting to a Google service. |
The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
string
""
This property specifies the password needed to open the certificate store, but only if the store type requires one. To determine if a password is necessary, refer to the documentation or configuration for your specific certificate store.
This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.
The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.
string
"*"
The value of this property is used to locate a matching certificate in the store. The search process works as follows:
You can set the value to '*' to automatically select the first certificate in the store. The certificate subject is a comma-separated list of distinguished name fields and values. For example: CN=www.server.com, OU=test, C=US, [email protected]. Common fields include:
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma, enclose it in quotes. For example: "O=ACME, Inc.".
The issuer of the Java Web Token.
string
""
The issuer of the Java Web Token. Enter the value of the service account email address.
This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys contain a copy of the issuer account.
The user subject for which the application is requesting delegated access.
string
""
The user subject for which the application is requesting delegated access. Enter the email address of the user for which the application is requesting delegated access.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | Specifies the certificate to be accepted from the server when connecting using TLS/SSL. |
Specifies 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 | Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5. |
Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.
string
"1"
This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.
The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.
When combined with the LogModules property, Verbosity can refine logging to specific categories of information.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| BrowsableSchemas | Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC . |
Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
string
""
Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| AWSWorkloadIdentityConfig | Configuration properties to provide when using Workload Identity Federation via AWS. |
| MaxRows | Specifies the maximum rows returned for queries without aggregation or GROUP BY. |
| PseudoColumns | Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property. |
| Timeout | Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
| WorkloadPoolId | The ID of your Workload Identity Federation pool. |
| WorkloadProjectId | The ID of the Google Cloud project that hosts your Workload Identity Federation pool. |
| WorkloadProviderId | The ID of your Workload Identity Federation pool provider. |
Configuration properties to provide when using Workload Identity Federation via AWS.
string
""
The properties are formatted as a semicolon-separated list of Key=Value properties, where the value is optionally quoted.
For example, this setting authenticates in AWS using a user's root keys:
AWSWorkloadIdentityConfig="AuhtScheme=AwsRootKeys;AccessKey='AKIAABCDEF123456';SecretKey=...;Region=us-east-1"
Specifies the maximum rows returned for queries without aggregation or GROUP BY.
int
-1
This property sets an upper limit on the number of rows the Cloud returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.
When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.
This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.
Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
string
""
This property allows you to define which pseudocolumns the Cloud exposes as table columns.
To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"
To include all pseudocolumns for all tables use: "*=*"
Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
int
60
This property controls the maximum time, in seconds, that the Cloud waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the Cloud cancels the operation and throws an exception.
The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.
Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.
Boolean determining if simple names should be used for tables and columns.
bool
false
Google Calendar 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.
The ID of your Workload Identity Federation pool.
string
""
The ID of your Workload Identity Federation pool.
The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
string
""
The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
The ID of your Workload Identity Federation pool provider.
string
""
The ID of your Workload Identity Federation pool provider.