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.
This tutorial will show you how to:
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;
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
string company$ = "Professional Sales and Service";
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.
string company$ = "Riding Cycles";
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.
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$ }
string thecompany$="Action Bicycle Specialists"; //define global string variable thecompany$ dbimport; //import data from database
Note: