CData Cloud offers access to Google Directory 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 Directory through CData Cloud.
CData Cloud allows you to standardize and configure connections to Google Directory 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 Directory 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 Directory and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Google Directory through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Google Directory 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 Directory:
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, re-set the following properties to connect:
Option 2: Transfer OAuth Settings
Prior to connecting on a headless machine, you must 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.
(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Google Directory".)
However, you must create a custom OAuth application to connect to Google Directory 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 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 Google Directory APIs as relational tables, views, and stored procedures. API limitations and requirements for the available objects are documented in this section.
The Cloud provider models the Google Directory API as relational Tables.
Views offer additional information from Google Directory.
Stored Procedures are function-like interfaces to the data source.
The Cloud models the data in Google Directory as a list of tables in a relational database that can be queried using standard SQL statements.
| Name | Description |
| ChromeOsDevices | Retrieve all Chrome devices for an account. |
| DomainAliases | Create, update, and query aliases of a domain. |
| Domains | Create, delete, and query the domains for a user. |
| GroupAliases | Create, delete, and query aliases for a group. |
| GroupMembers | Create, update, delete, and query the members for a group. |
| Groups | Create, update, delete, and query groups. |
| Notifications | Update, delete, and query notifications for a customer. |
| OrganizationUnits | Create, update, delete, and query the organization units for a customer. |
| RoleAssignments | Create, delete, and query roles assigned to users. |
| Tokens | Query and delete tokens for a user. |
| UserAliases | Lists aliases, which are alternative email addresses for a user. |
| Users | Query user information. |
Retrieve all Chrome devices for an account.
To get a list of all Chrome devices for an account, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM ChromeOsDevices
INSERTs are not supported for this table.
Updates a device's annotatedUser, annotatedLocation, or notes properties. To update a notification, the following columns are required: CustomerId and Id. If not specified, the CustomerId of the current account will be used.
UPDATE ChromeOsDevices SET AnnotatedUser = 'User_2' WHERE Id = '12345' AND CustomerId = '1234'.
Deletes are not supported for this table.
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The unique ID of the Chrome device. |
| SerialNumber | String | True |
The Chrome device serial number entered when the device was enabled. This value is the same as the Admin console's Serial Number in the Chrome OS Devices tab. |
| Model | String | True |
The device's model information. If the device does not have this information, this property is not included in the response. |
| MEID | String | True |
The Mobile Equipment Identifier (MEID) for the 3G mobile card in a mobile device. A MEID is typically used when adding a device to a wireless carrier's post-pay service plan. If the device does not have this information, this property is not included in the response. |
| LastSync | Datetime | True |
The date and time the device was last enrolled. |
| AnnotatedUser | String | False |
The user of the device as noted by the administrator. Maximum length is 100 characters. Empty values are allowed. |
| AnnotatedLocation | String | False |
The address or location of the device as noted by the administrator. Maximum length is 200 characters. Empty values are allowed. |
| AnnotatedAssetId | String | False |
The asset identifier as noted by an administrator or specified during enrollment. |
| Notes | String | False |
Notes about this device added by the administrator. This property can be searched with the list method's query parameter. Maximum length is 500 characters. Empty values are allowed. |
| OrgUnitPath | String | False |
The full parent path with the organizational unit's name associated with the device. Path names are case insensitive. If the parent organizational unit is the top-level organization, it is represented as a forward slash, /. This property can be updated using the API |
| OrderNumber | String | True |
The device's order number. Only devices directly purchased from Google have an order number. |
| MacAddress | String | True |
The device's wireless MAC address. If the device does not have this information, it is not included in the response. |
| WillAutoRenew | Boolean | True |
Determines if the device will auto renew its support after the support end date. This is a read-only property. |
| OsVersion | String | True |
The Chrome device's operating system version. |
| PlatformVersion | String | True |
The Chrome device's platform version. |
| FirmwareVersion | String | True |
The Chrome device's firmware version. |
| BootMode | String | True |
The boot mode for the device. |
| LastEnrollmentTime | String | True |
The date and time the device was last enrolled. |
| TmpVersionInfoAggr | String | True |
Trusted Platform Module (TPM). |
| ActiveTimeRangesAggr | String | True |
List of active time ranges. |
| RecentUsersAggr | String | True |
List of recent device users, in descending order, by last login time. |
| DeviceFilesAggr | String | True |
List of device files to download. |
| ETag | String | True |
ETag of the resource. |
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 |
| CustomerId | String |
Id of the customer |
Create, update, and query aliases of a domain.
To get a list of all the aliases for a domain, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM DomainAliases
To insert an alias, the following columns are required: CustomerId, ParentDomain, and DomainAliasName.
INSERT INTO DomainAliases (CustomerId, DomainAliasName, ParentDomain) VALUES ('12345', 'Alias', 'parentdomain.com')
UPDATEs are not supported for this table.
To delete an alias, the following columns are required: CustomerId and DomainAliasName.
DELETE FROM DomainAliases WHERE CustomerId = 'C020vaw0q' AND DomainAliasName = 'Alias'
| Name | Type | ReadOnly | Description |
| DomainAliasName [KEY] | String | False |
The domain alias name. |
| ParentDomain | String | False |
The parent domain name that the domain alias is associated with. |
| IsVerified | Boolean | True |
Indicates the verification state of a domain alias. |
| CreationDate | Timestamp | True |
Creation date timestamp of the domain alias in milliseconds. |
| ETag | String | True |
ETag of the resource |
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 |
| CustomerId | String |
Id of the customer |
Create, delete, and query the domains for a user.
To get a list of all the domains, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used, as in the following query.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Domains
To insert a domain, the following columns are required: CustomerId and DomainName.
INSERT INTO Domains (CustomerId, DomainName) VALUES ('12345', 'exampledomain.com')
UPDATEs are not supported for this table.
To delete a domain, the DomainName column is required.
DELETE FROM Domains WHERE DomainName = 'exampledomain.com'
| Name | Type | ReadOnly | Description |
| DomainName [KEY] | String | False |
The domain name. |
| IsPrimary | Boolean | True |
Indicates if the domain is a primary domain. |
| IsVerified | Boolean | True |
Indicates the verification state of a domain. |
| CreationDate | Datetime | True |
The creation date of the domain. |
| Aliases | String | True |
The aliases of the domain. |
| ETag | String | True |
ETag of the resource. |
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 |
| CustomerId | String |
Id of the customer |
Create, delete, and query aliases for a group.
To get a list of all the aliases for a group, the GroupId column is required. If not specified, the GroupId of the first group from the Groups table will be used.
The following query shows the only filter processed server side by the Google Directory API:
SELECT * FROM GroupAliases WHERE GroupId = '12345'
To insert an alias, the following columns are required: GroupId and Alias.
INSERT INTO GroupAliases (GroupId, Alias) VALUES ('12345', 'Alias')
UPDATEs are not supported for this table.
To delete an alias, the following columns are required: GroupId and Alias.
DELETE FROM GroupAliases WHERE GroupId = '12345' AND Alias = 'Alias'
| Name | Type | ReadOnly | Description |
| Alias [KEY] | String | False |
The alias email address. |
| GroupId | String | True |
Id of the group. |
| PrimaryEmail | String | True |
PrimaryEmail of the group. |
| ETag | String | True |
ETag of the resource. |
Create, update, delete, and query the members for a group.
To get a list of all the members of a group, the GroupId column is required. If not specified, the Id of the first group from the Groups table will be used.
The following query shows the only filter processed server side by the Google Directory API:
SELECT * FROM GroupMembers WHERE GroupId = '12345'
To insert a member, the following columns are required: Email and GroupId.
The Role column only accepts the following values: MEMBER, MANAGER, and OWNER.
INSERT INTO GroupMembers (Email, GroupId, Role) VALUES ('[email protected]', '12345', 'MEMBER')
To update a member, the following columns are required: GroupId and Id.
UPDATE GroupMembers SET Role = 'MEMBER' WHERE GroupId = '1234' AND Id = '12345'
To delete a member, the following columns are required: GroupId and Id.
DELETE FROM GroupMembers WHERE GroupId = '1234' AND Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | False |
The unique identifier for the member. |
| GroupId | String | True |
The unique identifier for the member. |
| String | False |
The email of the member. | |
| Role | String | False |
The name of the member. |
| Status | String | True |
The status of the member. |
| Type | String | True |
The type of members. |
| ETag | String | True |
ETag of the resource |
Create, update, delete, and query groups.
To get a list of all the groups, the CustomerId is required. You can either set it in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory API:
SELECT * FROM Groups
To insert a group, the Email column is required.
INSERT INTO Groups (Email, Name, Description) VALUES ('[email protected]', 'Group Example Name', 'Example Description')
To update a group, the Id is required.
UPDATE Groups SET Email = '[email protected]', Name = 'Group', Description = 'Description' WHERE Id = 1231
To delete a group, the Id column is required.
DELETE FROM Groups WHERE Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The unique identifier for the group. |
| String | False |
The email of the group. | |
| Name | String | False |
The name of the group. |
| MembersCount | Long | True |
The number of members. |
| Description | String | False |
Description of the group. |
| Aliases | String | True |
Aliases of the group. |
| AdminCreated | Boolean | True |
Indicates if the group was created by an admin. |
| ETag | String | True |
ETag of the resource |
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 |
| CustomerId | String |
The customer Id of the group. |
| Domain | String |
Domain name. |
Update, delete, and query notifications for a customer.
To get a list of all the notifications, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Notifications
INSERTs are not supported for this table.
To update a notification, the following columns are required: CustomerId and Id. If not specified, the CustomerId of the current account will be used.
UPDATE Notifications SET IsUnread = true WHERE Id = '12345' AND CustomerId = '1234'.
To delete a notification, the following columns are required: CustomerId and Id. If not specified, the CustomerId of the current account will be used.
DELETE FROM Notifications WHERE CustomerId = '1234' AND Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
Id of the notification |
| Subject | String | False |
Subject of the notification |
| Body | String | True |
The body of the notification |
| SendDate | Datetime | True |
The date when the notification was sent |
| FromAddress | String | True |
The address from which the notification is recieved |
| IsUnread | Boolean | False |
Indicates wether the notification is unread or not |
| ETag | String | True |
ETag of the resource |
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 |
| CustomerId | String |
Id of the customer |
Create, update, delete, and query the organization units for a customer.
To get a list of all the organization units, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM OrganizationUnits
To insert an organization unit, the following columns are required: CustomerId, Name, and ParentOrgUnitId. If not specified, the CustomerId of the current account will be used.
INSERT INTO OrganizationUnits (CustomerId, Name, Description, OrgUnitPath, ParentOrgUnitId, ParentOrgUnitPath) VALUES ('12345', 'OrgUnit Name', 'OrgUnit Description', 'Path', '123456', 'ParentPath', '1234')
To update an organization unit, the following columns are required: CustomerId and Id. If not specified, the CustomerId of the current account will be used.
UPDATE OrganizationUnits SET Name = 'OrgUnit Name', 'Description = 'OrgUnit Description', OrgUnitPath = 'Path', ParentOrgUnitId = '123456', ParentOrgUnitPath = 'ParentPath' WHERE CustomerId = '1234' AND Id = '12345'
To delete an organization unit, the following columns are required: CustomerId and Id. If not specified, the CustomerId of the current account will be used.
DELETE FROM OrganizationUnits WHERE CustomerId = '1234' AND Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
Id of the Organization Unit. |
| Name | String | False |
Name of the Organization Unit. |
| Description | String | False |
Description of the Organization Unit. |
| OrgUnitPath | String | False |
Path of the OrgOrganization Unit.Unit |
| ParentOrgUnitPath | String | False |
Path of the Organization Unit's parent. |
| ParentOrgUnitId | String | False |
Id of the Organization Unit's parent |
| ETag | String | True |
ETag of the resource. |
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 |
| CustomerId | String |
Id of the customer |
Create, delete, and query roles assigned to users.
To get a list of all the roles assigned to users, the CustomerId column is required. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM RoleAssignments
To assign a role to a user, the following columns are required: RoleId, UserId, ScopeType, and CustomerId. If not specified, the CustomerId of the current account will be used.
ScopeType has only two acceptable values : CUSTOMER and ORG_UNIT.
INSERT INTO RoleAssignments (RoleId, UserId, ScopeType) VALUES ('12345', '123456', 'CUSTOMER')
UPDATEs are not supported for this table.
To remove an assigned role from a user, the Id and CustomerId columns are required. If not specified, the CustomerId of the current account will be used.
DELETE FROM RoleAssignments WHERE Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The unique identifier of the role assignment. |
| RoleId | String | False |
The Id of the role that is assigned. |
| UserId | String | False |
The Id of the user this role is assigned to. |
| OrgUnitId | String | False |
If the role is restricted to an organizational unit, this contains the ID for the organizational unit the exercise of this role is restricted to. |
| ScopeType | String | False |
The scope in which this role is assigned. Acceptable values are |
| Etag | String | True |
Etag of the resource. |
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 |
| CustomerId | String |
Id of the customer |
Query and delete tokens for a user.
To get a current set of tokens a specified user has issued to 3rd party applications, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the Id of the first user from the Users table will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Tokens
INSERTs are not supported for this table.
UPDATEs are not supported for this table.
To delete a token, the UserId and Id columns are required.
DELETE FROM Tokens WHERE UserId = '12345' AND Id = '123456'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | False |
The Client ID of the application the token is issued to. |
| UserId | String | False |
Aggregate of child privileges. |
| DisplayText | String | False |
The displayable name of the application the token is issued to. |
| IsAnonymous | Boolean | False |
Indicates if the name of the privilege. |
| IsNativeApp | Boolean | False |
Indicates if the token is issued to an installed application. |
| ScopesAggregate | String | False |
Aggregate of child privileges. |
| Etag | String | False |
Etag of the resource. |
Lists aliases, which are alternative email addresses for a user.
To get a list of all the aliases for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Aliases
To insert an alias, the following columns are required: UserId and Alias.
INSERT INTO Aliases (UserId, Alias) VALUES ('12345', 'Alias')
UPDATEs are not supported for this table.
To delete an alias, the following columns are required: UserId and Alias.
DELETE FROM Aliases WHERE Id = '12345' AND Alias = 'Alias'
| Name | Type | ReadOnly | Description |
| Alias [KEY] | String | False |
The alias email address. |
| UserId | String | True |
Id of the user. |
| PrimaryEmail | String | True |
PrimaryEmail of the user. |
| ETag | String | True |
ETag of the resource. |
Query user information.
To get a list of all the users, CustomerId is required. You can either set it in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Users
To insert a user, the following columns are required: PrimaryEmail, FirstName, Surname, and Password.
INSERT INTO Users (PrimaryEmail, FirstName, Surname, Password, Suspended) VALUES ('[email protected]', 'John', 'Doe', '12345', true)
To update a user, the Id column is required.
UPDATE Users SET PrimaryEmail = '[email protected]', FirstName = 'John', Surname = 'Doe', Suspended = true WHERE Id = 1231
To delete users, the Id column is required.
DELETE FROM Users WHERE Id = '12345'
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The unique identifier for the user. |
| CustomerId | String | True |
The customer Id of the user. |
| PrimaryEmail | String | False |
The primary email of the user. |
| FirstName | String | False |
The first name of the user. |
| Surname | String | False |
The surname of the user. |
| Aliases | String | True |
The aliases of the user. |
| IsAdmin | Boolean | True |
Indicates if the user is an admin. |
| IsDelegatedAdmin | Boolean | True |
Indicates if the user is a delegated admin. |
| LastLoginDate | Datetime | True |
Last time the user logged on. |
| CreationDate | Datetime | True |
Creation date of the user. |
| DeletionDate | Datetime | True |
Deletion date of the user. |
| AgreedToTerms | Boolean | True |
Indicates if the user agreed to the terms or not. |
| Suspended | Boolean | False |
Indicates if the user got supsended. |
| SuspensionReason | String | True |
The reason the user got supsended. |
| OrgUnitPath | String | False |
The full path of the parent organization associated with the user. If the parent organization is the top-level, it is represented as a forward slash (/). |
| IsMailBoxSetup | Boolean | True |
Indicates if the user's Google mailbox is created. This property is only applicable if the user has been assigned a Gmail license. |
| IsEnrolledIn2Sv | Boolean | True |
Indicates if the user is enrolled in 2-step verification. |
| IsEnforcedIn2Sv | Boolean | True |
Indicates if the user is enforced in 2-step verification. |
| IncludeInGlobalAddressList | Boolean | True |
Indicates if the user's profile is visible in the G Suite global address list when the contact sharing feature is enabled for the domain. |
| ThumbnailPhotoUrl | String | True |
Photo Url of the user |
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 |
| Password | String |
The password of the user. |
| Domain | String |
Domain name |
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 |
| AppSpecificPasswords | Lists all Application Specific Passwords (passwords that are used with applications that do not accept verification codes) issued by a user. |
| MobileDevices | Lists mobile devices for an account. |
| Privileges | Lists all Privileges. |
| Roles | Lists roles in a domain. |
| UserAddresses | Lists the addresses for a user. |
| UserEmails | Query the emails for a user. |
| UserInstantMessagingAccounts | Query the IM accounts for a user. |
| UserLocations | Query the locations for a user. |
| UserOrganizations | Query the organizations for a user. |
| UserPhones | Query the phone numbers for a user. |
| UserWebsites | Retrieve a list of the websites of a user. |
| VerificationCodes | Query verification codes for a user. |
Lists all Application Specific Passwords (passwords that are used with applications that do not accept verification codes) issued by a user.
To get a list of all the application specific tokens issued by a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the Id of the first user from the Users table will be used.
For example, the following query is processed server side by the Google Directory API:
SELECT * FROM AppSpecificPasswords
| Name | Type | Description |
| Id [KEY] | String | The unique identifier of the ASP. |
| UserId | String | The unique identifier of the user who issued the ASP. |
| Name | String | Name of the ASP. |
| CreationDate | Datetime | The date when the ASP was created. |
| LastTimeUsed | Datetime | The time when the ASP was last used. |
| Etag | String | Etag of the resource. |
Lists mobile devices for an account.
To get a list of all mobile devices for an account, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM MobileDevices
| Name | Type | Description |
| Id [KEY] | String | The serial number for a Google Sync mobile device. For Android and iOS devices, this is a software generated unique identifier. |
| ResourceId | String | The unique ID the API service uses to identify the mobile device. |
| Name | String | List of the owner's usernames. |
| AccountsList | String | List of accounts added on device. |
| String | List of the owner's emails. | |
| Model | String | The mobile device's model name. |
| OS | String | The mobile device's operating system |
| Type | String | The type of mobile device. |
| Status | String | The device's status. |
| HardwareId | String | The IMEI/MEID unique identifier for Android hardware. |
| FirstSyncDate | Datetime | The date and time the device was initially synchronized with the policy settings in the Admin console. |
| LastSyncDate | Datetime | The date and time the device was last synchronized with the policy settings in the Admin console. |
| UserAgent | String | Gives information about the device such as os version. |
| SerialNumber | String | The device's serial number. |
| IMEI | String | The device's IMEI number. |
| MEID | String | The device's MEID number. |
| WiFiMacAddress | String | The device's MAC address on Wi-Fi networks. |
| NetworkOperator | String | Mobile Device mobile or network operator. |
| DefaultLanguage | String | The default langauge used on the device. |
| DeviceCompromisedStatus | String | The compromised device status. |
| BuildNumber | String | The device's operating system build number. |
| KernelVersion | String | The device's kernel version. |
| BasebandVersion | String | The device's baseband version. |
| Manufacturer | String | The device's manufacturer. |
| ReleaseVersion | String | Mobile Device release version version. |
| SecurityPatchLevel | String | The device's security patch level. |
| Brand | String | The device's brand. |
| BootloaderVersion | String | The device's bootloader version. |
| Hardware | String | The device's hardware. |
| EncryptionStatus | String | The device's encryption status. |
| DevicePasswordStatus | String | The device's password status |
| Privilege | String | DMAgentPermission. |
| UnknownSourcesStatus | Boolean | Indicates if unknown sources are enabled or disabled on device |
| AdbStatus | Boolean | Indicates if adb(USB debugging) is enabled or disabled on device |
| IsOnOwnerProfile | Boolean | Indicates if this account is on owner/primary profile or not. |
| SupportsWorkProfile | Boolean | Indicates if work profile is supported on device. |
| Etag | String | Etag of the resource |
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 |
| CustomerId | String | The Id of the customer |
Lists all Privileges.
To get a list of all privileges for an account, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Privileges
| Name | Type | Description |
| ServiceId [KEY] | String | The obfuscated ID of the service this privilege is for. |
| ServiceName [KEY] | String | The name of the service this privilege is for. |
| PrivilegeName | String | The name of the privilege. |
| ParentServiceId | String | The service Id of the parent privilege. |
| ParentPrivilegeName | String | The privilege name of the parent privilege. |
| IsOrganizationUnitRestrictable | Boolean | Indicates if the privilege can be restricted to an organization unit. |
| Etag | String | Etag of the resource. |
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 |
| CustomerId | String | Id of the customer |
Lists roles in a domain.
To get a list of all the roles, the CustomerId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the CustomerId of the current account will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM Roles
| Name | Type | Description |
| Id [KEY] | String | The unique identifier for the role. |
| Name | String | Name of the role. |
| Description | String | A short description of the role. |
| PrivilegeName | String | The name of the privilege. |
| ServiceId | String | The ID of the service the privilege is for. |
| IsSystemRole | Boolean | Indicates if it is a pre-defined system role. |
| IsSuperAdminRole | Boolean | Indicates if the role is a super admin role. |
| Etag | String | Etag of the resource. |
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 |
| CustomerId | String | Id of the customer |
Lists the addresses for a user.
To get a list of addresses for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the UserId of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserAddresses
| Name | Type | Description |
| UserId | String | The unique identifier for the user. |
| Type | String | The address type. |
| CustomType | String | The custom type of the address. |
| FormattedAddress | String | The full unstructured postal address. |
| PoBox | String | Post office box of the address. |
| ExtendedAddress | String | The extended address |
| StreetAddress | String | The street address |
| Locality | String | The town or city of the address. |
| Region | String | The abbreviated province or state. |
| PostalCode | String | The ZIP or postal code, if applicable. |
| Country | String | Country in the address. |
| CountryCode | String | The country code. Uses the ISO 3166-1 standard. |
| IsPrimary | Boolean | Indicates if this is the primary address of the user |
Query the emails for a user.
To get a list of email addresses for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserEmails
| Name | Type | Description |
| Address | String | The user's email address |
| UserId | String | The unique identifier for the user. |
| IsPrimary | String | Indicates if this is the user's primary email. |
| CustomType | String | The custom type of the email. |
| Type | String | The type of the email account. |
Query the IM accounts for a user.
To get a list of IM accounts for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserInstantMessagingAccounts
| Name | Type | Description |
| IM | String | The user's IM network ID. |
| UserId | String | The unique identifier for the user. |
| Protocol | String | The IM protocol identifies the IM network. |
| CustomProtocol | String | The custom type of the IM protcol. |
| IsPrimary | String | Indicates if this is the user's primary IM. |
| CustomType | String | The custom type of the IM account. |
| Type | String | The type of the IM account. |
Query the locations for a user.
To get a list of locations for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserLocations
| Name | Type | Description |
| Area | String | Textual location of the user. |
| UserId | String | The unique identifier for the user. |
| BuildingId | String | The building identifier. |
| DeskCode | String | The desk location. |
| FloorName | String | The floor name/number |
| FloorSection | String | The floor section. |
| CustomType | String | The custom type of the location. |
| Type | String | The type of the location. |
Query the organizations for a user.
To get a list of organizations for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserOrganizations
| Name | Type | Description |
| Name | String | The name of the organization. |
| UserId | String | The unique identifier for the user. |
| OrganizationDomain | String | The domain the organization belongs to. |
| Department | String | Specifies the department within the organization. |
| Description | String | The description of the organization. |
| Title | String | The user's title within the organization. |
| CostCenter | String | The cost center of the user's organization. |
| Location | String | The physical location of the organization. |
| IsPrimary | Boolean | Indicates if this is the user's primary organization. |
| Symbol | String | Text string symbol of the organization. |
| Type | String | Country in the address. |
| CustomType | String | If the value of type is custom, this property contains the custom type. |
Query the phone numbers for a user.
To get a list of phones for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserPhones
| Name | Type | Description |
| PhoneNumber | String | The user's phone number. |
| UserId | String | The unique identifier for the user. |
| IsPrimary | String | Indicates if this is the user's primary IM. |
| CustomType | String | The custom type of the phone number. |
| Type | String | The type of the phone number. |
Retrieve a list of the websites of a user.
To get a list of websites for a user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. Otherwise, the Cloud will automatically use the Id of the first user from the Users table.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM UserWebsites
| Name | Type | Description |
| URL | String | The URL of the website. |
| UserId | String | The unique identifier for the user. |
| CustomType | String | The custom type of the website. |
| Type | String | The type of the website. |
| IsPrimary | Boolean | Indicates if this is the user's primary website or not |
Query verification codes for a user.
To get a current set of valid backup verification codes for a specified user, the UserId column is required. It can be set in the connection string or in the WHERE clause condition. If not specified, the Id of the first user from the Users table will be used.
For example, the following query is processed server side by the Google Directory APIs:
SELECT * FROM VerificationCodes
| Name | Type | Description |
| UserId | String | The unique ID of the user. |
| VerificationCode | String | A current verification code for the user. |
| Etag | String | Etag of the resource. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Google Directory.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Google Directory, along with an indication of whether the procedure succeeded or failed.
| Name | Description |
| ChangeAdminStatus | Change the admin status of a user. |
| GenerateVerificationCodes | Generate verification codes for a user. |
| InvalidateVerificationCodes | Invalidate verification codes for a user. |
Change the admin status of a user.
| Name | Type | Description |
| UserId | String | Id of the user. The value can be the user's primary email address, alias email address, or unique user ID. |
| Status | String | New admin status of the user. |
Generate verification codes for a user.
| Name | Type | Description |
| UserId | String | Id of the user. |
Invalidate verification codes for a user.
| Name | Type | Description |
| UserId | String | Id of the user. |
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 Directory:
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 Group table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Group'
| 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 Group table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Group'
| 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:googledirectory: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 type of authentication to use when connecting to Google Directory. |
| Property | Description |
| Scope | Specify scope to obtain the initial access and refresh token. |
| 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. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
| OAuthJWTIssuer | The issuer of the Java Web Token. |
| OAuthJWTSubject | The user subject for which the application is requesting delegated access. |
| 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 |
| CustomerId | Restrict query results to this customer. |
| Domain | Restrict queries to this domain. |
| GroupId | Restrict query results to this group. |
| 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 Google Directory. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserId | Restrict query results to this user. |
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 Directory. |
The type of authentication to use when connecting to Google Directory.
string
"Auto"
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| Scope | Specify scope to obtain the initial access and refresh token. |
Specify scope to obtain the initial access and refresh token.
string
""
Specify scope to obtain the initial access and refresh token.
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. |
| OAuthJWTCertSubject | The subject of the OAuth JWT 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
"GOOGLEJSONBLOB"
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. |
| 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.
string
""
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.
The subject of the OAuth JWT certificate.
string
"*"
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.
| 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 it must be quoted.
The issuer of the Java Web Token.
string
""
The issuer of the Java Web Token. Enter the value of a delegated user Email Address.
This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys contain a copy of the issuer account.
The issuer of the Java Web Token. Enter the value of a delegated user 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 value of the Service Account Email.
The user subject for which the application is requesting delegated access. Enter the value of the Service Account Email.
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 |
| CustomerId | Restrict query results to this customer. |
| Domain | Restrict queries to this domain. |
| GroupId | Restrict query results to this group. |
| 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 Google Directory. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserId | Restrict query results to this user. |
Restrict query results to this customer.
string
"my_customer"
This property can be set in the connection string or query. Otherwise, the Cloud will use the Customer Id of the authenticated user. You can also get this value from the Users table.
Restrict queries to this domain.
string
""
The domain name (e.g., cdata.com). Use this connection property to get results from only one domain.
Restrict query results to this group.
string
""
This property must be set in the connection string or query. Otherwise, the Cloud will use the first found Group Id. You can get this value from the Groups table.
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 Google Directory.
int
100
The Pagesize property affects the maximum number of results to return per page from Google Directory. 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, "*=*".
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.
Restrict query results to this user.
string
""
The Id of the user. If not specified, the first user from the Users table will be used.