By Andy Garratt
Avoid using ‘SELECT * from ‘
When you use ‘SELECT * ‘ all the fields from the table or view are returned. If the table changes, the list of fields also changes, which might not be what you want, especially when you are mapping directly to a business object where the new names might not exist.
In addition, the asterisk (*) might return many columns in the results that are not needed but take up memory.
Instead, explicitly name the columns that you want returned as a result name, for example “SELECT <COLUMN1> AS <RESULT1NAME>, <COLUMN2> AS <RESULT2NAME>”. The use of AS means that your business object names do not need to match the database column names and can be more meaningful. For example, CST1NAM could map to CustomerName.
Use parameter markers
Do not construct an SQL statement by concatenating variable names, such as
“SELECT CUST_NAME FROM CUST_MAST WHERE CUST_NUM=”+tw.local.customer_number
If you concatenate variable names, the database cannot precompile and cache the select statement because it changes every time.
Instead, use parameter markers, such as in “SELECT CUST_NAME FROM CUST_MAST WHERE CUST_NUM=?”, and then set the parameter value.
Better yet, wrap database access in an SOA service that is invoked remotely or by using an Advanced Integration service.
Applicable editions: Express, Standard, and Advanced
Applicable releases: All