Devart Forums

Posted: **Tue 28 Mar 2006 15:51**

Hi,

Delphi 2006 SP1
CrLab MyDAC 4.30
MySQL 4.1.12

I've filled my SQL query with this :

Drop Temporary Table If Exists  tmpSitTranches;
CREATE Temporary Table tmpSitTranches
SELECT
  `tranches`.IDOrdreService,
  `tranches`.IDTranche,
  `tranches`.IdRepereTranche,
  `tranches`.Commentaire ,
  `tranches`.PVFForfaitaireTotalHT ,
   1 as IdRef
FROM
  `tranches`
WHERE
  (`tranches`.IdStade = 27);

Insert into  tmpSitTranches
SELECT
  `ordresservice`.IDAffaireFCI,
  `ordresservice`.IDOrdreService,
  `ordresservice`.IdRepereOrdreService,
  `ordresservice`.Commentaire ,
  `ordresservice`.PVFForfaitaireTotalHT ,
   0 as IdRef
FROM
  `ordresservice`
WHERE
  `ordresservice`.IDOrdreService in
  (select IDOrdreService from `tranches` WHERE (IdStade = 27));

Alter Table tmpSitTranches
Add COLUMN `Id` int(11) NOT NULL auto_increment,
Add PRIMARY KEY  (`Id`);

Select * from tmpSitTranches;

The SQL Query is working when passing it in the mysql command line + other MySQL DB tools...

When Setting my MyQuery.ACtive:=true, i'm getting this error :
ReceiveHeader: Net packets out of order: received[48], expected[1].

Here is the structure of my both tables :

Code: Select all

CREATE TABLE `ordresservice` (
  `IDOrdreService` int(11) NOT NULL auto_increment,
  `IDAffaireFCI` int(11) default NULL,
  `IdDevis` int(11) default NULL,
  `IdEtude` int(11) default NULL,
  `IDCommercial` varchar(4) default NULL,
  `IDAgence` char(3) default NULL,
  `IdClientFCI` int(11) default NULL,
  `DateCreation` datetime default NULL,
  `DateModification` datetime default NULL,
  `Utilisateur` varchar(16) default NULL,
  `ModificationUtilisateur` varchar(16) default NULL,
  `IdRepereOrdreService` int(11) default NULL,
  `IdStade` int(11) default NULL,
  `PVFTotalHT` float(9,3) default NULL,
  `DatePremiereSituationOS` datetime default NULL,
  `NbMoisDeroulementOS` int(11) default NULL,
  `QuantitePose` float(9,3) default NULL,
  `DateReceptionOS` datetime default NULL,
  `PVFForfaitaireTotalHT` float(9,3) default NULL,
  `PVFOriginalTotalHT` float(9,3) default NULL,
  `PRETotalProduitHT` float(9,3) default NULL,
  `IdClientFE2000` varchar(10) default NULL,
  `Commentaire` varchar(30) default NULL,
  `PRETotalPoseHT` float(9,3) default NULL,
  `PVFTotalPoseHT` float(9,3) default NULL,
  PRIMARY KEY  (`IDOrdreService`),
  KEY `IDAffaireFCI` (`IDAffaireFCI`),
  KEY `IdEtude` (`IdEtude`),
  KEY `IdDevis` (`IdDevis`),
  KEY `IDOrdreService` (`IDOrdreService`),
  KEY `DatePremiereSituationOS` (`DatePremiereSituationOS`),
  KEY `DateReceptionOS` (`DateReceptionOS`),
  KEY `IDAgence` (`IDAgence`),
  KEY `IdClientFE2000` (`IdClientFE2000`),
  KEY `IDAffaireFCI_2` (`IDAffaireFCI`,`IdClientFE2000`),
  CONSTRAINT `ordresservice_fk` FOREIGN KEY (`IDAffaireFCI`) REFERENCES `affairesfci` (`IdAffaireFCI`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `ordresservice_fk1` FOREIGN KEY (`IdClientFE2000`) REFERENCES `affairesfci` (`IdClientFE2000`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code: Select all

CREATE TABLE `tranches` (
  `IDTranche` int(11) NOT NULL auto_increment,
  `IDOrdreService` int(11) default NULL,
  `IDAffaireFCI` int(11) default NULL,
  `IdDevis` int(11) default NULL,
  `IdEtude` int(11) default NULL,
  `IDCommercial` varchar(4) default NULL,
  `IDAgence` char(3) default NULL,
  `IdClientFCI` int(11) default NULL,
  `DateCreation` datetime default NULL,
  `DateModification` datetime default NULL,
  `Utilisateur` varchar(16) default NULL,
  `ModificationUtilisateur` varchar(16) default NULL,
  `IdRepereTranche` int(11) default NULL,
  `IdStade` int(11) default NULL,
  `PVFTotalHT` float(9,3) default NULL,
  `QuantitePose` float(9,3) default NULL,
  `PVFForfaitaireTotalHT` float(9,3) default NULL,
  `PVFOriginalTotalHT` float(9,3) default NULL,
  `PRETotalProduitHT` float(9,3) default NULL,
  `IdClientFE2000` varchar(10) default NULL,
  `Commentaire` varchar(30) default NULL,
  `PRETotalPoseHT` float(9,3) default NULL,
  `PVFTotalPoseHT` float(9,3) default NULL,
  PRIMARY KEY  (`IDTranche`),
  KEY `IDAffaireFCIFe2000OS` (`IDOrdreService`),
  KEY `IDAffaireFCI` (`IDAffaireFCI`),
  KEY `IdEtude` (`IdEtude`),
  KEY `IdDevis` (`IdDevis`),
  KEY `IdClientFE2000` (`IdClientFE2000`),
  KEY `IDAffaireFCI_2` (`IDAffaireFCI`,`IdClientFE2000`),
  CONSTRAINT `tranches_fk` FOREIGN KEY (`IDAffaireFCI`) REFERENCES `affairesfci` (`IdAffaireFCI`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How can I make this working ?

Posted: **Tue 28 Mar 2006 16:06**

Strange, really strange, after disconnecting from the DB (Myconnection.connected:=false) and the activating the MyQuery again I've got 'time out' error , then 'transaction pending' error....

Disconnecting again solves the problem !

Posted: **Wed 29 Mar 2006 12:04**

It looks like the bug that was fixed in the latest version of MyDAC (4.30.0.11). Try to download it and run this query once more. If the problem persists, please send us (mydac*crlab*com) a complete small sample to demonstrate it, including script to create and fill table.

Posted: **Thu 30 Mar 2006 11:52**

Hi Anteus,

I got this with the latest build.

FWIW , I was also editing the 'ordresservice' table with a MySQL DB Manager tool while working in Delphi IDE...

Posted: **Thu 30 Mar 2006 15:40**

Unfortunately we couldn't reproduce the problem using new build of MyDAC. The information you have reported isn't enough. Please try to make complete small sample that shows the problem and send it to mydac*crlab*com.