DB
DB-obj
LabTalk Object Type:
- External
Object
Origin provides a DLL named odbc9.dll/odbc9_64.dll that allows you to
create an ODBC command object whose properties and methods connect Origin
to Microsoft's ODBC interface. You can create a command object named db
using the following LabTalk script:
dll -a db odbc; only available
for 32-bit Origin.
The command object db can now be used as an Origin object with
the following properties and methods.
Properties:
Property |
Access |
Description |
db.autosetcol |
Read/write
numeric |
This option automatically determines whether the worksheet
column type and name can be changed or not. The value of this
property can be 1 for true, and 0 for false. This affects each
column in the worksheet, and if enabled, the db.read()
method will set the column type as well as the column name. If
disabled, the read operation will return an error if the column
type in the worksheet is not consistent with the column type in
the database. (Used by the db.read() method only.) |
db.c1 |
Read/write
numeric |
Column number of wksname to send data to. |
db.dbc1 |
Read/write
numeric |
Column number in the database table to begin reading. By
default, the value of this parameter is 1 (see db.read.mode). |
db.dbc2 |
Read/write
numeric |
Column number in the database table to end reading (see
db.read.mode). |
db.dbcolcount |
Read only
numeric |
The total number of columns in a particular table from a
particular data source. |
db.dbcolname$ |
Read/write
string |
Name of the column in the database table (see db.readcol). |
db.dbcolnamelist$ |
Read/write
string |
All the column names in a particular database table. |
db.dbpath$ |
Read/write
string |
Drive and path to data source. |
db.dbpwd$ |
Read/write
string |
Database password for the user ID. |
db.dbr1 |
Read/write
numeric |
Row number in the database table to begin reading. By default,
the value of this parameter is 0 : read the data in the database
table from row 1 (see db.readrow). |
db.dbr2 |
Read/write
numeric |
Row number in the database table to end reading. If not
specified, db.readrow will return an error message (see
db.readrow). |
db.dbsource$ |
Read/write
string |
Name of the data source. This data source name should first
be defined by using the ODBC Administrator. If the name is empty
or there is no source with this name, an error message is sent
to the users. |
db.dbsourcecount |
Read only
numeric |
The total number of data sources in the user's computer. |
db.dbsourcelist$ |
Read only
string |
All the data source names in the user's computer. This means
all the data source names in the ODBC.INI file. |
db.dbsql$ |
Read/write
string |
SQL SELECT statement. |
db.dbtable$ |
Read/write
string |
Name of the table in the database. If the table name is
empty or there is no such table in the database, an error message
is sent to the users. |
db.dbtablecount |
Read only
numeric |
The total number of the tables in a particular data source. |
db.dbtablelist$ |
Read only
string |
All the table names in a particular data source. |
db.dbuid$ |
Read/write
string |
Database user ID. |
db.getcolinfo.name$ |
Read only
string |
The column name in a table. |
db.getcolinfo.type |
Read only
numeric |
The column type: 0 for numeric, 1 for text. |
db.r1 |
Read/write
numeric |
Row number of wksname to send data to. |
db.read.autoaddcol |
Read/write
numeric |
This option will determine whether the worksheet column
can be added or not. The value of this property can be 1 for true,
0 for false. If enabled, the db.read() method will automatically
add columns and the new columns will automatically set as the
correct type and name corresponding to the database table column
type and name. If disabled, and the total number of columns in
the table is larger than in the worksheet, the read operation
will return an error. (Used by the db.read() method only.) |
db.read.mode |
Read/write
numeric |
This property is only for the db.read() method. Define
mode as follows: mode = 0 default, read the entire database;
mode = 1, read a range of the database as specified by
dbc1 and dbc2 (see db.read(mode)). |
db.sqlcomment$ |
Read/write
string |
Holds any comments or description. |
db.tabletype$ |
Read/write
string |
"Table" or "View". |
db.wksname$ |
Read/write
string |
Name of the worksheet to receive the data. |
Methods:
Method |
Description |
db.addsource() |
Add the data source through the ODBC administrator. Open
a dialog box from which the user can add one data source. |
db.close() |
Close a database which has been opened (see db.open()). |
db.editsql() |
Open the ODBC Dialog dialog box with the Data Base and Select
Case tabs. If the data source is valid it should be listed in
the Data Source list box. If the table is valid for the source
it should be listed in the Table list box. Upon user confirmation,
convert the spreadsheet display as well as the table name and
database name into an SQL string to store in db.dbsql$. |
db.getcolinfo(colNumber) |
Get one column's information and set the information to
db.getcolinfo.name$ and db.getcolinfo.type. Input
the column number. |
db.getcolslist() |
Set db.dbcolcount and fill db.dbcolnamelist$. |
db.getsourcelist() |
Set db.dbsourcecount and fill db.dbsourcelist$. |
db.gettablelist() |
Set db.dbtablecount and fill db.dbtablelist$.
(This closes the database; if you want to get table information,
reopen the database.) |
db.loadsql([#]) |
Gets the comment, data source name, table name, and SQL
SELECT statement from an SQL text object : SQL1, SQL2, etc.
The first line of an SQL text object is a comment. The second
line is the name of the ODBC data source. The third line is the
name of the table. The fourth line is either TABLE or VIEW. The
fifth and subsequent lines are for the SELECT statement. The number
argument is optional - the default is 1. Return 0 for OK (i.e.
the data source is valid, the table exists within the source and
the SQL syntax is proper for this combination) and 1 if an error
occurs. |
db.open() |
Open a database as specified in db.dbsource$. |
db.read(mode) |
The argument 'mode' can be set with db.read.mode
if it is not specified in db.read() as an argument. When
specified as an argument, db.read.mode is ignored and uses
the specified argument instead. (To set the read column range
of a database table, see db.read.mode.) |
db.readcol() |
Read the data in the database for the specified column name.
If the column name is empty or the column name is not correct,
return an error message. (See db.readrow() for the db.autosetcol
option - db.dbc1 and db.dbc2 should be 0). |
db.readrow() |
Read the data in the table from db.dbr1 to db.dbr2.
If db.dbr2 is not specified, return an error message. Whether
the column type and name in the worksheet can be set or not is
determined by the db.autosetcol option. |
db.reset() |
Resets the db object properties. |
db.savesql([#]) |
Sets the db.comment$, data source name, table name,
and db.dbsql$ to an SQL text object : SQL1, SQL2,
etc. The first line of an SQL text object is a comment. The second
line is the name of the ODBC data source. The third line is the
name of the table. The number argument is optional - the default
is 1. Return 0 for OK and 1 if an error occurs. |
Examples:
You can use LabTalk script along with SQL commands to import data from
a data source without selecting the Data: Import from File:ODBC menu command.
SQL (Structured Query Language) is a standardized language that has evolved
for accessing data from a variety of databases. In this section, we will
describe only a small subset of SQL for the purposes of importing data
from a database into an Origin worksheet. The basic SQL commands and syntax
that we need to understand are shown in the following example of an SQL
query statement:
SELECT column list FROM table name
WHERE "columnname1">20 AND "columnname1" <= 40
AND "columnname2" = String
OR "columnname3" <> 20 ###
ORDER BY "columnname1" DESC
For example, suppose you have a data source named ProjectData with a
table named SalesTable and columns named State, City, Sales, and Balance.
Then the following SQL query:
SELECT "State", "Sales", "Balance" FROM SalesTable
WHERE "Sales" > 20 AND "Sales" <= 40 AND "STATE" = "Texas"
OR "Balance" <> 0
ORDER BY "Sales" DESC
selects data from columns State, Sales and Balance (but not City) from
the table named SalesTable satisfying Sales between 20 and 40 (including
40), State equal to Texas and Balance not equal to 0.
Finally, order the selection from the largest Sales value to the smallest
(DESC means descending order).
- To select all the columns of the table, use an asterisk * as in
SELECT * FROM SalesTable.
- To select ascending order, omit the word DESC.
- To select the original order in the data table, omit the entire
ORDER BY command.
Once a data source, data table, and an SQL selection are decided upon,
you can use LabTalk commands to import the selected data. The following
example illustrates this procedure.
In Step 1 of the example code, a Label Object named sql2 is made whose
label message is the following:
nwind_mdb
Order Details
SELECT * FROM "Order Details"
WHERE "UnitPrice" > 20 AND "UnitPrice" < 30 AND
"Quantity" < 30
ORDER BY "ProductID" DESC
The first two lines are the name of the data source and the name of
the data table, respectively. Use Origin's Data Base tab to assign or
determine data sources and tables. The remaining lines comprise the SQL
query statement, which can be any number of lines. You can use label names
sql1, sql2, etc. but not arbitrary names. This is because the method loadsql(#)
loads the label message of the label named sql#. Also, avoid sql1 because
sql1 is the default label name used by the Select Case tab to store your
selection and query. Instead, use sql2 etc. when writing script.
In Step 2 of the example code, an ODBC command object named db is created
using the LabTalk command:
dll -a db odbc;
This allows the db object to access the functions in odbc9.dll/odbc9_64.dll
as properties and methods.
In Step 3, the db object's methods and properties are used to
import data according to the information in the label message sql2.
Step 4 writes some information to the Script window.
Example LabTalk Script Code to Import a Data Source Table or View into
an Origin Worksheet with a specified SQL Statement:
// LabTalk script begins here:
// Step 1. Create a non-visible Label object called SQL2
// with the message: (substitute your query)
// SQL2
// nwind_mdb
// Order Details
// SELECT * FROM "Order Details"
// WHERE "UnitPrice" > 20 AND "UnitPrice" < 30 AND
// "Quantity" < 30
// ORDER BY "ProductID" DESC
// label -n name message. creates a Label object.
label -n SQL2 //SQL2 Statement
nwind_mdb
Order Details
TABLE
SELECT * FROM "Order Details"
WHERE "UnitPrice" > 20 AND "UnitPrice" < 30 AND "Quantity" < 30
ORDER BY "ProductID" DESC
;
SQL2.show = 0; // Clear Visible check box in Label Control box.
// Step 2. Create an odbc9.dll/odbc9_64.dll command object called db.
dll -a db odbc;
db.loadsql(2); // Load the message of sql2 label.
// Step 3. Now import into specified worksheet
repeat wks.ncols {del col(1)}; // empty active worksheet
db.wksname$ = %H; // %H is active wks
db.open();
db.autosetcol = 1;
db.read.autoaddcol = 1;
db.read(0);
db.close();
// Step 4. Show SQL query in Script window for verification.
type -a Data has been imported according to the SQL query:;
%A = sql2.text$;
%A = ;
// sql2.show = 1. make visible
type -a Data Source, Table/View and SQL Query:;
%A = sql2.text$;
%A = ;
type -a Origin Worksheet Name:;
%B = db.wksname$;
%B = ;
// End of LabTalk Script
|