2.3.1 dbEdit


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";

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 Query Builder 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.
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 Query Builder dialog (File: Database Import: New) or remove/load/edit a database query.

When connecting a database, the Query Builder typically creates two kind of files, .ODQ and .ODS files. After saving these files in the Query Builder, you can read them by any text reader (like Windows Notepad).

ODS file

The Data Source file. The .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 Query: Data Source: Save menu in Query Builder after connecting a database. 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 Query: Save menu in Query Builder after connecting a database. 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 ODS or ODQ file contain the connection string in the [DataSource] section. And you can also find this string in the Query Builder dialog:

ADO Connect String.png

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 Query Builder to construct a connection string, then copy and use this string in you code.

Examples

This example achieve data from an Origin built-in sample database (MS Access). Note that when you connect your database, please use the Query Builder dialog to construct your connection string.

// Construct the connection string. 
// Please use the Query Builder dialog to construct your connection string
string strdb$ = system.path.program$ +
		"Samples\Import and Export\stars.mdb";

string strConn$="Provider=Microsoft.Jet.OLEDB.4.0;
		Data Source=%(strdb$); User ID=; Password=;";

// The SQL
string strSQL$="SELECT Stars.Index, Stars.Name, Stars.LightYears, Stars.Magnitude
		FROM Stars
		WHERE Stars.LightYears<=100
		ORDER BY Stars.Magnitude, Stars.LightYears";

// Connect database and submit the SQL
dbEdit change conn:=strConn$ sql:=strSQL$;
// Import data
dbImport;
// Disconnect the database
dbEdit remove;

Related X-Functions

dbImport, dbInfo, dbPreview


Keywords:database, query