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

1. Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.

   

2. 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

3. Click the Test button to test if the connection is fine. If fine, click the OK button to connection to the database.
4. 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

   

5. 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.

6. Just click the last button on the toolbar, we can see the substitutions.

   

7. 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.
8. 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.

   

9. 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.

1. Activate the worksheet with data from database above. Click Open SQL Editor button and SQL Editor opens again, with the saved settings.
2. 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

   

3. 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.
4. 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.

1. Let's activate worksheet again and click to open SQL Editor.
2. 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.
3. Select Window: Script Window menu to open Script Window.
4. 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

5. The data in worksheet and graph are updated.

Note:

1. Three "global" LabTalk variables are defined here. Here "global" means the LabTalk variables can be "seen" and used by SQL Editor for the substitutions.
2. The last dbimport LabTalk command is the same as clicking the Import data button on Database Access toolbar.