I'm using D7 and DBISAM 3.24 in an application.  I'm loading data from a
permanent table into a memory table MemTimeOff, and then using a query
to load summarized data from that memory table to a second memory table
B.  With my test data, the first time I load table MemAccrue one record
appears - the expected result.  When I re-execute the second table load,
no records appear in table MemAccrue.  Here's what I'm doing (both
queries are Prepared when the data module is created, and UnPrepared
when the data module is destroyed - memory tables are created when the
data module is created)...

{ clear the table of previous entries }
qryUnloadAccrual.ExecSQL;
{ now populate it with the data for this date range }
qryLoadAccrual.Params[0].AsInteger := ReasonID;
qryLoadAccrual.Params[1].AsDate := StartDate;
qryLoadAccrual.Params[2].AsDate := EndDate;
qryLoadAccrual.Params[3].AsInteger := TempDay;
qryLoadAccrual.Params[4].AsInteger := ReasonID;
qryLoadAccrual.Params[5].AsDate := StartDate;
qryLoadAccrual.Params[6].AsDate := EndDate;
qryLoadAccrual.Params[7].AsInteger := TempDay;
qryLoadAccrual.ExecSQL;

The qryUnloadAccrual executes...

Delete from Memory MemAccrue

the qryLoadAccrual executes 3 statements

insert into Memory MemAccrue
select Extract(year from WeekDate), Extract(month from WeekDate)-1,
  Hours, count(*)
from Memory MemTimeOff
where ReasonID = :ReasonID
and WeekDate between :StartDate and :EndDate
and Extract(day from WeekDate) < :TempDay;

insert into Memory MemAccrue
select Extract(year from WeekDate), Extract(month from WeekDate),
  Hours, count(*)
from Memory MemTimeOff
where ReasonID = :ReasonID
and WeekDate between :StartDate and :EndDate
and Extract(day from WeekDate) >= :TempDay;

Update Memory MemAccrue
set MonthNo = 12,
    YearNo = YearNo -1
where MonthNo = 0;

So using the exact same set of parameter values, why would the second
execution of qryLoadAccrual not create a record in my MemAccrue table?
What am I doing wrong here?

Gordon,

<< So using the exact same set of parameter values, why would the second
execution of qryLoadAccrual not create a record in my MemAccrue table? What
am I doing wrong here? >>

I'm not sure what could be causing the problem.  Can you send me the tables
that you're using so I can try it here ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Gordon Turner <gordon@mycroftcomputing.com> wrote on Wed, 29 Mar 2006 09:48:42 -0500

>
>I'm using D7 and DBISAM 3.24 in an application. I'm loading data from a
>permanent table into a memory table MemTimeOff, and then using a query
>to load summarized data from that memory table to a second memory table
>B. With my test data, the first time I load table MemAccrue one record
>appears - the expected result. When I re-execute the second table load,
>no records appear in table MemAccrue. Here's what I'm doing (both
>queries are Prepared when the data module is created, and UnPrepared
>when the data module is destroyed - memory tables are created when the
>data module is created)...
>
<snip>


Gordon


Isn't there a problem in 3.x with the use of parameters in a multi-statement SQL? Like they only get used for the first or last statement? I seem to recall something like that - though whether that would affect what you are doing, I don't know.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Tim,

> I'm not sure what could be causing the problem.  Can you send me the tables
> that you're using so I can try it here ?

Thanks.  I sent you an email with the table and query information.

Jeff,

> Isn't there a problem in 3.x with the use of parameters in a multi-statement SQL? Like they only get used for the first or last statement? I seem to recall something like that - though whether that would affect what you are doing, I don't know.

Ahhh.  That seems to have been the problem.  When I broke the query into
three separate queries it worked.  Thanks for pointing that out - I
never would have thought to check into that.