3.7.5.7 DB

LabTalk Object Type:

External Object

This object is obsolete and exists only for backward compatibility. Please see the dbEdit, dbImport, dbInfo and dbPreview X-Functions.

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
English | Deutsch | 日本語