Hi,

I have a weird problem.
In a system we need to print batches of labels and therefor we issue a
query to a firebird database which contains a few joins.
It has to do with requested investigations (ANALYSIS) by a customer
which contains several materials (SPECIMEN) and several
testing-procedures (STAINING) to be performed on little pieces
(CASSETTE) taken from the materials.

The query issued is :

SELECT Analysis.Analysis, Analysis.AN_NUMBER, Analysis.An_Origin,
Analysis.An_Type,
Specimen.Specimen, Specimen.Number as SpecIndex, Specimen.Macroscopy,
Cassette.Cassette, Cassette.Number as CassIndex,
Sample.Sample, Sample.Number, Sample.Request,
Staining.Technique,Cassette.Material,
Staining.NAME as Staining, EXC.Exc_Type FROM SAMPLE SAMPLE
LEFT OUTER JOIN EXC EXC ON EXC.SAMPLE = SAMPLE.SAMPLE
INNER JOIN STAINING Staining ON SAMPLE.STAINING = STAINING.STAINING
INNER JOIN CASSETTE Cassette ON CASSETTE.CASSETTE = SAMPLE.CASSETTE
INNER JOIN SPECIMEN Specimen ON SPECIMEN.SPECIMEN = CASSETTE.SPECIMEN
INNER JOIN ANALYSIS ANALYSIS ON ANALYSIS.ANALYSIS = SPECIMEN.ANALYSIS
WHERE Sample.PRINTBATCH=%d ORDER BY Analysis.AN_NUMBER,
Specimen.Number,Cassette.NUMBER, Staining.NAME, Exc.Exc

This system has been functioning flawless in several enterprises for
over 5 years, until 2 months ago in 1 enterprise we appear to have
received a false result twice, being the investigation-number printed
not matching the real item in the database. All other information is
correct, it just seems that the join to the analysis-table, having a
unique key created by a generator, returns the wrong row.
This happens for just one sample,while other samples connected to the
same cassette display the proper analysisnumber (AN_NUMBER).


We examined the database, and everything seems to be ok. Also we found
no signs of corruption using the standard firebird tools ( gfix ).
Also no interesting entries in Firebird.log.
If we request a reprint of the wrong label for that specific
investigation we get the proper information.

The firebird-engine we use is 1.5.3.4870 .
Development in Delphi, using DbExpress.
Firebird engine runs on W2K server on VmWare as service superserver mode.
The difference with the past is that the engine has been upgraded from
1.5.2 and the server is migrated to VmWare.


Has anybody experienced a problem like this before ?.


Kind regards,

Henk.