CData Cloud は、クラウドホスト型のソリューションで、複数の標準サービスやプロトコルにまたがるSalesforce Marketing Cloud Account Engagement へのアクセスを実現します。MySQL またはSQL Server データベースに接続できるアプリケーションであれば、CData Cloud を介してSalesforce Marketing Cloud Account Engagement に接続できます。
CData Cloud により、他のOData エンドポイントや標準SQL Server / MySQL データベースと同じように、Salesforce Marketing Cloud Account Engagement への接続を標準化し、構成することができます。
このページでは、CData Cloud でのSalesforce Marketing Cloud Account Engagement への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。
接続の確立 は、CData Cloud にデータベースを作成するためのSalesforce Marketing Cloud Account Engagement への認証方法と必要な接続プロパティの設定方法について示します。
利用可能な標準サービスを経由してSalesforce Marketing Cloud Account Engagement からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。
Database タブで対応するアイコンを選択して、Salesforce Marketing Cloud Account Engagement に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。
Pardot ビジネスユニットID は「0Uv」で始まる18 文字です。Pardot アカウント設定情報にアクセスできない場合は、Salesforce 管理者に問い合わせてPardot ビジネスユニットID を入手してください。
AuthScheme は、すべてのユーザーアカウントフローでOAuth に設定する必要があります。
認証に関する2つの方法の違いは、カスタムOAuth アプリケーションを使用する場合に、2つの接続プロパティを追加で設定する必要があることだけです。
次の接続プロパティを設定して、接続してください。
初めに、次の接続プロパティを設定してOAuthAccessToken を取得します。
続いてストアドプロシージャを呼び出し、OAuth 交換を完了します。
アクセストークンとリフレッシュトークンを取得した後、データに接続し、OAuth アクセストークンを自動または手動でリフレッシュすることが可能です。
OAuth アクセストークンの自動リフレッシュ
ドライバーがOAuth アクセストークンを自動的にリフレッシュするようにするには、最初のデータ接続で次のように設定します。
OAuth アクセストークンの手動リフレッシュ
データ接続時に手動でOAuth アクセストークンをリフレッシュするために必要な値は、OAuth リフレッシュトークンのみです。
GetOAuthAccessToken によって返されたExpiresIn パラメータ値が経過した後に、RefreshOAuthAccessToken ストアドプロシージャを使用し、手動でOAuthAccessToken をリフレッシュします。次の接続プロパティを設定します。
次に、RefreshOAuthAccessToken を呼び出し、OAuthRefreshToken にGetOAuthAccessToken によって返されたOAuth リフレッシュトークンを指定します。新しいトークンが取得できたら、OAuthAccessToken プロパティにRefreshOAuthAccessToken によって返された値を設定し、新しい接続をオープンします。
最後に、OAuth リフレッシュトークンを保存し、OAuth アクセストークンの有効期限が切れた後に手動でリフレッシュできるようにします。
オプション1:Verifier code を取得および交換
Verifier code を取得するには、OAuth Authorization URL で認証する必要があります。
インターネットブラウザに対応したマシンから認証してOAuthVerifier 接続プロパティを取得する方法は次のとおりです。
ヘッドレスマシンでは、次の接続プロパティを設定してOAuth 認証値を取得します。
OAuth 設定ファイルが生成されたら、以下のように接続プロパティをリセットする必要があります。
オプション2:OAuth 設定を転送
ヘッドレスマシンでの接続に先立ち、インターネットブラウザに対応したデバイスでドライバとの接続をインストールし、作成する必要があります。上述の「デスクトップアプリケーション」の説明に従って、接続プロパティを設定します。
「デスクトップアプリケーション」の手順が完了すると、生成された認証値は、OAuthSettingsLocation で指定された場所に暗号化されて書き込まれます。デフォルトのファイル名はOAuthSettings.txt です。
接続テストに成功したら、OAuth 設定ファイルをヘッドレスマシンにコピーします。
ヘッドレスマシンで、次の接続プロパティを設定し、データに接続します。
ユーザー名とパスワードで認証する場合は、以下の接続プロパティを設定します。
AuthScheme をOAuthJWT に設定します。
OAuth アプリケーション(カスタムOAuth アプリケーションの作成 参照)作成後、次の接続プロパティを設定します。
Note:このフローではリフレッシュトークンは発行されません。
デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。
別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。
Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。
さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。
次のプロパティを設定します。
This section shows the available API objects and provides more information on executing SQL to Salesforce Marketing Cloud Account Engagement APIs.
The CData Cloud models Salesforce Marketing Cloud Account Engagement entities 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.
Cloud はSalesforce Marketing Cloud Account Engagement のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。
Name | Description |
Campaigns | Retrieve and modify campaigns. |
CustomFields | Retrieve and modify custom fields. |
ListMemberships | Retrieve and modify list memberships. |
Lists | Retrieve and modify lists. |
Opportunities | Retrieve and modify opportunities. |
ProspectAccounts | Retrieve and modify prospect accounts. |
Prospects | Retrieve and modify prospects. |
Retrieve and modify campaigns.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Campaigns WHERE Id = 43 SELECT * FROM Campaigns WHERE Name = 'Website Tracking'
This table does not have any required attributes when performing an insert.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this campaign. | |
Name | String | False |
Campaign's name. | |
Cost | Integer | False |
Cost associated to the campaign. | |
CrmFId | String | True |
The CRM FID associated to the campaign. |
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
CreatedAt | Datetime |
Filter by the creation time. |
UpdatedAt | Datetime |
Filter by the modification time. |
Retrieve and modify custom fields.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM CustomFields WHERE Id = 50 SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2019'
The following attributes are required when performing an insert: FieldId, Name.
INSERT INTO CustomFields (FieldId, Name) VALUES (100, 'Example')
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this custom field. | |
Name | String | False |
Custom field's name. | |
FieldId | String | True |
API ID for custom field. | |
Type | String | False |
type of field. | |
TypeId | Int64 | True |
Pardot ID for custom field's type. | |
CreatedAt | Datetime | True |
Time custom field was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time custom field was updated. | |
IsRecordMultipleResponses | Boolean | False |
If true, this custom field will record multiple responses. | |
CrmId | String | False |
The CRM ID of the field you would like to map to this custom field. | |
IsUseValues | Boolean | False |
If true, this custom field will use predefined values. |
Retrieve and modify list memberships.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM ListMemberships WHERE Id = 26 SELECT * FROM ListMemberships WHERE ListId = 44 SELECT * FROM ListMemberships WHERE CreatedAt > '01/01/2019'
The following attributes are required when performing an insert: ListId, ProspectId.
INSERT INTO ListMemberships (ListId, ProspectId) VALUES (20104, 3457990)
To retrieve deleted records, a GETDELETED statement can be executed.
The GETDELETED statement supports the same server side filters that are supported by the SELECT statement (except ID when using the equality(=) operator), but does not perform any client side filtering.
GETDELETED FROM ListMemberships WHERE ID = 231
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this list membership. | |
ListId | Int64 | True |
Lists.Id |
Pardot ID of the list for this membership. |
ProspectId | Int64 | True |
Prospects.Id |
Pardot ID of the prospect for this membership. |
OptedOut | Integer | False |
If value is 1, the prospect is unsubscribed from receiving emails from this list. | |
CreatedAt | Datetime | True |
Time that this membership was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time that this membership was updated. |
Retrieve and modify lists.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID of this list. | |
Name | String | False |
List's name (internal to Pardot). | |
IsPublic | Boolean | False |
If true, list will show on EPC pages to prospects. | |
IsDynamic | Boolean | True |
If true, list has prospects dynamically added to it via a set of chosen rules. | |
Title | String | False |
List's title (visible to subscribers). | |
Description | String | False |
List's description. | |
IsCrmVisible | Boolean | False |
If true, list will be visible in CRM to add or remove from. | |
CreatedAt | Datetime | True |
Time list was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time list was updated. |
Retrieve and modify opportunities.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Opportunities WHERE Id = 14 SELECT * FROM Opportunities WHERE ProspectId = 34 SELECT * FROM Opportunities WHERE ProspectEmail = '[email protected]'
The following attributes are required when performing an insert: CampaignId, Name, Value, Probability, ProspectEmail, ProspectId.
INSERT INTO Opportunities (CampaignId, Name, Value, Probability, ProspectEmail, ProspectId) VALUES (1, 'Example', 3, 75, '[email protected]', 2)
Note: This table cannot be updated if a Salesforce connector is present.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this opportunity. | |
CampaignId | Int64 | False |
Campaigns.Id |
Pardot ID of the campaign associated with this opportunity Note: Information about an opportunity's campaign association is returned in a campaign node in the XML response. However, updates to campaign associations are done by providing campaign_id during an UPDATE> request. See XML Response Formats in Using Opportunities for more details. |
Name | String | False |
Opportunity's name. | |
Value | Double | False |
Opportunity's value Restrictions: value must be a positive numeric value. | |
Probability | Integer | False |
Opportunity's probability Restrictions: value must be a positive numeric value between 0 and 100 inclusive. | |
Type | String | False |
Opportunity's type. | |
Stage | String | False |
Opportunity's stage. | |
Status | String | False |
Opportunity's status Restrictions: status must be either won, lost, or open. | |
ClosedAt | Datetime | False |
Opportunity's closed date Note: if this is left blank, the closed_at datetime (Closed Date within the app) will not be set, even when the Opportunity's stage, status or probability are set to indicate opportunity closure. | |
CreatedAt | Datetime | True |
Time opportunity was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time opportunity was updated in Pardot. | |
ProspectEmail | String | False |
Email of the prospect. | |
ProspectId | Int64 | False |
Id of the prospect. |
Retrieve and modify prospect accounts.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID of the prospect account. | |
CreatedAt | Datetime | False |
Filter by the creation time. | |
UpdatedAt | Datetime | False |
Filter by the modification time. |
Retrieve and modify prospects.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int63 | True |
Pardot ID for this prospect. | |
CampaignId | Int63 | False |
Campaigns.Id |
Pardot ID of the campaign associated with this prospect Note: Information about a prospect's campaign association is returned in a campaign node in the XML response. However, updates to campaign associations are done by providing campaign_id during an UPDATE> request. See XML Response Formats in Using Prospects for more details. |
Salutation | String | False |
Prospect's formal prefix. | |
FirstName | String | False |
Prospect's first name. | |
LastName | String | False |
Prospect's last name. | |
String | False |
Prospect's email address. | ||
Password | String | False |
Prospect's password. | |
Company | String | False |
Prospect's company. | |
ProspectAccountId | Int63 | False |
Prospect's account ID. | |
Website | String | False |
Prospect's website URL. | |
JobTitle | String | False |
Prospect's job title. | |
Department | String | False |
Prospect's department. | |
Country | String | False |
Prospect's country. | |
AddressOne | String | False |
Prospect's address, line 1. | |
AddressTwo | String | False |
Prospect's address, line 2. | |
City | String | False |
Prospect's city. | |
State | String | False |
Prospect's US state. | |
Territory | String | False |
Prospect's territory. | |
Zip | String | False |
Prospect's postal code. | |
Phone | String | False |
Prospect's phone number. | |
Fax | String | False |
Prospect's fax number. | |
Source | String | False |
Prospect's source. | |
AnnualRevenue | String | False |
Prospect's annual revenue. | |
Employees | String | False |
Prospect's number of employees. | |
Industry | String | False |
Prospect's industry. | |
YearsInBusiness | String | False |
Prospect's number of years in business. | |
Comments | String | False |
Comments about this prospect. | |
Notes | String | False |
Notes about this prospect. | |
Score | Integer | False |
Prospect's score. | |
Grade | String | True |
Prospect's letter grade. | |
LastActivityAt | Datetime | True |
Time stamp of this prospect's latest visitor activity. | |
RecentInteraction | String | True |
Describes the prospect's most recent interaction with Pardot. | |
CrmLeadFid | String | True |
Prospect's lead ID in a supported CRM system. | |
CrmContactFid | String | True |
Prospect's contact ID in a supported CRM system. | |
CrmOwnerFid | String | True |
Prospect's owner ID in a supported CRM system. | |
CrmAccountFid | String | True |
Account ID in a supported CRM system. | |
CrmLastSync | Datetime | True |
Last time this prospect was synced with a supported CRM system. | |
CrmUrl | String | True |
URL to view the prospect within the CRM system. | |
IsDoNotEmail | Boolean | False |
If value is 1, prospect prefers not to be emailed. | |
IsDoNotCall | Boolean | False |
If value is 1, prospect prefers not to be called. | |
OptedOut | Boolean | True |
If value is 1, prospect has opted out of marketing communications. | |
IsReviewed | Boolean | False |
If value is 1, prospect has been reviewed. | |
IsStarred | Boolean | False |
If value is 1, prospect has been starred. | |
CreatedAt | Datetime | True |
Time prospect was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time prospect was updated in Pardot. |
ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。
クエリは、ビューに対して通常のテーブルと同様に実行することができます。
Name | Description |
Account | Retrieve account info. |
CustomRedirects | Retrieve and modify custom redirects. |
DynamicContents | Retrieve dynamic contents. |
EmailClicks | Retrieve email clicks. |
Emails | Retrieve emails. |
EmailStats | Retrieve email stats. |
EmailTemplates | Retrieve email templates. |
Forms | Retrieve forms. |
LifecycleHistories | Retrieve lifecycle histories. |
LifecycleStages | Retrieve lifecycle stages. |
OneToOneEmails | Retrieve one to one emails. |
TagObjects | Retrieve tag objects. |
Tags | Retrieve tags. |
Users | Retrieve users. |
VisitorActivities | Retrieve visitor activities. |
Visitors | Retrieve visitors. |
VisitorsPageViews | Retrieve visitors page views. |
Visits | Retrieve visits. |
Retrieve account info.
The Cloud will process all filters client side.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this account. | |
Level | String | The level of product for the account. | |
Website | String | Account website. | |
VanityDomain | String | Custom vanity domain name. | |
PluginCampaignId | Int64 | Plugin ID for account campaign. | |
TrackingCodeTemplate | String | Markup and code for use in tracking templates. | |
Address1 | String | Account contact address, line 1. | |
Address2 | String | Account contact address, line 2. | |
City | String | Account contact city. | |
State | String | Account contact state. | |
Territory | String | Account contact territory. | |
Zip | String | Account contact zip code. | |
Country | String | Account contact country. | |
Phone | String | Account contact phone number. | |
Fax | String | Account contact fax number. |
Retrieve and modify custom redirects.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this custom redirect. | |
Name | String | Custom redirect's name. | |
Url | String | URL for the custom redirect. | |
Destination | String | URL the custom redirect leads to. | |
CampaignId | Int64 |
Campaigns.Id | The campaign associated with this custom redirect. |
CampaignName | String |
Campaigns.Name | The campaign associated with this custom redirect. |
CreatedAt | Datetime | Time custom redirect was created in Pardot. | |
UpdatedAt | Datetime | Last time custom redirect was updated. |
Retrieve dynamic contents.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM DynamicContents WHERE Id = 32 SELECT * FROM DynamicContents WHERE CreatedAt > '02/03/2019 23:11:33' SELECT * FROM DynamicContents WHERE UpdatedAt > '01/03/2018 23:11:33' AND UpdatedAt <= '02/03/2019 23:11:33'
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this dynamic content. | |
Name | String | Dynamic content's name. | |
EmbedCode | String | Code to embed this dynamic content onto your webpage. | |
EmbedUrl | String | URL to embed this dynamic content. | |
BaseContent | String | The default dynamic content. | |
BasedOn | String | Field that this dynamic content is based on. | |
Variation | String | The variation of content prospect will see based on the field's value Note: Information about a variation is returned in a variation node in the XML response. It contains the value of the field in the comparison tag and the content of the variation in the content tag. | |
CreatedAt | Datetime | Time dynamic content was created in Pardot. | |
UpdatedAt | Datetime | Last time dynamic content was updated. |
Retrieve email clicks.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM EmailClicks WHERE ListEmailId = 20 SELECT * FROM EmailClicks WHERE DripProgramActionId = 42 SELECT * FROM EmailClicks WHERE EmailTemplateId = 26 AND TrackerRedirectId = 31 AND CreatedAt > '02/03/2019 23:11:33'
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this email click. | |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
Url | String | URL of the email click. | |
ListEmailId | Int64 | Pardot ID for the associated list email. Value not present if null. | |
DripProgramActionId | Integer | Pardot ID for the associated drip program action. Value not present if null. | |
EmailTemplateId | Int64 | Pardot ID for the associated email template. Value not present if null. | |
TrackerRedirectId | Int64 | Pardot ID for the associated tracker redirect. Value not present if null. | |
CreatedAt | Datetime | Time that email click occurred. |
Retrieve emails.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following query is processed server side:
SELECT * FROM Emails WHERE Id = 10
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this email. | |
Name | String | Name of this email. | |
Subject | String | Email Subject. | |
Message | String | Contains text and html elements of different formats. | |
CreatedAt | Datetime | Time the Email Was Created. |
Retrieve email stats.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. To query this table, you need to specify the Id. The rest of the filter is executed client side within the Cloud.
For example, the following query is processed server side:
SELECT * FROM EmailStats WHERE Id = 10
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this email. | |
Sent | Integer | Sent status. | |
Delivered | Integer | Delivered status. | |
TotalClicks | Integer | Total clicks. | |
UniqueClicks | Integer | Unique clicks. | |
SoftBounced | Integer | Soft bounces. | |
HardBounced | Integer | Hard bounces. | |
OptOuts | Integer | Opt count. | |
SpamComplains | Integer | Spam complains. | |
Opens | Integer | Opens. | |
UniqueOpens | Integer | Unique opens. | |
DeliveryRate | String | Delivery rate. | |
OpensRate | String | Open rate. | |
ClickThroughRate | String | Click through rate. | |
UniqueClickThroughRate | String | Unique click through rate. | |
ClickOpenRatio | String | Click to open ration. | |
OptOutrate | String | Opt rate. | |
SpamComplaintRate | String | Spam complaint rate. |
Retrieve email templates.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM EmailTemplates WHERE Id = 22
Name | Type | References | Description |
Id | Int64 | Identifier. | |
EmailType | Integer | Email type. | |
Error | Integer | Error. | |
HtmlMessage | String | Html content. | |
IsAutoResponderEmail | Integer | Whether it is an auto responder email. | |
IsDripEmail | Integer | Whether it is a drip email. | |
IsListEmail | Integer | Whether it is a lsit email. | |
IsOneToOneEmail | Integer | Whether it is a one to one email. | |
Name | String | Name | |
SendOptionsReplyToAddressAggregate | String | Reply to addresses. | |
SendOptionsSendFromDataAggregate | String | Send from information. | |
Subject | String | Email subject. | |
TextMessage | String | Email text. | |
TrackedHtmlMessage | String | Tracked html message. | |
TrackedTextMessage | String | Tracked text message. | |
Type | Integer | Email type. |
Retrieve forms.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this form. | |
Name | String | Form's name. | |
CampaignId | Int64 |
Campaigns.Id | Pardot ID of the campaign associated with this form. |
EmbedCode | String | The code used to embed the form on your webpage. | |
CreatedAt | Datetime | Time form was created in Pardot. | |
UpdatedAt | Datetime | Last time form was updated. |
Retrieve lifecycle histories.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM LifecycleHistories WHERE Id = 49 SELECT * FROM LifecycleHistories WHERE Id >= 44 SELECT * FROM LifecycleHistories WHERE CreatedAt <= '02/03/2019 23:11:33'
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID of this lifecycle history. | |
ProspectId | Int64 |
Prospects.Id | Pardot's ID for the prospect in this stage. |
PreviousStageId | Int64 | Pardot ID of the stage this prospect was previously in. | |
NextStageId | Int64 | Pardot ID of the stage this prospect will be in next. | |
SecondsElapsed | Integer | Number of seconds for prospect to get to current stage. | |
CreatedAt | Datetime | Time lifecycle history was created in Pardot. |
Retrieve lifecycle stages.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM LifecycleStages WHERE Id = 18
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID of this lifecycle stage. | |
Name | String | Lifecycle stage's name. | |
Position | Integer | Lifcycle stage's position in lifecycle. | |
IsLocked | Boolean | If true, lifecycle stage is locked. |
Retrieve one to one emails.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM OneToOneEmails WHERE Id = 11
Name | Type | References | Description |
Id [KEY] | Int64 | Identifier. | |
EmailType | Integer | Email type. | |
IsAutoResponderEmail | Integer | Whether it is an auto responder email. | |
IsDripEmail | Integer | Where it is a drip email. | |
IsListEmail | Integer | Whether it is a list email. | |
IsOneToOneEmail | Integer | Whether it is a one to one email. | |
Name | String | Name. | |
Subject | String | Email subject. | |
Type | Integer | Email type. |
Retrieve tag objects.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM TagObjects WHERE Id <= 31 SELECT * FROM TagObjects WHERE TagId = 12 SELECT * FROM TagObjects WHERE CreatedAt <= '02/03/2019 23:11:33'
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID of the tag object. | |
TagId | Int64 |
Tags.Id | The Pardot ID of the tag. |
Type | String | The type of object associated with the tag. | |
ObjectId | Int64 | The Pardot ID of the object. | |
CreatedAt | Datetime | Time tag was associated with the object in Pardot. |
Retrieve tags.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Tags WHERE Id >= 42 SELECT * FROM Tags WHERE Name = 'criteria' SELECT * FROM Tags WHERE CreatedAt >= '02/03/2019 23:11:33'
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this tag. | |
Name | String | Tag's name. | |
CreatedAt | Datetime | Time tag was created in Pardot. | |
UpdatedAt | Datetime | Last time tag was updated. |
Retrieve users.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID of the user. | |
String | User's email address. | ||
FirstName | String | User's first name. | |
LastName | String | User's last name. | |
JobTitle | String | User's job title. | |
Role | String | User's role. | |
CreatedAt | Datetime | Time user was created in Pardot; Time is reported in the API user's preferred timezone. | |
UpdatedAt | Datetime | Last time user was updated in Pardot; Time is reported in the API user's preferred timezone. |
Retrieve visitor activities.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this visitor activity. | |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
VisitorId | Int64 |
Visitors.Id | Pardot ID for the associated visitor. |
Type | Integer | Visitor activity's type number; See listing below. | |
TypeName | String | Visitor activity's type name; See listing below. | |
Details | String | Details about this visitor activity such as the name of the object associated with this activity, the search phrase used in a site search query, etc. | |
EmailId | Int64 |
Emails.Id | Pardot ID of the email associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it. |
EmailTemplateId | Int64 | Pardot ID of the email template associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it. | |
ListEmailId | Int64 | Pardot ID of the list email associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it. | |
FormId | Int64 |
Forms.Id | Pardot ID of the form associated with this visitor activity Note: This node will only appear if this visitor activity has a form associated with it. |
FormHandlerId | Int64 | Pardot ID of the form handler associated with this visitor activity Note: This node will only appear if this visitor activity has a form handler associated with it. | |
SiteSearchQueryId | Int64 | Pardot ID of the site search query associated with this visitor activity Note: This node will only appear if this visitor activity has a site search query associated with it. | |
LandingPageId | Int64 | Pardot ID of the landing page associated with this visitor activity Note: This node will only appear if this visitor activity has a landing page associated with it. | |
PaidSearchIdId | Int64 | Pardot ID of the paid search ad associated with this visitor activity Note: This node will only appear if this visitor activity has a paid search ad associated with it. | |
MultivariateTestVariationId | Int64 | Pardot ID of the multivariate test variation associated with this visitor activity Note: This node will only appear if this visitor activity has a multivariate test variation associated with it. | |
VisitorPageViewId | Int64 | Pardot ID of the visitor page view associated with this visitor activity Note: This node will only appear if this visitor activity has a visitor page view associated with it. | |
FileId | Int64 | Pardot ID of the file associated with this visitor activity Note: This node will only appear if this visitor activity has a file associated with it. | |
CampaignId | Int64 |
Campaigns.Id | Campaign id. |
CampaignName | String | Campaign name. | |
CampaignCost | Integer | Campaign cost. | |
CreatedAt | Datetime | Time that visitor activity occurred. |
Retrieve visitors.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this visitor. | |
PageViewCount | Integer | Number of page views by this visitor. | |
IpAddress | String | Visitor's IP address. | |
Hostname | String | Visitor's hostname. | |
CampaignParameter | String | Visitor's campaign parameter utm_campaign from Google Analytics. | |
MediumParameter | String | Visitor's medium parameter utm_medium from Google Analytics. | |
SourceParameter | String | Visitor's source parameter utm_source from Google Analytics. | |
ContentParameter | String | Visitor's content parameter utm_content from Google Analytics. | |
TermParameter | String | Visitor's term parameter utm_term from Google Analytics. | |
CreatedAt | Datetime | Time visitor was created in Pardot. | |
UpdatedAt | Datetime | Last time visitor was updated in Pardot. | |
ProspectId | Int64 | Selects visits with the given Prospect IDs. |
Retrieve visitors page views.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this visitor page view. | |
VisitId | Int64 |
Visits.Id | Pardot ID for this visit. |
VisitorId | Int64 |
Visitors.Id | Pardot ID for the associated visitor. |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
Url | String | The Url of the page. | |
Title | String | The Title column of the VisitorsPageViews object. | |
CreatedAt | Datetime | The CreatedAt column of the VisitorsPageViews object. |
Retrieve visits.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this visit. | |
VisitorId | Int64 |
Visitors.Id | Pardot ID for the associated visitor. |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
VisitorPageViewCount | Integer | Number of page views for this visit. | |
FirstVisitorPageViewAt | Datetime | Time of first page view for this visit. | |
LastVisitorPageViewAt | Datetime | Time of last page view for this visit. | |
DurationInSeconds | Integer | Length of this visit. | |
CampaignParameter | String | Visit's campaign parameter utm_campaign from Google Analytics. | |
MediumParameter | String | Visit's medium parameter utm_medium from Google Analytics. | |
SourceParameter | String | Visit's source parameter utm_source from Google Analytics. | |
ContentParameter | String | Visit's content parameter utm_content from Google Analytics. | |
TermParameter | String | Visit's term parameter utm_term from Google Analytics. | |
CreatedAt | Datetime | Time visit was created in Pardot. | |
UpdatedAt | Datetime | Last time visit was updated in Pardot. |
ストアドプロシージャはファンクションライクなインターフェースで、Salesforce Marketing Cloud Account Engagement の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。
ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSalesforce Marketing Cloud Account Engagement から関連するレスポンスデータを返します。
Name | Description |
AssignProspect | Update the locations for a shipping zone. |
AssignProspectAccount | Update the locations for a shipping zone. |
SendEmail | Update the locations for a shipping zone. |
UnAssignProspect | Update the locations for a shipping zone. |
UnDeleteOpportunity | Update the locations for a shipping zone. |
Update the locations for a shipping zone.
EXEC AssignProspect ProspectId = 432, AssignmentType = 'user', AssignTo = 101 EXEC AssignProspect ProspectId = 432, AssignmentType = 'email', AssignTo = '[email protected]'
Name | Type | Required | Description |
ProspectId | String | False | The ID of the prospect. |
AssignmentType | String | False | The type of the assignment. Can be one of: email, user, group. |
AssignTo | String | False | The value to assign to. If the type is user or group the value should be the respective id. |
Name | Type | Description |
Success | String | This value shows a boolean indication of whether the operation was successful or not. |
Update the locations for a shipping zone.
EXEC AssignProspectAccount UserId = 432, ProspectAccountId = 501
Name | Type | Required | Description |
ProspectAccountId | String | False | The ID of the prospect account. |
UserId | String | False | The ID of the user. |
Name | Type | Description |
Success | String | This value shows a boolean indication of whether the operation was successful or not. |
Update the locations for a shipping zone.
One of: ProspectId, ProspectEmail or ListId.
One of: TemplateId or (TextContent, Name, Subject and (FromEmail, FromName or FromUserId))
Examples:
EXEC SendEmail CampaignId = 20006, ProspectId = 1807618, TemplateId = 13598 EXEC SendEmail CampaignId = 20006, ProspectEmail = '[email protected]', TextContent = 'content', Name = 'email name', Subject = 'email subject', FromUserId = 24486 EXEC SendEmail CampaignId = 20006, ListId = 20104, TextContent = 'content', Name = 'email name', Subject = 'email subject', FromEmail = '[email protected]', FromName = 'Name Surname'
Name | Type | Required | Description |
CampaignId | String | False | The ID of the campaign. |
ProspectId | String | False | The ID of the prospect. |
ProspectEmail | String | False | The email of the prospect. |
ListId | String | False | The ID of the lists to send to. |
TemplateId | String | False | The ID of the prospect. |
TextContent | String | False | The ID of the prospect. |
Name | String | False | The ID of the prospect. |
Subject | String | False | The ID of the prospect. |
FromEmail | String | False | The ID of the prospect. |
FromName | String | False | The ID of the prospect. |
FromUserId | String | False | The ID of the prospect. |
ReplyToEmail | String | False | The ID of the prospect. |
OperationalEmail | String | False | The ID of the prospect. |
HTMLContent | String | False | The ID of the prospect. |
Name | Type | Description |
Success | String | This value shows a boolean indication of whether the operation was successful or not. |
Update the locations for a shipping zone.
EXEC UnAssignProspect ProspectId = 432
Name | Type | Required | Description |
ProspectId | String | False | The ID of the prospect. |
Name | Type | Description |
Success | String | This value shows a boolean indication of whether the operation was successful or not. |
Update the locations for a shipping zone.
EXEC UnDeleteOpportunity Id = 600
Name | Type | Required | Description |
Id | String | False | The ID of the opportunity. |
Name | Type | Description |
Success | String | This value shows a boolean indication of whether the operation was successful or not. |
The CData Cloud models Salesforce Marketing Cloud Account Engagement Pardot API v5 objects 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.
テーブル describes a sample of the available standard tables.
Cloud はSalesforce Marketing Cloud Account Engagement のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。
Name | Description |
Campaigns | Retrieve and modify campaigns. |
CustomFields | Retrieve custom fields metadata. |
CustomRedirects | Track links on your website or third-party site with Pardot custom redirects. |
DynamicContent | Retrieve a collection of dynamic content |
Emails | Query emails to learn how prospects responded to the emails. |
EmailTemplates | Query email templates. |
Files | Upload images and other files to use in your Pardot emails, forms, and landing pages. |
FolderContents | View all objects contained within Folders. |
Folders | Retrieve folders or organize and nest your content and marketing assets in a way that makes sense for your team. |
FormHandlers | Retrieve,Create,Updata,Delete a collection of Form Handlers |
FormHandlersFields | Retrieve,Create,Update,Delete a collection of Form Handler Stages |
Forms | Retrieve forms. |
LandingPages | Retrieving a collection of landing pages |
LayoutTemplates | Retrieve and modify layout templates. |
LifecycleStages | Retrieving a collection of lifecycle stages |
ListEmail | A list email is a single email sent to a recipient list. |
ListMemberships | Retrieve,Create,Update,Delete a collection of list memberships |
Lists | Retrieve and modify lists. |
Prospects | Retrieve and modify prospects. |
TrackerDomains | Query the available tracker domains. |
Users | Query the available users. |
VisitorsPageViews | Retrieve visitors page views. |
Visits | Retrieve visits. |
Retrieve and modify campaigns.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Campaigns WHERE Id = 50 SELECT * FROM Campaigns WHERE Name = 'campaignName' SELECT * FROM Campaigns WHERE CreatedAt > '01/01/2022'
To retrieve campaigns that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM Campaings
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this campaign. | |
Name | String | True |
Campaign's name. | |
Cost | Integer | True |
Cost associated to the campaign. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
FolderId | Int64 | True |
ID of the folder containing this object. | |
ParentCampaignId | Int64 | True |
The ID of the parent campaign. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. | |
SalesforceId | String | True |
The alphanumeric Id of the Salesforce Org. |
Retrieve custom fields metadata.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM CustomFields WHERE Id = 50 SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2022' SELECT * FROM CustomFields WHERE UpdatedAt < '01/01/2022'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this custom field. | |
Name | String | False |
Custom field's name. | |
FieldId | String | True |
API ID for custom field. | |
Type | String | False |
The type of field. | |
CreatedAt | Datetime | True |
Time custom field was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time custom field was updated. | |
CreatedById | Int64 | False |
ID of the individual who created this object. | |
UpdatedById | Int64 | False |
ID of the individual who last updated this object. | |
IsRecordMultipleResponses | Boolean | False |
If true, this custom field will record multiple responses. | |
IsUseValues | Boolean | False |
If true, this custom field will use predefined values. | |
SalesforceId | String | False |
The alpha-numeric Id of the Salesforce Org. | |
IsRequired | String | False |
True if the custom field is required. | |
isAnalyticsSynced | Boolean | False |
True if the object is synced to Analytics. |
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
ValuesPrefill | String |
Write-Only field on create operation. Possible values: Countries, |
Track links on your website or third-party site with Pardot custom redirects.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM CustomRedirects WHERE Id = 40 SELECT * FROM CustomRedirects WHERE IsDeleted = 'true'
The following attributes are required when performing an insert: Name, CampaignId, DestinationUrl.
INSERT INTO CustomRedirects (Name, CampaignId, DestinationUrl) VALUES ('name', '1', 'https://example.com/content.html')
Update CustomRedirects attributes.
UPDATE CustomRedirects SET Name = 'newName', FolderId = '527425' WHERE Id = 24143
Remove all CustomRedirects objects or one by specifying the Id of the CustomRedirect.
DELETE FROM CustomRedirects WHERE Id = 10003
To retrieve custom redirects that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM CustomRedirects
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this campaign. | |
Name | String | False |
Name of the object for identification in Pardot. | |
CampaignId | Int64 | False |
Pardot Campaign related to this object. | |
DestinationUrl | String | False |
A fully qualified URL. The visitor who clicks the custom redirect is directed to this location. | |
FolderId | Int64 | False |
ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create. | |
TrackerDomainId | Int64 | False |
ID of the tracker domain to use in the URL for this object. Uses the primary tracker domain for the account if not specified on create. | |
VanityUrl | String | True |
Fully qualified vanity URL. Includes tracker domain and vanityUrlPath. | |
VanityUrlPath | String | False |
Vanity URL path (excluding protocol and host). Must be unique for each tracker domain. | |
GASource | String | False |
Google Analytics Campaign source. | |
GAMedium | String | False |
Google Analytics Campaign medium. | |
GATerm | String | False |
Google Analytics Campaign keyword. | |
GAContent | String | False |
Google Analytics Campaign content. | |
GACampaign | String | False |
Google Analytics Campaign ID. | |
SalesforceId | String | True |
ID of the Salesforce object representing this object. | |
TrackedUrl | String | True |
Fully qualified tracked URL. Does not include Vanity URL. | |
BitlyIsPersonalized | String | True |
True if a custom Bitly URL was set. | |
BitlyShortUrl | String | True |
If set, a fully qualified Bitly URL. | |
IsDeleted | Boolean | True |
True if the object is in the Pardot recycle bin. | |
CreatedAt | Datetime | True |
The object creation timestamp. | |
UpdatedAt | Datetime | True |
The timestamp of the last update of the object. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieve a collection of dynamic content
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this dynamic content. | |
Name | String | True |
Dynamic content's name. | |
EmbedCode | String | True |
The code used to embed the dynamic content into a web page. | |
EmbedUrl | String | True |
The URL to embed this dynamic content. | |
BaseContent | String | True |
The default content that shows when the prospect doesn't meet the criteria for a variation. | |
BasedOn | String | True |
The kind field that dynamic content is based on. Values are | |
CreatedAt | Datetime | True |
Time | |
UpdatedAt | Datetime | True |
Time the dynamic content was last updated; reporting in API user's timezone. | |
IsDeleted | Boolean | True |
When true, the object is in the recycle bin in Pardot. | |
CreatedByID | Int64 | True |
ID of the user who created this object. | |
UpdatedById | Int64 | True |
ID of the user who last updated this object. | |
FolderId | Int64 | True |
ID of the folder containing this object. | |
TrackerDomainId | Int64 | True |
The ID of the tracker domain associated with this object. |
Query emails to learn how prospects responded to the emails.
The Cloud uses the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Emails WHERE Id = 50 SELECT * FROM Emails WHERE ProspectId = 23 SELECT * FROM Emails WHERE SentAt > '01/01/2022'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the email. | |
ProspectId | Int64 | True |
Pardot prospect id related to this email. | |
CampaignId | Int64 | True |
Pardot campaign related to this object. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
ClientType | String | True |
Value is 'Builder' if the email was sent via the Lightning Email Builder. Value is 'Web' if it was sent via the legacy Pardot email builder. | |
TextMessage | String | True |
The text content of the email. This field is retrieved only when filtering with email id. | |
HtmlMessage | String | True |
The HTML content of the email. This field is retrieved only when filtering with email id. | |
ListId | Int64 | True |
ID of the list associated with this email. | |
listEmailId | Int64 | True |
ID of the list email associated with this email. | |
Name | String | True |
Name of the email for identification in Pardot. | |
SentAt | Datetime | True |
Time the email was sent. | |
Subject | String | True |
The email subject. |
Query email templates.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM EmailTemplates WHERE Id = 50 SELECT * FROM EmailTemplates WHERE UpdatedAt > '01/01/2022' SELECT * FROM EmailTemplates WHERE CreatedAt > '01/01/2022'
To insert EmailTemplates the following fields are required:
For inserting into this table specifying the SenderOptionsAggregate is required. The input for this field follows CData principles of deep Inserts. In Deep Insert, you simultaneously create a base object and link it to the related field in the main table.
You may either submit JSON data, or you may create a temporary table for the child table(in this case SenderOptions) and then reference the temporary table in the insert to the base table.
To submit JSON data, simply supply the values for the child table in JSON format.
For example, the following JSON adds two sender options in the email template.
[ { "address": "[email protected]", "name": "CDataTest", "type": "general_user" }, { "address": "[email protected]", "name": "CDataTest2", "type": "general_user" } ]
In order to execute the insert, simply reference or include as string literals the complete JSON. For example:
INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., '[{"address":"[email protected]","name":"CDataTest","type":"general_user"},{"address":"[email protected]","name":"CDataTest2","type":"general_user"}]')
If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.
Insert the child table fields into temporary table.
INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address1')
INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address2')
In the INSERT statement for the EmailTemplates table, reference the temporary tables in the SenderOptionsAggregate input.
INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., 'SenderOptions#TEMP')
Updating EmailTemplates:
UPDATE EmailTemplates SET TextMessage = 'newMessage' WHERE Id = 24143
Remove all email templates or a email template by specifying the Id of the template.
DELETE FROM EmailTemplates WHERE Id = 10003
To retrieve email templates that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM EmailTemplates
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the email. | |
Name | String | False |
Name of the object for identification in Pardot. | |
Subject | String | False |
Subject line for the email template. | |
TextMessage | String | False |
The text content of the email. This field is retrieved only when filtering with email id. | |
HtmlMessage | String | False |
The HTML content of the email. This field is retrieved only when filtering with email id. | |
IsOneToOneEmail | Boolean | False |
If True, the template is available for one-to-one emails. | |
IsAutoResponderEmail | Boolean | False |
If True, the template is available for autoresponder emails. | |
IsDripEmail | Boolean | False |
If True, the template is available for Engagement Programs. | |
IsListEmail | Boolean | False |
If True, the template is available for list emails. | |
TrackerDomainId | Int64 | False |
ID of the tracker domain used for the list email. | |
FolderId | Int64 | False |
ID of the folder containing this object. | |
Type | String | False |
'html', 'text', and 'htmlAndText' values. | |
CampaignId | Int64 | False |
Pardot campaign related to this object. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
SenderOptionsType | String | True |
The sending user type(s). | |
SenderOptionsAddress | String | True |
The sending email address(s). | |
SenderOptionsName | String | True |
The name(s) of the sender. | |
SenderOptionsUserId | String | True |
The user ID(s) of the sender. | |
SenderOptionsProspectCustomFieldId | String | True |
The ID of the prospect custom field that contains the sender value. Only available on read and query. | |
SenderOptionsAccountCustomFieldId | String | True |
The ID of the account custom field that contains the sender value. Only available on read and query. | |
ReplyToOptionsType | String | True |
The reply to user type(s). | |
ReplyToOptionsAddress | String | True |
The sending email address(s). | |
ReplyToOptionsUserId | String | True |
The user ID(s) of the sender. | |
ReplyToOptionsProspectCustomFieldId | String | True |
The ID of the prospect custom field that contains the sender value. Only available on read and query. | |
ReplyToOptionsAccountCustomFieldId | String | True |
The ID of the account custom field that contains the sender value. Only available on read and query. |
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
SenderOptionsAggregate | String |
This input is used for INSERT/UPDATE statements. Set this to SenderOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the sender options. Or simply reference or include as string literals the complete JSON. |
ReplyToOptionsAggregate | String |
This input is used for INSERT/UPDATE statements. Set this to ReplyToOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the replyTo options. Or simply reference or include as string literals the complete JSON. |
Upload images and other files to use in your Pardot emails, forms, and landing pages.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Files WHERE Id = 40 SELECT * FROM Files WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'
The following attributes are required when performing an insert: Name, FilePath.
INSERT INTO Files (Name, FilePath) VALUES ('file1', 'C:\\file1.txt')
Updating Files:
UPDATE Files SET Name = 'testingUpdate', FolderId = '33609' WHERE id = '804969'
Remove all files or a file by specifying the Id of the file.
DELETE FROM Files WHERE Id = 10003
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID of this file. | |
Name | String | False |
Name of the object for identification in Pardot. Uses the name of the file being uploaded if not specified on create. | |
FolderId | Int64 | False |
ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create. | |
CampaignId | Int64 | False |
Pardot campaign related to this object. Uses null if not specified on create. | |
VanityUrlPath | String | False |
Vanity URL path (excluding protocol and host). Must be unique for each tracker domain. | |
TrackerDomainId | Int64 | False |
ID of the TrackerDomain to use in the URL for this object. Uses the primary tracker domain for the account if not specified on create. | |
SalesforceId | String | True |
ID of the Salesforce object representing this object. | |
Url | String | True |
URL where the downloadable file can be accessed. | |
Size | Integer | True |
Size (in bytes) of the downloadable file. | |
BitlyIsPersonalized | Boolean | True |
True if the object has a bitly URL that is personalized. | |
BitlyShortUrl | String | True |
Bitly URL if present. | |
VanityUrl | String | True |
Vanity URL if present. | |
IsTracked | Boolean | True |
True if downloads of this file are recorded as visitor activity. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
FilePath | String |
This parameter is used only in INSERT operations to upload the content of a file. It should be set to the path of the file including the name. Ex: C:\Users\User\Desktop\test.txt or to the BASE64 encoded content of the file. |
View all objects contained within Folders.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the folder content storing the folder-to-object relationship. | |
FolderId | Int64 | True |
ID of the folder containing the object with ObjectId. | |
FolderRef | String | True |
The URL for reading the specific folder with FolderId. | |
ObjectType | String | True |
Type of object referenced by ObjectId. | |
ObjectId | Int64 | True |
ID of the object contained in the folder. | |
ObjectName | String | True |
Name of the object contained in the folder. | |
ObjectRef | String | True |
The URL for reading the specific object with ObjectId. | |
CreatedAt | Datetime | True |
Creation time of the object. | |
UpdatedAt | Datetime | True |
Last update time of the object. | |
CreatedById | Int64 | True |
ID of the user who created this object. | |
UpdatedById | Int64 | True |
ID of the user who last updated this object. |
Retrieve folders or organize and nest your content and marketing assets in a way that makes sense for your team.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Folders WHERE Id = 50 SELECT * FROM Folders WHERE ParentFolderId = 2
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID of this file. | |
Name | String | True |
Name of the folder. | |
ParentFolderId | Int64 | True |
ID of the folder containing this folder. This value is null when the folder is a root folder and doesn't have a parent. | |
Path | String | True |
The names of each folder from the root folder separated with forward slashes. | |
UsePermissions | Boolean | True |
True if the folder is configured to use permissions. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieve,Create,Updata,Delete a collection of Form Handlers
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of this object. | |
Name | String | False |
Name of the object for identification in Pardot. | |
FolderId | Int64 | False |
ID of the folder containing this object. | |
CampaignId | Int64 | False |
The ID of the campaign associated with this object. | |
TrackerDomainId | Int64 | False |
The ID of the tracker domain associated with this object. | |
IsDataForwarded | Boolean | False |
When true, enables data forwarding to the success location. | |
SucessLocation | String | False |
The URL the user is forwarded to if the submission is successful. | |
ErrorLocation | String | False |
The URL the user is forwarded to if the submission has an error. | |
IsAlwaysEmail | Boolean | False |
When true, disables visitor activity throttling and sends autoresponder emails after every submission. Learn more about throttling in Salesforce Help. | |
IsCookieless | Boolean | False |
When true, the form handler is in Kiosk/Data Entry Mode, and does not cookie browser as a submitted prospect. | |
SalesforceId | String | True |
Salesforce Id of the object. | |
EmbedCode | String | True |
Code to embed this form handler in a webpage. | |
CreatedAt | Datetime | True |
Creation time of this object. | |
UpdatedAt | Datetime | True |
Last updated time for the object. | |
CreatedById | Int64 | True |
ID of the user who created the object. | |
IsDeleted | Boolean | True |
True if the object is in the Pardot recycle bin. | |
UpdatedById | Int64 | True |
ID of the user who last updated the object. |
Retrieve,Create,Update,Delete a collection of Form Handler Stages
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of this object. | |
Name | String | False |
Name of the object for identification in Pardot. | |
FormHandlerId | Int64 | False |
The ID of the form handler this field belongs to. | |
IsRequired | Boolean | False |
When true, the field is required. | |
DataFormat | String | False |
The field's data format. Values are 'text', 'number','email', 'email not from isps and free email providers', and 'email with valid mail server'. | |
ProspectApiFieldId | String | False |
The name of the Pardot prospect field that the form handler field is mapped to. On standard fields the value is the name of the field. For example, 'firstName'. If mapping to a custom field, the value is the field name followed by __c. For example, the custom field 'foodChoice' becomes 'foodChoice__c'. | |
IsMaintainInitialValue | Boolean | False |
When true, the field maintains the initial value upon subsequent form submissions. | |
ErrorMessage | String | False |
Error message for the field. | |
CreatedAt | Datetime | True |
Creation time of this object. | |
CreatedById | Int64 | True |
ID of the user who created the object. | |
UpdatedAt | Datetime | True |
Last updated time for the object. | |
UpdatedById | Int64 | True |
ID of the user who last updated the object. |
Retrieve forms.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Forms WHERE Id <= 17 SELECT * FROM Forms WHERE IsDeleted = 'true'
To retrieve forms that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM Forms
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this form. | |
Name | String | True |
Form's name. | |
CampaignId | Int64 | True |
Campaigns.Id |
Pardot ID of the campaign associated with this form. |
EmbedCode | String | True |
The code used to embed the form on your webpage. | |
SalesforceId | String | True |
The alpha-numeric Id of the Salesforce Org. | |
LayoutTemplateId | Int64 | True |
The ID of the layout template associated with this object | |
SubmitButtonText | String | True |
The label used for the submit button | |
BeforeFormContent | String | True |
The HTML text displayed above the form | |
AfterFormContent | String | True |
The HTML text displayed below the form | |
ThankYouContent | String | True |
The HTML text displayed after the form is submitted | |
ThankYouCode | String | True |
Thank you code is typically Javascript used to call other systems like Google Conversion Tracking | |
IsAlwaysDisplay | Boolean | True |
Always display form after submission. Disable the 10 minute waiting period after submitting a form | |
RedirectLocation | String | True |
Redirect to this URL instead of showing the | |
IsUseRedirectLocation | Boolean | True |
When redirect location is valid and enabled. | |
FontSize | String | True |
Possible values: default, 8pt, 10pt, 12pt, 14pt , 18pt, 24pt, 36pt. | |
FontFamily | String | True |
Possible values: default, | |
FontColor | String | True |
6 digit number in hexadecimal format such as #123456 | |
LabelAlignment | String | True |
Possible values: default, above, left | |
RadioAlignment | String | True |
Possible values: default, horizontal, stacked | |
CheckboxAlignment | String | True |
Possible values: default, horizontal, stacked | |
RequiredCharacter | String | True |
Possible values: default , * | |
IsCookieless | String | True |
Kiosk/Data Entry Mode: Do not cookie browser as submitted prospect | |
ShowNotProspect | Boolean | True |
Include | |
IsCaptchaEnabled | Boolean | True |
Enable explicit bot protecting using reCAPTCHA | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
TrackerDomainId | Int64 | True |
The ID of the tracker domain associated with this object. | |
FolderId | Int64 | True |
ID of the folder containing this object. | |
CreatedAt | Datetime | False |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | False |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieving a collection of landing pages
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the object. | |
Name | String | True |
Name of the object for identification in Pardot. | |
SalesforceId | String | True |
ID of the Salesforce object representing this object. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
CreatedById | Int64 | True |
ID of the user who created this object. | |
CreatedAt | Datetime | True |
Creation time of the object. | |
UpdatedAt | Datetime | True |
Last update time of the object. | |
UpdatedById | Int64 | True |
ID of the user who last updated this object. | |
CampaignId | Int64 | True |
Pardot Campaign related to this object. | |
FormId | Int64 | True |
ID of the form related to this object. | |
LayoutTemplateId | Int64 | True |
ID of the layout template related to this object. | |
Title | String | True |
The landing page's title. | |
Description | String | True |
The landing page's meta description. Used for SEO. | |
IsDoNotIndex | Boolean | True |
True if the landing page is hidden from search engine indexing. | |
LayoutType | String | True |
Specifies where the landing page was created. Possible values are Layout Template, Landing Page Builder, Legacy Page Builder, Salesforce Builder. | |
ScriptFragment | String | True |
Script specified for the landing page. Available only on read requests. | |
LayoutCss | LayoutCss | True |
CSS code for the layout template. Available only on read requests. | |
LayoutCssGenerated | String | True |
Generated CSS code for the layout template. Available only on read requests. | |
LayoutTableBorder | Int | True |
Specifies the layout table border width. | |
RedirectLocation | String | True |
The redirect URL used if the prospect is redirected instead of showing the thank you content. | |
IsUseRedirectLocation | Boolean | True |
True if the prospect is redirected instead of showing the form thank you content. | |
BitlyIsPersonalized | Boolean | True |
True if the object has a bitly URL that is personalized. | |
BitlyShortUrl | String | True |
Bitly URL if present. | |
Url | String | True |
Link for the landing page. | |
VanityUrl | String | True |
Vanity URL if present. | |
VanityUrlPath | String | True |
The unique path for the asset, used to create the vanity URL. | |
TrackerDomainId | Int64 | True |
The ID of the tracker domain associated with this object. | |
FolderId | Int64 | True |
The ID of the folder associated with this object. |
Retrieve and modify layout templates.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM LayoutTemplates WHERE Id = 40 SELECT * FROM LayoutTemplates WHERE IsDeleted = 'true'
The following attributes are required when performing an insert: Name, LayoutContent.
INSERT INTO LayoutTemplates (Name, IsIncludeDefaultCss, LayoutContent, FolderId) VALUES ('name', 'true', 'content', 33621)
Updating LayoutTemplates:
UPDATE LayoutTemplates SET Name = 'newName' WHERE Id = 24143
Remove all layout templates or a layout template by specifying the Id of the template.
DELETE FROM LayoutTemplates WHERE Id = 10003
To retrieve layout templates that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM LayoutTemplates
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of this object. | |
Name | String | False |
Name of the object for identification in Pardot. Uses the name of the file being uploaded if not specified on create. | |
LayoutContent | String | False |
HTML content of this layout template encoded as JSON string. | |
FormContent | String | False |
HTML content encoded as JSON string that controls form display logic. Uses default values if not provided. | |
SiteSearchContent | String | False |
HTML content encoded as JSON string that controls the site search content. | |
IsIncludeDefaultCss | Boolean | False |
True if not supplying custom CSS styling. | |
FolderId | Int64 | False |
ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieving a collection of lifecycle stages
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the lifecycle stage. | |
Name | String | True |
Pardot ID of this lifecycle stage. | |
IsDeleted | Boolean | True |
True if the lifecycle stage is in the recycle bin in Pardot. | |
CreatedAt | Datetime | True |
Creation time of the lifecycle stage. | |
UpdatedAt | Datetime | True |
Last updated time for the lifecycle stage. | |
IsLocked | Boolean | True |
When true, lifecycle stage is locked. | |
Position | Int | True |
Lifcycle stage's position in lifecycle. | |
MatchType | String | True |
Match all is returned when a prospect must match all rules in order to be in this stage. Match any is returned when a prospect can match 1 or more rules |
A list email is a single email sent to a recipient list.
The Cloud uses the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM ListEmail WHERE Id = 50 SELECT * FROM ListEmail WHERE SentAt > '01/01/2022' SELECT * FROM ListEmail WHERE CreatedAt > '01/01/2022'
To retrieve list email that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM ListEmail
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the email. | |
Name | String | True |
Name of the object for identification in Pardot. | |
Subject | String | True |
The email subject. | |
TextMessage | String | True |
The text content of the email. This field is retrieved only when filtering with email id. | |
HtmlMessage | String | True |
The HTML content of the email. This field is retrieved only when filtering with email id. | |
CampaignId | Int64 | True |
Pardot campaign related to this object. | |
IsPaused | Boolean | True |
True if the email send is paused. | |
IsSent | Boolean | True |
True if the email has been sent. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
ClientType | String | True |
Value is 'Builder' if the email was sent via the Lightning Email Builder. Value is 'Web' if it was sent via the legacy Pardot email builder. | |
SentAt | Datetime | True |
Time the email was sent. | |
OperationalEmail | Boolean | True |
True if the email is operational. | |
EmailTemplateId | Int64 | True |
ID of the email template used for the list email. | |
TrackerDomainId | Int64 | True |
ID of the tracker domain used for the list email. | |
SenderOptionsType | String | True |
The sending user type(s). | |
SenderOptionsAddress | String | True |
The sending email address(s). | |
SenderOptionsName | String | True |
The name(s) of the sender. | |
SenderOptionsUserId | String | True |
The user ID(s) of the sender. | |
ReplyToOptionsType | String | True |
The reply to user type(s). | |
ReplyToOptionsAddress | String | True |
The sending email address(s). | |
ReplyToOptionsUserId | String | True |
The user ID(s) of the sender. |
Retrieve,Create,Update,Delete a collection of list memberships
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this list membership. | |
IsDeleted | Boolean | True |
When true, the list membership is in the Pardot recycle bin. | |
CreatedById | Int64 | True |
ID of the user who created this list membership. | |
CreatedAt | Datetime | True |
The time that this list membership was created. Reported in API user's preferred timezone. | |
UpdatedAt | Datetime | True |
The time that this list membership was last updated. Reported in API user's timezone. | |
UpdatedById | Int64 | True |
ID of the user who last updated this list membership. | |
OptedOut | Boolean | False |
When true, the prospect is unsubscribed from receiving emails from this list. | |
ListId | Int64 | False |
Pardot ID of the list for this membership | |
ProspectID | Int64 | False |
Pardot ID of the prospect for this membership |
Retrieve and modify lists.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Lists WHERE Id = 40 SELECT * FROM Lists WHERE IsDeleted = 'true' SELECT * FROM Lists WHERE CreatedAt > '01/01/2022'
The following attribute is required when performing an insert: Name.
INSERT INTO Lists (Name) VALUES ('name')
Update Lists attributes.
UPDATE Lists SET Name = 'newName' WHERE Id = 24143
Remove all lists or a list by specifying the Id of the list.
DELETE FROM Lists WHERE Id = 10003
To retrieve lists that are in the recycle bin in Pardot the following operation is supported:
GETDELETED FROM Lists
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID of this list. | |
Name | String | False |
List's name (internal to Pardot). | |
FolderId | Int64 | False |
ID of the folder containing this object. | |
CampaignId | Int64 | False |
Campaigns.Id |
Pardot campaign related to this object. Uses null if not specified on create. |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
IsDynamic | Boolean | True |
True if the list is dynamic. | |
IsPublic | Boolean | False |
True if the list is public. | |
Title | String | False |
Title of the list object. | |
Description | String | False |
List's description. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieve and modify prospects.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Prospects WHERE Id = 3457992 SELECT * FROM Prospects WHERE UserId = 14 SELECT * FROM Prospects WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'
To retrieve deleted prospects, a GETDELETED statement can be executed.
GETDELETED FROM Prospects
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this prospect. | |
AddressOne | String | False |
Prospect's address, line 1. | |
AddressTwo | String | False |
Prospect's address, line 2. | |
AnnualRevenue | String | False |
Prospect's annual revenue. | |
CampaignId | Int64 | False |
Pardot campaign related to this object. | |
CampaignParameter | String | True |
Prospect's campaign parameter utm_campaign from Google Analytics. | |
SalesforceCampaignId | String | True |
The alpha-numeric Id of the associated campaign in the Salesforce Org. | |
City | String | False |
Prospect's city. | |
Comments | String | False |
Comments about this prospect. | |
Company | String | False |
Prospect's company. | |
ContentParameter | String | True |
Prospect's content parameter utm_content from Google Analytics. | |
ConvertedAt | Datetime | True |
Time when prospect was converted from visitor. | |
ConvertedFromObjectName | String | True |
Name of object associated with visitor activity that resulted in prospect being converted from visitor. | |
ConvertedFromObjectType | String | True |
ype of object associated with visitor activity that resulted in prospect being converted from visitor. Supported values include 'Form', 'FormHandler', 'LandingPage', 'MultivariateTestVariation', and 'Video'. | |
Country | String | False |
Prospect's country. | |
SalesforceAccountId | String | True |
Account ID in a supported Salesforce system. | |
SalesforceContactId | String | True |
Prospect's contact ID in a supported Salesforce system. | |
SalesforceLastSync | Datetime | True |
Last time this prospect was synced with a supported Salesforce system. | |
SalesforceLeadId | String | True |
Prospect's lead ID in a supported Salesforce system. | |
SalesforceOwnerId | String | False |
Prospect's owner ID in a supported Salesforce system. | |
SalesforceId | String | False |
The alpha-numeric Id of the Salesforce Org. | |
Department | String | False |
Prospect's department. | |
String | False |
Prospect's email address. | ||
EmailBouncedAt | Datetime | True |
Time | |
EmailBouncedReason | String | True |
Reason why prospect email address hard bounced. | |
Employees | String | False |
Prospect's number of employees. | |
Fax | String | False |
Prospect's fax number. | |
FirstActivityAt | Datetime | True |
Time when first visitor activity occurred for this prospect. | |
FirstAssignedAt | Datetime | True |
Time prospect was first assigned to a user. | |
FirstName | String | False |
Prospect's first name. | |
FirstReferrerQuery | String | True |
First referrer's search query. | |
FirstReferrerType | String | True |
First referrer's vendor and type (such as 'Google Natural Search'). | |
FirstReferrerUrl | String | True |
First referrer's URL. | |
Grade | String | True |
Prospect's letter grade. | |
Industry | String | False |
Prospect's industry. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
IsDoNotCall | Boolean | False |
If value is 1, prospect prefers not to be called. | |
IsDoNotEmail | Boolean | False |
If value is 1, prospect prefers not to be emailed. | |
isEmailHardBounced | Boolean | True |
If true, prospect email address has hard bounced. | |
IsReviewed | Boolean | False |
If value is 1, prospect has been reviewed. | |
IsStarred | Boolean | False |
If value is 1, prospect has been starred. | |
JobTitle | String | False |
Prospect's job title. | |
LastActivityAt | Datetime | True |
Time stamp of this prospect's latest visitor activity. | |
LastName | String | False |
Prospect's last name. | |
MediumParameter | String | True |
Prospect's medium parameter utm_medium from Google Analytics. | |
Notes | String | False |
Notes about this prospect. | |
OptedOut | Boolean | False |
If value is 1, prospect has opted out of marketing communications. | |
Password | String | True |
Prospect's password. | |
Phone | String | False |
Prospect's phone number. | |
ProspectAccountId | Int64 | False |
Prospect's account ID. | |
Salutation | String | False |
Prospect's formal prefix. | |
Score | Integer | False |
Prospect's score. | |
Source | String | False |
Prospect's source. | |
SourceParameter | String | True |
Prospect's source parameter utm_source from Google Analytics. | |
State | String | False |
Prospect's US state. | |
TermParameter | String | True |
Prospect's term parameter utm_term from Google Analytics. | |
Territory | String | False |
Prospect's territory. | |
Website | String | False |
Prospect's website URL. | |
YearsInBusiness | String | False |
Prospect's number of years in business. | |
Zip | String | False |
Prospect's postal code. | |
AssignedToId | Int64 | True |
ID of the user who the object record is assigned to. | |
ProfileId | Int64 | False |
Pardot profile related to this object. | |
SalesforceUrl | String | True |
URL to view the prospect within the Salesforce system. | |
LifecycleStageId | Int64 | True |
Pardot lifecycle stage related to this object. | |
UserId | Int64 | False |
User the prospect is assigned to. | |
RecentInteraction | String | True |
Describes the prospect's most recent interaction with Pardot. | |
CreatedAt | Datetime | True |
Time prospect was created in Pardot. | |
UpdatedAt | Datetime | True |
Last time prospect was updated in Pardot. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Query the available tracker domains.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM TrackerDomains WHERE Id = 50
To retrieve deleted records, a GETDELETED statement can be executed.
GETDELETED FROM TrackerDomains
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this object. | |
Domain | String | True |
Hostname to which links using this tracker domain are resolved. | |
IsPrimary | Boolean | True |
True if the tracker domain is primary tracker domain for the account. Only one tracker domain can be designated as the primary and must be unique across all Pardot accounts. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
DefaultCampaignId | Int64 | True |
The ID of the default campaign for this tracker domain. | |
HttpsStatus | String | True |
Identify if this tracker domain is using http or https. | |
SslStatus | String | True |
Status of SSL for this vanity tracker domain. | |
SslStatusDetails | String | True |
Extra information for the status of SSL for this vanity tracker domain. | |
SslRequestedById | Int64 | True |
ID of the User who manually requested enabling SSL for this tracker domain. | |
ValidationStatus | String | True |
The status can be default, validated or not validated | |
ValidatedAt | Datetime | True |
Time when this tracker domain validation became validated. | |
VanityUrlStatus | String | True |
Indicates whether this tracker domain can serve vanity urls. | |
TrackingCode | String | True |
The tracking code for this object encoded as JSON string. A default campaign is required before generating tracking codes as well as enabling first party tracking in account settings. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Query the available users.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Users WHERE Id = 32 SELECT * FROM Users WHERE CreatedAt < '02/03/2019 23:11:33' AND CreatedAt > '02/03/2018 23:11:33'
To retrieve deleted users, a GETDELETED statement can be executed.
GETDELETED FROM Users
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this object. | |
String | True |
Email address of the user. | ||
FirstName | String | True |
First name of the user. | |
Username | String | True |
Formatted as an email address, though distinct from the email field on the user object. | |
JobTitle | String | True |
Job title of the user. | |
Role | String | True |
Role of the user. | |
SalesforceId | String | True |
Salesforce Id of the object. | |
IsDeleted | Boolean | True |
True if the object is in the recycle bin in Pardot. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. | |
CreatedById | Int64 | True |
ID of the individual who created this object. | |
UpdatedById | Int64 | True |
ID of the individual who last updated this object. |
Retrieve visitors page views.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM VisitorPageViews WHERE VisitId = 22 SELECT * FROM VisitorPageViews WHERE Id >= 22 AND VisitorId = 1234
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
Pardot ID for this visitor page view. | |
Url | String | True |
The Url of the page. | |
Title | String | True |
The Title column of the VisitorsPageViews object. | |
VisitId | Int64 | True |
Pardot ID for this visit. | |
VisitorId | Int64 | True |
Pardot ID for the associated visitor. | |
CampaignId | Int64 | False |
Pardot campaign related to this object. | |
SalesforceId | String | True |
Salesforce Id of the object. | |
DurationInSeconds | Integer | True |
Length of this page view. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. |
Retrieve visits.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM Visits WHERE Id = 22 SELECT * FROM Visits WHERE VisitorId > 234
Name | Type | ReadOnly | References | Description |
Id [KEY] | Int64 | True |
ID of the object. | |
VisitorId | Int64 | True |
Visitors.Id |
Pardot ID for the associated visitor. |
ProspectId | Int64 | True |
Prospects.Id |
Pardot ID for the associated prospect. |
VisitorPageViewCount | Integer | True |
Number of page views for this visit. | |
FirstVisitorPageViewAt | Datetime | True |
Time of first page view for this visit. | |
LastVisitorPageViewAt | Datetime | True |
Time of last page view for this visit. | |
DurationInSeconds | Integer | True |
Length of this visit. | |
CampaignParameter | String | True |
Visit's campaign parameter utm_campaign from Google Analytics. | |
MediumParameter | String | True |
Visit's medium parameter utm_medium from Google Analytics. | |
SourceParameter | String | True |
Visit's source parameter utm_source from Google Analytics. | |
ContentParameter | String | True |
Visit's content parameter utm_content from Google Analytics. | |
TermParameter | String | True |
Visit's term parameter utm_term from Google Analytics. | |
CreatedAt | Datetime | True |
The timestamp of when this object was created. | |
UpdatedAt | Datetime | True |
The timestamp of when this object was last updated. |
ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。
クエリは、ビューに対して通常のテーブルと同様に実行することができます。
Name | Description |
Account | Use the account resource to learn about the current user's Pardot account. |
DynamicContentVariations | Retrieve a collection of Dynamic Content Variations |
EmailSuppressionList | A Lists used as suppression lists for the list email. |
LifecycleHistories | Use lifecycle history resources to learn how a prospect is moving through their sales journey, from visitor to won opportunity. |
ListEmailRecipients | RecipientLists used by an email |
Opportunities | Retrieve a collection of opportunities |
ProspectAccounts | Retrieve and modify prospect accounts. |
ReplyToOptions | Query the replyTo options of email templates. |
SenderOptions | Query the sender options of email templates. |
VisitorActivities | Retrieve visitor activities. |
Visitors | Retrieve visitors. |
Use the account resource to learn about the current user's Pardot account.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this account. | |
Company | String | Account company. | |
Level | String | The level of product for the account. | |
Website | String | Account website. | |
PluginCampaignId | Int64 | Plugin ID for account campaign. | |
AddressOne | String | Account's address, line 1. | |
AddressTwo | String | Account's address, line 2. | |
City | String | Account's city. | |
State | String | Account's state. | |
Zip | String | Account's postal code. | |
Territory | String | Account's territory. | |
Country | String | Account's country. | |
Phone | String | Account's phone number. | |
Fax | String | Account's fax number. | |
AdminId | Int64 | ID of the individual who is the admin for this account. | |
CreatedAt | Datetime | The timestamp of when this object was created. | |
UpdatedAt | Datetime | The timestamp of when this object was last updated. | |
MaximumDailyApiCalls | Integer | Maximum daily API requests based on the timezone for this account. | |
ApiCallsUsed | Integer | Total daily API requests based on the timezone for this account. | |
CreatedById | Int64 | ID of the individual who created this object. | |
UpdatedById | Int64 | ID of the individual who last updated this object. |
Retrieve a collection of Dynamic Content Variations
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this dynamic content variation. | |
Comparison | String | The logic used to set the criteria for a variation. This field works together with the Dynamic Content basedOn field to determine which variation a prospect is served. | |
Content | String | The text content of the variation. | |
DynamicContentId | String | ID of the dynamic content this variation is associated with. |
A Lists used as suppression lists for the list email.
To retrieve a single suppressed email address, specify its ID:
SELECT * FROM EmailSuppressionList WHERE Id = '55555555'
To retrieve multiple suppressed email addresses, issue a nested SELECT statement to the ListEmail table.
SELECT * FROM EmailSuppressionList WHERE ListEmailId IN (SELECT Id FROM ListEmail)
Name | Type | References | Description |
Id [KEY] | Int64 | ID of the SuppressionList | |
ListEmailId [KEY] | Int64 | The ListEmail id | |
ListEmailName | String | ListEmail's name (internal to Pardot). | |
ListEmailCampaignId | Int64 | Pardot ListEmail campaign related to this object. Uses null if not specified on create. | |
Name | String | List's name (internal to Pardot). | |
FolderId | Int64 | ID of the folder containing this object. | |
IsDynamic | Boolean | True if the list is dynamic. | |
IsPublic | Boolean | True if the list is public. | |
Title | String | Title of the list object. | |
Description | String | List's description. |
Use lifecycle history resources to learn how a prospect is moving through their sales journey, from visitor to won opportunity.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID of this lifecycle history. | |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the prospect in this stage. |
PreviousStageId | Int64 | Pardot ID of the stage this prospect previously was in. | |
NextStageId | Int64 | Pardot ID of the stage this prospect is in next. | |
SecondsElapsed | Integer | Number of seconds for prospect to get to current stage. | |
CreatedAt | Datetime | The timestamp of when this object was created. |
RecipientLists used by an email
To retrieve a single email recipient, specify its ID:
SELECT * FROM ListEmailRecipients WHERE Id = '55555555'
To retrieve multiple email recipient, issue a nested SELECT statement to the ListEmail table.
SELECT * FROM ListEmailRecipients WHERE ListEmailId IN (SELECT Id FROM ListEmail)
Name | Type | References | Description |
Id [KEY] | Int64 | ID of the RecipientList. | |
ListEmailId [KEY] | Int64 | The ListEmail id | |
ListEmailName | String | ListEmail's name (internal to Pardot). | |
ListEmailCampaignId | Int64 | Pardot ListEmail campaign related to this object. Uses null if not specified on create. | |
Name | String | List's name (internal to Pardot). | |
FolderId | Int64 | ID of the folder containing this object. | |
IsDynamic | Boolean | True if the list is dynamic. | |
IsPublic | Boolean | True if the list is public. | |
Title | String | Title of the list object. | |
Description | String | List's description. |
Retrieve a collection of opportunities
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this opportunity. | |
CampaignId | Int64 | Pardot ID of the campaign associated with this opportunity. Information about an opportunity's campaign association can be returned using the Campaign relationship. | |
ClosedAt | Datetime | The opportunity's closed date. When left blank, the Closed Date field in Pardot isn't set, even when the opportunity's stage, status, or probability are set to indicate that the opportunity is closed. | |
CreatedAt | Datetime | Time opportunity is created in Pardot; Time is reported in API user's preferred timezone. | |
CreatedById | Int64 | ID of the user who created this object. | |
Name | String | The opportunity's name. | |
Probability | Int | The opportunity's probability. Must be a positive numeric value from 0 through 100, inclusive. | |
SalesforceId | String | The ID of the associated opportunity in Salesforce. | |
Stage | String | The opportunity's stage. | |
Status | String | The opportunity's status. Status must be either won, lost, or open. | |
Type | String | The opportunity's type. | |
UpdatedAt | Datetime | Last time opportunity was updated in Pardot; Time is reported in API user's preferred timezone. | |
UpdatedById | Int64 | ID of the user who last updated this object. | |
Value | Double | The opportunity's value. Must be a positive numeric value. |
Retrieve and modify prospect accounts.
The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM ProspectAccounts WHERE Id > 31
To retrieve deleted prospect accounts, a GETDELETED statement can be executed.
GETDELETED FROM ProspectAccounts
Name | Type | References | Description |
Id [KEY] | Int64 | ID of the object. | |
Name | String | Name of the object. | |
SalesforceId | String | ID of the Salesforce object representing this object. | |
IsDeleted | Boolean | True if the object is in the recycle bin in Pardot. | |
AnnualRevenue | String | Annual Revenue of the object. | |
BillingAddressOne | String | Billing Address of the object. | |
BillingAddressTwo | String | Second Billing Address of the object. | |
BillingCity | String | City of the object. | |
BillingCountry | String | County of the object. | |
BillingState | String | State of the object. | |
BillingZip | String | Zip of the object. | |
Description | String | Description of the object. | |
Employees | String | Employees of the object. | |
Fax | String | Fax of the object. | |
Industry | String | Industry of the object. | |
Number | String | Number of the object. | |
Ownership | String | Ownership of the object. | |
Phone | String | Phone Number of the object. | |
Rating | String | Rating of the object. | |
ShippingAddressOne | String | Shipping Address of the object. | |
ShippingAddressTwo | String | Second Shipping Address of the object. | |
ShippingCity | String | Shipping Address city of the object. | |
ShippingCountry | String | Shipping Address country of the object. | |
ShippingState | String | Shipping Address state of the object. | |
ShippingZip | String | Shipping Address zip of the object. | |
Sic | String | SIC of the object. | |
Site | String | Site of the object. | |
TickerSymbol | String | Ticker Symbol of the object. | |
Type | String | Type of the object. | |
Website | String | Website of the object. | |
CreatedAt | Datetime | Creation time of the object. | |
UpdatedAt | Datetime | Last update time of the object. | |
AssignedToId | Int64 | ID of the user who the object record is assigned to. | |
CreatedById | Int64 | ID of the individual who created this object. | |
UpdatedById | Int64 | ID of the individual who last updated this object. |
Query the replyTo options of email templates.
Name | Type | References | Description |
EmailId [KEY] | Int64 | ID of the email template. | |
Type | String | The sending user type. Can be general_user, specific_user, assigned_user, or account_owner. | |
Address | String | The sending email address(s). | |
UserId | String | The user ID(s) of the sender. | |
ProspectCustomFieldId | String | The ID of the prospect custom field that contains the sender value. Only available on read and query. | |
AccountCustomFieldId | String | The ID of the account custom field that contains the sender value. Only available on read and query. |
Query the sender options of email templates.
Name | Type | References | Description |
EmailId [KEY] | Int64 | ID of the email template. | |
Address | String | The sending email address. | |
Name | String | The name of the sender. | |
Type | String | The sending user type. Can be general_user, specific_user, assigned_user, or account_owner. | |
UserId | String | The user ID of the sender. | |
ProspectCustomFieldId | String | The ID of the prospect custom field that contains the sender value. Only available on read and query. | |
AccountCustomFieldId | String | The ID of the account custom field that contains the sender value. Only available on read and query. |
Retrieve visitor activities.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this campaign. | |
CampaignId | Int64 |
Campaigns.Id | Pardot ID for the associated campaign. |
CustomRedirectId | Int64 | Pardot ID of the custom redirect associated with this visitor activity Note: Appears only if this visitor activity has a custom redirect associated with it. | |
EmailId | Int64 |
Emails.Id | Pardot ID of the email associated with this visitor activity. Appears only if this visitor activity has an email associated with it. |
EmailTemplateId | Int64 | Pardot ID of the email template associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it. | |
FileId | Int64 |
Files.Id | Pardot ID of the file associated with this visitor activity Note: This node will only appear if this visitor activity has a file associated with it. |
FormHandlerId | Int64 |
FormHandlers.Id | Pardot ID of the form handler associated with this visitor activity Note: This node will only appear if this visitor activity has a form handler associated with it. |
FormId | Int64 |
Forms.Id | Pardot ID of the form associated with this visitor activity Note: This node will only appear if this visitor activity has a form associated with it. |
LandingPageId | Int64 |
LandingPages.Id | Pardot ID of the landing page associated with this visitor activity Note: This node will only appear if this visitor activity has a landing page associated with it. |
ListEmailId | Int64 |
ListEmail.Id | Pardot ID of the list email associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it. |
VisitId | Int64 |
Visits.Id | Pardot ID for the associated visit. |
VisitorId | Int64 |
Visitors.Id | Pardot ID for the associated visitor. |
VisitorPageViewId | Int64 | Pardot ID of the visitor page view associated with this visitor activity Note: This node will only appear if this visitor activity has a visitor page view associated with it. | |
OpportunityId | Int64 | Pardot ID for the associated opportunity. | |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
Details | String | Details about this visitor activity such as the name of the object associated with this activity, the search phrase used in a site search query, etc. | |
MultivariateTestVariationId | Int64 | Pardot ID of the multivariate test variation associated with this visitor activity Note: This node will only appear if this visitor activity has a multivariate test variation associated with it. | |
PaidSearchAdId | Int64 | Pardot ID of the paid search ad associated with this visitor activity. Appears only if this visitor activity has a paid search ad associated with it. | |
SiteSearchQueryId | Int64 | Pardot ID of the site search query associated with this visitor activity Note: This node will only appear if this visitor activity has a site search query associated with it. | |
Type | Integer | Visitor activity's type number. | |
TypeName | String | Visitor activity's type name. | |
CreatedAt | Datetime | The timestamp of when this object was created. | |
UpdatedAt | Datetime | The timestamp of when this object was last updated. |
Retrieve visitors.
Name | Type | References | Description |
Id [KEY] | Int64 | Pardot ID for this visitor. | |
DoNotSell | Boolean | Number of page views by this visitor. | |
CampaignId | String | Number of page views by this visitor. | |
IsIdentified | String | Number of page views by this visitor. | |
PageViewCount | Integer | Number of page views by this visitor. | |
IpAddress | String | Visitor's IP address. | |
Hostname | String | Visitor's hostname. | |
CampaignParameter | String | Visitor's campaign parameter utm_campaign from Google Analytics. | |
MediumParameter | String | Visitor's medium parameter utm_medium from Google Analytics. | |
SourceParameter | String | Visitor's source parameter utm_source from Google Analytics. | |
ContentParameter | String | Visitor's content parameter utm_content from Google Analytics. | |
TermParameter | String | Visitor's term parameter utm_term from Google Analytics. | |
CreatedAt | Datetime | The timestamp of when this object was created. | |
UpdatedAt | Datetime | The timestamp of when this object was last updated. | |
ProspectId | Int64 |
Prospects.Id | Pardot ID for the associated prospect. |
ストアドプロシージャはファンクションライクなインターフェースで、Salesforce Marketing Cloud Account Engagement の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。
ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSalesforce Marketing Cloud Account Engagement から関連するレスポンスデータを返します。
Name | Description |
このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。
以下のテーブルは、Salesforce Marketing Cloud Account Engagement のデータベースメタデータを返します。
以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。
次のテーブルは、データ変更クエリのクエリ統計を返します。
利用可能なデータベースをリストします。
次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | データベース名。 |
利用可能なスキーマをリストします。
次のクエリは、すべての利用可能なスキーマを取得します。
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | データベース名。 |
SchemaName | String | スキーマ名。 |
利用可能なテーブルをリストします。
次のクエリは、利用可能なテーブルおよびビューを取得します。
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | テーブルまたはビューを含むデータベース。 |
SchemaName | String | テーブルまたはビューを含むスキーマ。 |
TableName | String | テーブル名またはビュー名。 |
TableType | String | テーブルの種類(テーブルまたはビュー)。 |
Description | String | テーブルまたはビューの説明。 |
IsUpdateable | Boolean | テーブルが更新可能かどうか。 |
利用可能なテーブルおよびビューのカラムについて説明します。
次のクエリは、Prospects テーブルのカラムとデータ型を返します。
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Prospects'
Name | Type | Description |
CatalogName | String | テーブルまたはビューを含むデータベースの名前。 |
SchemaName | String | テーブルまたはビューを含むスキーマ。 |
TableName | String | カラムを含むテーブルまたはビューの名前。 |
ColumnName | String | カラム名。 |
DataTypeName | String | データ型の名前。 |
DataType | Int32 | データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。 |
Length | Int32 | カラムのストレージサイズ。 |
DisplaySize | Int32 | 指定されたカラムの通常の最大幅(文字数)。 |
NumericPrecision | Int32 | 数値データの最大桁数。文字データおよび日時データの場合は、カラムの長さ(文字数)。 |
NumericScale | Int32 | カラムのスケール(小数点以下の桁数)。 |
IsNullable | Boolean | カラムがNull を含められるかどうか。 |
Description | String | カラムの簡単な説明。 |
Ordinal | Int32 | カラムのシーケンスナンバー。 |
IsAutoIncrement | String | カラムに固定増分値が割り当てられるかどうか。 |
IsGeneratedColumn | String | 生成されたカラムであるかどうか。 |
IsHidden | Boolean | カラムが非表示かどうか。 |
IsArray | Boolean | カラムが配列かどうか。 |
IsReadOnly | Boolean | カラムが読み取り専用かどうか。 |
IsKey | Boolean | sys_tablecolumns から返されたフィールドがテーブルの主キーであるかどうか。 |
利用可能なストアドプロシージャをリストします。
次のクエリは、利用可能なストアドプロシージャを取得します。
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | ストアドプロシージャを含むデータベース。 |
SchemaName | String | ストアドプロシージャを含むスキーマ。 |
ProcedureName | String | ストアドプロシージャの名前。 |
Description | String | ストアドプロシージャの説明。 |
ProcedureType | String | PROCEDURE やFUNCTION などのプロシージャのタイプ。 |
ストアドプロシージャパラメータについて説明します。
次のクエリは、UnAssignProspect ストアドプロシージャのすべての入力パラメータについての情報を返します。
SELECT * FROM sys_procedureparameters WHERE ProcedureName='UnAssignProspect' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | ストアドプロシージャを含むデータベースの名前。 |
SchemaName | String | ストアドプロシージャを含むスキーマの名前。 |
ProcedureName | String | パラメータを含むストアドプロシージャの名前。 |
ColumnName | String | ストアドプロシージャパラメータの名前。 |
Direction | Int32 | パラメータのタイプに対応する整数値:input (1)。input/output (2)、またはoutput(4)。input/output タイプパラメータは、入力パラメータと出力パラメータの両方になれます。 |
DataTypeName | String | データ型の名前。 |
DataType | Int32 | データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。 |
Length | Int32 | 文字データの場合は、許可される文字数。数値データの場合は、許可される桁数。 |
NumericPrecision | Int32 | 数値データの場合は最大精度。文字データおよび日時データの場合は、カラムの長さ(文字数)。 |
NumericScale | Int32 | 数値データの小数点以下の桁数。 |
IsNullable | Boolean | パラメータがNull を含められるかどうか。 |
IsRequired | Boolean | プロシージャの実行にパラメータが必要かどうか。 |
IsArray | Boolean | パラメータが配列かどうか。 |
Description | String | パラメータの説明。 |
Ordinal | Int32 | パラメータのインデックス。 |
主キーおよび外部キーについて説明します。
次のクエリは、Prospects テーブルの主キーを取得します。
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Prospects'
Name | Type | Description |
CatalogName | String | キーを含むデータベースの名前。 |
SchemaName | String | キーを含むスキーマの名前。 |
TableName | String | キーを含むテーブルの名前。 |
ColumnName | String | キーカラムの名前 |
IsKey | Boolean | カラムがTableName フィールドで参照されるテーブル内の主キーかどうか。 |
IsForeignKey | Boolean | カラムがTableName フィールドで参照される外部キーかどうか。 |
PrimaryKeyName | String | 主キーの名前。 |
ForeignKeyName | String | 外部キーの名前。 |
ReferencedCatalogName | String | 主キーを含むデータベース。 |
ReferencedSchemaName | String | 主キーを含むスキーマ。 |
ReferencedTableName | String | 主キーを含むテーブル。 |
ReferencedColumnName | String | 主キーのカラム名。 |
外部キーについて説明します。
次のクエリは、他のテーブルを参照するすべての外部キーを取得します。
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
名前 | タイプ | 説明 |
CatalogName | String | キーを含むデータベースの名前。 |
SchemaName | String | キーを含むスキーマの名前。 |
TableName | String | キーを含むテーブルの名前。 |
ColumnName | String | キーカラムの名前 |
PrimaryKeyName | String | 主キーの名前。 |
ForeignKeyName | String | 外部キーの名前。 |
ReferencedCatalogName | String | 主キーを含むデータベース。 |
ReferencedSchemaName | String | 主キーを含むスキーマ。 |
ReferencedTableName | String | 主キーを含むテーブル。 |
ReferencedColumnName | String | 主キーのカラム名。 |
ForeignKeyType | String | 外部キーがインポート(他のテーブルを指す)キーかエクスポート(他のテーブルから参照される)キーかを指定します。 |
主キーについて説明します。
次のクエリは、すべてのテーブルとビューから主キーを取得します。
SELECT * FROM sys_primarykeys
Name | Type | Description |
CatalogName | String | キーを含むデータベースの名前。 |
SchemaName | String | キーを含むスキーマの名前。 |
TableName | String | キーを含むテーブルの名前。 |
ColumnName | String | キーカラムの名前。 |
KeySeq | String | 主キーのシーケンス番号。 |
KeyName | String | 主キーの名前。 |
利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。
次のクエリは、主キーでないすべてのインデックスを取得します。
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | インデックスを含むデータベースの名前。 |
SchemaName | String | インデックスを含むスキーマの名前。 |
TableName | String | インデックスを含むテーブルの名前。 |
IndexName | String | インデックス名。 |
ColumnName | String | インデックスに関連付けられたカラムの名前。 |
IsUnique | Boolean | インデックスが固有の場合はTrue。そうでない場合はFalse。 |
IsPrimary | Boolean | インデックスが主キーの場合はTrue。そうでない場合はFalse。 |
Type | Int16 | インデックスタイプに対応する整数値:statistic (0)、clustered (1)、hashed (2)、またはother (3)。 |
SortOrder | String | 並べ替え順序:A が昇順、D が降順。 |
OrdinalPosition | Int16 | インデックスのカラムのシーケンスナンバー。 |
利用可能な接続プロパティと、接続文字列に設定されている接続プロパティに関する情報を返します。
このテーブルをクエリする際は、config 接続文字列を使用する必要があります。
jdbc:cdata:salesforcepardot:config:
この接続文字列を使用すると、有効な接続がなくてもこのテーブルをクエリできます。
次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | 接続プロパティ名。 |
ShortDescription | String | 簡単な説明。 |
Type | String | 接続プロパティのデータ型。 |
Default | String | 明示的に設定されていない場合のデフォルト値。 |
Values | String | 可能な値のカンマ区切りリスト。別な値が指定されていると、検証エラーがスローされます。 |
Value | String | 設定した値またはあらかじめ設定されたデフォルト。 |
Required | Boolean | プロパティが接続に必要かどうか。 |
Category | String | 接続プロパティのカテゴリ。 |
IsSessionProperty | String | プロパティが、現在の接続に関する情報を保存するために使用されるセッションプロパティかどうか。 |
Sensitivity | String | プロパティの機密度。これは、プロパティがロギングおよび認証フォームで難読化されているかどうかを通知します。 |
PropertyName | String | キャメルケースの短縮形の接続プロパティ名。 |
Ordinal | Int32 | パラメータのインデックス。 |
CatOrdinal | Int32 | パラメータカテゴリのインデックス。 |
Hierarchy | String | このプロパティと一緒に設定する必要がある、関連のある依存プロパティを表示します。 |
Visible | Boolean | プロパティが接続UI に表示されるかどうかを通知します。 |
ETC | String | プロパティに関するその他のさまざまな情報。 |
Cloud がデータソースにオフロードできるSELECT クエリ処理について説明します。
SQL 構文の詳細については、SQL 準拠 を参照してください。
以下はSQL 機能のサンプルデータセットです。 SELECT 機能のいくつかの側面がサポートされている場合には、カンマ区切りのリストで返されます。サポートされていない場合、カラムにはNO が入ります。
名前 | 説明 | 有効な値 |
AGGREGATE_FUNCTIONS | サポートされている集計関数。 | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | COUNT 関数がサポートされているかどうか。 | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | 識別子をエスケープするための開始文字。 | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | 識別子をエスケープするための終了文字。 | ] |
SUPPORTED_OPERATORS | サポートされているSQL 演算子。 | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | GROUP BY がサポートされているかどうか。サポートされている場合、どのレベルでサポートされているか。 | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
OJ_CAPABILITIES | サポートされている外部結合の種類。 | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
OUTER_JOINS | 外部結合がサポートされているかどうか。 | YES, NO |
SUBQUERIES | サブクエリがサポートされているかどうか。サポートされていれば、どのレベルでサポートされているか。 | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
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 | サポートされている数値関数。 | 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 | サポートされている日付および時刻関数。 | 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 | レプリケーション中にスキップされたテーブルを示します。 | |
REPLICATION_TIMECHECK_COLUMNS | レプリケーション中に更新判断のカラムとして使用するかどうかを、(指定された順に)チェックするカラムのリストを含む文字列の配列。 | |
IDENTIFIER_PATTERN | 識別子としてどの文字列が有効かを示す文字列値。 | |
SUPPORT_TRANSACTION | プロバイダーが、コミットやロールバックなどのトランザクションをサポートしているかどうかを示します。 | YES, NO |
DIALECT | 使用するSQL ダイアレクトを示します。 | |
KEY_PROPERTIES | Uniform データベースを特定するプロパティを示します。 | |
SUPPORTS_MULTIPLE_SCHEMAS | プロバイダー用に複数のスキームが存在するかどうかを示します。 | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | プロバイダー用に複数のカタログが存在するかどうかを示します。 | YES, NO |
DATASYNCVERSION | このドライバーにアクセスするために必要な、CData Sync のバージョン。 | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | このドライバーのCData Sync カテゴリ。 | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | API で提供されている以上の、追加SQL 機能がサポートされているかどうか。 | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | バッチ操作がサポートされているかどうか。 | YES, NO |
SQL_CAP | このドライバーでサポートされているすべてのSQL 機能。 | 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 | 使用したいcacheOptions を指定する文字列値。 | |
ENABLE_EF_ADVANCED_QUERY | ドライバーがEntity Framework の高度なクエリをサポートしているかどうかを示します。サポートしていなければ、クエリはクライアントサイドで処理されます。 | YES, NO |
PSEUDO_COLUMNS | 利用可能な疑似カラムを示す文字列の配列。 | |
MERGE_ALWAYS | 値がtrue であれば、CData Sync 内でMerge Model が強制的に実行されます。 | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | レプリケート開始日時を返すSELECT クエリ。 | |
REPLICATION_MIN_FUNCTION | サーバーサイドでmin を実行するために使用する式名を、プロバイダーが指定できるようになります。 | |
REPLICATION_START_DATE | レプリケート開始日を、プロバイダーが指定できるようになります。 | |
REPLICATION_MAX_DATE_QUERY | レプリケート終了日時を返すSELECT クエリ。 | |
REPLICATION_MAX_FUNCTION | サーバーサイドでmax を実行するために使用する式名を、プロバイダーが指定できるようになります。 | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | 初回のレプリケートで、レプリケートをチャンクに分割しないテーブルのリスト。 | |
CHECKCACHE_USE_PARENTID | CheckCache 構文を親キーカラムに対して実行するかどうかを示します。 | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | スキーマファイルの生成に使用できる、ストアドプロシージャを示します。 |
次のクエリは、WHERE 句で使用できる演算子を取得します。
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
WHERE 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデル セクションを参照してください。
Name | Type | Description |
NAME | String | SQL 構文のコンポーネント、またはサーバー上で処理できる機能。 |
VALUE | String | サポートされるSQL またはSQL 構文の詳細。 |
試行された変更に関する情報を返します。
次のクエリは、バッチ処理で変更された行のId を取得します。
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | データ変更処理から返された、データベース生成Id。 |
Batch | String | バッチの識別子。1 は単一処理。 |
Operation | String | バッチ内の処理の結果:INSERTED、UPDATED、またはDELETED。 |
Message | String | SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。 |
プロパティ | 説明 |
AuthScheme | The type of authentication to use when connecting to Salesforce Marketing Cloud Account Engagement. |
Schema | Specify the Salesforce Marketing Cloud Account Engagement API version to use. SalesforcePardotV5 is the new rebuilt API. |
BusinessUnitID | Your Salesforce Marketing Cloud Account Engagement Business Unit ID (BU). Required when connecting via OAuth 2.0. |
IsDemoAccount | Whether the provided account is a demo account or not. |
UseSalesforceSandbox | A boolean determining if the connection should be made to a Salesforce sandbox account. |
User | The user that is SSO-enabled. |
Password | The password of the user that is SSO-enabled. |
SecurityToken | The security token used to authenticate access to the Salesforce Marketing Cloud Account Engagement account. |
プロパティ | 説明 |
BulkMode | Determines if Bulk APIs will be used and if the driver should wait for the results. |
プロパティ | 説明 |
OAuthGrantType | OAuth フローのグラント種別。 |
プロパティ | 説明 |
OAuthJWTCert | JWT 証明書のストア。 |
OAuthJWTCertType | JWT 証明書を格納するキーストアの種類。 |
OAuthJWTCertPassword | OAuth JWT 証明書のパスワード。 |
OAuthJWTCertSubject | OAuth JWT 証明書のサブジェクト。 |
OAuthJWTIssuer | Java Web Token の発行者。 |
OAuthJWTSubject | アプリケーションからデリゲートアクセスの要求対象となるユーザーサブジェクト。 |
プロパティ | 説明 |
SSLServerCert | TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。 |
プロパティ | 説明 |
Verbosity | ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。 |
プロパティ | 説明 |
BrowsableSchemas | このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。 |
プロパティ | 説明 |
Archived | Specify whether to get archived or unarchived records. |
IncludeMessage | Set this property when querying Emails table. If false, the response does not include the html or text body of the email message. Default value is true. |
MaxRows | クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。 |
MaxThreads | Specifies the number of concurrent requests. |
Pagesize | Salesforce Marketing Cloud Account Engagement から返されるページあたりの結果の最大数。 |
PseudoColumns | このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。 |
ResponseMode | The Pardot API supports several output formats, each of which returns different levels of detail in the response. Supported output formats include: full - Returns all supported data for the Pardot object and all objects associated with it. simple - Returns all supported data for for the Pardot object. bulk -- Returns basic data for an object. Does not provide object associations and total object count. The default value is simple. Not applicable for SalesforcePardotV5 schema. |
Timeout | タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。 |
ValueDelimiter | The delimiter used for multi-select fields values. |
このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。
プロパティ | 説明 |
AuthScheme | The type of authentication to use when connecting to Salesforce Marketing Cloud Account Engagement. |
Schema | Specify the Salesforce Marketing Cloud Account Engagement API version to use. SalesforcePardotV5 is the new rebuilt API. |
BusinessUnitID | Your Salesforce Marketing Cloud Account Engagement Business Unit ID (BU). Required when connecting via OAuth 2.0. |
IsDemoAccount | Whether the provided account is a demo account or not. |
UseSalesforceSandbox | A boolean determining if the connection should be made to a Salesforce sandbox account. |
User | The user that is SSO-enabled. |
Password | The password of the user that is SSO-enabled. |
SecurityToken | The security token used to authenticate access to the Salesforce Marketing Cloud Account Engagement account. |
The type of authentication to use when connecting to Salesforce Marketing Cloud Account Engagement.
string
"Auto"
Specify the Salesforce Marketing Cloud Account Engagement API version to use. SalesforcePardotV5 is the new rebuilt API.
string
"SalesforcePardotV5"
Specify the Salesforce Marketing Cloud Account Engagement API version to use. SalesforcePardotV5 is the rebuilt API.
Your Salesforce Marketing Cloud Account Engagement Business Unit ID (BU). Required when connecting via OAuth 2.0.
string
""
Whether the provided account is a demo account or not.
bool
false
If set to true, all the requests will be made to the Pardot demo endpoints.
A boolean determining if the connection should be made to a Salesforce sandbox account.
bool
false
To connect to a Salesforce sandbox account, set UseSalesforceSandbox = true and append the sandbox name to the end of the user name. For example, if your user name is "user", and sandbox name in Salesforce is "sandbox", the specified User should appear as "user.sandbox".
The user that is SSO-enabled.
string
""
An SSO-enabled user is one who can log in to the Pardot user interface (https://pi.pardot.com) using "Log In with Salesforce" or who can access Pardot using the Pardot Lightning App. Together with Password, this field is used for OAuth flow with PASSWORD grant type.
The password of the user that is SSO-enabled.
string
""
An SSO-enabled user is one who can log in to the Pardot user interface (https://pi.pardot.com) using "Log In with Salesforce" or who can access Pardot using the Pardot Lightning App. Together with User, this field is used for OAuth flow with PASSWORD grant type.
The security token used to authenticate access to the Salesforce Marketing Cloud Account Engagement account.
string
""
Together with User and Password, this field can be used to authenticate against the Salesforce server. This is only required if your organization is setup to require it. A security token can be obtained by going to your profile information and resetting your security token. If your password is reset, you will also need to reset the security token.
このセクションでは、本プロバイダーの接続文字列で設定可能なBulkAPI プロパティの全リストを提供します。
プロパティ | 説明 |
BulkMode | Determines if Bulk APIs will be used and if the driver should wait for the results. |
Determines if Bulk APIs will be used and if the driver should wait for the results.
string
"None"
This property can have the following values:
Use BulkMode=BulkWaitForResults if you want to know if a job failed or not, and get more information about the rows that failed by querying LastResultInfo#TEMP table. Since a bulk job may take some initial time until it starts getting processed, this will cause some delays. If you do not want to wait until the job is finished, you can use BulkMode=Bulk
OAuth フローのグラント種別。
string
"CODE"
次のオプションが利用可能です:CODE,PASSWORD
このセクションでは、本プロバイダーの接続文字列で設定可能なJWT OAuth プロパティの全リストを提供します。
プロパティ | 説明 |
OAuthJWTCert | JWT 証明書のストア。 |
OAuthJWTCertType | JWT 証明書を格納するキーストアの種類。 |
OAuthJWTCertPassword | OAuth JWT 証明書のパスワード。 |
OAuthJWTCertSubject | OAuth JWT 証明書のサブジェクト。 |
OAuthJWTIssuer | Java Web Token の発行者。 |
OAuthJWTSubject | アプリケーションからデリゲートアクセスの要求対象となるユーザーサブジェクト。 |
JWT 証明書のストア。
string
""
クライアント証明書のための証明書ストア名。
OAuthJWTCertType フィールドは、OAuthJWTCert により指定された証明書ストアの種類を指定します。 ストアがパスワードで保護されている場合は、OAuthJWTCertPassword でパスワードを指定します。
OAuthJWTCert は、OAuthJWTCertSubject フィールドとともにクライアント証明書を指定するために使われます。 OAuthJWTCert に値がある場合で、OAuthJWTCertSubject が設定されている場合は、証明書の検索が始まります。 詳しくは、OAuthJWTCertSubject フィールドを参照してください。
証明書ストアの指定はプラットフォームに依存します。
Windows の共通のユーザとシステム証明書ストアの指定は以下のとおりです。
MY | 個人証明書と関連付けられた秘密キーを格納している証明書ストア。 |
CA | 証明機関の証明書。 |
ROOT | ルート証明書。 |
SPC | ソフトウェア発行元証明書。 |
Javaでは、証明書ストアは通常、証明書および任意の秘密キーを含むファイルです。
証明書ストアの種類がPFXFile の場合は、このプロパティにファイル名を設定します。 PFXBlob の場合は、このプロパティをPFX ファイルのバイナリコンテンツ(例えば、PKCS12証明書ストア)に設定する必要があります。
JWT 証明書を格納するキーストアの種類。
string
"PEMKEY_BLOB"
このプロパティには次の値の一つを設定できます。
USER | Windows の場合、現在のユーザーにより所有された証明書ストアであることを指定します。 Note:この種類はJava では利用できません。 |
MACHINE | Windows の場合、この証明書ストアがシステムストアであることを指定します。 Note:この種類はJava では利用できません。 |
PFXFILE | この証明書ストアは、証明書を含むPFX(PKCS12)ファイルの名前です。 |
PFXBLOB | この証明書ストアは、PFX(PKCS12)形式の証明書ストアを表すBase-64でエンコードされた文字列です。 |
JKSFILE | この証明書ストアは、証明書を含むJava key store(JKS)ファイルの名前です。 Note:この種類はJava のみで利用できます。 |
JKSBLOB | この証明書ストアは、Java key store(JKS)形式の証明書ストアを表すBase-64でエンコードされた文字列です。 Note:この種類はJava のみで利用できます。 |
PEMKEY_FILE | この証明書ストアは、秘密キーと任意の証明書を含むPEM でエンコードされたファイルの名前です。 |
PEMKEY_BLOB | この証明書ストアは、秘密キーと任意の証明書を含むBase-64でエンコードされた文字列です。 |
PUBLIC_KEY_FILE | この証明書ストアは、PEM またはDER でエンコードされた公開キーの証明書を含むファイルの名前です。 |
PUBLIC_KEY_BLOB | この証明書ストアは、PEM またはDER でエンコードされた公開キーの証明書を含むBase-64でエンコードされた文字列です。 |
SSHPUBLIC_KEY_FILE | この証明書ストアは、SSH 公開キーを含むファイルの名前です。 |
SSHPUBLIC_KEY_BLOB | この証明書ストアは、SSH 公開キーを含むBase-64でエンコードされた文字列です。 |
P7BFILE | この証明書ストアは、証明書を含むPKCS7 ファイルの名前です。 |
PPKFILE | この証明書ストアは、PuTTY 秘密キー(PPK)を含むファイルの名前です。 |
XMLFILE | この証明書ストアは、XML 形式の証明書を含むファイルの名前です。 |
XMLBLOB | この証明書ストアは、XML 形式の証明書を含む文字列の名前です。 |
OAuth JWT 証明書のパスワード。
string
""
証明書ストアでパスワードが必要である場合、このプロパティを使用してパスワードを指定し、証明書ストアにアクセスできます。
OAuth JWT 証明書のサブジェクト。
string
"*"
証明書のサブジェクトは、証明書をロードするときにストア内の証明書を検索するために使用されます。
完全に一致するものが見つからない場合、ストアはプロパティの値を含むサブジェクトを検索します。
それでも一致するものが見つからない場合、プロパティは空白で設定され、証明書は選択されません。
"*" に設定すると、証明書ストアの1番目の証明書が選択されます。
証明書のサブジェクトは識別の名前フィールドおよび値のカンマ区切りのリストです。 例えば、"CN=www.server.com, OU=test, C=US, [email protected]"。共通のフィールドとその説明は以下のとおりです。
フィールド | 説明 |
CN | 共通名。一般的には、www.server.com のようなホスト名です。 |
O | 法人名 |
OU | 法人の部署名 |
L | 法人の住所(市町村名) |
S | 法人の住所(都道府県) |
C | 国名 |
E | Eメールアドレス |
フィールド値にカンマが含まれている場合は、それを引用符で囲む必要があります。
Java Web Token の発行者。
string
""
Java Web Token の発行者。 通常は、OAuth アプリケーションのクライアントId またはE メールアドレスとなります。
アプリケーションからデリゲートアクセスの要求対象となるユーザーサブジェクト。
string
""
アプリケーションからデリゲートアクセスの要求対象となるユーザーサブジェクトです。通常は、ユーザーのアカウント名またはE メールアドレスとなります。
このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。
プロパティ | 説明 |
SSLServerCert | TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。 |
TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。
string
""
TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。
このプロパティは、次のフォームを取ります:
説明 | 例 |
フルPEM 証明書(例では省略されています) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
証明書を保有するローカルファイルへのパス。 | C:\cert.cer |
公開鍵(例では省略されています) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
MD5 Thumbprint (hex 値はスペースおよびコロン区切り) | ecadbdda5a1529c58a1e9e09828d70e4 |
SHA1 Thumbprint (hex 値はスペースおよびコロン区切り) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。
すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。
このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。
プロパティ | 説明 |
Verbosity | ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。 |
このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。
プロパティ | 説明 |
BrowsableSchemas | このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。 |
このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
string
""
スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、 パフォーマンスが向上します。
このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。
プロパティ | 説明 |
Archived | Specify whether to get archived or unarchived records. |
IncludeMessage | Set this property when querying Emails table. If false, the response does not include the html or text body of the email message. Default value is true. |
MaxRows | クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。 |
MaxThreads | Specifies the number of concurrent requests. |
Pagesize | Salesforce Marketing Cloud Account Engagement から返されるページあたりの結果の最大数。 |
PseudoColumns | このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。 |
ResponseMode | The Pardot API supports several output formats, each of which returns different levels of detail in the response. Supported output formats include: full - Returns all supported data for the Pardot object and all objects associated with it. simple - Returns all supported data for for the Pardot object. bulk -- Returns basic data for an object. Does not provide object associations and total object count. The default value is simple. Not applicable for SalesforcePardotV5 schema. |
Timeout | タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。 |
ValueDelimiter | The delimiter used for multi-select fields values. |
Specify whether to get archived or unarchived records.
bool
false
This property is only valid for tables OneToOneEmails and EmailTemplates of SalesforcePardot.
Set this property when querying Emails table. If false, the response does not include the html or text body of the email message. Default value is true.
bool
true
Set this property when querying Emails table. If false, the response does not include the html or text body of the email message. Default value is true.
クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
int
-1
クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
Specifies the number of concurrent requests.
string
"5"
This property allows you to issue multiple requests simultaneously, thereby improving performance. However, the number of concurrent requests should be reduced to avoid the following error "You have exceeded your concurrent request limit".
Salesforce Marketing Cloud Account Engagement から返されるページあたりの結果の最大数。
int
1000
Pagesize プロパティは、Salesforce Marketing Cloud Account Engagement から返されるページあたりの結果の最大数に影響を与えます。より大きい値を設定すると、1ページあたりの消費メモリが増える代わりに、パフォーマンスが向上する場合があります。
このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
string
""
Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。
The Pardot API supports several output formats, each of which returns different levels of detail in the response. Supported output formats include: full - Returns all supported data for the Pardot object and all objects associated with it. simple - Returns all supported data for for the Pardot object. bulk -- Returns basic data for an object. Does not provide object associations and total object count. The default value is simple. Not applicable for SalesforcePardotV5 schema.
string
"simple"
The Pardot API supports several output formats, each of which returns different levels of detail in the response. The default value is simple. Not applicable for SalesforcePardotV5 schema.
タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
int
60
Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。
Timeout の有効期限が切れても処理が完了していない場合は、Cloud は例外をスローします。
The delimiter used for multi-select fields values.
string
","
The delimiter used for multi-select fields values.