9.3.4 Update Database Importing By LabTalk Substitution
Summary
 | This tutorial is for illustrative purposes only. The connection string shown in the tutorial does not connect to an installed database. If you wish to set up an AdventureWorks database on your own server so that you can work through the process discussed in the tutorial, see this GitHub page.
|
This tutorial shows how to import data from database into Origin worksheet by using SQL Editor with LabTalk substitution. And then make a column plot for the imported data. Then update the worksheet data and the plot by changing the defined LabTalk variables.
Minimum Origin Version Required: Origin 8.5.1 SR0
What you will learn
This tutorial will show you how to:
- Import data using SQL Editor.
- Use LabTalk substitution in SQL statement.
- Make column plot.
- Update database importing by LabTalk substitution.
Steps
Suppose we have already set up a SQL server named AdventureWorks2008 on a server machine noho.
Import Data from Database and Make Column Plot
- Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.

- Select File: Edit Connection String... menu and put the connection string below to the text box.
Provider=SQLOLEDB.1;
Password=labtalk2015;
Persist Security Info=TRUE;
USER ID=CONNECT;
Initial Catalog=AdventureWorks2008;
DATA SOURCE=noho
- Click the Test button to test if the connection is fine. If fine, click the OK button to connection to the database.
- Select Query: LabTalk... menu in SQL Editor to open LabTalk Support Settings dialog. In this dialog, check the Enable LabTalk (%,$) Substitution checkbox, and then put the following LabTalk script to Before Query Script textbox.
string myStartDate$ = "1/1/2003"; // For the first date substitution
string myEndDate$ = "12/31/2003"; // For the second date substitution
string myStrName$ = "LineTotalFor2003"; // For the name

- Click OK to go back SQL Editor. In the right text box, put the following SQL statements.
SELECT Production.ProductCategory.Name, LINETOALANDNAMEYEAR.%(myStrName$) FROM
(SELECT SUM(SALEANDPRODUCTYEAR.LineTotal) AS %(myStrName$), Production.ProductSubcategory.ProductCategoryID
FROM
(SELECT SALEINFOYEAR.LineTotal, PRODUCTINFOYEAR.ProductSubcategoryID
FROM
(SELECT Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderDetail.LineTotal, Sales.SalesOrderDetail.ProductID
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail
ON Sales.SalesOrderHeader.SalesOrderID=Sales.SalesOrderDetail.SalesOrderID
WHERE Sales.SalesOrderHeader.OrderDate BETWEEN '%(myStartDate$)' AND '%(myEndDate$)') AS SALEINFOYEAR
INNER JOIN
(SELECT Production.Product.ProductID, Production.Product.ProductSubcategoryID
FROM Production.Product) AS PRODUCTINFOYEAR
ON SALEINFOYEAR.ProductID=PRODUCTINFOYEAR.ProductID) AS SALEANDPRODUCTYEAR
INNER JOIN Production.ProductSubcategory
ON SALEANDPRODUCTYEAR.ProductSubcategoryID=Production.ProductSubcategory.ProductSubcategoryID
GROUP BY Production.ProductSubcategory.ProductCategoryID) AS LINETOALANDNAMEYEAR
INNER JOIN Production.ProductCategory
ON LINETOALANDNAMEYEAR.ProductCategoryID=Production.ProductCategory.ProductCategoryID

As we can see that there are three LabTalk variables are used in the statements.
- Just click the last button
on the toolbar, we can see the substitutions.

- Select menu File: Save to Active Worksheet to save these settings to the worksheet, and then click the Import data to worksheet button
to import the data into worksheet.
- Close SQL Editor. We can see the imported data form the image below. The Long Name for column B is "LineTotalFor2003", which is set in the LabTalk variable.

- Highlight column B from the worksheet, then select menu Plot: Column/Bar/Pie: Column to make a column plot.

Update Database Importing By LabTalk Substitution
LabTalk variables myStartDate$, myEndDate$, and myStrName$ are used to control which year's data will be imported from database. One way to import data of another year is by modifying these variable values in LabTalk Support Settings dialog.
- Activate the worksheet with data from database above. Click Open SQL Editor button
and SQL Editor opens again, with the saved settings.
- Select menu Query: LabTalk to open LabTalk Support Settings dialog. Now, change the values of these three variables: myStartDate$, myEndDate$, and myStrName$.
string myStartDate$ = "1/1/2004"; // For the first date substitution
string myEndDate$ = "12/31/2004"; // For the second date substitution
string myStrName$ = "LineTotalFor2004"; // For the name

- Click OK to go back to SQL Editor. Click Save Query Settings button and then click the Import data to worksheet button
. Close SQL Editor.
- Now we can see that the data in worksheet and graph are updated.


But you will need to open the SQL Editor dialog everytime to change the variable values which is not convenient. A nicer way is to modify the LabTalk Support Settings to use global variables. Then we can modify the global variables outside SQL Editor and reimport.
- Let's activate worksheet again and click
to open SQL Editor.
- Choose Query: LabTalk... menu and modify the LabTalk Support Settings as follows.
string myStartDate$ = ""; // For the first date substitution
string myEndDate$ = ""; // For the second date substitution
string myStrName$ = ""; // For the name
if(exist(startDate$, 18) == 18) // if startDate$ exists or not
{
myStartDate$ = startDate$; // if yes, use it as myStartDate$
}
else
{
myStartDate$ = "1/1/2003"; // if no, use 1/1/2003 as myStartDate$
}
if(exist(endDate$, 18) == 18) // if endDate$ exists or not
{
myEndDate$ = endDate$; // if yes, use it as myEndDate$
}
else
{
myEndDate$ = "12/31/2003"; // if no, use 12/31/2003
}
if(exist(strName$, 18) == 18) // if strName$ exists
{
myStrName$ = strName$; // if yes, use it as strName$
}
else
{
myStrName$ = "LineTotalFor2003"; // if not, useLineTotalFor2003 as strName$
}

Click Save Query Settings button in SQL Editor and close it.
- Select Window: Script Window menu to open Script Window.
- Run the following script by pasting them into Script window, highlighting them and then pressing ENTER keyboard.
string startDate$ = "1/1/2003"; //define startDate$ string varaible
string endDate$ = "12/31/2004"; //define endDate$ string variable
string strName$ = "LineTotalFor2003and2004"; //define strName string variable
dbimport; //import data from database
- The data in worksheet and graph are updated.
Note:
- Three "global" LabTalk variables are defined here. Here "global" means the LabTalk variables can be "seen" and used by SQL Editor for the substitutions.
- The last dbimport LabTalk command is the same as clicking the Import data button
on Database Access toolbar.
|