2.3.1 dbEdit

1 Brief Information
2 Additional Information
3 Command Line Usage
4 X-Function Execution Options
5 Variables
6 Description
7 Related X-Functions

Brief Information

Open SQL Editor dialog

Additional Information

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

Variables

Display Name	Variable Name	I/O and Type	Default Value	Description
Command Options	execute	Input int	0	Specify the database operation performed to the worksheet. Option list: 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	fname	Input string	""	When the execute variable is Load, you can use this variable to specify the path of the .odq or .ods file to be loaded.
Worksheet	iw	Input Worksheet	<active>	Specify the worksheet to operate on.
New SQL string	sql	Input string	""	Specify a new SQL string to write into the current query in the worksheet.
New Connection string	connect	Input string	""	Specify a new connection string to write into the current query in the worksheet.
Import Option	option	Input int	0	Specify the way to import data. Option list: 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.

Description

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.

ODS file

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:

[DataSource]
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

ODQ file

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:

[DataSource]
Provider=OraOLEDB.Oracle.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=orcl;Extended Properties=""

[SQL]
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

[UseODBC]
ADO

Connection String

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.