このページでは、CData Cloud でのFreshBooks への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

FreshBooks への接続

接続の確立 は、CData Cloud にデータベースを作成するためのFreshBooks への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してFreshBooks からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、FreshBooks に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

FreshBooks への接続

FreshBooks はOAuth 認証標準を利用しています。OAuth を使って認証するには、アプリを作成してOAuthClientId、OAuthClientSecret、およびCallbackURL 接続プロパティを取得する必要があります。

Alpha API への接続

FreshBooks Alpha API への認証にはOAuth 2.0 認証標準を使用します。

OAuth では認証するユーザーにブラウザでFreshBooks との通信を要求します。次のセクションで説明するとおり、Cloud はさまざまな方法でこれをサポートします。

アプリケーションを登録

OAuth クライアントクレデンシャルを取得するには：

1. FreshBooks デベロッパーサイト https://my.freshbooks.com/#/developer にログインして、Create an App をクリックします。
2. ユーザーがアプリへの許可を求められる際に表示される情報を入力します。
3. リダイレクトURL を指定します。

   リダイレクトURI をhttps://localhost:33333/ または他の同様のhttps URL に設定します。

   Web アプリケーションを作成する場合は、コールバックURL を ユーザーがアプリケーションを認可した後に表示したいWeb アプリケーションのページに設定してください。

デスクトップアプリケーションからFreshBooks へ認証

次の接続プロパティを設定して、接続してください。

• OAuthClientId： 接続する会社の名前に設定します。
• OAuthClientSecret： アプリケーション設定のコンシューマーシークレットに設定。
• InitiateOAuth： GETANDREFRESH に設定。InitiateOAuth を使って、OAuth 交換や、手動での接続文字列のアクセストークン設定の繰り返しを避けられます。
• AccountId： データに接続するにはこれを設定します。この値を取得するには、IdentityInfo ビューをクエリします。

接続すると、Cloud はデフォルトブラウザでOAuth エンドポイントを開きます。ログインして、アプリケーションにアクセス許可を与えます。Cloud が以下のOAuth プロセスを完了します。

1. OAuthAccessToken およびOAuthAccessTokenSecret を取得して、リクエストを認証します。
2. アクセストークンの期限が切れたときにはリフレッシュしてください。
3. OAuth 値を保存し、接続の間に維持されるようにします。

Web アプリケーションからFreshBooks へ認証

次の接続プロパティを設定し、アクセストークンを取得します。

• OAuthClientId： 接続する会社の名前に設定します。
• OAuthClientSecret： アプリケーション設定のコンシューマーシークレットに設定。

Web アプリケーションから接続する場合、またはCloud にブラウザウィンドウを開く権限がない場合は、temporary verification value とアクセストークンを交換する必要があります。

1. GetOAuthAuthorizationURL を呼び出します。ストアドプロシージャがFreshBooks のOAuth エンドポイントのURL を返します。
2. OAuth エンドポイントにログインして、アプリケーションを認可します。コールバックURL にリダイレクトされます。

   コールバックURL は、クエリ文字列パラメータ内にverifier code を保有します。パラメータの名前は"oauth_verifier" です。verifier code をコールバックURL から取得します。

3. GetOAuthAccessToken を呼び出します。ストアドプロシージャがアクセストークン を返します。

次の接続プロパティを設定し、データに接続します。

• AccountId
• OAuthClientSecret
• OAuthAccessToken
• OAuthAccessTokenSecret

Note： AccountId を取得するには、IdentityInfo ビューをクエリします。

トークンのリフレッシュ

アクセストークンの期限が切れたときに自動でリフレッシュするには、InitiateOAuth をREFRESH に設定しOAuthRefreshToken を設定してください。あるいは、アクセストークンの期限が切れたときにRefreshOAuthAccessToken ストアドプロシージャを呼び出してアクセトークンをリフレッシュします。

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

The CData Cloud can model your account as a database through one API the データモデル. You can programmatically access the available entities by querying the available システムテーブル.

Key Features

• The Cloud models FreshBooks Categories, Projects, and more, allowing you to write SQL to query FreshBooks data.
• Stored procedures allow you to execute operations to FreshBooks, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your FreshBooks account are immediately reflected when using the Cloud.

Alpha API

The データモデル uses the OAuth 2.0 specification.

Cloud はFreshBooks のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - FreshBooks テーブル

Name	Description
BillPayments	Payments are a record of the payments made on your invoices.
Bills	A Bill is used to record a business transaction where the items and services from a Vendor have been provided to the business owner, but payment isn’t due until a later date.
BillVendors	A Vendor will work with your business to provide goods or services with a Bill to be paid at a later date.
Clients	A client in the new FreshBooks is a resource representing an entity you send invoices to.
Estimates	Estimates in FreshBooks provides Owners and Clients a way to agree and negotiate on the price and scope of work before it commences.
Expenses	Expenses are used to track expenditures your business incurs.
InvoiceProfiles	Invoice Profiles are used to create recurring invoices. They have the ability to be saved as a draft invoice or be automatically sent out to the client via email.
Invoices	Invoices in FreshBooks are what gets sent to Clients, detailing specific goods or services performed or provided by the Administrator of their System, and the amount that Client owes to the Admin.
Items	Items are stored from invoice lines to make invoicing easier in the future.
OtherIncomes	Other Income is for recording income that doesn’t require an invoice and is received through other means.
Payments	Payments are a record of the payments made on your invoices.
Projects	Projects in FreshBooks are used to track business projects and related information such as hourly rate, service(s) being offered, projected end date...etc.
Staff	Staff are your employees. Staff created via this endpoint are only Accounting representations of the staff members of your business, for the purpose of ownership of invoices, expenses, etc. To create staff members for actual use please use the Staff Invitation flow in the new FreshBooks' web interface.
Taxes	FreshBooks allows you to store your previously used taxes to re-apply conveniently.

Payments are a record of the payments made on your invoices.

Table Specific Information

Insert

To insert, BillID, PaidDate,Amount, PaymentType and Code fields are required.

The below example illustrates how to insert in BillPayments table:

INSERT Into BillPayments (BillID, PaidDate, Amount, PaymentType, Code) Values ('12435', '2023-11-02', 2, 'Cash', 'INR')

Update

To update, the Id value is required in the WHERE clause.For example:

UPDATE BillPayments set PaymentType = 'Check' where id = 8082

Delete

To delete, Id value is required in the WHERE clause.

DELETE from BillPayments where Id = '26157'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the payment.
BillId	Integer	False	The identifier of the related bill.
Amount	Decimal	False	Amount paid on invoice to two decimal places.
Code	String	False	Three-letter currency code of payment.
PaidDate	Date	False	Date the payment was made. (YYYY-MM-DD)
MatchedWithExpense	Boolean	True	Indicates whether or not the payment was converted from a credit on a client's account.
Note	String	False	Notes on payment, often used for credit card reference number.
PaymentType	String	False	The type of the payment: 'Check', 'Credit', 'Cash', etc.
VisState	Integer	True	0 for active, 1 for deleted.

A Bill is used to record a business transaction where the items and services from a Vendor have been provided to the business owner, but payment isn’t due until a later date.

Table Specific Information

Insert

To insert, VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate fields are required. To insert Bills with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures.

You can insert Bills with multiple line items using aggregates. CategoryId, UnitCostAmount and Quantity is required. For example:

Insert Into Bills (VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate) Values ('16307', '2023-11-02', 2, INR, en, '[{\"categoryid\":\"46781\",\"quantity\":\"1\",\"unit_cost\":{\"amount\":\"200\"}}]')

You can also insert Bills with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT Into BillsLineItems#Temp (CategoryId, UnitCostAmount, Quantity) Values ('4331719', 1, 2)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

