2.3.1 dbEdit


Contents

Brief Information

Open SQL Editor dialog

Additional Information

X-Function not designed for Auto GetN Dialog.

Command Line Usage

  1. dbEdit execute:=change;
  2. dbEdit execute:=load fname:="%Ystars.ods" iw:=[book2]sheet1!;
  3. 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.

Related X-Functions

dbImport, dbInfo, dbPreview

Keywords:database, query