LabWare LIMS SQL Code
Written by Gloria Metrick on . Published as written. It may not reflect how these systems work today.
Today brings you another installment of keyword search terms used to find my blog that I have decided to blog on. Yesterday, I noticed the search term “labware lims sql code” and couldn’t pass on talking about that.
Using SQL Code in LabWare
If you’re looking to use SQL Code in your LabWare LIMS, be frugal about it. If you’re new to the system, just know that there are many features that will get data for you for simpler searches. There are also tools for updating records with the system’s programming language.
Overall, you should avoid updating, deleting or inserting records using direct SQL commands. It might seem faster, but you’re then overriding all the things that the system is doing for you, such as security checks, auditing, etc…
It’s Not Just About LabWare Nor About Regulations
Some projects completely disallow the use of any but the SQL SELECT command, while others allow them only for extremely limited cases. This is not just true of the regulated systems, but many systems do not want to lose their ability to track audits, manage security, etc… by doing something like this that steps outside the system’s boundries.
By the way, this is true of many of the products out there, that you want to be cautious about using SQL statements injudiciously. You should be extremely cautious about directly applying SQL commands to any of these systems, whether it’s a LIMS, ELN, SDMS, CDS, etc…
An Example
As a Developer, in a Development system, to create good test data, I do sometimes adjust data by directly doing a SQL UPDATE outside of the LIMS. As one is often creating new fields and tables to create new features, it sometimes means there is no mechanism in the current system to change these fields until you build it. So, to start out, I will admit that I sometimes do that.
BUT!!!! I have known of people to be a little careless and goof-up everyone’s data in the Development system by doing this. AND for those people new to doing these commands, it’s not that hard to royally mess-up the Development database data by doing this.
On top of all that, the Development environment is a place where this is acceptable when people are careful and know what they’re doing. Allowing people to do this in their Production system is unthinkable.
A Rare Exception
There are rare times when a bug causes the Production data to be wrong in some way that can be easily fixed by a SQL UPDATE. Some companies allow for a change control mechanism where the exact statement is written up and approved by a number of people before someone like a system administrator is allowed to actually run the update. It is a rare case and is heavily controlled.
Backup
Note I haven’t mentioned that you can recover from SQL UPDATE/DELETE/INSERT mistakes by brining up your backup. Think of it, this way, if you’ve loaded all your data for several days of work and then find a problem, people need to keep working – going back to the backup copy from several days ago and having them stop to reload and rework everything isn’t much of an option in a busy Production system. So, don’t be too casual in your dependence on your backup system when you’re thinking about these issues.
Gloria Metrick
GeoMetrick Enterprises
http://www.GeoMetrick.com/