Connecting to a Database and Importing Data

はじめに

Note: This is second post in a series entitled “Database Connectivity Basics”.

In this installment in the Database Connectivity Basics series, I’ll introduce you to the basics of connecting to a database and importing data.

It references  The SQL Editor Dialog post for various terms and dialog item descriptions.

First Steps

The first step is deciding whether you will use using an ODBC connection or an OLE DB connection. For many databases, it will be an ODBC connection accessed via an ODBC Data Source you create. For Microsoft products, you generally use OLE DB and the Data Link Properties dialog.

Because ODBC connections tend to be more frequent that OLE DB, we’ll focus on ODBC in this post and use it to connect to a MySQL database. In fact, you too can connect to this database (it is live on the internet). You can download the project file from here.

The first step is to install the ODBC driver and create an ODBC Data Source. You can watch this video to learn how to do both. We’ll replace the Connection Parameters in the video with these real values:

• Data Source Name: MySQL
• TCP/IP Server: 98.118.55.12    Port: 3306
• User: olabts
• Password: origin2020
• Database: Sample1

Having created the Data Source, we can now launch Origin and connect to the database.

Making the Origin Connection

So we have our ODBC Data Source and have launched Origin. Now let’s connect as worksheet to the OBDC data source and import some data.

With a workbook active, we’ll follow these steps:

1. Go to the Data menu and select Connect to Database: New.
2. When asked if there is a Connection String, click No.
3. The Data Link Properties dialog will open and we’ll specify the settings illustrated below and then click OK.

At this point, The SQL Editor dialog will open.

Importing data

With the SQL Editor  dialog open, paste the following SQL Statement into the edit box:

SELECT orders.orderNumber, orders.orderDate, orders.shippedDate,
orders.status, customers.customerName,
COUNT(orderdetails.orderNumber) as itemCount
FROM orders
INNER JOIN customers
ON customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails
ON  orderdetails.orderNumber = orders.orderNumber
GROUP BY orderdetails.orderNumber
ORDER BY orders.orderDate

And then click the Preview button to see a preview of the data you will import:

Next, simply clock the OK button to import the data.

That’s  all it take to import data from a database.

まとめ

In this post in the series, We learned how to connect Origin to a MySQL database and import data. In the next part we will go over some things you should know about Origin and Databases.