How to Import Data by Connecting to an SQL Database

Introduction

This article describes how to import data from an SQL database into Displayr.

Requirements

• An SQL database to connect to. In this example we are using a public MySQL database called Ensembl.
• Displayr must be able to connect (over the public internet) to the database server. This may require your network administrators to reconfigure firewalls. There is no predictable outbound IP address used by Displayr.
• You must know:
  • Type of database (brand, e.g. Microsoft SQL Server)
  • Server name
  • Database name
  • User name
  • Password

Method

1. Select Data Sets > Plus (+) > SQL.

2. Enter a Name for your data set.

3. Select the appropriate Data provider. In this case, we will select MySql.Data.MySqlClient. If your provider is not listed, please contact Support.

4. Use the correct Connection string and then press Test to check the connection is OK. Your connection string will contain all the necessary parameters, such as server, database, user id and password similar to below:

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=True;

You can find more examples of SQL connection strings here. In this example, we will use:

Server=ensembldb.ensembl.org; Database=aedes_aegypti_core_48_1b; UID=anonymous;Port=3306

5. Click the Test button to make sure you get the "Connection OK" message. 

6. OPTIONAL: Adjust Maximum cases to limit the number of records pulled from the data.

7. Set update cycle via Automatically refresh every X hours.

8. Write your SQL command in the box provided. Here we will pull data from specific fields from this database:

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,TABLE_TYPE,CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'acanthochromis_polyacanthus_core_94_1'

9. Press OK.

See Also

How to Import Data into Displayr