INSERT Into Bills (VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate) Values ('16307', '2023-11-02', 2, INR, en, BillsLineItems#Temp)

Update

To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

Insert Into BillsLineItems#Temp (CategoryId, UnitCostAmount, Quantity) Values ('4331719', 1, 2)

UPDATE Bills SET IssueDate = '2023-11-01', LinesAggregate = 'BillsLineItems#TEMP' WHERE Id = 29153

Delete

To delete, Id value is required in the WHERE clause.

DELETE from Bills where Id = '26157'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Uniquely identifies the bill associated with the business.
VendorId	Integer	False	Unique identifier of client.
Amount	String	True	Bill subtotatal amount excluding the taxes.
AmountCode	String	True	Three-letter currency code
AttachmentId	String	True	Attachment Id
AttachmentType	String	True	Media Type of Attachment
AttachmentJWT	String	True	Attachment JWT
BillNumber	String	False	Reference to vendor bill number
BillPaymentsAggregate	String	True	Bill Payments made against the bill
CreatedAt	Datetime	True	Time the invoice was created, YYYY-MM-DD HH:MM:SS format
CurrencyCode	String	False	Three-letter currency code
DueDate	Date	True	Date for which the bill is due for payment
DueOffsetDays	Integer	False	Number of days from the issue date that the invoice needs to be set to due
IssueDate	Date	False	Date when the bill was issued by the vendor
Language	String	False	Two-letter language code, e.g. “en”
LinesAggregate	String	False	Array of bill line items.
OutstandingAmount	String	True	Outstanding/unpaid amount on the bill
OutstandingCode	String	True	Three-letter currency code
OverallCategory	String	True	If multiple categories are selected in the bill lines, then overall_category is Split. Otherwise, it will be the selected category.
OverallDescription	String	True	First non-null value of bill line descriptions
PaidAmount	String	True	Paid amount on the bill
PaidCode	String	True	Three-letter currency code
Status	String	True	Status of the bill: “unpaid”, “overdue”, “partial”, “paid”
TaxAmountAmount	String	True	Total tax amount on the bill
TaxAmountCode	String	True	Three-letter currency code
TotalAmountAmount	String	True	Grand total amount on bill amount tax amount
TotalAmountCode	String	True	Three-letter currency code
UpdatedAt	Datetime	True	Last time the resource was updated. YYYY-MM-DD HH:MM:SS format
VisState	Integer	True	0 for active, 1 for deleted, 2 for archived

A Vendor will work with your business to provide goods or services with a Bill to be paid at a later date.

Table Specific Information

Insert

To insert, VendorName, CurrencyCode and Language fields are required. To insert BillVendors with multiple tax defaults, you can either insert an aggregate value into the TaxDefaults field, or use a temporary table. This section provides examples of both procedures.

You can insert BillVendors with multiple tax defaults using aggregates. For example:

Insert Into BillVendors (VendorName, CurrencyCode, Language, TaxDefaultsAggregate) Values ('Test23', 'INR', 'en', '[{\"system_taxid\":\"7620\"}]')

You can also insert Invoices with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO BillVendorsTaxDefaults#TEMP (SystemTaxId) VALUES ('23145')

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO BillVendors (VendorName, CurrencyCode, Language, TaxDefaultsAggregate) Values ('Test23', 'INR', 'en',  BillVendorsTaxDefaults#TEMP)

Update

To update, the VendorId value is required in the WHERE clause. For example:

Update BillVendors set VendorName = 'testing' where VendorId = 16651

Delete

To delete, VendorId value is required in the WHERE clause.

DELETE from BillVendors where VendorId = '26157'

Columns

Name	Type	ReadOnly	Description
VendorId [KEY]	Integer	True	Unique identifier for vendor
AccountNumber	String	False	Account number of the vendor
City	String	False	City of vendor
Country	String	False	Country of vendor
CreatedAt	Datetime	True	The vendor was created, YYYY-MM-DD HH:MM:SS format
CurrencyCode	String	False	Default three-letter currency code for vendor
Is1099	Boolean	False	Set true if vendor is a 1099 contractor
Language	String	False	Two-letter language code, e.g. “en”
Note	String	True	Note
OutstandingBalanceAmount	String	True	The outstanding amount to be paid to the Vendor
OutstandingBalanceCode	String	True	Three-letter currency code
OverdueBalanceAmount	String	True	Overdue amount to be paid to the Vendor
OverdueBalanceCode	String	True	Three-letter currency code
Phone	String	False	Phone number
PostalCode	String	False	Postal code
PrimaryContactEmail	String	False	Vendor primary email
PrimaryContactFirstName	String	False	Vendor primary first name
PrimaryContactLastName	String	False	Vendor primary last name
Province	String	False	Province
Street	String	False	Street address
Street2	String	False	Street address 2nd part
TaxDefaultsAggregate	String	False	Tax Defaults Aggregate
UpdatedAt	Datetime	True	Time of last modification to resource
VendorName	String	False	Vendor Name
VisState	Integer	True	Visibility state, possible values are 0, 1, 2
Website	String	False	Vendor website address

A client in the new FreshBooks is a resource representing an entity you send invoices to.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of a client.
AccountingSystemId	String	True	The unique identifier of the accounting system.
AllowLateNotifications	Boolean	False	Indicates whether client is allowed late notifications. (deprecated)
AllowLateFees	Boolean	False	Indicates whether client is allowed late fees. (deprecated)
BusinessPhone	String	False	The business phone number of the client.
CompanyIndustry	String	False	Description of the industry the client is in.
CompanySize	String	False	Size of client's company.
CurrencyCode	String	False	3-letter shortcode for client's preferred currency.
DirectLinkToken	String	True	(deprecated)
Email	String	False	The email of the client.
Fax	String	False	The fax number of the client.
FirstName	String	False	The first name of the client.
HomePhone	String	False	The home phone number of the client.
Language	String	False	Shortcode indicating user language.
LastActivity	Datetime	False	Time of last client activity.
LastLogin	String	False	Client's last login time.
Level	Integer	True	Indication of access level on system. (deprecated)
LastName	String	False	The last name of the client.
MobilePhone	String	False	The mobile phone number of the client.
Note	String	False	Notes kept by administrator about client.
Notified	Boolean	False	(deprecated)
NumLogins	Integer	False	Number of client logins.
Organization	String	False	Name for client's business.
BillingCity	String	False	Client's billing city.
BillingPostalCode	String	False	Client's billing postal code.
BillingCountry	String	False	Client's billing country.
BillingProvince	String	False	Client's billing province.
BillingStreetAddress	String	False	Client's billing street address.
BillingStreetAddress2	String	False	Second line of client's billing street address.
PrefEmail	Boolean	False	Boolean value indicating whether they prefer email over ground mail.
PrefGmail	Boolean	False	Boolean value indicating whether they prefer ground mail over email.
ShippingCity	String	False	Client's shipping city.
ShippingCode	String	False	Client's shipping postal code.
ShippingCountry	String	False	Client's shipping country.
ShippingProvince	String	False	Client's shipping province.
ShippingStreetAddress	String	False	Client's shipping street address.
ShippingStreetAddress2	String	False	Second line of client's shipping street address.
SignupDate	Datetime	False	Time of user signup.
StatementToken	String	True	(deprecated)
Subdomain	String	True	Client subdomain name. (deprecated)
Updated	Datetime	False	Time of last modification to resource.
Username	String	False	Username used by client to log in.
VatName	String	False	Value Added Tax name.
VatNumber	Integer	False	Value Added Tax number.
VisState	Integer	False	Visibility state of the client - values can be active, deleted, or archived.

Estimates in FreshBooks provides Owners and Clients a way to agree and negotiate on the price and scope of work before it commences.

Table Specific Information

Insert

To insert, CustomerId and CreateDate fields are required. To insert Estimates with multiple line items, you can either insert into the LinesAggregate field an aggregate value, or use a temporary table. This section provides examples of both procedures.

You can insert Estimates with multiple line items using aggregates. For example:

Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03',
               '[\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"Paperwork\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"5000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      },\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"TV Ads\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"3000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      }\n" +
        "    ]')"

You can also insert Estimates with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO EstimatesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO EstimatesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('C', 300 )
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('D', 150 )
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('E', 200 )

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'EstimatesLineItems#TEMP')

Update

To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO Estimate2195LineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO Estimate2195LineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE Estimates SET EstimateNumber = 5, DiscountValue = 15, LinesAggregate = 'Estimate2195LineItems#TEMP' WHERE id = 2195

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of an estimate.
AccountingSystemId	String	True	The unique identifier for the system.
UIStatus	String	True	Estimate UI status
Status	String	True	Estimate status
Accepted	Boolean	True	Indicates whether estimate has been accepted.
Amount	Deicmal	True	Total amount of estimate, to two decimal places.
AmountCode	String	True	Three letter currency code associated with the amount.
DiscountTotalAmount	Decimal	True	Amount of discount, to two decimal places.
DiscountTotalCode	String	True	Three letter currency code associated with the discount.
Description	String	True	Description of first line of estimate.
CurrentOrganization	String	True	Name of organization being estimated.
Invoiced	String	True	Indicator of whether this estimate has been converted to an invoice that was sent.
OwnerId	Integer	True	Identifier of the creator of the estimate. (writable on create only)
SentId	Integer	True	Identifier of user who sent the estimate, typically 1 for admin. (writable on create only)
CreatedAt	Datetime	True	The time the estimate was created. (writable on create only)
Updated	Datetime	True	The time the estimate was last updated. (writable on create only)
DisplayStatus	String	True	Description of status shown in FreshBooks UI. Either draft, sent, or viewed. (writable on create only)
ReplyStatus	String	True	Description of status shown in Classic FreshBooks UI. Either replied or resolved. (deprecated)
EstimateNumber	String	False	User-specified and visible estimate Id.
CustomerId	Integer	False	Unique identifier of client.
CreateDate	Date	False	The date the estimate was created.
DiscountValue	Decimal	False	Decimal-string amount of discount.
PONumber	String	False	Post Office box number for address on estimate.
Template	String	False	Choice of rendering style. (internal, deprecated)
CurrencyCode	String	False	Three-letter currency code for estimate.
Language	String	False	Two-letter language code.
Terms	String	False	Terms listed on estimate.
Notes	String	False	Notes listed on estimate.
Address	String	False	First line of address listed on estimate.
ExtArchive	Integer	False	0 or 1 indicating whether estimate is archived or not. (deprecated)
VisState	Integer	False	0 indicates active, 1 indicates deleted.
Street	String	False	Street for address on estimate.
Street2	String	False	Second line of address on estimate.
City	String	False	City of address on estimate.
Province	String	False	Province of address on estimate.
Country	String	False	Country of address on estimate.
Organization	String	False	Name of organization being estimated.
FirstName	String	False	First name of client on estimate.
LastName	String	False	Last name of client being estimated.
VatName	String	False	Value Added Tax name if provided.
VatNumber	String	False	Value Added Tax number if provided.
LinesAggregate	String	False	An aggregate of lines associated with an estimate. See the help for the Estimates table for more information on inserting and updating to this field.

Expenses are used to track expenditures your business incurs.

Columns

Name	Type	ReadOnly	Description
ExpenseId [KEY]	Integer	True	A unique identifier for the expense, unique to business id.
AccountName	String	False	The name of the related account, if applicable.
AccountId	Integer	False	The identifier of the related account, if applicable.
AccountingSystemId	String	False	The identifier of the system.
Amount	Decimal	False	The amount of the expense.
BackgroundJobId	Integer	False	The identifier of the related background job if applicable.
BankName	String	False	The name of the bank the expense was imported from, if applicable.
CategoryId	Integer	False	The identifier of the related expense category.
ClientId	Integer	False	The Id of the related client, if applicable.
Code	String	False	Three-letter currency code for expense.
CompoundedTax	Boolean	False	Boolean indicated if the tax was a compound tax.
Date	Date	False	Date of the expense. (YYYY-MM-DD)
ExtInvoiceId	Integer	False	The identifier of the related contractor invoice if applicable.
ExtSystemId	Integer	False	The identifier of the related contractor system if applicable.
HasReceipt	Boolean	False	Boolean indicating if there is a receipt attached.
InvoiceId	Integer	False	Identifier of the related invoice, if applicable.
IsDuplicate	Boolean	False	Boolean indicating if this is a duplicated expense.
MarkupPercent	String	False	Note of percent to mark the expense up. (String-decimal)
Notes	String	False	Notes about the expense.
ProfileId	Integer	False	Identifier of related profile, if applicable.
ProjectId	Integer	False	Identifier of related project, if applicable.
StaffId	Integer	False	Identifier of related staff member, if applicable.
Status	Integer	False	Values from expense status table.
TaxAmount1	Decimal	False	The amount of the first tax.
TaxAmount2	Decimal	False	The amount of the second tax.
TaxName1	String	False	Name of first tax.
TaxName2	String	False	Name of second tax.
TaxPercent1	Decimal	False	The percent of the first tax.
TaxPercent2	Decimal	False	The percent of the second tax.
TransactionId	Integer	False	Identifier of related transaction, if applicable.
Updated	Datetime	False	Time the invoice was last updated. (YYYY-MM-DD HH:MM:SS)
Vendor	String	False	Name of vendor.
VisState	Integer	False	0 for active, 1 for deleted.

Invoice Profiles are used to create recurring invoices. They have the ability to be saved as a draft invoice or be automatically sent out to the client via email.

Table Specific Information

Insert

To insert, Frequency, CreateDate, NumberRecurring and CustomerID fields are required. To insert InvoiceProfiles with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures.

You can insert Invoices with multiple line items using aggregates. For example:

Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761','[{ \"description\": \"This is description\", \"taxName1\": \"Tax1\", \"taxAmount1\": 0,\"name\": \"Paperwork\", \"unit_cost\": {\"amount\": \"5000.00\", \"code\": \"USD\"}}]')

You can also insert InvoiceProfiles with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761', 'InvoicesLineItems#TEMP')

Update

To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE InvoiceProfiles SET CreateDate = '2023-11-08', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE Id = 29153

Delete

To delete, Id value is required in the WHERE clause.

DELETE from InvoiceProfiles where Id = '26157'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique id for the invoice profile
AccountingSystemId	String	False	Unique id for the system
Address	String	False	The address on the invoice
Amount	String	True	The amount to be paid for invoice
AmountCode	String	True	Three-letter currency code
AutoBill	Boolean	True	Whether this invoice has a credit card saved
BillGateway	String	False	The gateway that is used for payment
City	String	False	City for address on invoice
Code	String	True	Three-letter currency code
Country	String	False	Country for address on invoice
CreateDate	Date	False	The create day of the invoice profile
CurrencyCode	String	True	Three-letter currency code for invoice
CustomerId	Integer	False	The unique id for the client of the invoice
Description	String	False	The description of the invocie
Disable	Boolean	False	True will disable the auto-generation of invoices
DiscountTotalAmount	String	True	The amount of the discount
DiscountTotalCode	String	True	Three-letter currency code for the discount
DiscountValue	String	False	Decimal-string amount
DueOffsetDays	Integer	False	Number of days from creation that invoice is due
ExtArchive	Integer	False	0 or 1 indicating archived or not
Fname	String	False	The first name of client on invoice
Frequency	String	False	The frequency the invoice will be created. In the form of xy where x is an integer and y is either d,w,m,y. (example: Every two weeks would be 2w)
IncludeUnbilledTime	Boolean	False	True if unbilled time is included, false otherwise
Language	String	False	2 letter code representing the language
Lname	String	False	The last name of client on invoice
Notes	Unknown	False	Notes listed on invoice
NumberRecurring	Integer	False	The number of invoices that will be generated, 0 for infinite
OccurrencesToDate	Integer	True	Number of invoices that have been generated
Organization	String	False	Name of the organization belonging to the client
OwnerId	Integer	True	Id of creator of invoice. 1 if business admin, other if created by e.g. a contractor
PaymentDetails	String	False	Details for payment for the invoice
PoNumber	String	False	Post office box number for address on invoice
ProfileId	Integer	True	Profile Id
Province	String	False	Province for address on invoice.
SendEmail	Boolean	False	True email invoice on creation, false will leave as draft
SendGmail	Boolean	False	True to send invoice via ground mail
Street	String	False	Street for address on invoice
Street2	String	False	Second street for address on invoice
Terms	String	False	Terms listed on invoice
Updated	Datetime	True	The date the invoice profile was updated
Vatname	String	False	Value added tax name if provided
Vatnumber	String	False	Value added tax number if provided
Visstate	Integer	False	0 for active, 1 for deleted
LinesAggregate	String	False	An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field.

Invoices in FreshBooks are what gets sent to Clients, detailing specific goods or services performed or provided by the Administrator of their System, and the amount that Client owes to the Admin.

Table Specific Information

Insert

To insert, CustomerId and CreateDate fields are required. To insert Invoices with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures.

You can insert Invoices with multiple line items using aggregates. For example:

Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03',
               '[\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"Paperwork\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"5000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      },\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"TV Ads\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"3000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      }\n" +
        "    ]')"

You can also insert Invoices with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('C', 300)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('D', 150)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('E', 200)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'InvoicesLineItems#TEMP')

Update

To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE Invoices SET CreateDate = '2018-01-01', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE Id = 29153

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Invoice Id that is unique to this system.
AccountingSystemId	String	True	Unique Id for the system.
AccountId	String	True	Unique Id for the system.
TotalAmount	Decimal	True	Total amount of invoice, to two decimal places.
PaidAmount	Decimal	True	Amount paid on invoice, to two decimal places.
PaidCode	String	True	Three-letter currency code.
OutstandingAmount	Decimal	True	Amount outstanding on the invoice, to two decimal places.
OutstandingCode	String	True	Three-letter currency code.
DiscountTotalAmount	Decimal	True	Amount of discount, to two decimal places.
DiscountTotalCode	String	True	Three-letter currency code.
Description	String	True	Description of first line of invoice.
CurrentOrganization	String	True	Name of organization being invoiced.
DueDate	Date	True	Date invoice is marked as due by. (YYYY-MM-DD, calculated from due_offset_days)
DatePaid	Date	True	Date invoice was fully paid. (YYYY-MM-DD)
OwnerId	Integer	False	Id of the invoice's creator. (writable on create only)
EstimateId	Integer	False	Id of associated estimate, 0 if none.(writable on create only)
BasecampId	Integer	False	Id of connected basecamp account, 0 if none.(writable on create only)
SentId	Integer	False	User Id of user who sent the invoice.(writable on create only)
Status	String	False	Invoice status.(writable on create only)
Parent	Integer	False	Id of object this invoice was generated from, 0 if none.(writable on create only)
CreatedAt	Datetime	False	Time the invoice was created. (YYYY-MM-DD, writable on create only)
Updated	Datetime	False	Time the invoice was last updated. (YYYY-MM-DD, writable on create only)
DisplayStatus	String	False	Description of status shown in Freshbooks UI.(writable on create only)
AutobillStatus	String	False	Description of autobill status.(writable on create only)
PaymentStatus	String	False	Description of payment status.(writable on create only)
LastOrderStatus	String	False	Describes the status of the last attempted payment.(writable on create only)
DisputeStatus	String	False	Description of whether invoice has been disputed.(writable on create only)
DepositStatus	String	False	Description of deposits applied to invoice.(writable on create only)
AutoBill	String	False	Whether this invoice has a credit card saved.(writable on create only)
V3Status	String	False	Description of Invoice status.(writable on create only)
InvoiceNumber	String	False	User-specified and visible Invoice Id.
CustomerId	Integer	False	Client Id unique to this system.
CreateDate	Date	False	Date invoice was created. (YYYY-MM-DD)
GenerationDate	Date	False	Date invoice was generated from object.(YYYY-MM-DD, or null if not)
DiscountValue	Decimal	False	Decimal-string amount of discount.
DiscountDescription	String	False	Public note about discount.
PONumber	String	False	Reference number for address on invoice.
Template	String	False	Choice of rendering style. (internal, deprecated)
CurrencyCode	String	False	Three-letter currency code for invoice.
Language	String	False	Two-letter language code.
Terms	String	False	Terms listed on invoice.
Notes	String	False	Notes listed on invoice.
Address	String	False	First line of address on invoice.
ReturnUri	String	False	(deprecated)
DepositAmount	Decimal	False	Amount required as deposit, null if none.
DepositPercentage	Decimal	False	Percentage of the invoice's value required as a deposit.
Gmail	String	False	Whether to send via ground mail.
ShowAttachments	String	False	Whether attachments on invoice are rendered.
VisState	Integer	False	Whether active (0) or deleted (1).
Street	String	False	Street for address on invoice.
Street2	String	False	Second line of street for address on invoice.
City	String	False	City for address on invoice.
Province	String	False	Province for address on invoice.
Code	String	False	Zip code for address on invoice.
Country	String	False	Country for address on invoice.
Organization	String	False	Name of organization being invoiced.
FirstName	String	False	First name of client on invoice.
LastName	String	False	Last name of client on invoice.
VatName	String	False	Value added tax name, if provided.
VatNumber	String	False	Value added tax number, if provided.
DueOffsetDays	Integer	False	Number of days from creation that invoice is due.
LinesAggregate	String	False	An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field.

Items are stored from invoice lines to make invoicing easier in the future.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Unique identifier of the item.
AccountingSystemId	String	True	Unique identifier of business client exists on.
Amount	Decimal	False	Amount paid on invoice, to two decimal places.
Code	String	False	Three-letter currency code.
Description	String	False	Descriptive text for item.
Inventory	String	False	Count of inventory.
Name	String	False	Descriptive name of item.
Quantity	String	False	Number to multiply unit cost by.
Tax1	Integer	False	Identifier of tax on invoice.
Tax2	Integer	False	Identifier of second tax on invoice if applicable.
Updated	Datetime	False	Date object was last updated. (YYYY-MM-DD)
VisState	Integer	False	0 for active, 1 for deleted.

Other Income is for recording income that doesn’t require an invoice and is received through other means.

Table Specific Information

Insert

To insert OtherIncomes Date and Source fields are required. To insert OtherIncomes with multiple taxes, you can either insert an aggregate value into the TaxesAggregate field, or use a temporary table. This section provides examples of both procedures.

You can insert OtherIncomes with multiple taxes using aggregates. For example:

Insert INTO OtherIncomes (Date, Source, TaxesAggregate) VALUES ('2018-02-03','Shopify','[{\"amount\" : \"5\", \"name\" : \"GST\"}]')"

You can also insert OtherIncomes with multiple taxes using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax1', 500)
INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax2', 200)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for TaxesAggregate. For example:

Insert INTO OtherIncomes (Date, Source, TaxesAggregate) VALUES ('2018-02-03','Shopify', OtherIncomesTaxes#TEMP)

Update

To update, the IncomeId value is required in the WHERE clause. To update TaxesAggregate, you need to override the aggregate TaxesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax1', 500)
INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax2', 300)

UPDATE OtherIncomes SET Date = '2018-01-01', TaxesAggregate = 'InvoiceLineItems#TEMP' WHERE IncomeId = 29153

Delete

To delete, IncomeId value is required in the WHERE clause.

DELETE from OtherIncomes where IncomeId = '26157'

Columns

Name	Type	ReadOnly	Description
IncomeId [KEY]	Integer	True	Unique identifier of this other income entry within the business
Amount	String	False	Amount of the income, to two decimal places
AmountCode	String	False	Three-letter currency code
CategoryName	String	False	Options include: advertising, in_person_sales, online_sales, rentals, other
CreatedAt	Datetime	True	Time the other income entry was created, YYYY-MM-DD HH:MM:SS format
Date	Date	False	The date the income was received, YYYY-MM-DD format
Note	String	False	Notes on the income
PaymentType	String	False	Optional type of payment made. “Check”, “Credit”, “Cash”, etc.
Source	String	False	Source of external income. E.g. Shopify, Etsy, Farmers’ Market
Sourceid	Unknown	False	Source ID
TaxesAggregate	String	False	Taxes Aggregate
UpdatedAt	Datetime	True	Time the other income entry was last updated, YYYY-MM-DD HH:MM:SS format
Userid	Unknown	True	User Id
VisState	Integer	True	0 for active,1 for deleted,2 for archived(more info on vis_state)

Payments are a record of the payments made on your invoices.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the payment.
AccountingSystemId	String	True	The unique identifier of the business the client exists on.
Amount	Decimal	False	Amount paid on invoice to two decimal places.
ClientId	Integer	True	Identifier of client who made the payment.
Code	String	False	Three-letter currency code of payment.
CreditId	Integer	False	Identifier of related credit.
Date	String	False	Date the payment was made. (YYYY-MM-DD)
FromCredit	Boolean	False	Indicates whether or not the payment was converted from a credit on a client's account.
Gateway	String	True	The payment processor used, if any.
InvoiceId	Integer	False	The identifier of the related invoice.
Note	String	False	Notes on payment, often used for credit card reference number.
OrderId	Integer	False	Identifier of related order.
OverpaymentId	Integer	True	Identifier of related overpayment if relevant.
TransactionId	Integer	False	Identifier of the related transaction. (deprecated)
Type	String	False	The type of the payment: 'Check', 'Credit', 'Cash', etc.
Updated	Datetime	True	Date object was last updated. (YYYY-MM-DD)
VisState	Integer	False	0 for active, 1 for deleted.

Projects in FreshBooks are used to track business projects and related information such as hourly rate, service(s) being offered, projected end date...etc.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the project.
Title	String	False	Title of the project.
ClientId	String	False	Unique identifier of the client.
GroupId	String	True	Unique id of group membership.
FixedPrice	Decimal	False	Used for flat-rate projects. Represents the amount being charged to the client for the project.
UpdatedAt	Datetime	True	The time the project was last updated.
Sample	Boolean	True	True if project is sample.
Active	Boolean	True	Whether the project is active or not.
Rate	String	True	The hourly rate of the project. Only applies to hourly_rate projects.
ProjectType	String	False	Type of project: fixed_price, hourly_rate.
Description	String	True	Description of project.
LoggedDuration	Integer	True	The time logged for the project in seconds.
Internal	Boolean	True	Clarifies that the project is internally within the company (client is the company).
DueDate	Date	True	Date of projected completion.
Complete	Boolean	True	Whether the project is completed or not.
BilledAmount	Decimal	True	The amount billed for the project.
BilledStatus	String	True	Shows the billed status.
BillingMethod	String	True	The method of payment for the project.
CreatedAt	Datetime	True	The date/time the project was created.
Budget	Integer	True	Budget for project.

Staff are your employees. Staff created via this endpoint are only Accounting representations of the staff members of your business, for the purpose of ownership of invoices, expenses, etc. To create staff members for actual use please use the Staff Invitation flow in the new FreshBooks' web interface.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	False	The unique identifier of the staff member to this business id.
AccountingSystemId	String	False	The unique identifier of the business staff member exists on.
APIToken	String	False	Token used for Classic API. (deprecated)
BusinessPhone	String	False	The business phone number.
CurrencyCode	String	False	Three-digit shortcode for preferred currency.
DisplayName	String	False	Name chosen by staff member to display.
Email	String	False	Email address for staff member.
Fax	String	False	Fax number for staff member.
FirstName	String	False	First name of staff member.
HomePhone	String	False	Staff member's home phone number.
Language	String	False	Staff member's selected language.
LastLogin	Datetime	False	Date and time the staff account was last logged into.
Level	Integer	False	Description of staff member access level. (deprecated)
LastName	String	False	Last name of staff member.
MobilePhone	String	False	Staff member's mobile phone number.
Note	String	False	Notes about staff member.
Logins	Integer	False	Number of times the staff member has logged in.
Organization	String	False	Organization the staff member is affiliated with.
BillingCity	String	False	Staff member's billing address city.
BillingCode	String	False	Staff member's billing address zip code.
BillingCountry	String	False	Staff member's billing address country.
BillingProvince	String	False	Staff member's billing address province.
BillingStreet	String	False	Staff member's billing address primary street information.
BillingStreet2	String	False	Staff member's billing address secondary street information.
Rate	String	False	Rate this staff member is billed at.
SignupDate	Datetime	False	Date the staff member account was created. (YYYY-MM-DD HH:MM:SS)
Updated	Datetime	False	Date the staff member account was last updated. (YYYY-MM-DD HH:MM:SS)
Username	String	False	Username specified for the staff member; randomly assigned if none specified at creation time.
VisState	Integer	False	Visibility state: 'active,' 'deleted,' or 'archived.'

FreshBooks allows you to store your previously used taxes to re-apply conveniently.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the tax.
AccountingSystemId	String	True	The unique identifier of the business the client exists on.
Amount	Decimal	False	The string-decimal representing percentage value of tax.
Compound	Boolean	False	Indicates whether this is a compound tax to calculate on top of primary taxes.
Name	String	False	Identifiable name for the tax.
Number	String	False	An external number that identifies the tax submission.
TaxId	Integer	True	The unique identifier of the tax within this business.
Updated	Datetime	True	The date the staff object was last updated. (YYYY-MM-DD HH:MM:SS)

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - FreshBooks ビュー

Name	Description
AccountsAgingReport	The Account Aging Report shows details regarding overdue invoices from clients.
BillsLineItems	Bill lines are used to determine the amount of a bill.
BillVendorsTaxDefaults	FreshBooks allows you to store your previously used taxes to re-apply conveniently.
EstimatesLineItems	Estimate lines are used to determine the amount of an estimate.
ExpenseCategories	FreshBooks supplies Expense Categories to group expenses together to aid in expense tracking.
ExpensesReport	The Expenses Report shows all the information involving your Expenses.
Gateways	Gateways are also referred to as payment processors. The information returned by these endpoints specifies what payment processors are enabled for your businesses.
IdentityInfo	You can find ids for the Businesses and Accounts a user can interact with by querying the IdentityInfo view.
InvoicesLineItems	Invoice lines are used to determine the amount of an invoice, in addition to being able to tie the invoice to rebilled expenses.
InvoicesReport	The Invoice Details Report shows all the information involving your invoices.
OtherIncomesTaxes	Other Income is for recording income that doesn’t require an invoice and is received through other means.
PaymentsCollectedReport	The Payments Collected Report shows details regarding collected payments made to your business.
ProfitLossReport	The Profit/Loss Report shows all the information involving both your Profits and Losses.
Systems	An Accounting System represents an entity that can send invoices. It is the central point of association between all of a single Administrator of a single Business, Invoices, Clients, Staff, Expenses, and Reports.
Tasks	Tasks in Freshbooks represent services that your business offers to clients. Tasks are used to keep track of details of the service such as name and hourly rate.
TaxSummaryReport	The Tax Summary Report that outlines the taxes involved with your sales.
TimeEntries	Time Entries represent time spent working for a client or project.

The Account Aging Report shows details regarding overdue invoices from clients.

Columns

Name	Type	Description
UserId [KEY]	String	Unique identifier of the client
Lname	String	The last name of the client
Fname	String	The first name of the client
Organization	String	Organization of the client
Email	String	Email of the client
CurrencyCode	String	Three letter currency code
CompanyName	String	Company Name of the client
DownloadToken	String	The download token allows you to download the report into a csv file
EndDate	Date	The ending date for the profit/loss report query
Account_0To30Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 0-30 interval.
Account_0To30Interval_Code	String	Three letter currency code
Account_31To60Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 31-60 interval.
Account_31To60Interval_Code	String	Three letter currency code
Account_61To90Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 61-90 interval.
Account_61To90Interval_Code	String	Three letter currency code
Account_From91_Amount	String	The total amount due for a particular account from all its overdue invoices for more than 90 days.
Account_From91_Code	String	Three letter currency code
AccountTotalAmount	String	The total amount due for a particular account from all its overdue invoices.
AccountTotalCode	String	Three letter currency code
Totals_0To30Interval_Amount	String	The total amount due from all overdue invoices in 0-30 interval.
Totals_0To30Interval_Code	String	Three letter currency code
Totals_31To60Interval_Amount	String	The total amount due from all overdue invoices in 31-60 interval
Totals_31To60Interval_Code	String	Three letter currency code
Totals_61To90Interval_Amount	String	The total amount due from all overdue invoices in 61-90 interval
Totals_61To90Interval_Code	String	Three letter currency code
Totals_From91_Amount	String	The total amount due from all overdue invoices for more than 90 interval
Totals_From91_Code	String	Three letter currency code
TotalsTotalAmount	String	The total amount due from all overdue invoices from all the intervals
TotalsTotalCode	String	Three letter currency code

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
StartDate	String	The starting date for the expense report query.

Bill lines are used to determine the amount of a bill.

Columns

Name	Type	Description
LineId [KEY]	Integer	Line Id unique to this bill.
CategoryId	String	Id of related expense category.
BillId	Integer	Id of related bill.
CategoryName	String	Name of related expense category.
ListIndex	Integer	Line number on the Bill
Description	String	Description of line item.
Amount	Decimal	Amount calculated from quantity and unit_cost.
Code	String	Three-letter currency code.
TotalAmount	Decimal	Total amount calculated from quantity and unit_cost.
TotalAmountCode	String	Three letter currency code.
Quantity	Integer	Quantity of the line unit.
UnitCostAmount	Decimal	unit cost amount of the line item.
UnitCostCode	String	Three-letter currency code.
TaxName1	String	Name for the first tax on the bill line.
TaxName2	String	Name for the second tax on the bill line.
TaxPercent1	Integer	Percentage of first tax to 2 decimal places.
TaxPercent2	Integer	Percentage of second tax to 2 decimal places.
TaxAuthorityId1	String	Tax Authority 1.
TaxAuthorityId2	String	Tax Authority 2.
TaxAmount1Amount	Decimal	First tax amount, in percentage. (up to three decimal places)
TaxAmount1Code	String	Three letter currency code
TaxAmount2Amount	Decimal	Second tax amount, in percentage. (up to three decimal places)
TaxAmount2Code	String	Three letter currency code

FreshBooks allows you to store your previously used taxes to re-apply conveniently.

Columns

Name	Type	Description
TaxId [KEY]	Integer	The unique identifier of the tax.
VendorId [KEY]	Integer	The unique identifier of the vendor.
SystemTaxId	Integer	Tax id in your FreshBooks business.
Amount	String	Populated from related system tax
Enabled	Boolean	If the tax is enabled for the vendor or not.
Name	String	Populated from related system tax
TaxAuthorityId	String	Custom identifier for tax tax authority.
UpdatedAt	Datetime	Time the resource was updated, YYYY-MM-DD HH:MM:SS format
CreatedAt	Datetime	Time the resource was created, YYYY-MM-DD HH:MM:SS format

Estimate lines are used to determine the amount of an estimate.

Columns

Name	Type	Description
LineId	String	Line Id unique to this estimate.
EstimateId	Integer	Id of the line's estimate.
CompoundedTax	String
Description	String	Description for the estimate line item.
Amount	Decimal	Amount of estimate line item account, to two decimal places.
Code	String	Three-letter currency code.
Name	String	Name for the estimate line item.
Quantity	String	Quantity of the estimate line item, to be multiplied against unit cost.
UnitCostAmount	Decimal	Unit cost of the line item.
UnitCostCode	String	Three-letter currency code.
TaxName1	String	Name for the first tax on the estimate line.
TaxAmount1	Decimal	First tax amount, in percentage. (up to three decimal places)
TaxNumber1	String
TaxName2	String	Name for the second tax on the estimate line.
TaxAmount2	Decimal	Second tax amount, in percentage. (up to three decimal places)
TaxNumber2	String

FreshBooks supplies Expense Categories to group expenses together to aid in expense tracking.

Columns

Name	Type	Description
Categoryid [KEY]	Integer	A unique identifier for the category, unique to this business id.
Category	String	Name for this category.
IsCogs	Boolean	Represents cost of goods sold.
IsEditable	Boolean	Indicates whether this can be edited.
ParentId	Integer	Categoryid of parent category.
VisState	Integer	0 for active, 1 for deleted.

The Expenses Report shows all the information involving your Expenses.

Columns

Name	Type	Description
ExpenseId [KEY]	String	Unique id for the expense.
GroupId	String	Unique id for the group.
Amount	Decimal	The amount of money that is owed or been paid.
Code	String	The currency that the amount is in.
VendorId	String	The unique id for the vendor.
Vendor	String	The name of the vendor.
Notes	String	Custom notes about the expense.
ClientId	String	Unique id for the client.
AuthorId	String	Id for the author.
CreatedDate	Date	The date the expense took place.
TaxName1	String	The name of the first tax.
TaxPercent1	Decimal	The percentage you are being taxed on.
TaxAmount1	Decimal	The amount of the first tax.
TaxCode1	String	The currency of the first tax.
TaxName2	String	The name of the second tax.
TaxPercent2	Decimal	The percentage you are being taxed on.
TaxAmount2	Decimal	The amount of the second tax.
TaxCode2	String	The currency of the second tax.
CategoryId	String	The identifier of the category.
CompanyName	String	Name of the company that the expenses are charged too.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

Gateways are also referred to as payment processors. The information returned by these endpoints specifies what payment processors are enabled for your businesses.

Columns

Name	Type	Description
Id [KEY]	String	Unique identifier to the business Id for the gateway, unique to the whole environment.
GatewayName	String	The name of the gateway.
SingleGatewayId	Integer	Unique identifier to the business Id for the gateway.

You can find ids for the Businesses and Accounts a user can interact with by querying the IdentityInfo view.

Columns

Name	Type	Description
Id [KEY]	Integer	Unique identifier of identity.
Name	String	Name of business.
AccountId	String	Unique identifier of accounting system, if applicable.

Invoice lines are used to determine the amount of an invoice, in addition to being able to tie the invoice to rebilled expenses.

Columns

Name	Type	Description
LineId	String	Line Id unique to this invoice.
InvoiceId	Integer	Id of related invoice.
Amount	String	Total amount of an invoice line, to two decimal places.
Code	String	Three-letter currency code.
Updated	Datetime	Time that the invoice was last updated. (YYYY-MM-DD)
Type	Integer	Invoice line type: normal invoice (0) or rebilling expense (1)
Quantity	Integer	Quantity of the invoice line item, to be multiplied against unit cost.
UnitCostAmount	Decimal	Unit cost amount, to two decimal places.
UnitCostCode	String	Three-letter currency code.
Description	String	Description for the invoice line item.
Name	String	Name for the invoice line item.
TaxName1	String	Name for the first tax on invoice line.
TaxAmount1	Decimal	First tax percentage amount, up to three decimal places.
TaxName2	String	Name for the second tax on invoice line.
TaxAmount2	Decimal	Second tax percentage amount, up to three decimal places.

The Invoice Details Report shows all the information involving your invoices.

Columns

Name	Type	Description
CreatedDate	Date	The date the invoice was created.
InvoiceId [KEY]	String	The unique id of the invoice.
DueOffsetDays	Int	Number of days from creation that the invoice is due.
Amount	Decimal	The amount of money that is owed or been paid.
Code	String	The currency that the amount is in.
PostOfficeNumber	Int	Post office box number for address on invoice.
TaxAmount	Decimal	Amount of tax.
TaxCode	String	Three-letter currency code.
PaidAmount	Decimal	Amount paid.
PaidCode	String	Three-letter currency code.
DatePaid	Date	The date the invoice was paid.
DiscountAmount	Decimal	The amount discount.
DiscountCode	String	Three-letter currency code.
InvoiceNumber	String	User-specified and visible invoice id.
currency_code	String	Three-letter currency code for invoice.
UserId	String	The unique id of the client.
FirstName	String	First name of the user.
LastName	String	The last name of the user.
Email	String	The email of the user.
Organization	String	Name of the organization the user is a part of.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

Other Income is for recording income that doesn’t require an invoice and is received through other means.

Columns

Name	Type	Description
IncomeId	Integer	Unique identifier of this other income entry within the business
Amount	String	Amount of the income, to two decimal places
Name	String	Name of the tax

The Payments Collected Report shows details regarding collected payments made to your business.

Columns

Name	Type	Description
InvoiceId	String	The unique id of the invoice.
ClientId	String	The unique id of the client.
Amount	Decimal	Amount paid on invoice to two decimal places.
Code	String	Three-letter currency code.
Client	String	The name of the business client belongs to.
Description	String	The description of the payment.
Date	Date	The date of the payment.
InvoiceNumber	String	A custom id for the invoice.
Method	String	Method of payment.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

The Profit/Loss Report shows all the information involving both your Profits and Losses.

Columns

Name	Type	Description
ProfitsAmount	Decimal	Amount of total profits.
ProfitsCode	String	Three-letter currency code.
IncomesAmount	Decimal	Amount of total incomes.
IncomesCode	String	Three-letter currency code.
ExpensesAmount	Decimal	Amount of total expenses.
ExpensesCode	String	Three-letter currency code.
FromDate	Date	The beginning date of the report.
ToDate	Date	The ending date of the report.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
StartDate	String	the starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

An Accounting System represents an entity that can send invoices. It is the central point of association between all of a single Administrator of a single Business, Invoices, Clients, Staff, Expenses, and Reports.

Columns

Name	Type	Description
Id [KEY]	Integer	The unique identifier of the system.
AccountId	String	The second unique identifier for the system.
Active	Boolean	Indicates whether the system is live or cancelled.
Amount	Decimal	Two-place decimal formatted amount being paid for the system.
AutoBill	Integer	Count of the number of autobills the system is allowed to have.
BillingStatus	String	Description of whether the owner of the system has paid when they were supposed to.
BusinessPhone	String	Main phone number for the business.
BusinessType	String	A description of the business.
City	String	The city this business is based in.
Code	String	The postal code for the business.
Country	String	The country this business is based in.
CurrencyCode	String	The default three-letter currency code for the business.
Date	Date	The signup date. (YYYY-MM-DD)
DiscountId	String	Identifier for tracking discounts (internal use).
DaylightSavingsTime	String	Indicates whether to use daylight savings time version of system's timezone.
Duration	Integer	(deprecated)
Email	String	The administrator email.
Fax	String	The fax number for a business.
GSTAmount	Decimal	The two-place decimal formatted amount of gst being paid for the system.
HeardAboutUsVia	String	Channel system came to Freshbooks through.
InfoEmail	String	The public email address to display.
IP	String	The signup IP address.
LandingUrl	String	The landing url used for analytics.
MasterlockBilling	String	Indicates whether a system is billed via a particular service.
MobilePhone	String	The mobile phone number for the business.
ModernSystem	String	Indicates whether the system belongs to the new version of FreshBooks or FreshBooks Classic.
Name	String	Descriptive name of the business.
Clients	Integer	Number of clients allowed on business.
Staff	String	Number of staff allowed on business.
PaymentAmount	Decimal	Two-place decimal formatted amount being paid for system.
PaymentFrequency	Integer	How many months per pay cycle. (internal)
Province	String	The province or state of the business.
ReferralId	String	A referral ID for analytics.
ReferringUrl	String	Tracks origin of user signup for analytics.
Salutation	String	The preferred greeting of the user.
SizeLimit	Integer	(deprecated)
SplitToken	String	Used for tracking split tests.
Street	String	The first line of the street of the business.
Street2	String	The second line of the street of the business.
TestSystem	String	Indicates whether this system is marked by FreshBooks as a test system.
Timezone	String	The timezone the business is in. (deprecated)
TimezoneId	Integer	The identifier of the timezone the business is in. (deprecated)

Tasks in Freshbooks represent services that your business offers to clients. Tasks are used to keep track of details of the service such as name and hourly rate.

Columns

Name	Type	Description
Id [KEY]	String	Unique identifier of the task.
Name	String	The name of the task.
UpdatedAt	Datetime	The Date/Time the task was last updated
Description	String	The description of the task.
RateAmount	Decimal	The hourly amount charged for the task.
RateCode	String	The currency of the rate.
Billable	Boolean	True if the task is billable.
Tax1	Decimal	Amount of first tax.
Tax2	Decimal	Amount of second tax.
VisState	Integer	0 marks the task as active, 1 if inactive.

The Tax Summary Report that outlines the taxes involved with your sales.

Columns

Name	Type	Description
CashBased	Boolean	True toggles tax to be calculated on collected sales rather than billed
CurrencyCode	String	Three-letter currency code for overdue payments
DownloadToken	String	The download token allows you to download the report into a csv file
EndDate	Date	The ending date for the tax summary report query
StartDate	Date	The starting date for the tax summary report query
TaxableAmountPaid	String	Taxable Amount Paid
TaxableAmountPaidCode	String	Taxable Amount Paid Code
TaxableAmountCollected	String	Taxable Amount Collcted Amount
TaxableAmountCollectedCode	String	Taxable Amount Collected Code
TaxCollectedAmount	String	Tax Collected Amount
TaxCollectedCode	String	Tax Collected Code
TaxName	String	Tax Name
NetTaxAmount	String	Net Tax Amount
NetTaxCode	String	Net Tax Code
NetTaxableAmount	String	Net Taxable Amount
NetTaxableCode	String	Net Taxable Amount Code
TaxPaidAmount	String	Tax Paid Amount
TaxPaidCode	String	Tax Paid Code
TotalInvoicedAmount	String	Total Invoiced Amount
TotalInvoicedCode	String	Total Invoiced Code

Time Entries represent time spent working for a client or project.

Columns

Name	Type	Description
Id [KEY]	String	Unique identifier of the time entry.
ClientId	String	Unique identifier of the client.
ProjectId	String	Unique identifier of the project.
TaskId	String	Unique identifier of the task.
IsLogged	Boolean	False if the time entry is being created from a running timer.
CreatedAt	Datetime	The date when the time entry was created.
Billable	Boolean	True for entries that can be automatically added to an invoice.
TimerId	String	Unique identifier of the timer.
TimerIsRunning	Boolean	True if the timer is running.
PendingTask	String	The pending task.
Description	String	Notes of the user on the time entry.
Active	Boolean	True if the time entry is active.
Internal	Boolean	True if the time entry is not assigned to a client.
PendingProject	String	The pending project.
PendingClient	String	The pending client.
Duration	Integer	Duration of the time entry.
ServiceId	String	Unique identifier of the service.
Billed	Boolean	True if the entry time is billed.
StartedAt	Datetime	The date/time when the time entry started.

ストアドプロシージャはファンクションライクなインターフェースで、FreshBooks の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにFreshBooks から関連するレスポンスデータを返します。

CData Cloud - FreshBooks ストアドプロシージャ

Name	Description
CreateExpenseWithAttachment	To let end users create an expense with attachment.
CreateInvoiceWithAttachment	To let end users upload attachment to an invoice.

To let end users create an expense with attachment.

Input

Name	Type	Required	Description
Attachment	String	False	The full path of the attachment to upload.
FileName	String	False	Name of the file. If content is not empty.
Amount	Decimal	False	The amount of the expense.
CategoryId	Integer	True	The identifier of the related expense category.
Date	Date	False	Date of the expense. (YYYY-MM-DD)
StaffId	Integer	True	Identifier of related staff member, if applicable.

Result Set Columns

Name	Type	Description
Success	String	True if the image is uploaded successfully.

To let end users upload attachment to an invoice.

Execute

Create an invoice with attachment:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount) Values ('E', 200 )

EXECUTE CreateInvoiceWithAttachment Atatchment = 'C:\files\log.txt', CustomerId = '33578', CreateDate = '2023-11-06', LinesAggregate= InvoiceLineItems#TEMP 

Input

Name	Type	Required	Description
Attachment	String	False	The full path of the attachment to upload.
CustomerId	Integer	True	Client Id unique to this system.
CreateDate	Date	True	Date invoice was created. (YYYY-MM-DD)
LinesAggregate	String	False	An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field.
FileName	String	False	Name of the file. If content is not empty.
OwnerId	Integer	False	Id of the invoice's creator. (writable on create only)
EstimateId	Integer	False	Id of associated estimate, 0 if none.(writable on create only)
BasecampId	Integer	False	Id of connected basecamp account, 0 if none.(writable on create only)
SentId	Integer	False	User Id of user who sent the invoice.(writable on create only)
Status	String	False	Invoice status.(writable on create only)
Parent	Integer	False	Id of object this invoice was generated from, 0 if none.(writable on create only)
CreatedAt	Datetime	False	Time the invoice was created. (YYYY-MM-DD, writable on create only)
Updated	Datetime	False	Time the invoice was last updated. (YYYY-MM-DD, writable on create only)
DisplayStatus	String	False	Description of status shown in Freshbooks UI.(writable on create only)
AutobillStatus	String	False	Description of autobill status.(writable on create only)
PaymentStatus	String	False	Description of payment status.(writable on create only)
LastOrderStatus	String	False	Describes the status of the last attempted payment.(writable on create only)
DisputeStatus	String	False	Description of whether invoice has been disputed.(writable on create only)
DepositStatus	String	False	Description of deposits applied to invoice.(writable on create only)
AutoBill	String	False	Whether this invoice has a credit card saved.(writable on create only)
V3Status	String	False	Description of Invoice status.(writable on create only)
InvoiceNumber	String	False	User-specified and visible Invoice Id.
GenerationDate	Date	False	Date invoice was generated from object.(YYYY-MM-DD, or null if not)
DiscountValue	Decimal	False	Decimal-string amount of discount.
DiscountDescription	String	False	Public note about discount.
PONumber	String	False	Reference number for address on invoice.
Template	String	False	Choice of rendering style. (internal, deprecated)
CurrencyCode	String	False	Three-letter currency code for invoice.
Language	String	False	Two-letter language code.
Terms	String	False	Terms listed on invoice.
Notes	String	False	Notes listed on invoice.
Address	String	False	First line of address on invoice.
ReturnUri	String	False	(deprecated)
DepositAmount	Decimal	False	Amount required as deposit, null if none.
DepositPercentage	Decimal	False	Percentage of the invoice's value required as a deposit.
Gmail	String	False	Whether to send via ground mail.
ShowAttachments	String	False	Whether attachments on invoice are rendered.
VisState	Integer	False	Whether active (0) or deleted (1).
Street	String	False	Street for address on invoice.
Street2	String	False	Second line of street for address on invoice.
City	String	False	City for address on invoice.
Province	String	False	Province for address on invoice.
Code	String	False	Zip code for address on invoice.
Country	String	False	Country for address on invoice.
Organization	String	False	Name of organization being invoiced.
FirstName	String	False	First name of client on invoice.
LastName	String	False	Last name of client on invoice.
VatName	String	False	Value added tax name, if provided.
VatNumber	String	False	Value added tax number, if provided.
DueOffsetDays	Integer	False	Number of days from creation that invoice is due.

Result Set Columns

Name	Type	Description
Success	String	True if the image is uploaded successfully.

このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。

スキーマテーブル

以下のテーブルは、FreshBooks のデータベースメタデータを返します。

• sys_catalogs：利用可能なデータベースをリスト。
• sys_schemas：利用可能なスキーマをリスト。
• sys_tables：利用可能なテーブルおよびビューをリスト。
• sys_tablecolumns：利用可能なテーブルおよびビューのカラムについて説明。
• sys_procedures：利用可能なストアドプロシージャについて説明。
• sys_procedureparameters：ストアドプロシージャパラメータについて説明。
• sys_keycolumns：主キーおよび外部キーについて説明。
• sys_indexes：利用可能なインデックスについて説明。

データソーステーブル

以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。

• sys_connection_props：利用可能な接続プロパティについての情報を返す。
• sys_sqlinfo：Cloud がデータソースにオフロードできるSELECT クエリについて説明。

クエリ情報テーブル

次のテーブルは、データ変更クエリのクエリ統計を返します。

• sys_identity：バッチ処理または単一の更新に関する情報を返す。

利用可能なデータベースをリストします。

次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	データベース名。

利用可能なスキーマをリストします。

次のクエリは、すべての利用可能なスキーマを取得します。

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	データベース名。
SchemaName	String	スキーマ名。

利用可能なテーブルをリストします。

次のクエリは、利用可能なテーブルおよびビューを取得します。

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベース。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	テーブル名またはビュー名。
TableType	String	テーブルの種類（テーブルまたはビュー）。
Description	String	テーブルまたはビューの説明。
IsUpdateable	Boolean	テーブルが更新可能かどうか。

利用可能なテーブルおよびビューのカラムについて説明します。

次のクエリは、Clients テーブルのカラムとデータ型を返します。

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Clients' 

Columns

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
          

Columns

Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベース。
SchemaName	String	ストアドプロシージャを含むスキーマ。
ProcedureName	String	ストアドプロシージャの名前。
Description	String	ストアドプロシージャの説明。
ProcedureType	String	PROCEDURE やFUNCTION などのプロシージャのタイプ。

ストアドプロシージャパラメータについて説明します。

次のクエリは、GetOAuthAccessToken ストアドプロシージャのすべての入力パラメータについての情報を返します。

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAccessToken' AND Direction=1 OR Direction=2

Columns

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	パラメータのインデックス。

主キーおよび外部キーについて説明します。

次のクエリは、Clients テーブルの主キーを取得します。

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Clients' 
          

Columns

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
          

Columns

Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前。
KeySeq	String	主キーのシーケンス番号。
KeyName	String	主キーの名前。

利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。

次のクエリは、主キーでないすべてのインデックスを取得します。

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

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:freshbooks:config:

この接続文字列を使用すると、有効な接続がなくてもこのテーブルをクエリできます。

次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

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 準拠 を参照してください。

データソースのSELECT 機能

以下は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 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデル セクションを参照してください。

Columns

Name	Type	Description
NAME	String	SQL 構文のコンポーネント、またはサーバー上で処理できる機能。
VALUE	String	サポートされるSQL またはSQL 構文の詳細。

試行された変更に関する情報を返します。

次のクエリは、バッチ処理で変更された行のId を取得します。

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	データ変更処理から返された、データベース生成Id。
Batch	String	バッチの識別子。1 は単一処理。
Operation	String	バッチ内の処理の結果：INSERTED、UPDATED、またはDELETED。
Message	String	SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

Authentication

---

プロパティ	説明
AccountId	接続先のアカウントId。値を指定しない場合は、初めに返されたプロファイルが使われます。

OAuth

---

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
AuthToken	OAuth アクセストークンをリクエストおよび取得するために使用される認証トークン。
AuthKey	OAuth アクセストークンをリクエストおよび取得するために使用される認証シークレット。

SSL

---

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Logging

---

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Schema

---

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

Miscellaneous

---

プロパティ	説明
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

このセクションでは、本プロバイダーの接続文字列で設定可能なAuthentication プロパティの全リストを提供します。

---

プロパティ	説明
AccountId	接続先のアカウントId。値を指定しない場合は、初めに返されたプロファイルが使われます。

接続先のアカウントId。値を指定しない場合は、初めに返されたプロファイルが使われます。

データ型

string

デフォルト値

""

解説

利用可能なアカウントId は、IdentityInfo ビューで確認できます。

このセクションでは、本プロバイダーの接続文字列で設定可能なOAuth プロパティの全リストを提供します。

---

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。
AuthToken	OAuth アクセストークンをリクエストおよび取得するために使用される認証トークン。
AuthKey	OAuth アクセストークンをリクエストおよび取得するために使用される認証シークレット。

OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。

データ型

string

デフォルト値

""

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId 値、およびクライアントシークレットOAuthClientSecret が提供されます。

OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

データ型

string

デフォルト値

""

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId が提供されます。また、コンシューマーシークレットと呼ばれるクライアントシークレットも提供されます。クライアントシークレットをOAuthClientSecret プロパティに設定します。

OAuth アクセストークンをリクエストおよび取得するために使用される認証トークン。

データ型

string

デフォルト値

""

解説

このプロパティは、OAuth 1.0でヘッドレス認証を実行する場合にのみ必要です。これは、GetOAuthAuthorizationUrl ストアドプロシージャから取得できます。

GetOAuthAccessToken ストアドプロシージャでAuthKey とともに指定して、OAuthAccessToken を取得できます。

OAuth アクセストークンをリクエストおよび取得するために使用される認証シークレット。

データ型

string

デフォルト値

""

解説

このプロパティは、OAuth 1.0でヘッドレス認証を実行する場合にのみ必要です。これは、GetOAuthAuthorizationUrl ストアドプロシージャから取得できます。

GetOAuthAccessToken ストアドプロシージャでAuthToken とともに指定して、OAuthAccessToken を取得できます。

このセクションでは、本プロバイダーの接続文字列で設定可能な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 レベル。

ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

データ型

string

デフォルト値

"1"

解説

Verbosity レベルは、Cloud がLogfile にどの程度の詳細さで記載するかを決定します。1 から5 までのVerbosity レベルがサポートされています。より詳しい説明は、ログ ページに記載されています。

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

---

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

データ型

string

デフォルト値

""

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、 パフォーマンスが向上します。

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

---

プロパティ	説明
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

データ型

int

デフォルト値

-1

解説

クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。

このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。

データ型

string

デフォルト値

""

解説

Entity Framework ではテーブルカラムでない疑似カラムに値を設定できないため、この設定はEntity Framework で特に便利です。この接続設定の値は、"Table1=Column1, Table1=Column2, Table2=Column3" の形式です。"*=*" のように"*" 文字を使用して、すべてのテーブルとすべてのカラムを含めることができます。

タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

データ型

int

デフォルト値

60

解説

Timeout が0に設定されている場合は、操作がタイムアウトしません。処理が正常に完了するか、エラー状態になるまで実行されます。

Timeout の有効期限が切れても処理が完了していない場合は、Cloud は例外をスローします。