Basecamp Connection
This documentation is based on version 21.0.8383 of the connector.
This documentation is based on version 21.0.8383 of the connector.
The connector models Basecamp 2 and Basecamp 3 accounts as bidirectional tables.
In addition to authenticating, set the following connection properties to access Basecamp tables.
If you do not specify the ProjectId connection property, the connector will use the first project ID returned from the Projects table in your first authentication to Basecamp.
http://basecamp.com/<AccountId>
Basecamp 2 uses basic or OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to log into their own accounts.
Basecamp 3 requires OAuth 2.0.
In basic authentication, you use your login credentials to connect. Set the following properties:
To use OAuth, set the AuthScheme to OAuth.
You can connect without setting any connection properties for your user credentials. Set InitiateOAuth to GETANDREFRESH to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
When you connect, the connector opens the Basecamp OAuth endpoint in your default browser. Log in and grant permissions to the connector. The connector then completes the following OAuth process:
See Using OAuth Authentication for other OAuth authentication flows.
OAuth requires the authenticating user to interact with Basecamp using the browser. The connector facilitates this in various ways as described below.
Instead of connecting with the connector's embedded credentials, you can register an app to obtain the OAuthClientId and OAuthClientSecret.
See Creating a Custom OAuth App for a procedure.
You can connect without setting any connection properties for your user credentials. After setting the following, you are ready to connect:
https://basecamp.com/<AccountId>
Creating a custom OAuth app is optional as the connector is already registered with Basecamp and you can connect with its embedded credentials.
You might want to create a custom OAuth app to change the information displayed when users log into Basecamp to grant permissions to the connector.After setting the following, you are ready to connect:
https://basecamp.com/<AccountId>
To create Basecamp data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.
Obtain a Verifier Code
Follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property:
On the headless machine, set the following connection properties to obtain the OAuth authentication values.
https://basecamp.com/<AccountId>
After the OAuth settings file is generated, set the following properties to connect to data:
https://basecamp.com/<AccountId>
Transfer OAuth Settings
Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the connector and connect with the following properties set:
https://basecamp.com/<AccountId>
https://basecamp.com/<AccountId>
Create a Custom OAuth App
Creating a custom OAuth app is optional in the headless OAuth flow; you can skip creating an app by connecting with the connector's embedded OAuth credentials. You might want to create a custom OAuth app to change the information displayed when users log into Basecamp to grant permissions to the connector.
See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.
Obtain a Verifier Code
Set the following properties on the headless machine:
https://basecamp.com/<AccountId>
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
https://basecamp.com/<AccountId>
After the OAuth settings file is generated, set the following properties to connect to data:
https://basecamp.com/<AccountId>
Transfer OAuth Settings
Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the connector and connect with the following properties set:
https://basecamp.com/<AccountId>
Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
https://basecamp.com/<AccountId>
Creating a custom OAuth app is optional as the connector is already registered with Basecamp and you can connect with its embedded credentials.
You might want to create a custom OAuth app to change the information displayed when users log into the Basecamp OAuth endpoint to grant permissions to the connector. Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.Register your Basecamp app on integrate.37signals.com to obtain the following connection properties:
This section details a selection of advanced features of the Basecamp connector.
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.
The Jitterbit Connector for Basecamp allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:For example:
{ "MyView": { "query": "SELECT * FROM Projects WHERE MyColumn = 'value'" }, "MyView2": { "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)" } }Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
SELECT * FROM Customers WHERE City = 'Raleigh';An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
The Jitterbit Connector for Basecamp models the Basecamp 2 and Basecamp APIs as relational tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.
The available entities, as well as any API limitations and requirements for querying these entities, are documented in Basecamp 2 Data Model and Basecamp 3 Data Model. You can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.
The Data Models illustrate an example of what your Basecamp environment might look like. The actual data model will be obtained dynamically based on your Basecamp account.
Basecamp 2 Data Model describes the schemas available to connect to Basecamp 2 accounts. You can use tables to work with live Basecamp data. You can use stored procedures* provided by Jitterbit Connector for Basecamp to automate working with Basecamp data.
Basecamp 3 Data Model describes the schemas available to connect to Basecamp 3 accounts. You can use tables to work with live Basecamp data. You can use stored procedures* provided by Jitterbit Connector for Basecamp to automate working with Basecamp data.
This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 2 accounts.
The connector models the data in Basecamp into a list of tables that can be queried using standard SQL statements.
Generally, querying Basecamp tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
Accesses | Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp. |
CalendarEvents | Retrieve, create, update, and delete Calendar Events on Basecamp. |
Calendars | Retrieve, create, update, and delete Calendars on Basecamp. |
Documents | Retrieve, create, update, and delete Documents on Basecamp. |
People | Retrieve and delete People on Basecamp. |
Projects | Retrieve, create, update, and delete Projects on Basecamp. |
Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp.
You need to specify a ProjectId or CalendarId to retrieve Accesses. These columns are also the only columns supported by Basecamp as filter criteria; they can be used with the '=' operator.
SELECT * FROM Accesses WHERE ProjectId = '11111111' SELECT * FROM Accesses WHERE CalendarId = '11111111'
You can give someone access to a Project or Calendar using their Email Addresses or Ids (if they are part of the organization already).
INSERT INTO Accesses (ProjectId, EmailAddresses) VALUES ('11111111', 'someone@example.com, someoneelse@example.com') INSERT INTO Accesses (CalendarId, Ids) VALUES ('11111111', '123456789, 987654321')
Update is not a supported operation for the table Accesses.
To revoke access to someone from a Project or Calendar, specify the ProjectId or CalendarId and the Person's Id.
DELETE FROM Accesses WHERE ProjectId = '11111111' AND Id = '123456789' DELETE FROM Accesses WHERE CalendarId = '11111111' AND ID = '123456789'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier of the person with access to the project or calendar. | |
Admin | Boolean | True |
Boolean value to show if the current person is an admin or not. | |
AppUrl | String | True |
The app URL. | |
AvatarUrl | String | True |
The avatar URL of the person. | |
CanCreateProjects | Boolean | True |
Whether the person can create projects or not. | |
CreatedAt | Datetime | True |
The date and time when the record was created. | |
EmailAddress | String | True |
The email address of the person with access to the project or calendar. | |
FullsizeAvatarUrl | String | True |
The full-size avatar URL of the person. | |
IdentityId | String | True |
The identity ID of the person with access to the project or calendar. | |
IsClient | Boolean | False |
Is the returned user a client or not. | |
Name | String | True |
The name of the person with access to the project or calendar. | |
Trashed | Boolean | False |
Boolean value for deleted people. | |
UpdatedAt | Datetime | True |
The date and time when the record was last updated. | |
URL | String | True |
The URL of the person. | |
Ids | String | False |
A comma separated list of user Ids to be granted access to a project or calendar. | |
EmailAddresses | String | False |
A comma separated list of user email addresses to be granted access to a project or calendar. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ProjectId | String |
The Project ID associated with the access. |
CalendarId | String |
The Calendar ID associated with the access. |
Retrieve, create, update, and delete Calendar Events on Basecamp.
Basecamp supports only a limited subset of columns to be used as filter criteria in the WHERE clause. All Boolean columns for this table can be used with the '=' operator. ProjectId, CalendarId, StartsAt, and EndsAt can also be used with the "=" operator. Additionally, the StartsAt column can be used with the '>=' operator.
The following query filters on a particular time range.
SELECT * FROM CalendarEvents WHERE StartsAt = '2016-01-01' AND EndsAt = '2016-02-01'The following queries get CalendarEvents for a certain Project or Calendar.
SELECT * FROM CalendarEvents WHERE ProjectId= '123456768' SELECT * FROM CalendarEvents WHERE CalendarId = '123456768'
Specify the ProjectId or CalendarId and StartsAt when creating a new CalendarEvent.
INSERT INTO CalendarEvents (ProjectId, Summary, Description, AllDay, StartsAt) VALUES ('12345678', 'My single, all-day Project event', 'Details to follow', 'true', 2016-02-01)
Specify the ProjectId or CalendarId and the CalendarEvent ID when updating or deleting CalendarEvents.
UPDATE CalendarEvents SET Summary = 'Updated, two-day Project event', Description = 'Details to follow', AllDay = 'true', StartsAt = '2016-01-01', EndsAt = '2016-01-03' WHERE ProjectId = '12345678' AND Id = '12345678' DELETE FROM CalendarEvents WHERE ProjectId = '12345678' AND ID = '12345678'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier for the calendar event. | |
AllDay | Boolean | False |
Whether the calendar event is an all-day event. | |
AppUrl | String | True |
The app URL. | |
BucketAppUrl | String | True |
The calendar event bucket app URL. | |
BucketColor | String | True |
The calendar event bucket color. | |
BucketId | String | True |
The calendar event bucket Id. | |
BucketName | String | True |
The calendar event bucket name. | |
BucketType | String | True |
The calendar event bucket type. | |
BucketUrl | String | True |
The calendar event bucket URL. | |
CommentsCount | Integer | True |
The number of comments on this calendar event. | |
CreatedAt | Datetime | True |
The date and time when the calendar event was created. | |
CreatorAvatarUrl | String | False |
Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False |
Creator full-size avatar URL. | |
CreatorId | String | False |
The ID of the creator of the calendar event. | |
CreatorName | String | False |
The name of the creator of the calendar event. | |
Description | String | False |
The description for the calendar event. | |
EndsAt | Datetime | False |
The end date and time for the calendar event. | |
Past | Boolean | False |
Boolean value for past calendar events. | |
Private | Boolean | False |
Whether this calendar event is private. | |
RemindAt | Datetime | False |
The date and time for the calendar event reminder. | |
StartsAt | Datetime | False |
The start date and time for the calendar event. | |
SubscribersId | String | False |
The ID of the subscriber to the calendar event. | |
SubscribersName | String | False |
The name of the subscriber to the calendar event. | |
Summary | String | False |
The summary for the calendar event. | |
Trashed | Boolean | False |
Boolean value for deleted calendar events. | |
UpdatedAt | Datetime | True |
The date and time when the calendar event was updated. | |
Url | String | True |
The URL of the calendar event. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ProjectId | String |
The ID of the Project associated with the calendar event. |
CalendarId | String |
The calendar ID associated with the calendar event. |
Retrieve, create, update, and delete Calendars on Basecamp.
Select all from Calendars or specify an ID to retrieve more detail on a certain Calendar (Id is the only column supported as search criteria by Basecamp).
SELECT * FROM Calendars WHERE ID = '123456789'
Specify (at least) the Name property to create a new Calendar under the organization.
INSERT INTO Calendars (Name) VALUES ('New Test Calendar')
Specify a Calendar ID to update or delete Calendars. Set the Name property again if you are updating.
UPDATE Calendars SET Name = 'This is a new name for the Test Calendar.' WHERE Id = '123456789' DELETE FROM Calendars WHERE ID = '123456789'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier for the calendar. | |
AccessesAppUrl | String | True |
The accesses app URL. | |
AccessesCount | Integer | True |
The accesses count. | |
AccessesUpdatedAt | Datetime | True |
The time when accesses were updated. | |
AccessesUrl | String | True |
The accesses URL. | |
AppUrl | String | True |
The app URL. | |
CalendarEventsCount | Integer | True |
Number of calendar events. | |
CalendarEventsUpdatedAt | Datetime | True |
The date and time when the calendar event was last updated. | |
CalendarEventsUrlsPast | String | True |
URL of the past calendar event. | |
CalendarEventsUrlsUpcoming | String | True |
The date and time when the calendar was last updated. | |
Color | String | True |
The color of the calendar. | |
CreatedAt | Datetime | True |
The date and time when the document was created. | |
CreatorAvatarUrl | String | False |
Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False |
Creator full-size avatar Url. | |
CreatorId | String | False |
The ID of the creator of the calendar. | |
CreatorName | String | False |
The name of the creator of the calendar. | |
Name | String | False |
The name of the calendar. | |
UpdatedAt | Datetime | True |
The date and time when the calendar was last updated. | |
Url | String | True |
The URL of the calendar. |
Retrieve, create, update, and delete Documents on Basecamp.
You can retrieve all Documents (for all Projects), select Documents belonging to a certain Project only, or specify a Project and a certain Document at the same time (ProjectId and ID are the only columns supported as search criteria by Basecamp).
You can also sort Documents by UpdatedAt, CreatedAt, or Title.
SELECT * FROM Documents WHERE ProjectId = '11111111' AND Id = '1234567689' SELECT * FROM Documents ORDER BY UpdatedAt SELECT * FROM Documents ORDER BY Title DESC
To add a new Document to a major Project, specify the ProjectId and provide the Document's title or some Content.
INSERT INTO Documents (ProjectId, Title, Content) VALUES ('11111111', 'Memo', 'This is our document content')
Specify the ProjectId and the Document ID to update or delete a Document.
UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND Id = '1234567689' DELETE FROM Documents WHERE ProjectId = '11111111' AND ID = '12345678'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier for the document. | |
AppUrl | String | True |
The app URL. | |
BucketAppUrl | String | True |
The topic bucket app URL. | |
BucketColor | String | True |
The to-do bucket color. | |
BucketId | String | True |
The to-do bucket Id. | |
BucketName | String | True |
The to-do bucket name. | |
BucketType | String | True |
The to-do bucket type. | |
BucketUrl | String | True |
The to-do bucket URL. | |
Content | String | False |
The content of the document. | |
CreatedAt | Datetime | True |
The date and time when the document was created. | |
CreatorAvatarUrl | String | False |
Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False |
Creator full-size avatar URL. | |
CreatorId | String | False |
The ID of the creator of the document. | |
CreatorName | String | False |
The name of the creator of the document. | |
LastUpdaterId | String | False |
The ID of the person who last updated the document. | |
LastUpdaterName | String | False |
The name of the person who last updated the document. | |
Private | Boolean | False |
Whether this to-do is private. | |
SubscribersId | String | False |
The ID of the subscriber to the document. | |
SubscribersName | String | False |
The name of the subscriber to the document. | |
Title | String | False |
The title of the document. | |
Trashed | Boolean | False |
Boolean value for deleted to-dos. | |
UpdatedAt | Datetime | True |
The date and time when the document was last updated. | |
URL | String | True |
The URL of the document. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ProjectId | String |
The ID of the Project associated with the document. |
Retrieve and delete People on Basecamp.
Retrieve all People from the organization, specify an ID to get more info, or see who has been deleted (admin access is required). ID and Trashed are the columns supported by Basecamp as search criteria.
SELECT * FROM People WHERE Id = '123456789' SELECT * FROM People WHERE Trashed = true
New People can be invited directly to Projects via the Accesses table.
Update is not a supported operation for the table People.
Specify the Person's ID to delete the record from the organization.
DELETE FROM People WHERE ID = '123456789'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier for the person. | |
AccountOwner | Boolean | True |
Boolean value to show if the current person is the account owner. | |
Admin | Boolean | True |
Boolean value to show if the current person is an admin or not. | |
AssignedTodosCount | Integer | True |
Number of assigned to-dos. | |
AssignedTodosUpdatedAt | Datetime | True |
The date and time when the record was updated. | |
AssignedTodosUrl | String | True |
The URL of the assigned to-do. | |
AppUrl | String | True |
The app URL. | |
AvatarUrl | String | True |
The avatar URL of the person. | |
CalendarEventsTodayCount | Integer | True |
Number of events for today. | |
CanCreateProjects | Boolean | True |
Whether the person can create projects or not. | |
CreatedAt | Datetime | True |
The date and time when the record was created. | |
EmailAddress | String | True |
The email address of the person. | |
EventsAppUrl | String | True |
The events app URL. | |
EventsCount | Integer | True |
Number of events. | |
EventsUpdatedAt | Datetime | True |
The date and time when the event was updated. | |
EventsUrl | String | True |
The URL of the assigned to-do. | |
FullsizeAvatarUrl | String | True |
The full-size avatar URL of the person. | |
HasBasecampSiblings | Boolean | False |
Boolean value for Basecamp siblings. | |
IdentityId | String | True |
The identity ID of the person. | |
Name | String | True |
The name of the person. | |
OutstandingTodosCount | Integer | True |
Number of outstanding to-dos. | |
Trashed | Boolean | False |
Boolean value for deleted people. | |
UpdatedAt | Datetime | True |
The date and time when the record was last updated. | |
Url | String | True |
The URL of the person. |
Retrieve, create, update, and delete Projects on Basecamp.
You can retrieve all active Projects from the organization, filter records by Drafts and Archived Projects, or simply set a Project ID for more information. Drafts, Archived, and ID are the columns supported as search criteria by Basecamp.
SELECT * FROM Projects WHERE Drafts = True SELECT * FROM Projects WHERE Advanced = True SELECT * FROM Projects WHERE ID = '11111111'
Specify the Name and Description properties at a minimum to create a new Project.
INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')
Simply set the Project ID to update or delete a project you have access to.
UPDATE Projects SET Name = 'Updated Proj', Description = 'This is an updated test project.' WHERE Id = '11111111' DELETE FROM Projects WHERE ID = '11111111'
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The unique identifier of the project. | |
AccessesAppUrl | String | True |
The accesses app URL. | |
AccessesCount | Integer | True |
The accesses count. | |
AccessesUpdatedAt | Datetime | True |
Time when accesses were updated. | |
AccessesUrl | String | True |
The accesses URL. | |
AppUrl | String | True |
The app URL. | |
Archived | Boolean | False |
When user archives a project, this value will be set to true. | |
AttachmentsAppUrl | String | True |
The attachments app URL. | |
AttachmentsCount | Integer | True |
The attachments count. | |
AttachmentsUpdatedAt | Datetime | True |
Time when attachments were updated. | |
AttachmentsUrl | String | True |
The attachments URL. | |
CalendarEventsAppUrl | String | True |
The calendar events app URL. | |
CalendarEventsCount | Integer | True |
The calendar events count. | |
CalendarEventsUpdatedAt | Datetime | True |
Time when calendar events were updated. | |
CalendarEventsUrl | String | True |
The calendar events URL. | |
Color | String | True |
Color. | |
CreatedAt | Datetime | True |
The creation time of the project. | |
CreatorAvatarUrl | String | False |
Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | False |
Creator full-size Avatar URL. | |
CreatorId | String | False |
The ID of the creator. | |
CreatorName | String | False |
The name of the creator. | |
Description | String | False |
The description of the project. | |
DocumentsAppUrl | String | True |
The documents app URL. | |
DocumentsCount | Integer | True |
The documents count. | |
DocumentsUpdatedAt | Datetime | True |
Time when documents were updated. | |
DocumentsUrl | String | True |
The documents URL. | |
Drafts | Boolean | False |
Boolean value for a draft project. | |
ForwardsAppUrl | String | True |
The forwards app URL. | |
ForwardsCount | Integer | True |
The forwards count. | |
ForwardsUpdatedAt | Datetime | True |
Time when forwards were updated. | |
ForwardsUrl | String | True |
The forwards URL. | |
IsClientProject | Boolean | False |
Whether the project is related to clients or not. | |
LastEventAt | Datetime | False |
Time when the project was last modified. | |
Name | String | False |
The name of the project. | |
Starred | Boolean | False |
Boolean value for a starred project. | |
Template | Boolean | False |
Boolean value for a template project. | |
TodolistsAppUrl | String | True |
The todolists app URL. | |
TodolistsCompletedCount | Integer | True |
The todolists completed count. | |
TodolistsRemainingCount | Integer | True |
The todolists remaining count. | |
TodolistsUpdatedAt | Datetime | True |
Time when todolists were updated. | |
TodolistsUrl | String | True |
The todolists URL. | |
TopicsAppUrl | String | True |
The topics app URL. | |
TopicsCount | Integer | True |
The topics count. | |
TopicsUpdatedAt | Datetime | True |
Time when topics were updated. | |
TopicsUrl | String | True |
The topics URL. | |
Trashed | Boolean | False |
Boolean value for a trashed project. | |
UpdatedAt | Datetime | True |
Time when the project was updated. | |
URL | String | True |
The URL of the project. |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Attachments | Retrieve Attachments on Basecamp. |
Comments | Retrieve, create, update, and delete Calendars on Basecamp. |
Events | Retrieve activity Events on Basecamp. |
Forwards | Retrieve Forwards on Basecamp. |
ToDoLists | Retrieve ToDoLists on Basecamp. |
ToDos | Retrieve ToDos on Basecamp. |
Topics | Retrieve Topics on Basecamp. |
Retrieve Attachments on Basecamp.
Name | Type | References | Description |
Id [KEY] | String | The ID of the attachment. | |
Key | String | The key of the attachment. | |
Name | String | The name of the attachment. | |
ByteSize | Int | The size of the attachment in bytes. | |
CreatedAt | Datetime | The date when the attachment was created. | |
UpdatedAt | Datetime | The date when the attachment was last updated. | |
Url | String | The Url of the attachment. | |
AppUrl | String | The AppUrl of the attachment. | |
ThumbnailUrl | String | The Thumbnail Url of the attachment. | |
Private | Boolean | Indicates if the attachemtn is private. | |
Trashed | Boolean | Indicates if the attachment is trashed. | |
Tags | String | The tags of the attachment. | |
CreatorId | String | The ID of the attachment creator. | |
CreatorName | String | The name of the attachment creator. | |
CreatorAvatarUrl | String desc= | ||
CreatorFullsizeAvatarUrl | String desc= | ||
AttachableId | String | The ID of the attachable. | |
AttachableType | String | The type of the attachable. | |
AttachableUrl | String | The Url of the attachable. | |
AttachableAppUrl | String | The AppUrl of the attachable. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The ID of the project |
Retrieve, create, update, and delete Calendars on Basecamp.
You can retrieve all active Comments on the platform, and any field of the table can filter the result.
SELECT * FROM Comments SELECT * FROM Comments WHERE ProjectId = '1111111' AND ItemType = 'Message'
Name | Type | References | Description |
Id [KEY] | String | The unique identifier for the comment. | |
ProjectId [KEY] | String | The unique identifier for the project where the comment is contained. | |
ItemId | String | The unique identifier for the topic containing the comment. | |
ItemType | String | Type of the object containing the comment. Valid values are: Message, Document, Forward, CalendarEvent, Upload, Todolist, Todo. | |
AttachmentsAggregate | String | The attachments related to the comment. | |
Content | String | The content of the comment. | |
Private | Boolean | Privacy of the comment | |
Trashed | Boolean | If the comment was trashed or not. | |
CreatedAt | Datetime | The date and time when the document was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar Url. | |
CreatorId | String | The ID of the creator of the comment. | |
CreatorName | String | The name of the creator of the comment. | |
UpdatedAt | Datetime | The date and time when the comment was last updated. |
Retrieve activity Events on Basecamp.
You can retrieve all activity Events or filter data by the created date. The columns and operators supported by Basecamp as search criteria are shown below.
Column | Operators |
ProjectId | = |
PersonId | = |
CreatedAt | >, >= |
For example, the following query retrieves activity occurring after the specified date for the specified person:
SELECT * FROM Events WHERE CreatedAt >= '2016-01-01' AND PersonId = '1111111'
Name | Type | References | Description |
Id [KEY] | String | The unique identifier of the event. | |
Action | String | The action description for the event. | |
BucketAppUrl | String | The calendar events app URL. | |
BucketColor | String | The bucket event color. | |
BucketId | String | The bucket event Id. | |
BucketName | String | The bucket event name. | |
BucketType | String | The bucket event type. | |
BucketUrl | String | The bucket event URL. | |
CreatedAt | Datetime | The date and time when the event was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the event creator. | |
CreatorName | String | The name of the event creator. | |
EventableAppUrl | String | Eventable App URL. | |
EventableId | String | Eventable Id. | |
EventableType | String | Eventable type. | |
EventableUrl | String | Eventable URL. | |
Excerpt | String | The type of excerpt for this event. | |
HtmlUrl | String | The HTML URL of the event. | |
Private | Boolean | Whether the event is private. | |
RawExcerpt | String | The raw excerpt type for this event. | |
Summary | String | The summary for the event. | |
Target | String | The target project of this event. | |
UpdatedAt | Datetime | The date and time when the event was last updated. | |
URL | String | The URL of the event. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The ID of the Project associated with this event. | |
PersonId | String | The ID of the Person associated with this event. |
Retrieve Forwards on Basecamp.
Name | Type | References | Description |
Id [KEY] | String | The ID of the forward. | |
Subject | String | The subject of the forward. | |
From | String | ||
CreatedAt | Datetime | The date when the forward was created. | |
UpdatedAt | Datetime | The date when the forward was last updated. | |
Url | String | The Url of the forward. | |
AppUrl | String | The AppUrl of the forward. | |
Private | Boolean | Indicates if the forward is private. | |
Trashed | Boolean | Indicates if the forward is trashed. | |
BucketId | String | The bucket ID of the forward. | |
BucketName | String | The bucket name of the forward. | |
BucketType | String | The bucket type of the forward. | |
BucketUrl | String | The bucket Url of the forward. | |
BucketAppUrl | String | The bucket app URL of the forward. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The ID of the project |
Retrieve ToDoLists on Basecamp.
The following columns are supported by Basecamp as search criteria:
CreatedAt can be used with the '>' or '>=' operators.
The following query retrieves only completed ToDoLists belonging to a certain Project since a certain date.
SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND Completed = True AND CreatedAt > '2016-01-01'M
The following query retrieves a single ToDoList from a certain Project.
SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ID = '123456789'The following query retrieves ToDoLists that are assigned to a certain Person.
SELECT * FROM ToDoLists WHERE PersonId = '11111111'
Name | Type | References | Description |
Id [KEY] | String | The unique identifier of the topic. | |
AppUrl | String | The app URL. | |
AssignedTodos | String | Assigned to-dos. | |
BucketAppUrl | String | The topic bucket app URL. | |
BucketColor | String | The to-do bucket color. | |
BucketId | String | The to-do bucket Id. | |
BucketName | String | The to-do bucket name. | |
BucketType | String | The to-do bucket type. | |
BucketUrl | String | The to-do bucket URL. | |
Completed | Boolean | Whether the to-do list is completed. | |
CompletedCount | Integer | Number of completed to-dos. | |
CreatedAt | Datetime | The date and time when the record was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the creator. | |
CreatorName | String | The name of the creator. | |
Description | String | The to-do list description. | |
Name | String | The name of the to-do list. | |
Position | Integer | Position number of the current to-do list. | |
Private | Boolean | Whether the to-do list is private. | |
RemainingCount | Integer | Number of unfinished to-dos. | |
SubscribersId | String | The ID of the subscriber. | |
SubscribersName | String | The name of the subscriber. | |
ToDos | String | To-dos list. | |
Trashed | Boolean | Boolean value for deleted to-do lists. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
URL | String | The URL of the to-do list. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The ID of the Project associated with the to-do list. | |
PersonId | String | The ID of the Person associated with the to-do list. |
Retrieve ToDos on Basecamp.
The following are columns supported as search criteria by Basecamp:
DueAt and DueOn can be used with the '>' or'>=' operators.
The following query retrieves only completed ToDos belonging to a certain Project.
SELECT * FROM ToDos WHERE ProjectId = '11111111' AND Completed = TrueThe following query filters ToDos that are due since a certain date.
SELECT * FROM ToDos WHERE ProjectId = '11111111' AND DueAt > '2016-01-01'The following query lists the remaining ToDos that belong to a ToDo list.
SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '123456789' AND Remaining = true
Name | Type | References | Description |
Id [KEY] | String | The unique identifier of the topic. | |
AppUrl | String | The app URL. | |
AssigneeId | String | Id of the person assigned to. | |
AssigneeName | String | Name of the person assigned to. | |
AssigneeType | String | Type of assignee. | |
CommentsCount | Integer | Number of comments on this to-do. | |
Completed | Boolean | Whether the to-do is completed. | |
Content | String | Description of the to-do. | |
CreatedAt | Datetime | The date and time when the record was created. | |
CreatorAvatarUrl | String | Creator Avatar URL. | |
CreatorFullsizeAvatarUrl | String | Creator full-size avatar URL. | |
CreatorId | String | The ID of the creator. | |
CreatorName | String | The name of the creator. | |
DueAt | Datetime | The date and time when the to-do is set due. | |
DueOn | Datetime | The date and time when the to-do is set due. | |
Position | Integer | Position number of the current to-do on the list. | |
Private | Boolean | Whether this to-do is private. | |
Remaining | Boolean | Boolean value for remaining to-dos. | |
SubscribersId | String | The ID of the subscriber. | |
SubscribersName | String | The name of the subscriber. | |
ToDoList | String | To-do list. | |
ToDoListId | String | The to-do list Id. | |
Trashed | Boolean | Boolean value for deleted to-dos. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
URL | String | The URL of the to-do. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The ID of the Project associated with the to-do. |
Retrieve Topics on Basecamp.
You can retrieve all active Topics, sort Topics that belong to a Project, or only list archived Topics (ProjectId and Archived are the only columns supported as search criteria by Basecamp).
SELECT * FROM Topics WHERE ProjectId = '1111111' ORDER BY UpdatedAt ASC SELECT * FROM Topics WHERE ProjectId = '1111111' AND Archived = True
Name | Type | References | Description |
Id [KEY] | String | The unique identifier of the topic. | |
Archived | Boolean | Boolean value for archived topics. | |
Attachments | Integer | The number of attachments to the topic. | |
BucketAppUrl | String | The topic bucket app URL. | |
BucketColor | String | The bucket topic color. | |
BucketId | String | The bucket topic Id. | |
BucketName | String | The bucket topic name. | |
BucketType | String | The bucket topic type. | |
BucketUrl | String | The bucket topic URL. | |
CreatedAt | Datetime | The date and time when the record was created. | |
Excerpt | String | The excerpt from the latest comment on the topic. | |
LastUpdaterId | String | The ID of the person who made the last comment. | |
LastUpdaterName | String | The name of the person who last updated the topic. | |
Private | Boolean | Whether the topic is private. | |
Title | String | The title of the topic. | |
TopicableAppUrl | String | The URL of the topicable Basecamp entity. | |
TopicableId | String | The ID of the topicable Basecamp entity. | |
TopicableType | String | The type of the topicable Basecamp entity. | |
TopicableUrl | String | The URL of the topicable Basecamp entity. | |
Trashed | Boolean | Boolean value for deleted topics. | |
UpdatedAt | Datetime | The date and time when the record was last updated. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | String | The Project Id. |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetOAuthAccessToken | Gets the OAuth access token from Basecamp. |
GetOAuthAuthorizationURL | Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp. |
PublishProject | Will publish (activate) a project created from a template (draft project). |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Basecamp services. |
StarProject | Will star a specified project. |
UnstarProject | Will unstar a specified project. |
Gets the OAuth access token from Basecamp.
Name | Type | Required | Description |
AuthMode | String | True | WEB or APP. The Basecamp authentication mode. Default is APP.
The default value is APP. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. |
Verifier | String | False | The verifier token returned by Basecamp. Required for only the Web AuthMode. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Name | Type | Description |
OAuthAccessToken | String | The OAuth token. |
OAuthRefreshToken | String | The OAuth refresh token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.
Name | Type | Required | Description |
CallbackUrl | String | False | The URL that Basecamp will return to after the user has authorized your app. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Name | Type | Description |
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
Will publish (activate) a project created from a template (draft project).
Name | Type | Required | Description |
ProjectId | String | True | ID of the project. |
Name | Type | Description |
Result | String | Project creation result. |
Refreshes the OAuth access token used for authentication with various Basecamp services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Will star a specified project.
Name | Type | Required | Description |
ProjectId | String | True | ID of the project. |
Name | Type | Description |
Result | String | Project star result. |
Will unstar a specified project.
Name | Type | Required | Description |
ProjectId | String | True | ID of the project. |
Name | Type | Description |
Result | String | Project unstar result. |
This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 3 accounts.
The connector models the data in Basecamp into a list of tables that can be queried using standard SQL statements.
Generally, querying Basecamp tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Name | Description |
CalendarEvents | Retrieve calendar events on Basecamp 3. |
Comments | Retrieve comments on a Basecamp 3 recording. |
Documents | Retrieve, create, update, and delete Documents on Basecamp 3. |
Messages | Retrieve messages on Basecamp 3. |
Projects | Retrieve, create, update, and delete Projects on Basecamp 3. |
ToDoLists | Retrieve ToDoLists on Basecamp 3. |
ToDos | Retrieve ToDos on Basecamp 3. |
Vaults | Retrieve Vaults(folders) on Basecamp 3 folders. |
Retrieve calendar events on Basecamp 3.
You can query Calendar Events in the specified project.
The following columns are supported as search criteria by Basecamp:
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM CalendarEvents WHERE ProjectId = '11111111' AND CalendarId = '22222222' AND Status = 'active'You can also retrieve a single calendar event by specifying the Id.
SELECT * FROM CalendarEvents WHERE ProjectId = '11111111' AND ID = '33333333'
You can insert a new Calendar Event by providing the ProjectId, CalendarId, StartsAt, EndsAt, and Summary as required parameters.
INSERT INTO CalendarEvents (ProjectId, CalendarId, Summary, StartsAt, EndsAt, Description, Notify, ParticipantsIds ) VALUES ( '1111111', '2222222', 'summary test', '2017-11-11 09:00Z', '2017-11-11 11:00Z', 'description', 'false', '3333333, 4444444')
You can also change the Calendar Event by providing the ProjectId and event Id.
UPDATE CalendarEvents SET Summary = 'Updated summary', StartsAt = '2017-11-12', EndsAt = '2017-11-12', AllDay = 'true' WHERE ProjectId = '11111111' AND ID = '1234567689'
Calendar Events cannot be deleted.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the calendar event. | |
ProjectId | Long | True |
Projects.Id |
The Project ID associated with the schedule. |
CalendarId | Long | True |
Projects.CalendarId |
The Calendar ID that the event belongs to. |
Status | String | True |
The status of the calendar event. | |
Type | String | True |
The type of the returned record. | |
CreatedAt | Datetime | True |
The date and time when the calendar event was created. | |
UpdatedAt | Datetime | True |
The date and time when the calendar event was last updated. | |
Description | String | False |
Provided description for the calendar event. | |
AllDay | Boolean | False |
Indicates that the event is an all day event. | |
StartsAt | Datetime | False |
The date and time when the calendar event starts. | |
EndsAt | Datetime | False |
The date and time when the calendar event ends. | |
Summary | String | False |
Provided summary for the calendar event. | |
ParticipantsIds | String | False |
A list of comma separated participant ids. | |
ParticipantsNames | String | True |
A list of comma separated participant names. | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
BucketName | String | True |
Name of the containing bucket. | |
BucketType | String | True |
Type of the containing bucket. | |
CreatorId | Long | True |
People.Id |
The ID of the creator. |
CreatorName | String | True |
The name of the creator. | |
CommentsCount | Integer | True |
Number of comments the document has. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Notify | String |
When set to true, will notify the participants about the event. |
Retrieve comments on a Basecamp 3 recording.
The columns in Comments are filtered client side. You can either get all comments for a recording by specifying the RecordingId or you can get a certain comment by specifying its Id.
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Comments WHERE ProjectId = '11111111' AND RecordingId = '222222222' AND Content = 'Exempli gratia' AND CreatedAt < '2017-04-18T15:53:38.040-06:00'
You can also retrieve a single comment by specifying the ID (given that you've specified the ProjectId in the connection)
SELECT * FROM Comments WHERE ID = '1234567'
You can insert a new comment, by providing the required ProjectId, RecordingId, and Content.
INSERT INTO Comments (Content, ProjectId, RecordingId) VALUES ('Very informative document. Thanks!', 114857506, 392946856)
You can change the columns that are not read-only by providing the Id, ProjectId, and new values.
UPDATE Comments SET Content='I like this better now' WHERE ProjectId='18061798' AND Id='1308746423'
Comments cannot be deleted (unsupported by the API).
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The ID of the comment. | |
Content | String | False |
The conent of the comment. | |
Title | String | True |
The title of the comment. | |
Status | String | True |
The status of the comment. | |
CreatedAt | Datetime | True |
The time the comment was created. | |
UpdatedAt | Datetime | True |
The time the comment was last updated. | |
InheritsStatus | Boolean | True |
Whether this comment inherits its status or not. | |
URL | String | True |
The URL of the comment. | |
VisibleToClients | Boolean | True |
Whether this comment is visible to clients or not. | |
ProjectId | Long | False |
Projects.Id |
The ID of the project this comment is a child of. A project contains recordings. |
ProjectName | String | True |
The name of the project this comment is a child of. | |
RecordingId | Long | False |
Recordings.Id |
The ID of the recording the comment is written on. A recording can be a comment, document, message, question:answer, schedule:entry, todo, todolist or upload. |
CommenterId | String | True |
The ID of the commenter. | |
CommenterName | String | True |
The name of the commenter. | |
CommenterIsAdmin | Boolean | True |
Whether the commenter is an admin or not. | |
CommenterIsOwner | Boolean | True |
Whether the commenter is the owner of the recording or not. | |
CommenterTitle | String | True |
The commenter's title. | |
CommenterEmail | String | True |
The email address of the commenter. | |
CommenterAvatarURL | String | True |
The URL of the avatar of the commenter. | |
CommenterCompanyId | String | True |
The company ID of the commenter. | |
CommenterCompanyName | String | True |
The name of the company of the commenter. | |
AppURL | String | True |
The URL of the app this comment belongs to. | |
BookmarkURL | String | True |
The URL of the bookmark. | |
CommenterAttachableSGID | String | True |
Te attachable SGID of the commenter. |
Retrieve, create, update, and delete Documents on Basecamp 3.
You can retrieve all documents belonging to a certain Project and Vault only, or specify a Project and a certain Document at the same time (ProjectId, VaultId and ID are the only columns supported as search criteria by Basecamp).
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Documents WHERE ProjectId = '11111111' AND VaultId = '222222222' SELECT * FROM Documents WHERE ProjectId = '11111111' AND ID = '1234567689'
To add a new document to a major project, you need to specify the ProjectId, Vaultid, Title, Content, and Status (optional).
INSERT INTO Documents (ProjectId, Vaultid, Title, Content, Status) VALUES ('11111111', '222222222', 'Memo', 'This is our document content', 'active')
Specify the ProjectId and the document ID to update a document.
UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND ID = '1234567689'
DELETE is not supported for this table.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier for the document. | |
ProjectId | Long | True |
Projects.Id |
The ID of the Project associated with the document. |
VaultId | Long | True |
Vaults.Id |
The Vault ID associated with the document. |
Status | String | False |
The status of the vault. | |
Title | String | False |
The title of the document. | |
Content | String | False |
The content of the Document | |
Type | String | True |
The type of the returned record. | |
CreatedAt | Datetime | True |
The date and time when the document was created. | |
UpdatedAt | Datetime | True |
The date and time when the document was last updated. | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
BucketName | String | True |
Name of the containing bucket. | |
BucketType | String | True |
Type of the containing bucket. | |
CreatorId | Long | True |
People.Id |
The ID of the creator. |
CreatorName | String | True |
The name of the creator. | |
CommentsCount | Integer | True |
Number of comments the document has. |
Retrieve messages on Basecamp 3.
You can query messages posted on the message board of the specified project.
The columns supported as search criteria by Basecamp are ProjectId, MessageBoardId.
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Messages WHERE ProjectId = '11111111' AND MessageBoardId = '22222222'You can also retrieve a single message by specifying the Id.
SELECT * FROM Messages WHERE ProjectId = '11111111' AND ID = '33333333'
You can insert a new message, by providing the required ProjectId, MessageBoardId, and Subject in addition to the message related parameters.
INSERT INTO Messages (ProjectId, MessageBoardId, Content, Subject, Status ) VALUES ('1111111', '2222222', 'Content test', 'Subject test', 'active')
You can also update messages by providing the ProjectId and message Id.
UPDATE Messages SET subject = 'Updated subject', content = 'Updated content' WHERE ProjectId = '11111111' AND ID = '1234567689'
Messages cannot be deleted.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the message. | |
ProjectId | Long | True |
Projects.Id |
The Project ID associated with the schedule. |
MessageBoardId | Long | True |
Projects.MessageBoardId |
The Message Board ID that the message belongs to. |
Status | String | False |
The status of the message | |
Type | String | True |
The type of the returned record. | |
CreatedAt | Datetime | True |
The date and time when the message was created. | |
UpdatedAt | Datetime | True |
The date and time when the message was last updated. | |
Content | String | False |
Provided content for the message | |
Subject | String | False |
Subject of the message | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
BucketName | String | True |
Name of the containing bucket. | |
BucketType | String | True |
Type of the containing bucket. | |
CreatorId | Long | True |
People.Id |
The ID of the creator. |
CreatorName | String | True |
The name of the creator. | |
CategoryId | Long | False |
The ID of the category. | |
CategoryName | String | True |
The name of the category. | |
CommentsCount | Integer | True |
Number of comments the document has. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
IncludeReplies | Boolean |
Set this to true to retrieve replies for each message. |
Retrieve, create, update, and delete Projects on Basecamp 3.
You can retrieve all active Projects from the organization, or set a project ID to retrieve a single project. ID is the only column supported as search criteria by Basecamp.
SELECT * FROM Projects SELECT * FROM Projects WHERE ID = '11111111'
Specify at least the Name and optionally Description properties to create a new Project.
INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')
Set the Project ID to update or delete a project you have access to.
UPDATE Projects SET Name = 'Updated Project', Description = 'This is an updated test project.' WHERE Id = '11111111' DELETE FROM Projects WHERE ID = '11111111'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the project. | |
Status | String | True |
The status of the project. | |
Purpose | String | True |
The purpose of the project. | |
CreatedAt | Datetime | True |
The creation time of the project. | |
UpdatedAt | Datetime | True |
Time when the project was updated. | |
Description | String | False |
The description of the project. | |
Name | String | False |
The name of the project. | |
Bookmarked | Boolean | False |
Boolean value for a bookmarked project. | |
CalendarId | Long | True |
Id of the project's Calendar. | |
ChatId | Long | True |
Id of the project's Chat. | |
ToDoSetId | Long | True |
Id of the project's ToDo Set. | |
MessageBoardId | Long | True |
Id of the project's Message Board. | |
QuestionnaireId | Long | True |
Id of the project's Questionnaire. | |
VaultId | Long | True |
Id of the project's Vault. | |
InboxId | Long | True |
Id of the project's Inbox. |
Retrieve ToDoLists on Basecamp 3.
The following columns are supported by Basecamp as search criteria:
ToDoSetId can be retrieved in the Projects table.
The following query retrieves only completed to-do lists belonging to a certain Project and a certain Status. If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ToDoSetId = '2222222' AND Status = 'active'
The following query retrieves a single ToDoList from a certain Project.
SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ID = '123456789'
Specify the Name, ProjectId, Description (optional), and the ToDoSetId to insert a to-do list.
INSERT INTO ToDoLists (Name, Description, ProjectId, ToDoSetId ) VALUES ('Name', 'desc', '11111111', '2222222')
Basecamp allows changing the name and description of the to-do list, provided that the ProjectId and to-do list ID are included in the query:
UPDATE ToDoLists Set Name = 'Updated Name', Description = 'Updated desc' Where ProjectId = '11111111' AND ID = '1234567689'
To-do lists cannot be deleted.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the topic. | |
ProjectId | Long | True |
Projects.Id |
The ID of the Project associated with the to-do list. |
ToDoSetId | Long | True |
Projects.ToDoSetId |
The ID of the To Do Set the to-do list belongs to. |
Status | String | True |
The to-do status. | |
Type | String | True |
The record type. | |
Name | String | False |
The to-do name. | |
Completed | Boolean | True |
The to-do state. | |
CompletedRatio | String | True |
The to-do state in ratio. | |
Description | String | False |
The to-do list description. | |
CreatedAt | Datetime | True |
The date and time when the event was created. | |
UpdatedAt | Datetime | True |
The date and time when the record was last updated. | |
BucketName | String | True |
The to-do list bucket name. | |
BucketType | String | True |
The to-do list bucket type. | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
CreatorId | Long | True |
People.Id |
Event creator Id. |
CreatorAttachableSgid | String | True |
Event creator Attachable SGID. | |
CreatorName | String | True |
Event creator Name. | |
CreatorEmailAddress | String | True |
Event creator Email Address. | |
CreatorTitle | String | True |
Event creator Title. | |
CreatorBio | String | True |
Event creator Bio. | |
CreatorCreatedAt | Datetime | True |
Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | True |
Event creator update date and time. | |
CreatorAdmin | Boolean | True |
Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | True |
Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | True |
Event creator's timezone. | |
CreatorCompanyId | Long | True |
Event creator's company Id. | |
CreatorCompanyName | String | True |
Event creator's company name. | |
CommentsCount | Integer | True |
Number of coments the document has. |
Retrieve ToDos on Basecamp 3.
The following columns are supported as search criteria by Basecamp:
By default, only active to-dos will be retrieved.
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '222222222' AND Status = 'active' AND Completed = TrueYou can also retrieve a single to-do by specifying the Id.
SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ID = '33333333'
You can insert a new to-do by providing the required ProjectId, ToDoListId, and Content in addition to the to-do related parameters.
INSERT INTO ToDos (Content, Description , DueOn, StartsOn, AssigneeIds, ProjectId, ToDoListId ) VALUES ('What to do','description','2017-11-11', '2017-11-12' , '333333333','1111111111', '22222222')
You can change the columns that are not read-only by providing the ProjectId, to-do Id, and new values.
UPDATE ToDos Set Content = 'Updated content' Where ProjectId = '11111111' AND ID = '1234567689'
You can change the Completed state of the to-do by providing the ProjectId and to-do Id. The Completed column can only be updated standalone, and other columns cannot be updated in the same query.
UPDATE ToDos Set Completed = True Where ProjectId = '11111111' AND ID = '1234567689'
To-dos cannot be deleted.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the todo. | |
ProjectId | Long | True |
Projects.Id |
The ID of the Project associated with the to-do list. |
ToDoListId | Long | True |
ToDoLists.Id |
The ID of the To Do list the to-do belongs to. |
Status | String | True |
The state the todo. | |
CreatedAt | Datetime | True |
The date and time when the event was created. | |
UpdatedAt | Datetime | True |
The date and time when the record was last updated. | |
StartsOn | Date | False |
The date and time when the todo should start. | |
DueOn | Date | False |
The date and time when the todo is due. | |
CommentsCount | Integer | True |
Number of coments the document has. | |
Description | String | False |
The to-do list description. | |
Content | String | False |
What to do. | |
AssigneesIds | String | False |
List of assigned user Ids. | |
Completed | Boolean | False |
The to-do state. | |
CompletedAt | Datetime | True |
The tdate and time when the todo was completed. | |
CompletionCreatorId | String | True |
The ID of the user who completed the todo. | |
CompletionCreatorName | String | True |
The name of the user who completed the todo. | |
BucketName | String | True |
The to-do list bucket name. | |
BucketType | String | True |
The to-do list bucket type. | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
CreatorId | Long | True |
People.Id |
Event creator Id. |
CreatorAttachableSgid | String | True |
Event creator Attachable SGID. | |
CreatorName | String | True |
Event creator Name. | |
CreatorEmailAddress | String | True |
Event creator Email Address. | |
CreatorTitle | String | True |
Event creator Title. | |
CreatorBio | String | True |
Event creator Bio. | |
CreatorCreatedAt | Datetime | True |
Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | True |
Event creator update date and time. | |
CreatorAdmin | Boolean | True |
Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | True |
Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | True |
Event creator's timezone. | |
CreatorCompanyId | String | True |
Event creator's company Id. | |
CreatorCompanyName | String | True |
Event creator's company name. |
Retrieve Vaults(folders) on Basecamp 3 folders.
All projects have a primary Vault (folder). Additional Vaults may be nested under the primary Vault or any child Vault.
The columns supported as search criteria by Basecamp are ProjectId, VaultId, and Id. If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Vaults WHERE ProjectId = '11111111' AND VaultId = '222222222'You can also retrieve a single To-do by specifying the Id.
SELECT * FROM Vaults WHERE ProjectId = '11111111' ID = '333333333'
You can insert a new Vault by providing the ProjectId, VaultId, and Title, which are required parameters.
INSERT INTO Vaults (Title, ProjectId, Vaultid) VALUES ('InsertedVault', '11111111', '222222222')
You can update the Vault title by providing the ProjectId, VaultId, and the new title.
UPDATE Vaults SET Title = 'Updated title' WHERE ProjectId = '11111111' AND ID = '333333333'
Vaults cannot be deleted.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier of the topic. | |
ProjectId | Long | True |
Projects.Id |
The Project ID associated with the vault. |
Status | String | True |
The status of the vault. | |
Title | String | False |
The title of the returned record. | |
Type | String | True |
The type of the returned record. | |
CreatedAt | Datetime | True |
The date and time when the record was created. | |
UpdatedAt | Datetime | True |
The date and time when the record was last updated. | |
ParentId | Long | True |
Id of the parent. | |
ParentTitle | String | True |
Title of the parent. | |
ParentType | String | True |
Type of the parent. | |
BucketId | Long | True |
Id of the containing bucket. | |
BucketName | String | True |
Name of the containing bucket. | |
BucketType | String | True |
Type of the containing bucket. | |
CreatorId | Long | True |
People.Id |
The ID of the creator. |
CreatorName | String | True |
The name of the creator. | |
DocumentsCount | Integer | True |
Number of documents the vault contains. | |
UploadsCount | Integer | True |
Number of uploads the vault contains. | |
VaultsCount | Integer | True |
Number of vaults the vault contains. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
VaultId | Long |
The Vault ID that the record belongs to. |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Name | Description |
Assignments | Retrieve Assignments on Basecamp 3. |
Calendars | Retrieve calendars on Basecamp 3. |
Events | Retrieve activity Events on Basecamp 3. |
People | Retrieve People on Basecamp 3. |
Recordings | Retrieve recordings on Basecamp 3 folders. |
Uploads | Retrieve Uploads on Basecamp 3. |
Retrieve Assignments on Basecamp 3.
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier for the person. | |
TodoListId | Long |
ToDoLists.Id | The unique identifier for the todolist. |
ProjectId | Long |
Projects.Id | Use filter to retrieve active people on the project with the given ID. |
Name | String | The name of the person. | |
Owner | Boolean | Boolean value to show if the current person is the account owner. | |
Admin | Boolean | Boolean value to show if the current person is an admin or not. | |
Title | String | The title of the person. | |
Bio | String | Bio description of the person. | |
CreatedAt | Datetime | The date and time when the record was created. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
EmailAddress | String | The email address of the person. | |
AttachableSgid | String | The attachable Set Group ID. | |
PersonableType | String | Type of the person. | |
TimeZone | String | Human readable timezone for the user. | |
CompanyId | Long | Id of users company. | |
CompanyName | String | Name of users company. |
Retrieve calendars on Basecamp 3.
There is only one Calendar (Schedule) in each Basecamp project.
Retrieve more detail on a specific Calendar (ProjectId and ID are the only columns supported as search criteria by Basecamp). If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Calendars WHERE ProjectId = '111111111' AND ID = '123456789'
Name | Type | References | Description |
Id [KEY] | Long |
Projects.CalendarId | The unique identifier of the calendar. |
ProjectId | Long |
Projects.Id | The Project ID associated with the calendar. |
Status | String | The status of the calendar. | |
Type | String | The type of the returned record. | |
CreatedAt | Datetime | The date and time when the calendar was created. | |
UpdatedAt | Datetime | The date and time when the calendar was last updated. | |
Title | String | Title of the calendar. | |
EntriesCount | Integer | Number of calendar events. | |
BucketId | Long | Id of the containing bucket. | |
BucketName | String | Name of the containing bucket. | |
BucketType | String | Type of the containing bucket. | |
CreatorId | Long |
People.Id | The ID of the creator. |
CreatorName | String | The name of the creator. |
Retrieve activity Events on Basecamp 3.
An event is created any time a recording changes.
You can retrieve all activity Events in the specified project and recording. The columns supported as search criteria by Basecamp are ProjectId and RecordingId.
If ProjectId is not specified in the query, the default ProjectId will be used.
SELECT * FROM Events WHERE ProjectId = '1111111' AND RecordingId = '22222222'
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier of the event. | |
ProjectId | Long |
Projects.Id | The ID of the Project associated with the document. |
RecordingId | Long |
Recordings.Id | The ID of the Recording associated with this event. |
Action | String | The action description for the event. | |
Details | String | Details on the event. | |
CreatedAt | Datetime | The date and time when the event was created. | |
CreatorId | Long |
People.Id | Event creator Id. |
CreatorAttachableSgid | String | Event creator Attachable SGID. | |
CreatorName | String | Event creator Name. | |
CreatorEmailAddress | String | Event creator Email Address. | |
CreatorTitle | String | Event creator Title. | |
CreatorBio | String | Event creator Bio. | |
CreatorCreatedAt | Datetime | Event creator creation date and time. | |
CreatorUpdatedAt | Datetime | Event creator update date and time. | |
CreatorAdmin | Boolean | Boolean indicationg if the event creator is an admin. | |
CreatorOwner | Boolean | Boolean indicationg if the event creator is an owner. | |
CreatorTimeZone | String | Event creator's timezone. | |
CreatorCompanyId | Long | Event creator's company Id. | |
CreatorCompanyName | String | Event creator's company name. |
Retrieve People on Basecamp 3.
Retrieve all People visible to the current user, list people by Project, or specify an ID to get more information. ID and ProjectId are the columns supported by Basecamp as search criteria.
SELECT * FROM People SELECT * FROM People WHERE Id = '123456789' SELECT * FROM People WHERE ProjectId = '5168618'
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier for the person. | |
Name | String | The name of the person. | |
Owner | Boolean | Boolean value to show if the current person is the account owner. | |
Admin | Boolean | Boolean value to show if the current person is an admin or not. | |
Title | String | The title of the person. | |
Bio | String | Bio description of the person. | |
CreatedAt | Datetime | The date and time when the record was created. | |
UpdatedAt | Datetime | The date and time when the record was last updated. | |
EmailAddress | String | The email address of the person. | |
AttachableSgid | String | The attachable Set Group ID. | |
PersonableType | String | Type of the person. | |
TimeZone | String | Human readable timezone for the user. | |
CompanyId | Long | Id of users company. | |
CompanyName | String | Name of users company. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
ProjectId | Long | Use filter to retrieve active people on the project with the given ID. |
Retrieve recordings on Basecamp 3 folders.
Retrieve most of the data structures in the Basecamp 3, which are represented as "Recordings."
The columns supported as search criteria by Basecamp are Type (required parameter), Bucket, and Status.
SELECT * FROM Recordings WHERE type = 'Upload' AND Bucket = '1111111, 22222222' AND Status = 'active'
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier of the topic. | |
Status | String | The status of the vault.
The allowed values are active, archived, trashed. | |
Type | String | The type of the vault.
The allowed values are Comment, Document, Message, Question:Answer, Schedule:Entry, Todo, Todolist, Upload. | |
CreatedAt | Datetime | The date and time when the event was created. | |
UpdatedAt | Datetime | The date and time when the event was last modified. | |
CreatorId | Long |
People.Id | Event Creator. |
CreatorName | String | Event Creator. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
Bucket | String | Single or comma separated list of project IDs. Default: All active projects visible to the current user |
Retrieve Uploads on Basecamp 3.
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier for the file uploaded. | |
ProjectId | Long |
Projects.Id | The ID of the Project associated with the file uploaded. |
VaultId | Long |
Vaults.Id | The Vault ID associated with the file uploaded. |
Status | String | The status of the vault. | |
Title | String | The title of the file uploaded. | |
ContentType | String | The content type of the file uploaded | |
Type | String | The type of the returned record. | |
CreatedAt | Datetime | The date and time when the file uploaded was created. | |
UpdatedAt | Datetime | The date and time when the file uploaded was last updated. | |
ParentTitle | String | Title of the parent. | |
ParentType | String | Type of the parent. | |
BucketName | String | Name of the containing bucket. | |
BucketType | String | Type of the containing bucket. | |
CreatorId | Long |
People.Id | The ID of the creator. |
CreatorName | String | The name of the creator. | |
CommentsCount | Integer | Number of comments the file uploaded has. | |
DownloadURL | String | Number of comments the file uploaded has. | |
ByteSize | Integer | Number of comments the file uploaded has. |
NOTE: Stored procedures are not currently supported. See the above note for details.
Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Name | Description |
GetOAuthAccessToken | Gets the OAuth access token from Basecamp. |
GetOAuthAuthorizationURL | Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with various Basecamp services. |
Gets the OAuth access token from Basecamp.
Name | Type | Required | Description |
AuthMode | String | True | WEB or APP. The Basecamp authentication mode. Default is APP.
The default value is APP. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. |
Verifier | String | False | The verifier token returned by Basecamp. Required for only the Web AuthMode. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Name | Type | Description |
OAuthAccessToken | String | The OAuth token. |
OAuthRefreshToken | String | The OAuth refresh token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.
Name | Type | Required | Description |
CallbackUrl | String | False | The URL that Basecamp will return to after the user has authorized your app. |
State | String | False | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Name | Type | Description |
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
Refreshes the OAuth access token used for authentication with various Basecamp services.
Name | Type | Required | Description |
OAuthRefreshToken | String | True | The refresh token returned with the previous access token. |
Name | Type | Description |
OAuthAccessToken | String | The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Basecamp:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Name | Type | Description |
CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Name | Type | Description |
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Name | Type | Description |
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Projects table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Projects'
Name | Type | Description |
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Name | Type | Description |
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure* parameters.
The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2
Name | Type | Description |
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure* containing the parameter. |
ColumnName | String | The name of the stored procedure* parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys. The following query retrieves the primary key for the Projects table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Projects'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Name | Type | Description |
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Name | Type | Description |
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:basecamp:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Name | Type | Description |
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the connector can offload to the data source.
When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
COUNT | Whether COUNT function is supported. | YES, NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source, Destination, Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures* that can be used for generating schema files. |
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Name | Type | Description |
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Name | Type | Description |
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
AuthScheme | The type of authentication to use when connecting to Basecamp. |
Schema | Specify the Basecamp version to connect with. |
User | Username of the currently authenticated user. |
Password | Password of the currently authenticated user. |
AccountId | Account ID of the currently authenticated user. |
ProjectId | The Project ID of the currently authenticated user. |
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthAuthorizationURL | The authorization URL for the OAuth service. |
OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
This section provides a complete list of authentication properties you can configure.
Property | Description |
AuthScheme | The type of authentication to use when connecting to Basecamp. |
Schema | Specify the Basecamp version to connect with. |
User | Username of the currently authenticated user. |
Password | Password of the currently authenticated user. |
AccountId | Account ID of the currently authenticated user. |
ProjectId | The Project ID of the currently authenticated user. |
The type of authentication to use when connecting to Basecamp.
string
"Auto"
Specify the Basecamp version to connect with.
string
"V2"
The schemas available are V2 (to use the Basecamp 2 API) andV3 (to use the Basecamp 3 API).
Username of the currently authenticated user.
string
""
Username of the currently authenticated user. This property is required to execute commands.
Password of the currently authenticated user.
string
""
Password of the currently authenticated user. This property is required to execute commands.
Account ID of the currently authenticated user.
string
""
Account ID of the currently authenticated user. This property is required to execute commands.
The Project ID of the currently authenticated user.
string
""
The Project ID of the currently authenticated user. By default it will be set to the first Project ID of your projects list.
This section provides a complete list of OAuth properties you can configure.
Property | Description |
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthAuthorizationURL | The authorization URL for the OAuth service. |
OAuthAccessTokenURL | The URL to retrieve the OAuth access token from. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
string
"OFF"
The following options are available:
The client ID assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
The access token for connecting using OAuth.
string
""
The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
string
""
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
The authorization URL for the OAuth service.
string
""
The authorization URL for the OAuth service. At this URL, the user logs into the server and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
The URL to retrieve the OAuth access token from.
string
""
The URL to retrieve the OAuth access token from. In OAuth 1.0, the authorized request token is exchanged for the access token at this URL.
The verifier code returned from the OAuth authorization URL.
string
""
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
See to obtain the OAuthVerifier value.
Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set. To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.The OAuth refresh token for the corresponding OAuth access token.
string
""
The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.
The lifetime in seconds of the OAuth AccessToken.
string
""
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
The Unix epoch timestamp in milliseconds when the current Access Token was created.
string
""
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
This section provides a complete list of SSL properties you can configure.
Property | Description |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of schema properties you can configure.
Property | Description |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\Basecamp Data Provider\Schema"
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note: Given that this connector supports multiple schemas, the structure for Basecamp custom schema files is as follows:
If left unspecified, the default location is "%APPDATA%\\Basecamp Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:
Platform | %APPDATA% |
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
string
""
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
string
""
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
int
-1
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
string
""
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the connector throws an exception.