9.2.4 Update Database Importing By LabTalk Substitution

Summary

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.

The SQL database we use here is AdventureWorks database. For detailed information of how to attach an AdventureWorks database, please refer to CodePlex website.

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.
    ImportDataDatabase 1.png
  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
    ImportDataDatabase 21.png
  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
    ImportDataDatabase 22.png

    As we can see that there are three LabTalk variables are used in the statements.

  6. Just click the last button Button db Preview LabTalk Substitute.png on the toolbar, we can see the substitutions.
    ImportDataDatabase 23.png
  7. Select menu File: Save to Active Worksheet to save these settings to the worksheet, and then click the Import data to worksheet button Button db Import Data.png 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.
    ImportDataDatabase 24.png
  9. Highlight column B from the worksheet, then select menu Plot: Column/Bar/Pie: Column to make a column plot.
    ImportDataDatabase 25.png

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 Button db Create.png 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
    ImportDataDatabase 26.png
  3. Click OK to go back to SQL Editor. Click Save Query Settings button and then click the Import data to worksheet button Button db Import Data.png. Close SQL Editor.
  4. Now we can see that the data in worksheet and graph are updated.
    ImportDataDatabase 27.png
    ImportDataDatabase 28.png

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 Button db Create.png 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$
    }
    ImportDataDatabase 29.png
  3. Click Save Query Settings button in SQL Editor and close it.
  4. Select Window: Script Window menu to open Script Window.
  5. 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
  6. 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 Button db Import Data.png on Database Access toolbar.