DateWise, Ltd

Details of why our queries are better

The technique used by Vic Fanberg to write queries against any third party vendor's database was invented by him for that specific purpose. These vendors can change the structure of their database at any time and traditional queries can break at that time. To lessen the likelihood that a vendor change to the structure of the database will cause existing queries to break, his queries minimize how much of the query is hard coded.

To put it visually, here is the way a traditional INSERT statement is written by every other developer we are aware of. It is a list of table columns and either a list of new values for those columns or dynamically calculated new values. The point is, traditional queries hard code the entire list of column names.

Traditional insert queries have the column names hard coded

The method above has a problem in that vendors constantly add new columns, change attributes of existing columns, remove existing columns and sometimes change even the values contained within the column as their product changes over time.

Below is the way our queries are structured. We start by placing an intentional prototype record in the database table that all new rows of a specific type will be copied from. The concept is that as the vendor changes the database structure, they will make the corresponding change to the prototype records. That forces our queries to dynamically adapt to the new list of columns and avoid most problems caused by vendor schema changes.

Our break resistant queries read column names from a stored prototype row

This does not mean that there is no need to test our queries whenever the underlying software is updated, but it does mean that there will be fewer times that our break resistant queries need changes applied to them.

Specifically, our break resistant queries may need changes when the table it refers to is split into multiple tables, as in a normalization process; or the specific columns named in the query are altered, and possibly when the acceptable values of a named column are changed. Whatever happens to unnamed columns does not affect the break resistant query. Combining multiple tables into one generally does not require any changes to the break resistant query.

By way of comparison, the traditional query will often need modification whenever any hard coded column in the table is altered, added, or removed; the acceptable values of a named column are updated; multiple tables are combined into one; or the single table is split into multiple tables.