6.9.2 The SQL Editor Dialog BoxImpDatabase-SQLEditor-Dialog
Database Overview
Origin's database connectivity and import functionality has been updated. For more information, we suggest these blog posts:
The SQL Editor
The SQL Editor is a simple GUI for setting up database connections, editing SQL strings and importing database data. The dialog includes:
- Table list: List of tables in the selected database. For each table, there is a list of fields. Items may be double-clicked to add them to the SQL query being built in the SQL statement edit box.
- SQL statement edit box: Manually type in SQL here. Double-click in Table list to add items from the list to the SQL.
- Toggle preview panel visibility: Click to show or hide the preview panel.
- Preview panel: Displays preview of data when the Preview button is clicked.
- Generate preview: Click Preview to generate up to 50 rows of preview data.
- Import data: Click OK to close the SQL Editor dialog, save the query to the worksheet, and import data specified by the query into the worksheet.
| To adjust the height of top panel, toggle the Preview panel off (click the double up-arrow on the right), then drag to adjust height.
|
Dialog Settings
The File menu
Options in this menu work for establishing a database connection, and managing the connection and query files (See this blog post for an explanation of *.ODS and *.ODQ files).
New
|
Start a completely new connection with a blank SQL query. Opens the Data Link Properties dialog.
|
Open
|
Opens an existing Query file (*.ODQ file) or a Data Source file (*.ODS file).
|
Save to Active Worksheet
|
Saves connection information and SQL statement to the active worksheet. Good option if you want to save that information but don’t want to import data immediately. Use File: Close to close the dialog afterwards.
|
Save Connection As
|
Creates an *.ODS file from current connection information. If you want to include credentials/login info in the connection, do not encrypt it beforehand.
|
Show Connection String
|
Displays the connection string for current connection in the Message tab of the preview panel.
|
Edit Connection String
|
Modify some part of the current connection string. Or use it to copy connection string to clipboard.
|
Save Connection and Query As
|
Creates an *.ODQ files from current connection information and SQL statement.
|
Close
|
Closes the dialog.
|
The View menu
Text Size
|
Adjust SQL statement editor font size.
|
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 a node to add it to the right SQL statement edit box.
|
Show Table Views in Table List
|
Show or hide table views in the table list.
|
The Query menu
LabTalk
|
Opens the LabTalk Support Settings dialog. This dialog lets you define LabTalk variables, execute Labtalk script and use in LabTalk in your SQL string. The dialog options include:
- Enable LabTalk (%, $) Substitution:
Check to enable LabTalk substitution in SQL string.
- Ignore Substitution Inside Single Quotes:
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 examples below.
|
Preview Substituted String
|
Preview the actual SQL string when using LabTalk in right panel. When it's checked, the SQL statement edit box isn't editable.
|
Preview
|
Preview the SQL query results in bottom Preview tab.
|
Examples: Use LabTalk in your SQL string
Suppose 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 a WHERE clause. For example, open LabTalk Support Settings (Query: LabTalk) and enter a Before Query Script like this:
%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 resulting SQL would be:
- Enable LabTalk (%, $) Substitution -- Unchecked.
SELECT country, years, amount FROM salesrecords
WHERE country = '%A' AND years = $(YY)
- Enable LabTalk (%, $) Substitution -- Checked, Ignore Substitution Inside Single Quotes -- Unchecked.
SELECT country, years, amount FROM salesrecords
WHERE country = 'Japan' AND years = 2010
- Enable LabTalk (%, $) Substitution -- Checked, Ignore Substitution Inside Single Quotes -- Checked.
SELECT country, years, amount FROM salesrecords
WHERE country = '%A' AND years = 2010
| To use an actual '%' sign when you have unchecked Ignore Substitution Inside Single Quotes, use a double percent, '%%', instead.
|
The Settings menu
Import Data by OLE DB
|
Use OLE DB for importing data. (Enabled for both OLE DB and ODBC).
|
Import Data by ODBC
|
Use ODBC for importing data. (Only enabled for ODBC connections).
|
Auto Resize Worksheet
|
Auto resize worksheet column width to fit record size.
|
Encrypt Login Info
|
Encrypts credentials/login info such that it is no longer human-readable when displayed in Origin.
|
Buttons
Butons are provided for most frequently used menus in this dialog, including:
|