Wks.DB

Database importing to a worksheet is handled as object in Origin. The database import settings and information can be accessed and changed using the properties in the table below.

Properties

Property Type Access Description
wks.db.sql$ string Read/write Specify a new SQL string to write into the current query in the worksheet.
wks.db.conn$ string Read/write Specify a new connection string to write into the current query in the worksheet.
wks.db.odbc int Read/write Specify whether to use ODBC to import data: 1 = Use ODBC, 0 = Use ADO object.

Methods

Method Description
wks.db.test(n, strErr$) Test connection and return found records. n is the maximum number of records to get, return number of records. Return negative code if error, strErr$ contains the error message.
wks.db.run(strErr$) If Select, then import database, otherwise execute to database, strErr$ may have error message. See EX1 before for example.
wks.DB.export(filepath, optn) Export to database filepath. If filepath is not specified, then it will do update using current connection string. optn determines whether to skip hidden columns/rows. Default(0) means do not ignore any hidden columns and rows, 1 = skip hidden colum, 2 = skip hidden row.

Examples

EX1 Insert Record

wks.db.conn$="Driver={SQLite3 ODBC Driver};Database=C:\Users\dev\Downloads\hawaii.sqlite";
wks.db.sql$="select * from station where name like 'bb'";
if(wks.db.test(1)==0) {
        wks.db.sql$="INSERT INTO station VALUES('aa','bb', 21.4,-157.9,152.7)";
        wks.db.run();
        type "name = bb inserted";
}
wks.db.sql$="select * from station";
wks.db.run();

EX2 Remove Record

wks.db.conn$="Driver={SQLite3 ODBC Driver};Database=C:\Users\dev\Downloads\hawaii.sqlite";
string ssql$="select * from station";
wks.db.sql$=ssql$;
wks.db.odbc=1;
wks.db.run();
wks.db.sql$="select * from station where name like 'bb'";
i=wks.db.test(100);
type "name = bb has $(i) records";
wks.db.sql$="delete from station where name like 'bb'";
wks.db.run();
wks.db.sql$="select * from station where name like 'bb'";
i=wks.db.test(100);
type "after delete, name = bb has $(i) records";

EX3 Export all sheets in book

string dbName$=page.LongName$ + ".sqlite";
string strPath$ = %Y%(dbName$);
strPath$=;
doc -e LW {
        type "Adding [%H]%(wks.Name$)";
        wks.db.export(strPath$);
}
English | Deutsch | 日本語