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.
| DBExecute | |
|---|---|
| Runs the specified query on the database. | |
| Syntax | DBExecute (mdbpath, sql) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. sql: SQL query to be executed. |
| Outputs | The number of effected 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. |
| DBLookup | |
|---|---|
| Returns the value of a single field in the first result of the query. | |
| Syntax | DBLookup (mdbpath, sql, param, field) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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") 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 (mdbpath, sql, field) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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") 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 (mdbpath, sql) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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" |
| See Also | DBLookup, DBLookupRow, DBLookupCol |
| DBLookupColValues | |
|---|---|
| Returns the chosen column of the results of the query as a pipe ("|") separated string. | |
| Syntax | DBLookupColValues (mdbpath, sql, field) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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") 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 (mdbpath, sql, param) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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") 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 (mdbpath, sql, param) |
| Inputs | mdbpath: The path relative to the PROGRAM_PATH folder where the mdb file can be found. 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") 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 |