problem with mydac4std when using sum in a query = access violation

[jwcgroup]

Delphi 7, mydac 4.40.0.25

One query to extract data from stock and join stock levels mysql table, with a sum of a stkqty on the stocklevels table - query as follows :

select zstock.partnr, zstock.description, zlevels.partid,
sum(zlevels.qty), zlevels.locn from zstock
left outer join zlevels on
(zstock.partnr = zlevels.partid)
group by partnr

produces correct results, all is ok.

Problem : When using the ide, right click on the query, choose data editor, then click exit, I get an access violation at address 030CACC3 in module dac70.bpl.

If I choose CLOSE then exit, all is ok.

Further problem : When using a live/running system, if I make changes to either the stock or the stocklevels dataset, needing a refresh of the query, I get the same error above and a failure.

Any refreshes to the query result in failure. If i close the query, then reopen it all is ok.

If I remove the sum(zlevels.qty) to just having the qty, all is ok. Sum is the problem.

For reference, I have tried this with mydac pro 5.20.1.15, and it works without any problem. It only fails with version 4!!!

I want to migrate to v5, but i have a very large project working smoothly on v4 and dont have time to change at this point.

AARRGGGh!

Any one offer a solution / assistance on this bizarre bug?






I have posted sql structures below if anyone would like to test


--
-- Table structure for table `zstock`
--

CREATE TABLE `zstock` (
`id` int(10) NOT NULL auto_increment,
`partnr` varchar(45) NOT NULL default '',
`description` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `zstock`
--

INSERT INTO `zstock` (`id`, `partnr`, `description`) VALUES
(1, '123', 'bob testing 123'),
(2, 'joncooperpart', 'no stock for this one');







--
-- Table structure for table `zlevels`
--

CREATE TABLE `zlevels` (
`id` int(10) NOT NULL auto_increment,
`partid` varchar(45) NOT NULL default '',
`qty` int(10) NOT NULL default '0',
`locn` varchar(45) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `zlevels`
--

INSERT INTO `zlevels` (`id`, `partid`, `qty`, `locn`) VALUES
(1, '123', 5, '1st van'),
(2, '110', 17, '2nd van'),
(3, '123', 19, '2nd van');

[Antaeus]

To avoid this problem try to replace the sum(zlevels.qty) statement with sum(qty), or provide an alias for the calculated field: sum(zlevels.qty) as zlevels_qty

[jwcgroup]

many thanks,

support for your products is worth every penny of the initial investment.