If you are using Automation Senders, your third party automation software will need to access lab2lab's Oracle database. When the setup script is run, it creates an account with a default name of “C##lab2lab_extern”, to enable external access. You may change the name and the password too, if you prefer. This account is only used by the external software, so if the name is changed there is no need for lab2lab to be informed about this.
Database Tables
When the schema creation script is run, it creates three tables called VIALS_EXTERNAL, METHODS_EXTERNAL and ARM_SUBMISSIONS for use with any Automation Senders. It also creates a SUBSYSTEMS table which is used by other aspects of lab2lab but which the external account can access read-only.
The table names listed above are the internal names for those tables. The external account access the tables through three “public synonyms”.
|
Internal Table Name |
External Public Synonym |
|
VIALS_EXTERNAL |
LAB2LAB_VIALS |
|
METHODS_EXTERNAL |
LAB2LAB_ANALYSES |
|
ARM_SUBMISSIONS |
LAB2LAB_ARMS |
The reason for this is so that it is obvious in the source code of any external application what relates to lab2lab. From an internal lab2lab point of view it makes more sense to use the _EXTERNAL suffix as the naming convention for these tables. The external account can also read data back from the SUBSYSTEMS table through its public synonym which, for historic reasons, is also SUSBSYSTEMS.
Vials and Methods Tables
These are the fields of the VIALS_EXTERNAL/LAB2LAB_VIALS table.
|
Column Name |
Comment |
|
ENTRY_ID |
Automatically generated and incremented numeric value. |
|
BARCODE |
Ten-character string. This must be unique in the table. |
|
SAMPLE_REFERENCE |
String of up to 128 characters. This may have to be in an enforced format to comply with your ELN/LIMS naming conventions. |
|
SECONDARY_REFERENCE |
Optional value that the external software can populate. lab2lab ignores this. |
|
PRIORITY |
The only valid values are Overnight, Low or Normal. The default value is Normal. |
|
ON_COMPLETION |
The only valid values are RetainInBuffer or SendToWaste. The default value is SendToWaste. |
|
STORAGE_TARGET |
Where to store the vial while waiting to be analysed and, if the RetainInBuffer option was set for ON_COMPLETION, where to retain the vial after analysis. |
|
STATUS |
Updated by lab2lab to give some indication of the progress of the vial. The only valid values are Unknown, Submitted, Processing, Completed, InError or Departed. Default value is Unknown. |
|
REGISTERED_AT |
Populated by lab2lab with a timestamp to indicate when it noticed the new entry and registered the vial in the system. |
|
PHYSICALLY_SUBMITTED |
Populated by lab2lab with a timestamp to indicate when the cap sensor in the Automation Sender first saw the vial. |
|
LOCATION |
Populated by lab2lab with the name of the vial’s location |
|
MESSAGE |
Additional feedback from lab2lab |
The external account has SELECT, INSERT, UPDATE and DELETE permissions on this table.
The second table used for vial registration and feedback is the METHODS_EXTERNAL/LAB2LAB_ANALYSES table.
These are the fields of the METHODS_EXTERNAL/LAB2LAB_ANALYSES table.
|
Column Name |
Comment |
|
ENTRY_ID |
Automatically generated and incremented numeric value. |
|
METHOD_NAME |
The name of the method to be used to analyse the vial’s contents. This name must match an entry in the lab2lab Director’s method list, as defined in the Lab2Lab.ini file. |
|
VIAL_BARCODE |
Barcode of the vial which should match a value in the previous table. |
|
STATUS |
Updated by lab2lab. Only valid entries are Pending, Transferred, Running, Completed, Cancelled or Error. The default value is Pending. |
|
REGISTERED_AT |
Populated by lab2lab with a timestamp to indicate when this method registered in the system. |
|
START_TIME |
Populated by lab2lab with a timestamp to indicate when the method was started. |
|
END_TIME |
Populated by lab2lab with a timestamp to indicate when the method was finished. |
|
INSTRUMENT |
Populated by lab2lab with the name of the instrument used to analyse the vial’s contents. |
|
DATA_FOLDER |
Populated by lab2lab with the path to the data folder containing the results from the analysis |
|
RESULTS_EXPORTED |
Either 0 or 1. Default is 0. Used by the third-party software to keep track of whether it has exported the analytical results. |
The external account has SELECT, INSERT, UPDATE and DELETE permissions on this table.
Arm Submissions
Unlike the previous two tables, for the ARM_SUBMISSIONS/LAB2LAB_ARMS the external account is only granted SELECT and UPDATE permissions. This is because there is only one entry per Automation Sender and the lab2lab Director will create it. After that it should only ever be queried or updated. This applies to the third-party software and the Director application.
ARM_SUBMISSIONS/LAB2LAB_ARMS
|
Column Name |
Comment |
|
NAME |
The name of the Automation Sender, as defined in the Director’s Config file. |
|
IS_EXTENDED |
Either 0 or 1. Updated by lab2lab to indicate the arm position. |
|
VIAL_PRESENT |
Either 0 or 1. Updated by the third-party software from 0 to 1 when the robot inserts a new vial, and then from 1 back to 0 by lab2lab when the vial is sent to the Router. |
|
ADDRESS_ID |
The numeric value of the location ID in lab2lab’s list of location addresses. |
|
LAST_UPDATED |
Timestamp updated by any software when it edits the data in the row. |
System Status Feedback
Finally, third-party software is granted SELECT (no UPDATE, only SELECT) access to the SUBSYSTEMS table. As previously mentioned, this is, for historical reasons, through a public synonym that is also called SUBSYSTEMS.
SUBSYSTEMS
|
Column Name |
Comment |
|
SYSTEM |
The name of the lab2lab system to which the subsystem belongs. For larger installations there can be more than one system list. |
|
NAME |
Name of the subsystem as defined in the Director’s config file. |
|
ADDRESS_ID |
The numeric ID value that maps to the physical address. This can be null if the subsystem in question is a purely software component. |
|
TYPE |
Must be either Unknown, Bin, Sender, Instrument, Buffer, Transceiver, Router, Transport or Scheduler. |
|
STATUS |
Must be either Unknown, OffLine, Shutdown, InError, Busy or Ready. |
|
LAST_UPDATE |
Timestamp for when last updated. |
|
ADDITIONAL_INFO |
Optional additional explanatory text, for example to explain why something is OffLine. This can be used in the UI. |