3.7.5.103 Wks.DBWks-DB-obj
Object, DatabaseDatabase 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.
PropertiesDatabase Property
| 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.
|
MethodsColumn Method
Layer method
| 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$);
}
|