This article describes how to import data from an SQL database into Displayr.
- 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
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:
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:
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'acanthochromis_polyacanthus_core_94_1'
9. Press OK.