Data Everywhere's Database Connector allows you to synchronize a database table and a Data Everywhere feed. The Connector can be configured to publish data out of a database, or it can be configured to refresh the data in the database with data from a Data Everywhere feed.
The Connector is a small piece of software that must be installed on a server or computer that can connect to the database you would like to query. Your database does not need to be accessible from outside of your network; you will not need to make any changes to your firewall or your security rules. Data Everywhere does not access your database from the Internet - you send data to Data Everywhere, instead of Data Everywhere pulling your data from across the Internet.
The connector supports most types of databases, including but not limited to:
- Microsoft Access via OLEDB
- Microsoft SQL Server
- MySQL and MariaDB
The Connector can either publish or refresh the data in the database.
Get started by creating a Data Everywhere feed in Excel or Google Sheets. Be sure to note the feed id, which is listed on the bottom of the main page for a feed on the Data Everywhere website.
To use the connector, you must download it and install it to a local directory.
The connector is configured in a file called connections.ini; a sample of this file is included in the download as "connections.ini.sample". You will want to rename the sample file to connections.ini, and edit the file to reflect your configuration. The connections.ini contains the login information for your Data Everywhere account:
Edit the Email and Password blocks to be your Data Everywhere login credentials.
Now you will need to add query configurations for sending or receiving data. You can have as many queries as you wish, and you may have any mixture of sending and receiving queries.
Configuration for writing data to a database
Each query specifies the information required to connect to your local database, the query that it is to be run, and the feed or table that should be updated with the result of the query. The database connection information is the same format for each query, and each query must specify its own connection information.
Configuring Connection Information
Here's a sample connection information block, with explanation below:
Connection Information Explained
The name of your query, in this case "SampleQuery". You'll use this name to specify that this query should run when you invoke the Connector from the command line. The query name must not contain spaces.
The type of database you would like to connect to. The supported types are:
- Odbc - use for an ODBC connection to a SQL database
- OleDb - use for Microsoft Access and other OLE connections. More information for Access users here
- Oracle - use for connecting to Oracle databases; you will need the Oracle provided drivers for your system
- PostgreSQL - use for PostgreSQL
- SqlServer - for native connections to MS SqlServer
Ask your system administrator for a connection string to access the database; this will point to the location of your database, and will contain the username and password for accessing your local database.
The easiest way to obtain the connection string is to copy it from another tool that already connects to the database; the connection string can often be found in a properties tab for the connection.
For Microsoft Access, replace PATH_TO_YOUR_DATABASEFILE with the actual file path to your .accdb file, and use:
ConnectionString=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PATH_TO_YOUR_DATABASEFILE;Persist Security Info=False;
The results of the query will replace the contents of the Data Everywhere feed with this ID. The ID is found on the Data Everywhere website at the bottom of each feed home page.
Configure the query to send or receive data:
Once you have completed the connections.ini, you can run the connector.
Open a command line prompt, change to the directory in which you installed the connector, and type
DatabaseConnector.exe -q YourQuery
You will see the connector start up, and run one sync of your data. (If you are downloading data, you will need to run the setup command explained here .) Correct any errors that you see reported.
This one-time publish mode is intended for use with an external scheduler, such as the Windows Scheduler. We have an article with very detailed instructions on setting up the Database Connector to use the Windows Scheduler.