As a developer, you can use the ODBC channel to connect to MSSQL databases and to Jira.
Before you connect
To setup the connection with MSSQL, we recommend that you
In your ODBC Driver (must be 64-bit) on your private network, ensure that you have created a “System User” Data Source Name (DSN). The DSN credentials will be used when you configure your first Pipeline using ODBC Channel steps.
Use TCP/IP Network Protocol in SQL Server
Ensure SQL browser service is turned on
On the My pipelines page, click Create a pipeline. Enter name, description, and tag fields and click the Create Pipelines button. The pipelines wizard displays:
- From the wizard you choose the connections for the first steps of your pipeline. When you choose a type, you'll add the channels and steps for your first steps. You can always add more steps later. A Triggered pipeline is started by a specific event in real-time. A Scheduled pipeline will start according to a schedule. A Manual pipeline only starts manually. Once you've completed the wizard, you'll be prompted for connection detail, if necessary. If you are an experienced builder, click the Start from scratch button and then from the right side of the page, choose the All to list all available channels.
- Expand ODBC and click Connect to ODBC
Click the Proxy connection check box to use the Pipelines Agent.
How to reconnect the ODBC channel
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.
- Select a pipeline that already has ODBC in it.
- Open a step containing ODBC.
- Under account, select Connect (or reconnect) and follow the process above, How to connect.
The steps you can use with ODBC are: Queries and Rows.
|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.|
|Action||Create a row.||Select the table where you want to create a row. You can select up to 50 columns for values. Primary and Foreign Keys will be denoted, but are not required in the case of auto-generated values. There is no output for the Create step – if you want to fetch the value after creation, you will need to run a Search.|
|Query||Search rows.||Provides a SELECT query that will return a set of rows that can be iterated over. You need to know schema of the table, including table names, column names, data types.
You can provide a list of up to 50 column names (comma-separated header), to map the results to specified data types.
Provided column names must match the names in the results — alternatively, aliases can be used in the query, and the column names must match the aliases
Options will appear with data type specifiers. If something is not available, you can 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 |
Options will appear with inputs where the user can provide values for the params
These are the known limits at this time:
- Max 50 selected columns or params.
- The "Create Row" step can only be used within the default schema (dbo).
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_INDENTIFIERsetting is switched on.
- To check the current state execute:
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:
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
Use a SQL query that combines data in different databases:
SELECT somecolumn from DATABASE1.dbo.table1
SELECT othercolumn from DATABASE2.dbo.table2
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
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