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.
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: 18.104.22.168 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:
- Go to the Data menu and select Connect to Database: New.
- When asked if there is a Connection String, click No.
- 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.
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.
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.