9.3.4 Update Database Importing By LabTalk Substitution

Summary

The database used in this tutorial has been set up on Microsoft Azure.

This tutorial shows how to import data from database into Origin worksheet with defined LabTalk variable in SQL and plot a column graph from the imported data. Then modify the LabTalk variable and reimport database.

The procedure is based on Origin 2023b.

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

Import Data from Database

1. Start a new project. Choose Data: Connect to Database: New... menu or click clicking the Open SQL Editor button on the Database Access toolbar.

   

2. Choose Connection ctring radio button and click OK. Paste the Connection String below.

   Driver={SQL Server}; Server=olab.DATABASE.windows.net; Port=1433; 
   DATABASE=sample1; Uid=Olabts; Pwd=Origin@2024;

   If you are using ODBC Drive 18 for SQL Server, use

   Driver={ODBC Driver 18 FOR SQL Server}; Server=olab.DATABASE.windows.net; Port=1433; 
   DATABASE=sample1; Uid=Olabts; Pwd=Origin@2024;

3. Click the OK button to connection to the database.
4. Copy and paste the following query into the SQL Editor text box. The query shows total sale of each product by specified company name.

   SELECT SalesLT.ProductCategory.Name AS ProductName, 
   SUM(SalesLT.SalesOrderDetail.LineTotal) AS '%(company$)'
   FROM SalesLT.SalesOrderDetail 
   INNER JOIN SalesLT.Product
   ON SalesLT.Product.ProductID=SalesLT.SalesOrderDetail.ProductID
   INNER JOIN SalesLT.SalesOrderHeader 
   ON SalesLT.SalesOrderHeader.SalesOrderID =SalesLT.SalesOrderDetail.SalesOrderID 
   INNER JOIN SalesLT.ProductCategory 
   ON SalesLT.Product.ProductCategoryID  = SalesLT.ProductCategory.ProductCategoryID 
   INNER JOIN SalesLT.Customer 
   ON SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID 
   WHERE SalesLT.Customer.CompanyName LIKE '%(company$)'
   GROUP BY SalesLT.ProductCategory.Name

5. To add a Labtalk string variable company, select Query: LabTalk... to open the LabTalk Support Setting dialog.

   

6. Check the Enable LabTalk (%,$) substitution checkbox.
7. Type the script below to define a LabTalk string variable company.

   string company$ = "Professional Sales and Service";

   in the Before Query Script box. Click OK.
   

   

8. Click the button to preview the SQL query string with LabTalk variable substituted in the SQL Editor box.
   

   

9. Click the OK button to import data to workbook. By default the SQL is saved together with worksheet.
10. The icon on the top left of the workbook indicates the sheet contains an SQL query.

    

Plot a Column Graph

1. Highlight column B and plot a column graph.
2. Turn on frame and grid. Turn off ticks and remove axis title. Rotate tick labels.
3. Click in white area within layer frame to select the layer and click Auto Rescale mini toolbar button so axis will auto rescale if new data comes in.

   

4. Click close upper edge of white space on graph and choose Add Page title mini toolbar button.

   

5. Enter %(1,@LA). This is the substitution to show 1st plot's column long name.
6. Click on %(1, @LA) and click the Link to Substitution mini toolbar button to turn it on.

   

7. The long name of column B will be used as page title. We will get this final graph.

   

Update Database Importing By LabTalk Substitution

There may be multiple places in SQL with company info. The benefit of using LabTalk variable is that user only needs to change variable in LabTalk Support Settings dialog. All places with such variable will be substituted on execution.

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 to

   string company$ = "Riding Cycles";

   

3. Click OK to go back to SQL Editor. Click OK to import to workbook. Graph will auto update.

   

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 company$ = "";  // For the first company substitution
   
   if(exist(thecompany$, 18) == 18)  // if thecompany$ exists
   {
   	company$ = thecompany$;  // if yes, assign to company$
   }
   else
   {
   	company$ = "Professional Sales and Service";  // if no, use "Professional Sales and Service" as company$
   }

   

Click OK button to close the SQL Editor dialog.
3. Make sure the worksheet with database is the active window.
4. Select Window: Script Window menu to open Script Window.
5. Paste the following into Script window. Highlight all and pressENTER keyboard to run the commands.

   string thecompany$="Action Bicycle Specialists"; //define global string variable thecompany$
   dbimport; //import data from database

6. The data in worksheet and graph are updated.

   

Note:

1. "global" means the LabTalk variables can be "seen" and used by SQL Editor for the substitutions. Variables created without declaration (only allowed for types double, string, and dataset), e.g. e.g. name$="Smith" become Project variables and are saved with the Origin Project file.
2. The last dbimport LabTalk command is the same as clicking the Import data button on Database Access toolbar.
3. Use string company$ = "%%Supplies"; to find companies ending with Supplies. After substitution, the SQL will be like WHERE SalesLT.Customer.CompanyName LIKE '%Supplies'