database import settings and information can be accessed and changed using the properties in the table below.
Database importing to a worksheet is handled as object in Origin. TheProperty | 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. |
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. |
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$); }