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 LabWare LIMS SQL

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 and update data for you. You need to focus on learning those. There are also tools for updating records with the system’s programming language.

Overall, you should avoid updating, deleting or inserting records using direct LabWare LIMS SQL commands. It might execute 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’s LIMS / ELN Nor About Regulations

Then, what LabWare LIMS SQL is allowed? Some projects completely disallow the use of any but the SQL SELECT command. Others allow them only for extremely limited cases. This is not just true of the regulated systems. 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 boundaries. Here at GeoMetrick Enterprises, you can count on us not to step outside these boundaries. Read why at “LabWare LIMS: We’re the Experts.”

By the way, this is true of many of the products out there. It’s true that you want to be cautious about using SQL statements injudiciously. You should be extremely cautious about directly applying SQL commands to any of your systems. This is true whether it’s a LIMS, ELN, SDMS, CDS, etc…

An Example

As a Developer in a Development system, I need to create good test data. To do this, I do sometimes adjust data by directly doing a SQL UPDATE outside of the LIMS. So, this LabWare LIMS SQL application is outside what a Production system is doing. Developers get tools to do these tasks, so don’t be surprised if this isn’t something most people have access to. But as a develop 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 need to create some in order to create test data.

BUT!!!! I have known of people to be a little careless and goof-up everyone’s data in the Development system by making careless UPDATEs. AND for those people new to SQL, it’s not that hard to royally mess-up the Development database data by doing this.

With all that said, 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.

Side note: Yet another option is to have a personal database copy to work on so that the main Development system isn’t contaminated with erroneous SQL commands. Whether this is possible partly depends on the type of license in effect.

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 LabWare LIMS 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

Notice I haven’t mentioned that you can recover from a LabWare LIMS SQL UPDATE/DELETE/INSERT mistakes by bringing up your backup. It’s not just you who might have been adding data and programs since the last backup. They all need to keep working. They need and want to keep working on what they’ve already built. It’s not going to help them if they have to stop what they’re doing and wait for the backup restoration. So, don’t be too casual in your dependence on your backup system when you’re thinking about these issues.

http://geometrick.com/labware-lims/
Read More About the LabWare LIMS / ELN

0 Thoughts to “LabWare LIMS SQL Code”

  1. DF

    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. 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. DF

    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. 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.

  5. […] Yesterday, I received a request to write something a little more of a how-to treat your LabWare LIMS regarding outside code.  I happened to realize that I already wrote something like that in the past with regard to using SQL commands, which is a popular way to get around the system functions. As such, here’s a link to it:   LabWare LIMS SQL Code […]

Leave a Comment