DLN:CC Beta - Database record corruption

[Dave Martin]

Re Beta 1.0.3 (DB version 2.2.3) Build Apr 26 2018 15:55:31

 

This is initially relates to silent corruption of a field in the Equipment Overview, but may have wider implications…

 

If one is using DLN:CC Equipment Overview as a mini asset register as per the sample database, and populates a price, there are silent and insidious problems if one enters anything other than digits 0 to 9 or a decimal point by including say  a currency symbol (e.g. £ for GBP) or a comma (used by many to delimit thousands, or in some regions as the separator twixt the integer portion and decimal fraction of a real number).

 

What happens is:

On the equipment overview details screen, the data entry field (whether adding a new record or editing an existing record) allows one to enter costs such as

123.45

1234.56

1,234.56

1.234,56

£123.45

£1,234.56

GBP 123.45

without any objection either when the Price field is exited or when the [Save] button is clicked.

 

However, if one then saves, closes and goes back to view the record details, in all except the first two cases, the Price is now ‘0’.

 

Without seeing the code, from the above it appears the failure is because the UI is accepting (meaningful and valid) ‘text’ but the database is only accepting a real with a decimal point separator. Insidiously, the whole database INSERT / UPDATE isn’t failing – if for example the serial number and price are changed, the serial number is saved/updated but the price can be corrupted to 0 if it contains anything except 0 to 9 or a decimal point.

 

Couple of points:

1. As there is unlikely to be any arithmetic on equipment prices, in this specific case might be best to just make the Price column in the database a text varchar, rather than numeric, column.

More importantly though:

      2.   If there are restrictions as to what can be stored in a field, the UI needs up-front validation either when the field is exited or when the [Save] button is clicked.

      3.  IF the UI is posting the input from the user intact and it is being rejected by the database, then it raises the possibility that the result of commands which modify the database may not be being monitored.

 

Dave