MySQL is a database program that is free to download and use. Keeping your data in a MySQL database allows you to access specific portions based on user-defined queries. In order to use a MySQL database file within Origin8, you will need to have an existing MySQL database set up on your network, and the ODBC connector driver on the machine from which you are connecting to the database.
Installing a .sql file into an existing MySQL database:
For this tutorial, we will be referencing a database of cities and countries of the world which can be found at http://dev.mysql.com/doc/. You can also find further documentation on all of the subjects covered here at that site.
If you are connecting to your MySQL database through a network, you must perform the first part of the setup on the computer serving your MySQL database. You can do this through a remote connection application like Windows' Remote Desktop. Once logged into the computer serving the MySQL database, open your command prompt. Navigate to the directory in which the world file is saved using the CD command and enter the following line in the prompt:
mysql –u root –p
This will connect to your server through the administrative or “root” account. MySQL should prompt you to enter your root password. Once connected, type the following lines:
create database world;
use world;
source world.sql;
You will see MySQL compiling your database. When it is finished, you can test your installation by typing
show tables;
This command should cause MySQL to display the available tables in world.sql: City, Country, and CountryLanguage.
Connecting to your MySQL database:
In order to connect to your MySQL database file you will need the MySQL Connector/ODBC on the computer you intend to connect to the database, which you can obtain from: http://dev.mysql.com/downloads/connector/odbc/3.51.html
Choose the version appropriate to your operating system and download the installer. Simply follow the on-screen instructions to add the ODBC connector driver to your computer.
The next step is setting up your connection. Open the Windows Control Panel and the “Administrative Tools” folder. (Vista users may have to switch to “Classic View” in order to see this file path) Double-click on the icon “Data Sources (ODBC).” Click on the “Add…” button.
Give your data source a memorable name like “World” for our example, and a short description if you like. For “server” enter the name of the source computer. If your data is on \\CompanyServer\, simply type “CompanyServer.” The default administrative user name for MySQL is "root," and the password you associated with that account when installing MySQL should be given here. If you have set up your database and connection properly, you will see the name of the database as you saved it in the MySQL database in the dropdown menu at the bottom of the dialog box.
You can click “Test” to see if your connection is running properly, and “OK” to set up the connection. You can now access your data within Origin. Click “OK” in the ODBC Data Source Administrator dialog to complete the process.
Accessing and Manipulating Your Data in Origin:
You can access your database through the “Database Access” toolbar. You can open this toolbar via “View: Toolbars…” The very first button on the toolbar is the “Create/Edit SQL” button. Click on it to open the Query Builder dialog. To add a file, click “Query: Data Source: New.” Open the “Provider” tab and select Microsoft OLE DB Provider for ODBC Drivers, then click “Next.” From the first drop down in the dialog, you should be able to select your newly added dataset. Click “OK” and your database will appear in the right-hand side of the query builder.
Drag the “city” and “country” icons into the center window of the query builder. By checking the boxes of the fields you want to display in your eventual output, you can start building the sheet that Origin will create upon import. You can also link datasets together when they have similar fields. In our example, you can link “CountryCode” in the “city” dataset to “Code” in the “country” dataset by dragging “CountryCode” onto “Code.” This will link the two datasets together so that they appear as one sheet upon import. Check the boxes in the “city” database for name and population. Then check the boxes in the “country” database for Continent and GNP. Click on the import button. This will create a single datasheet showing every city in the database
There are many possibilities for how you can view the available data. For instance, lets say you wanted to view the data by city, but you only wanted to view large cities in countries with high GDP per capita. Let’s say you were only interested in viewing the country and city name.
First link CountryCode with Code so that operations performed on the country dataset will affect the city dataset. Now check “Name” in both datasets and “Population” in the “Country” dataset. Notice that they appear below in the “Expression” field. Since we don’t want to display the city’s population, we can uncheck the “Output” box. If we want to display only those cities with more than one million residents, we can type “> 1E6” in the “Criteria” field.
You can also create your own expressions. Since the GDP is expressed in millions in this dataset, we can add the expression “country.GNP * 1E6 / country.Population”. By adding “> 20000” to the “Criteria” field, we are instructing Origin to import only those entries in which are associated with a country in which the GDP per capita is greater than $20,000. You do not need to include this expression in your output.
You can also sort your output by whichever expressions you like. By choosing “Sort Type” Descending for city.Population as well as our GDP per capita expression and selecting a Sort Order in the field to the right, you can sort the worksheet by GDP per capita and then by size of city. You can check your progress at any time with the preview field or by importing the data and hitting the "-" button at the top of the import dialog.