wks.db object for Database Import


Version: 2023b

Type: Features

Category: Programming

Subcategory: LabTalk

Jira: ORG-26673


Syntax

  • wks.db.sql$, wks.db.conn$, wks.db.odbc

  • wks.db.test(n, strErr$), n = max 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, otherwise execute to db, strErr$ may have error message

  • wks.DB.export(filepath, optn), if filepath not specified, then it will do update using current connection string. optn default=0, bit1 = skip colum, bit2 = skip row

Example

  • 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();
  • 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";
  • 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$);
    }