Using CabMasterPro's middleware functions for database access, you can retrieve values and even update a Microsoft Access database. This lets you integrate your custom libraries directly with costing and stock databases that may already be in use in your operation.

CabMasterPro shares database connections for performance, ensuring there is only ever one connection to each database. And the connection is held once open, until CabMasterPro closes, also for performance. You should always lookup values using database indexes and parameterised queries, again for performance reasons.

SQLite

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.

Example of database functions in use

For an example of these functions in use extract the zip file database.zip (click to open) to your CabMasterPro directory (where CabMasterPro.exe is found) then load "DatabaseSample.qid" (found inside the zip) into CabMasterPro.

This example drawing shows four different simple examples of how to use database calls in your function. It uses the data inside abc.mdb, which can be viewed using Microsoft Access. The commands used are:

• dblookup("abc","select * from table1 where code=?" ,"green","cost")
  • Gets the cost from Table1 of abc.mdb (Also found in database.zip) for something with a code of "green".
  • This value is 234.56
    
• dblookuprow("abc","select * from table1 where code=?","green")
  • Gets the row from Table1 where the code is "green" as an array.
  • This value is ["Green","234.56"]
    
• dblookupcollabels("abc","select * from table1")
  • Gives the labels of all the columns of Table1 as a pipe separated string.
  • This value is code|cost
    
• dblookupcolvalues("abc","select * from table1","code")
  • Gives all the code values of Table1 as a pipe separated string.
  • This value is blue|green|red.