Inserting Data Into Your Database With The Database Connector

Overview

The Data Everywhere Database Connector moves data into and out of local databases, such as MySQL, SqlServer, PostgreSQL, or Access. This data can be sourced from any other Data Everywhere Connector, such as Google Forms, Google Sheets, Salesforce, Excel, or even another database.

Click here to download and install the database connector.

The Database Connector is a small Windows program. Contact us for more detailed instructions for installation on OSX or Linux; it can be run on those operating systems under the Mono framework.

The Database Connector is controlled by the connections.ini file and a tracking table that is created in your database. To get started, please follow the instructions here for general setup of the connector.

Tracking Table

Queries that insert data into your local database will maintain a history of the data in a table in your database called de_dbconn_config. This tracking table records which version of the feed is currently stored in your database. The Database Connector will not download data from the server if the version that is stored in your database matches the available version from the server. You can create this table yourself, or you can instruct the Connector to create it for you; setup your query in connections.ini, and just run the Connector with the command:

DatabaseConnector.exe -q YourQuery --setup true

You must have the connection information for the specified query is present in your connections.ini file in order for the tracking table to be created in your database. You do not need to rerun the setup command for other queries that use the same database; all queries will share the same tracking table. If you ever wish to reset the data you have stored locally and force the system to download a fresh copy, just delete that feed's row from the tracking table.

Query Configuration

Each query is listed in connections.ini, and you may have as many queries defined as you like. Please see the main configuration article Configuring The Database Connector.

Let's take a look at the configuration for an insert.

InsertStatement=insert into test_download(birthdate, name, weight) values( {3:DATE}, {1}, {2:DECIMAL} )

The InsertStatement will be executed once for each row that is being downloaded. The Connector will map the value of each column into the statement at the {1..N} markers, so that the first column of the feed will replace {1}, the second column of the feed will replace {2}, etc.

By default, the Connector will treat all columns as String data. However, you may specify a different data type. The supported types are:

  • STRING
  • DECIMAL
  • INTEGER
  • DATE
  • DATETIME

Specify your data type with a colon separator: {1:DECIMAL}, or {2:DATETIME}. STRING is the default value.

BeforeStatement=truncate table test_download

If the Connector determines that the local data is out of date, it will execute the BeforeStatment once, before it begins the inserts for that query. This is a good time to clear out the existing data from your table. Please note that Access does not support the truncate command; for Access, you will need to run 

BeforeStatement=delete from table test_download

The Connector also supports an AfterCommand that will run after all of the inserts are complete, in the same manner as the BeforeCommand.

Still need help? Contact Us Contact Us