Skip to main content

ODBC channel

As a developer, you can use the ODBC channel to connect to databases.

See the Channel Catalog to see which Plans have access to this channel.

At this time, the MSSQL and Oracle databases are supported. Connectivity to other databases, while possible, are not supported by the Quickbase technical support and Services team at this time.

Note: If the returned data in your SQL is more than 100 items, you should use table sorting in your query and the Cursor Pagination Type on your step to make sure that you receive the right results during paging, such as sorting on a unique ID in the table, for example:

ODRER BY [INVOICE_MACHINE_LINE_FACT_KEY] DESC

The ODBC channel and databases

Use the ODBC channel to connect to different databases and Jira.

Before you connect

To set up the connection with a database, we recommend that you

  • Ensure that you have installed an ODBC Driver for your database

  • Create a Data Source Name (DSN) to use in the connection string

  • Your ODBC Driver and DSN must be both 64 bit

  • If the agent-client is running as System user, your DSN should be created as System DSN. Otherwise use User DSN and create it with the same user the agent-client is running with. Otherwise the DSN won’t be accessible.

More about DSN

You should set up your DSN with the user that will be running the agent. If the agent is running as System user, it should be system DSN. In the logs file (C:\logs\QuickbasePipelinesAgent.log), you could see if the DSN is visible to the agent:

The following DSNs are configured:
{dsn_name}:{dsn_driver} connect with: DSN={{{dsn_name}}}

If not, you'll see No DSNs configured Configure it properly according to which user the agent is running.

Your configuration should be user or system DSN depending how you will be running your agent client. If it is a user DSN, but you are defining it logged in as user X and the agent client is running with user Y, it won’t see your configuration, since it's visible only for your user

In another example, one of our clients had special SQL user with the correct rights to the database, but had chosen With windows NT authentication using the network login ID, so their connection failed. The right choice for you depends on your MSSQL server configuration, but you need to be sensitive that the choice here is important.Screenshot_2022-12-08_at_17.46.04.png

Connecting using a proxy

If you’re connecting the Agent to your client using a proxy and you experience errors such as these:

ERROR:cloudpipes_agent_client.self_diagnostics:self_diagnostics.py:34:Connection to www.pipelines.quickbase.com on IP XXX.XX.XXX.XX and port 443 failed. Please check your firewall settings and Internet connectivity 2022-12-14 08:41:51,203 INFO:cloudpipes_agent_client.self_diagnostics:self_diagnostics.py:159:Self diagnostics completed
2022-12-14 08:41:51,203 DEBUG:urllib3.connectionpool:connectionpool.py:1005:Starting new HTTPS connection (1): www.pipelines.quickbase.com:443
2022-12-14 08:41:51,314 ERROR:cloudpipes_agent_client.state:state.py:148:Failed to start because of ProxyError(MaxRetryError("HTTPSConnectionPool(host='www.pipelines.quickbase.com', port=443): Max retries exceeded with url: /api/agent_config (Caused by ProxyError('Cannot connect to proxy.', ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None)))"))

Here are some steps to help you troubleshoot the problem:

Try connecting with the proxy OFF. We recommend contacting your security team, as each configuration may be unique. If you can connect with it off, it means there is a proxy configuration that may need to be updated to allow your new connection.

How to Connect

  1. On the My pipelines page, select Create Pipeline. 
    new.connect.png
  2. Search for the first step for your new pipeline. You can always add more steps later.

For more information about connections, see How to connect to a channel.

4. Expand ODBC, and then select Connect to ODBC.
      Learn more about ODBC Connection string structure in Microsoft documentation.

5. Select the Proxy connection check box to use the Pipelines Agent.

How to reconnect

You may need to reconnect your account to a channel. Reasons may be (but not limited to):

  • If you need to connect a different account.
  • Authorization updates, such as a changed password.
  • Editing the access rights that Pipelines has to the channel.

To reconnect:

  1. Select a pipeline that already has this channel in it.
  2. Open a step that contains this channel.
  3. Under account, select Connect (or reconnect) and follow the process above, How to connect.

Steps

The steps you can use with ODBC are: Queries and Rows.

Type Name Description
Queries
Action Execute SQL. Executes your SQL. This is intended for data modifications, such as INSERT, UPDATE, DELETE, etc. You should know the schema of the table, including table names, column names, data types, and param names. You also need whatever permissions are required for the action you want to perform. Your DB administrator will need to setup those permissions in the database — pipelines will not check for this.
Rows
Action Create a row. Select the table where you want to create a row. You can select up to 100 columns for values. Primary and Foreign Keys are denoted but aren't required in the case of auto-generated values. There is no output for the Create step. To fetch the value after creation, run a Search.
When nothing is selected, the default mechanism is ([ ]).
Query Search rows. Provides a SELECT query that returns a set of rows that can be iterated over. You need the schema of the table, including table names, column names, and data types. You can provide a list of up to 100 column names (comma-separated header), to map the results to specified data types. Provided column names must match the column names in your database table the results. Aliases can also be used in the query; the column names must match the aliases. After adding your column names a dropdown data type selector/specifier displays for each column name. Options display with data type specifiers. If something is unavailable, use the default String, and transform the data later in the pipeline using Jinja expressions if needed. As a best practice, any dynamic filter criteria should be using query params, denoted by ? in the Query input. Provide an alias for each param (UP TO 50), corresponding to the location of the param in the query, for example:

SELECT * FROM companies WHERE company_id = ?
Options display with inputs where the user can provide values for the params

Limits

These are the known limits at this time:

  • Max 100 selected columns
  • Max 50 selected params

Working with MSSQL

Here are some tips when working with MSSQL:

  • In some cases it is possible to use double quotes (“ “) but to do so, you need to check if QUOTED_INDENTIFIER setting is switched on.
  • To check the current state execute: SELECT sessionproperty('QUOTED_IDENTIFIER')

Escaping columns with special characters

Use square brackets ([ ]) to escape/select columns/identifiers which contain special characters in their name. For example:

SELECT [my column with spaces] as my_column FROM …

More about MSSQL Identifiers see: Database Identifiers

Query multiple databases using one query

You can query a multiple databases in one query in one of the following ways:

  1. Create a view that combines data from two tables in different databases and from pipelines query for that view like:

    Rows → Search Rows step

    SELECT * FROM context_db.dbo.view_that_collects_data_from_2_dbs

  2. Use a SQL query that combines data in different databases:

    SELECT somecolumn from DATABASE1.dbo.table1
    UNION
    SELECT othercolumn from DATABASE2.dbo.table2

  3. Query data from different databases:

    When you establish connection to odbc you are always in context of a database. You can specify a different database context from the DSN like

    DSN=mssql;UID=sa;PWD=YourSTRONG!Passw0rd; DATABASE=SOMEDATABASE;

    If you don’t specify a database by default you are in context of the master database. So if you want to query for a data that is in a different database you should specify the full table path which is usually like ex:

    SELECT * from SOMEDATABASE.dbo.SOMETABLE;

Example of usage

For examples of ODBC channel usage, see Quickbase Pipelines Agent use cases

Related articles

Top