Skip to content

LabWare LIMS SQL Code

May 6, 2010

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/

Advertisements
5 Comments leave one →
  1. May 9, 2010 6:26 pm

    Hi!

    Nice post! But one question remain: What type of SQL (language) do we have to use with LabWare?

    Nice Blog btw!

    Cheers DF

  2. May 10, 2010 12:34 pm

    You’ll use the SQL for whatever database you’re using, and this is true of most of the software that allow you to write direct SQL commands. For example, if you’re using Oracle, use SQL compatible with Oracle. We sometimes talk about trying to make our SQL as standard as possible so it can be used in any database. But with that said, here are a couple things to consider:

    1. Most companies don’t end up switching a major database product. If a company is using SQL*Server for their database product for a particular piece of software, they don’t tend to switch to Oracle, and vice versa. So far, none of my customers have done that, even if the rest of their company, itself, switches their standard database platform.

    2. Most of the capability problems come for those software products (such as LabWare LIMS) that allow you to copy the system and run it on MS Access in order to develop and support the system with a personal copy. MS Access has a much more limited SQL than Oracle or SQL*Server and you can’t just restrain your commands only to those that work in MS Access or you lose too much capability. So, for those who are in a situation where they have this problem, proceed carefully. You’ll have to do something to make it work in MS Access and to make sure you don’t copy it back to the SQL*Server or Oracle database that the main system is on.

  3. May 18, 2010 4:43 pm

    Well, I fully agree with that, but LabWare also uses a lot of SQL under the hood without knowing which database the customer uses in the end. Do they use a kind of adapter class for each type of database e.g.? Whatsoever, I think I’ll find it out in July when I’m going to take the admin course ;).

  4. May 18, 2010 5:45 pm

    If you turn your debugger on, you will be able to see the SQL code. If you took the most basic of SQL code that is what you will see. As you can imagine, it will mainly consist of SELECT statements.

Trackbacks

  1. LabWare LIMS and SQL, Specifically | Out on a LIMS®: The Blog For People Who Risk Life and LIMS™ on a Daily Basis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: