Kanbanize allows you to periodically sync the result set of an SQL statement to Kanbanize cards.
This is especially useful when you are using a legacy system, but you want to track items in an agile workflow.
The connector app (dbKonnector_1_3) is a Java standalone application that runs locally.
Below are some details about the DB connector usage.
OS - Linux, Windows
Java platforms - Java 1.6+
Databases - MySQL, Oracle, Microsoft SQL Server, PostgreSQL
Supported Kanbanize fields
- all standard and custom fields
- card location fields - column, lane
- The sync interval is 1 hour - This means that once an hour, the SQL statement will be executed and the changes in the result set will be applied to the Kanbanize cards.
- Allowed cards to sync - 200 - This means that if the result set contains more than 200 records, the db connector will not execute the sync operation.
- Contact support with a request for the DB connector application.
- Extract the dbKonnector_1_3.zip archive
- Go to “dbKonnector_1_3/drivers”
- Download the jdbc driver for the database type and place it inside the “drivers” folder
Oracle - http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
MS SQL - https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server
PostgreSQL - https://jdbc.postgresql.org/download.html
- Go to “dbKonnector_1_3/config”
- Open “application.properties”
- First, you have to setup the database connection parameters
- Then, you have to setup the Kanbanize connection parameters
“mycompanyname” is the subdomain of your account. If you access Kanbanize at https://mycompanyname.kanbanize.com then the subdomain is “mycompanyname”.
Set ‘kanbanize.apikey’ to the API key of any of your Kanbanize users.
We recommend using a dedicated Administrator user or a standard user (e.g. db_user) with permissions to access and modify cards on the desired boards.
To find out the user’s API key, do the following:
1. Login to the system with the corresponding user
2. Click on your username in the upper right corner of the screen
2. Click on My account
3. Select API, then copy the API key
- Now, it’s time to set the sync parameters
This parameter holds the SQL statement.
The statement must use SQL aliases that map DB table column names to Kanbanize card fields.
Requirement: 'extlink' is the only required alias because it is used to link Kanbanize cards to DB records. It must be unique for each record.
The id of the board where all cards corresponding to the sql records will be kept.
If a record is no longer returned from the SQL statement what should happen with the corresponding Kanbanize card?
This parameter accepts three values:
- delete - deletes the card from the Kanban board
- archive - archives the card
- nothing - does nothing
NOTE: Only cards in the Done section can be archived.
If a record is no longer returned from the SQL query and the card is not in the Done section, it won’t be archived automatically.
Let's take a look at the following example for a ticketing system:
db.layer.1.sql=SELECT ticket_summary as title, ticket_text as description, ticket_id as extlink, ticket_status as column FROM `tickets_table` limit 200
The SQL statement will be executed when you start the app and the result set will be passed to Kanbanize. This process will repeat in one hour intervals. For each record Kanbanize will check if a card with external link equal to the value of extlink exists. If it finds a matching card, the corresponding fields of the card will be updated. Otherwise a new card will be created in the board with id 15 in a column with the same name as the ticket status. If no column name matches the ticket status, the card will be created in the Backlog. If a record is no longer returned, the corresponding card will be deleted.
Important: 'extlink' is the only required alias because it is used to link Kanbanize cards to DB records. It must be unique for each record.
- Go to “dbKonnector_1_3/bin”
- Open a terminal
- For Linux execute “./dbKonnector”, for Windows execute “dbKonnector.bat”
If you have any trouble don’t hesitate to contact us.