CabMasterPro User Guide
These functions deal with accessing data stored in databases outside CabMasterPro - see tutorial on Database Functions for a guide to these functions.
Performance, whether fast or slow, will depend on what you try to do in middleware. Click here to see notes...
CabMasterPro internally keeps the database open from the time of the first reference to it, then detects subsequent references to the same database and reuses the open connection for efficiency.
Internal database indexing and caching should look after you better than could be done with external guesses at what to cache, so CabMasterPro does not attempt more caching of the data itself.
In practice, queries are only evaluated when the data in your drawing changes, so the performance can be quite snappy. We suggest using the CabMasterPro Debug function to analyse what's happening if performance appears to be slow. This will give you debug tracing output whenever a function is evaluated, and you can see what triggers it.
All the database middleware functions support databases in SQLite format, in addition to Microsoft Access (mdb and accdb) plus other database formats where there are OLEDB Windows drivers. SQLite is a simple and very widely used format, described here: https://sqlite.org
It is the most used database format in the world, built into all mobile phones and supported cross platform on most computers. It is public domain and free.
| DBExecute | |
|---|---|
| Runs the specified query on the database. | |
| Syntax | DBExecute (dbpath, sql) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed. |
| Outputs | The number of affected rows. |
| Examples |
DBExecute("abc", "UPDATE TABLE1 SET CODE=""Pink"" WHERE CODE=""Red""") would change all codes which are Red to Pink and return the number of fields changed. This uses the database file “abc.mdb” in the TableFolder. |
| DBLookup | |
|---|---|
| Returns the value of a single field in the first result of the query. | |
| Syntax | DBLookup (dbpath, sql, param, field) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed, must have a parameter represented by a '?' Param: The value of the parameter field from the SQL query Field: The database field to return the value of. |
| Outputs | The value of the field specified in the resulting SQL result. |
| Notes |
Queries should usually be designed to return just one record, but if more than one record is returned, the first matching record will be used. |
| Examples | DBLookup("abc", "SELECT CODE,COST FROM TABLE1 WHERE CODE=?","Red","COST") uses the database file “abc.mdb” in the TableFolder and will return the cost field in the row of Table1 in abc.mdb who's code is "Red". |
| See Also | DBLookupRow, DBLookupRowValues |
| DBLookupCol | |
|---|---|
| Returns the chosen column of the results of the query as an array. | |
| Syntax | DBLookupCol (dbpath, sql, field) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed Field: The column header of the column of results desired. |
| Outputs | The specified column of the SQL result in an array. |
| Examples |
DBLookupCol("abc", "SELECT CODE,COST FROM TABLE1","COST") uses the database file “abc.mdb” in the TableFolder and will return the cost of every item in table1 in abc.mdb as an array. |
| See Also | DBLookup, DBLookupRow, DBLookupColValues |
| DBLookupColLabels | |
|---|---|
| Returns the column headers in the query as a pipe ("|") separated string. | |
| Syntax | DBLookupCol (dbpath, sql) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the& database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed |
| Outputs | The column headers of the result as a pipe ("|") separated string. |
| Examples |
DBLookupColLabels("abc", "SELECT CODE,COST FROM TABLE1") will return "CODE|COST" This uses the database file “abc.mdb” in the TableFolder. |
| See Also | DBLookup, DBLookupRow, DBLookupCol |
| DBLookupColValues | |
|---|---|
| Returns the chosen column of the results of the query as a pipe ("|") separated string. | |
| Syntax | DBLookupColValues (dbpath, sql, field) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed Field: The column header of the column of results desired. |
| Outputs | The specified column of the SQL result in a pipe ("|") separated string. |
| Examples | DBLookupCol("abc", "SELECT CODE,COST FROM TABLE1","COST") uses the database file “abc.mdb” in the TableFolder and will return the cost of every item in table1 in abc.mdb as a pipe ("|") separated string. |
| See Also | DBLookup, DBLookupRowValues, DBLookupCol |
| DBLookupRow | |
|---|---|
| Returns the first result of the query as an array. | |
| Syntax | DBLookupRow (dbpath, sql, param) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed, must have a parameter represented by a '?' Param: The value of the parameter field from the SQL query |
| Outputs | The value of the first row of the SQL result in an array. |
| Notes |
Queries should usually be designed to return just one record, but if more than one record is returned, the first matching record will be used. |
| Examples | DBLookupRow("abc", "SELECT CODE,COST FROM TABLE1 WHERE CODE=?","Red") uses the database file “abc.mdb” in the TableFolder and will return the code and cost of Table1 in abc.mdb who's code is "Red" as an array. |
| See Also | DBLookup, DBLookupRowValues |
| DBLookupRowValues | |
|---|---|
| Returns the first result of the query as a pipe ("|") separated string. | |
| Syntax | DBLookupRowValues (dbpath, sql, param) |
| Inputs | dbpath: The full path or relative path to the TableFolder where the database file can be found. Default extension is .mdb but the file extension can be .mdb or .accdb for Access or .db for SQLite. sql: SQL query to be executed, must have a parameter represented by a '?' Param: The value of the parameter field from the SQL query |
| Outputs | The value of the first row of the SQL result in a pipe ("|") separated string. |
| Notes |
Queries should usually be designed to return just one record, but if more than one record is returned, the first matching record will be used. |
| Examples | DBLookupRowValues("abc", "SELECT CODE,COST FROM TABLE1 WHERE CODE=?","Red") uses the database file “abc.mdb” in the TableFolder and will return the code and cost of Table1 in abc.mdb who's code is "Red" as a pipe ("|") separated string. |
| See Also | DBLookupRow, DBLookup |