SBM ModScript, Part 9 - SQL Queries
In ModScript, we can read queries from the SBM database. One way to do this is to read items from a table, where each item is based on that specific table's schema. This is what the AppRecord, VarRecord, and ProjectBasedRecord classes (and child classes) are for. When you create one with Ext.CreateAppRecord(), Ext.CreateVarRecord(), or Ext.CreateProjectBasedRecord(), you always pass in the table ID that the record will be based on, so for the life of that object, it will be associated with that table and its schema. Also, we have the AppRecordList (and child classes), which is created with Ext.CreateAppRecordList(), and which also will be bound to a specific table ID on creation. Thus, all items in the AppRecordList will be objects of that tables' type.
All these classes can be used to read records from the SBM table for which the class is bound, using the following functions:
- Read an item by TS_ID or name.
- Read an item by a column from that table's schema
- Use two column values to identify the item to read.
- Read the item with any SQL where clause. This function can use SQL bind parameters.
- Read an item by TS_UUID
- Read an entire table, this could potentially use a lot of resources.
- Read a list of items by a column from that table's schema
- Use two column values to identify the items to read.
- Read the list of items with any SQL where clause. This function can use SQL bind parameters.
When using the ReadWithWhere() functions, I'd encourage the usage of SQL bind parameters. When trying to build SQL using dynamic values, you need to worry that the values you are pulling from fields or other sources could have embedded single-quotes ( ticks: ' ), which can break the SQL. These values would need to be encoded if added directly to the SQL query. However, if instead you put a question mark in the SQL, you can bind the value to it using a Vector of bind values. These bind values do not need to have the ticks encoded, which simplifies your script. In Part 2 of this series, you saw me use SQL bind parameters:
containerList.ReadWithWhere( "TS_ID in (select TS_SOURCERECORDID from TS_USAGES where TS_FIELDID=? and TS_RELATEDRECORDID=?)", [ Pair(DBTypeConstants.INTEGER, relational.GetId()), Pair(DBTypeConstants.INTEGER, Shell.Item().GetId()) ] );
Each entry in the Vector is a Pair, with the first value being the data type for the value, and the second value being the value I am binding to my SQL.
ModScript also allows for SQL queries that are not tied directly to tables. The Shell.Db() method returns an AppDb object which points to the current SBM AE schema. It has the following functions which allow for free SQL to be executed, (each take an optional Vector of SQL bind parameters):
- Returns a single integer read from the database.
- Returns a single string read from the database.
- Fills a Vector with integers read from the database.
- Fills a Vector with Pairs of integers read from the database.
- Fills a Vector with strings read from the database.
With these, you can execute any SQL you want as long as the output of the SQL can be bound to as a single int, varchar, a list of ints, list of varchars, or a list of pairs of ints. In 11.4, we added AppDb.ReadDynaSQL(), which allows you to specify any number of columns, with their column types, which makes you truly free to execute any SQL you want.
Keep in mind that queries can be slow. In SBM, we try to identify any slow queries built into our product, and we add database indexes to mitigate the performance slowdown. However, we can't do this for queries that you add, so you will probably want to watch for places where you should add your own indexes to the database to help speed up your queries.