OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis     
 
Skip Navigation Links
All BooksExpand All Books
Origin HelpExpand Origin Help
Importing and Exporting DataExpand Importing and Exporting Data
Importing Data from a DatabaseExpand Importing Data from a Database

6.8.1 The SQL Editor Dialog Box

Overview

Sqleditor.png

The SQL Editor is a simple GUI to setup database connect and edit the SQL strings and import database data. The dialog includes:

  • Menu and toolbar buttons
  • The left Table List panel: Lists tables, views, fields and file types of a database.
  • The right SQL Edit Box: is used to edit SQL string. (Note: This is not a database development tool. You can only include one SQL query, as complex as your database supports. The stop procedure is not supported.
  • The Preview tab: Preview query results.
  • The Message tab: Click this tab when there is an error to see messages.


Note: The dialog is resizable by drag and resize. To adjust height of top panel only, you must click the double up arrow on right side of the dialog to hide Preview and Messages tabs first.

Dialog Settings

The File menu

Options in this menu work for establishing a database connection, and managing the connection and query files (ODS and ODQ files).

New Opens the Data Link Properties dialog to setup a database connection.
Open Opens a Query file (.ODQ files) or a Data Source file (.ODS file).
Save to Active Worksheet Save SQL and connection information to the active worksheet.
Save Connection Save the database connection information as an .ODS file.
Save Connection As Save the current database connection information as another .ODS file.
Show Connection String Show the connection string on Message tab.
Edit Connection String Advanced option. You can edit the connection string directly here.

The View menu

Text Size Set the font size on SQL edit box.
Show Table List Show or hide left panel. Origin reads database metadata to construct this table. This may take time to construct. Hiding the table list can speed up connecting to a large database. Double click the node to add it to the right SQL edit box.

The Query menu

New SQL using Query Builder... Open the Query Builder dialog inside SQL Editor to construct a SQL string.

Note: This is only available in 32 bit Origin. The existing SQL string in right panel of SQL Editor will not be loaded in the opened Query Builder. It starts from scratch to reconstruct the SQL string. Click OK in Query Builder will go back to SQL Editor with updated SQL string on right panel.

LabTalk Opens the LabTalk Support Settings dialog. This dialog allows you define LabTalk variables and execute Labtalk script and use in your SQL string. The dialog options including:
  • Enable LabTalk (%, $) Substitution:
    Check to enable LabTalk substitution in SQL string.
  • Ignore Substitution Inside Single Quotation Marks::
    Check to ignore substitution inside ' '.
  • Before Query Script:
    Script that runs before applying a SQL string. This is used to define LabTalk variables, etc.

More information, please see the example below.

Preview Substituted String Preview the actual SQL string when using LabTalk in right panel. When it's checked, the SQL Edit box isn't editable.
Preview Preview the SQL query results in bottom Preview tab.
Import Import database data into active worksheet.


Example: Use LabTalk in your SQL string

Supposed the original SQL string is:

SELECT country, years, amount FROM salesrecords
WHERE country = 'USA' AND years = 2010

To change the condition, you can use LabTalk variables in WHERE clause. For example, open this LabTalk Support Settings and enter Before Query Script like:

%A = Japan; //define a string variable
int YY = 2010; //define an integer variable

And change the SQL into

SELECT country, years, amount FROM salesrecords
WHERE country = '%A' AND years = $(YY)

Then the actual SQL could be:

  1. Enable LabTalk (%, $) Substitution -- Unchecked.
    SELECT country, years, amount FROM salesrecords
    WHERE country = '%A' AND years = $(YY)
  2. Enable LabTalk (%, $) Substitution -- Checked, Ignore Substitution Inside Single Quotation Marks -- Unchecked.
    SELECT country, years, amount FROM salesrecords
    WHERE country = 'Japan' AND years = 2010
  3. Enable LabTalk (%, $) Substitution -- Checked, Ignore Substitution Inside Single Quotation Marks -- Checked.
    SELECT country, years, amount FROM salesrecords
    WHERE country = '%A' AND years = 2010

Note: How about actually want a '%' sign when uncheck Ignore Substitution Inside Single Quotation Marks? Use double percent, '%%', instead.

The Settings menu

Import Data by ADO ADO Stands for ActiveX Data Objects, a set of COM objects for accessing database. By default, Origin connects database through ADO.
Import Data by ODBC ODBC: Stands for Open Database Connectivity. An application program interface for accessing database. You should connect database by ODBC before you can check this.
Overwrite Long Name Overwrite the worksheet long name by database fields' name.
Auto Resize Worksheet Auto resize worksheet column width to fit record size.

Buttons

Butons are provided for most frequently used menus in this dialog, including:

Open Db editor open.png File: Open... menu Open an .ODQ or .ODS file.
Save Db editor save.png File: Save to Active Worksheet/Save Connection and Query menu. If the dialog is opened by File: New... menu and not saved as .ODQ file yet, SQL Editor dialog title will show Untitled.ODQ. Then Save button will save Connection and Query to active worksheet only. If the current connection or query is loaded from .ODQ file or has been saved as .ODQ file, the SQL Editor dialog title will show the file name. Then Save button will save to the corresponding file.

Note: Anytime you click Import button, the connect and Query will be automatically saved to active worksheet. If you open the SQL Editor again, Origin actually loads the query from the sheet and show the book and sheet name [Book1]Sheet1 in dialog title. If you make change again, and click Save, it will save to active worksheet.

New Db editor connect.png File: New... menu Opens the Data Link Properties dialog to create a new database connection
Create new SQL statements using Graphical Query Builder ToolDb editor builder.png Query: New SQL using Query Builder... menu Open the Query Builder dialog to reconstruct a SQL string.
Preview Db editor preview.png Query: Preview menu Preview the SQL query results.
Import Db editor import.png Query: Import menu Import database data into worksheet.
Preview the query string after substituting all LabTalk variables Db editor labtalk.png Query: Preview Substituted String menu Preview the actural SQL string when LabTalk variables are defined and enable LabTalk (%,$) Substitution is checked in Query: LabTalk... dialog.
 

© OriginLab Corporation. All rights reserved.