Contents |
Create/Edit/Remove/Load Query
X-Function not designed for Auto GetN Dialog.
| Display Name | Variable Name | I/O and Type | Default Value | Description |
|---|---|---|---|---|
| Command Options | execute |
Input int | | Specify the database operation performed to the worksheet.
Option list:
|
| 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 | | 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 | | Specify the way to import data.
Option list:
|
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).
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
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
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:
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.
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;