Help!

We're getting a very strange problem. We write software for the small
business retail industry. We have sites all over the country, with 3 or
four computers running Win 95 or 98, linked in a peer to peer network.

One of the machines is a server, and also controls various pieces of
hardware that write various data to the database. Normally there will be
one or two other machines, running POS software and also writing to the
server db. We are running FB 1.2, and the all the software is written in
Delphi 5 using the IB Express components.

At the end of each shift, the terminals (POS machines) will draw a shift
report. We have been running the report against a stored procedure in the
FB db. The report sometimes (and it seems that perrformance degrades over
time quite quickly - 2 or 3 days - say within 5 or 6 shifts) print blank
results.

We have checked the tables - the data is there. The data in the joined
table is there. When we run the query that the stored procedure contains as
a normal select, it produces the data. When we "select * from procedurename
(par1, par2)", we get a null result.

?

It almost seems as if the db or the client is timing the procedure out - I
have gone crazy checking indices, etc., etc., but nothing seems to help. I
have used FB and IB before (all the way since 4.2), and love it, and it's
taken me a year to convince my boss that actually dBase III+ files arn't
very good at storing data. Now he's threatening to go back to them ... :-(

Here is the SP: (If I cut and past the "select" part of the code into
wisql, and fill in the params, it works fine)

ALTER PROCEDURE "GETSALESBYDRYSTOCK"
(
"POSID" INTEGER,
"SHIFTNUM" INTEGER
)
RETURNS
(
"DESCRIP" VARCHAR(50),
"STOK_CODE" VARCHAR(13),
"AMOUNT" FLOAT,
"DEPT_CODE" VARCHAR(2),
"UTOTAL" FLOAT,
"MTOTAL" FLOAT
)
AS
BEGIN
FOR SELECT
l.Descrip, l.Stok_Code, l.Dept_Code, s.Amount,
SUM(l.UQuantity) AS UTotal, SUM(l.MQuantity) AS MTotal
FROM log l JOIN STOCK S
ON l.Stok_Code = s.Code
WHERE l.Tran_Type LIKE '#%'
AND Misc_Trans = 0
AND l.Sal_Ref = 'S'
AND l.POS_ID = :POSID
AND SHIFTNUM = :SHIFTNUM
GROUP BY l.Stok_Code, l.Descrip, s.Amount, l.Dept_Code
ORDER BY l.Descrip
INTO
:DESCRIP, :STOK_CODE, :DEPT_CODE, :AMOUNT, :UTOTAL, MTOTAL
DO
BEGIN
SUSPEND;
END
END
^


[Non-text portions of this message have been removed]