Open SQL Editor dialog
X-Function not designed for Auto GetN Dialog.
Command Line Usage
- dbEdit execute:=change;
- dbEdit execute:=load fname:="%Ystars.ods" iw:=[book2]sheet1!;
- dbEdit sql:="Select Stars.Name, Stars.LightYears From Stars";
X-Function Execution Options
Please refer to the page for additional option switches when accessing the x-function from script
|| Specify the database operation performed to the worksheet.
- change:Create/Modify Query
- Open the SQL Editor dialog to create or modify the query.
- askrem:Remove Query with User Confirmation
- Remove query from worksheet with user's confirmation.
- load:Load Query File
- Load a query (.ODQ or .ODS) file to the worksheet. The name of the file should be specified with the fname variable.
- remove:Remove Query
- Remove the query from the worksheet.
- new:Create New Connection
- Add a database connection icon to the active worksheet and open the SQL Editor with "Data Connector" button configuration.
- wks:Connector Mode
- Edit an existing database query in the active worksheet. SQL Editor will have "Data Connector" button configuration.
|Input ODQ or ODS file path
|| When the execute variable is Load, you can use this variable to specify the path of the .odq or .ods file to be loaded.
|| Specify the worksheet to operate on.
|New SQL string
|| Specify a new SQL string to write into the current query in the worksheet.
|New Connection string
|| Specify a new connection string to write into the current query in the worksheet.
|| Specify the way to import data.
- ado:Use ADO Object to Import Data
- Use the ADO object to import data.
- odbc:Use ODBC to Import Data
- Use ODBC to import data.
This tool opens the SQL Editor dialog to create a database query or remove/load/edit a database query.
Note: ODS and ODQ files discussed below are plain text files that can be viewed and edited in NotePad.
A Data Source file. An.ODS file contains the connection string that allows Origin to open a connection to database. The file contains information like the name of the server, where the database is, the user ID and password needed to connect to the server, and so on.
You can create an .ODS file from the File: Save Connection As... menu in SQL Editor. Below is an example for SQL Server database:
Provider=SQLOLEDB.1;Password=abc123;Persist Security Info=True;User ID=tester;Initial Catalog=Northwind;Data Source=MYSQLSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYSQLSERVER;Use Encryption for Data=False;Tag with column collation when possible=False
The Data Query file. The .ODQ file contains both the connection string and the SQL query used to retrieve information from the database.
You can create an .ODQ file from the File: Save Connection and Query As... menu in SQL Editor. Below is an example for Oracle database:
Provider=OraOLEDB.Oracle.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=orcl;Extended Properties=""
Select SCOTT.DEPT.DNAME, SCOTT.DEPT.LOC, SCOTT.EMP.ENAME, SCOTT.EMP.JOB, SCOTT.EMP.SAL
From SCOTT.DEPT Inner Join SCOTT.EMP On SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO
The two examples above show that either an ODS or ODQ file contains the connection string in the [DataSource] section. And you can also find this string in the the SQL Editor by selecting File: Show Connection String or File: Edit Connection String....
When performing query by Script (or Origin C), the key is to use this connection string to setup the database connection, and then submit a query. So, before querying any data, you should use the SQL Editor to get a connection string from a manually created connection/query, then copy and use this string in your code.
dbImport, dbInfo, dbPreview