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.
    ImportDataDatabase 1.png
  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=Origin2023;

    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=Origin2023;
  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.
    Db LT Script1.png
  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.
    ImportDB for LT sub company.png
  8. Click the Db editor labtalk.png button to preview the SQL query string with LabTalk variable substituted in the SQL Editor box.
    ImportDB for LT sub company SQL.png
  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.
    ImportDB for LT sub company1 imported.png

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.
    Axis auto rescale.png
  4. Click close upper edge of white space on graph and choose Add Page title mini toolbar button.
    Add page title mt.png
  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.
    Add page title link to substitution.png
  7. The long name of column B will be used as page title. We will get this final graph.
    ImportDB for LT sub colum graph1.png

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 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 to
    string company$ = "Riding Cycles";
    ImportDB for LT sub company2.png
  3. Click OK to go back to SQL Editor. Click OK to import to workbook. Graph will auto update.
    ImportDB for LT sub colum graph2.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 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$
    }
    ImportDB for LT sub global.png
  3. Click OK button to close the SQL Editor dialog.
  4. Make sure the worksheet with database is the active window.
  5. Select Window: Script Window menu to open Script Window.
  6. 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
  7. The data in worksheet and graph are updated.
    ImportDB for LT sub company3.png

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 Button db Import Data.png 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'