relay.pl - a PERL program and SQL queries to retrieve data from the Lyon Tracker DB

Introduction

relay.pl is a perl program, which can be used to query data from the TrackerDB by giving an sql query file. It is based on the example found at the Lyon DB group page.

Installation Instructions

Linux/UNIX

If you are using Redhat 8.0 (or higher) no additional things are required.

To use the program with an older version of Redhat or another Distribution, you need to install the PERL-module XML::Parser from CPAN. This could be done at the perl CPAN shell, which could be started with the command perl -MCPAN -e shell. On the prompt, enter install XML::Parser. This module needs the expat library installed at your system. It could be installed as RPM or directly from source.

Windows

You have to download a PERL distribution for Win32, for example ActivePerl. It already includes the XML::Parser module.

Useful SQL queries

• INVENTORY
• TRANSFERS
• ASSEMBLY
• sensor LOGISTICS_SHEET
• sensor PQC
• sensor QTC
  
  
• all queries
  

Usage

The syntax to use this program is one of the following two possibilities:

• ./relay.pl sql_query.txt

command line switches:

• -v verbose. print query and db status.
• -h no header. suppress column name in answer.
• -s read query from stdin and not from file.
  (example: cat file.sql |relay.pl -s)
• -t set timeout for answer in seconds.
  

example:

$> ./relay.pl -v flatband.sql

<select db="prod">
OBJECT_ID, FLAT_BAND_VOLTAGE AS V_FB
from tsmos_1_sen_ a
where
OBJECT_ID LIKE '302114330146%' and
status = 'reference'
</select>

Status: 200 DBQuery: OK

OBJECT_ID V_FB
30211433014601 3.8
30211433014625 2

The output of the program is the query itself (green; if you use the -v switch) and the answer of the DB (red), incuding a status-line, which gives you "200 DBQuery: OK", or any error string beginning with "400", if you have a error in your query. The result of the query is written below.

Download and view the Presentation at Sensor meeting (PDF; CERN, 23.Oct.2002)

version history:

NEW VERSION (V2.7) corrected bug with SIG_IGN signal (Ctrl-C) to terminate

NEW VERSION (V2.6) added parsing of #HEADER tag in sql file

19.Jan.2004: NEW VERSION (V2.5) added fallback host if primary server fails

4.Dec.2003: NEW VERSION (V2.4) added flag -t for timeout

19. Nov. 2003: NEW VERSION V2.3: added timeout for connecting and slow answer

24.Sept.2003: NEW VERSION (V2.2) of relay.pl is available. changes: fixed bug with -h option

30.Jul.2003: NEW VERSION (V2.1) of relay.pl is available. changes: new options (no header, readfromstdin)

5.Jun.2003: NEW VERSION (V2.0) of relay.pl is available. changes:

• completly rewritten parser, which corrects a bug when parsing output lines with empty column values. See here for an example of this issue.
• comments in the sql files are now possible (use '#' in beginning of line)