These functions deal with accessing data stored in databases outside CabMasterPro.

For a guide to these functions see the Database Functions tutorial.

Performance of database calls

It depends what you try to do in middleware, whether the performance will be fast or slow. 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