Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
Inconsistant Results for ExecSQL |
Wed, Mar 29 2006 9:49 AM | Permanent Link |
Gordon Turner | 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? |
Wed, Mar 29 2006 4:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Mar 29 2006 4:31 PM | Permanent Link |
Jeff Cook | 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 |
Wed, Mar 29 2006 6:53 PM | Permanent Link |
Gordon Turner | 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. |
Thu, Mar 30 2006 8:13 AM | Permanent Link |
Gordon Turner | 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. |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |