Skip to content

Database Connection Notes

ThinkAutomation supports native access to the following database types:

  • Microsoft SQL Server
  • Microsoft SQL Server Azure
  • MySQL / Maria DB
  • SQLite
  • Oracle
  • PostgreSQL
  • DB2
  • Firebird
  • Microsoft Access
  • MongoDB

In addition it can also connect to an ODBC DSN and use any OLEDB driver.

For all database types you can either specify the connection string directly or click the ... button on the Connection String entry to use the Connection String Builder.

SQL Server

Set the Instance to the name or network address of the instance of SQL Server to connect or use '(local)' for the local instance. An optional port number can be specified after the name (eg: 'MySQLServer, 1433'). To force a protocol add one of the prefixes - tcp, np or lpc. eg: 'tcp:servername'.

SQL Server Azure

Set the Server to the address of your Azure Database followed by comma port number. Eg: 'mydatabase.database.windows.net,1433'

MySQL

If you use MySQL for the ThinkAutomation Message Store or if you are using ThinkAutomation to store very large records you may need to increase the max_allowed_packet option on your MySQL server, as a single record may be too large for an Insert. See Packet Too Large.

Microsoft Access

Before connecting to a Microsoft Access database you need to install the Microsoft Access Database Engine. Go to: https://www.microsoft.com/en-us/download/details.aspx?id=54920 and install the 64 bit engine (accessdatabaseengine.exe). Office does not need to be installed, however if Office is installed on the ThinkAutomation computer it will need to be the 64 bit version.

IBM DB2

Before connecting to a DB2 database the IBM Data Server Runtime Client must be installed. The IBM Data Server Driver package can be downloaded here. The IBM.Data.DB2.dll IBM DB2 .NET Data Provider library must be in the global path or copied to the ThinkAutomation program files folder.

Copy the \Program Files\IBM\IBM DATA SERVER DRIVER\bin\netf40\IBM.Data.DB2.dll file to \Program Files\ThinkAutomation\ folder or add C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin\netf40\ to your PATH environment variable (you will need to restart the computer after adding the path).

OLEDB

ThinkAutomation can connect to any OLEDB data source configured on your machine. The connection string must use 'OLE DB Provider=providername' not 'Provider=providername'. This is because ThinkAutomation uses 'Provider=' internally to indicate the database type.

MongoDB

ThinkAutomation can read and update documents from MongoDB databases. This can be any locally installed MongoDB or cloud based MongoDB compatible document databases such as MongoDB Atlas, Amazon DocumentDB or Azure Cosmos DB.

ODBC Data Sources

To connect to an ODBC data source. First, ensure the required ODBC driver is installed. Configure a System DSN using the Windows ODBC Data Source Administrator. When connecting to the database in ThinkAutomation, select ODBC as the Database Type and enter the DSN name created earlier. ODBC drivers are available for many database types.

Parker Software can supply high performance ODBC drivers that work with ThinkAutomation for the following data sources and cloud based services:

  • FreshBooks
  • Magento
  • Mailchimp
  • NetSuite
  • QuickBooks
  • SAP ASE
  • Asana
  • Amazon Redshift
  • Google BigQuery
  • Confluence
  • HubSpot
  • WooCommerce
  • Jira
  • Zendesk
  • Shopify
  • Stripe
  • Slack
  • FoxPro/xBase/Interbase
  • plus many more....

Please contact us for details and pricing.

SQL Identifiers

Universal Quoting Of Identifiers

All database servers support quoting for identifiers that contain special symbols like spaces or dots. ThinkAutomation allows you to wrap identifiers universally so that quotation is appropriate for every database server. Use the following syntax:

"identifier" For example, expression "table1"."field1" turns into "table1"."field1" in Oracle and PostgreSQL, into [table1].[field1] in MS SQL Server, and into `table1`.`field1` in MySQL server. Do not confuse with single quotes, which are intended to wrap string constants.

Comments

Comments are inserted with two hyphens (comments out the text till the end of current line). For multiline comment, wrap it into /.../ sequences. Example:

--This is a single-line comment

/*This one
  spans over
  several lines*/

Date And Time Constants

When using date/time constants in SQL statements the parts of date are separated with hyphen, time parts are separated with colon, and space is expected between the two parts. The following table illustrates date/time format:

Literal Type Format Example
date yyyy-mm-dd {date '2022-01-01'}
time hh🇲🇲ss {time '23:59:49'}
timestamp yyyy-mm-dd hh🇲🇲ss {timestamp '2022-01-01 09:00:00'}

The following SQL statement:

SELECT * FROM emp WHERE HIREDATE>{date '1982-01-15'}

in MySQL evaluates to

SELECT * FROM emp WHERE HIREDATE>CAST('1982-01-15' AS DATETIME)

and in Oracle to

SELECT * FROM emp WHERE HIREDATE>TO_DATE('1982-01-15', 'YYYY-MM-DD')