Bug : MyQuery Editor => SQL Generator : Not working with temporary tables

swierzbicki · Post by **swierzbicki** » Thu 26 Jan 2006 11:52

Hi Ikar,

I have some problems with my temporary table.
I put in a temporary table some records (with a prize and quantity)
To really sum up, I let the user modify the Quantity and then update the "physical" table with values changed within the temporary table.

From time to time the user is getting an Update failed : 0 record found.
(strange since he is editing datas from the temporary table).

So, I decided to use my own update SQL Statement by using the SQL Generator.

I found that the SQL generator is not able to works with temporaries table. I guess that you are creating a new connection (for getting table / fields info).
So, in the SQL generator everything is grayed !

Ikar · Post by **Ikar** » Thu 26 Jan 2006 13:35

Yes, it can be a problem.

> From time to time the user is getting an Update failed : 0 record found.

Please specify list of fields (with types), used in WHERE clause.

> I guess that you are creating a new connection (for getting table /
fields info).

We will think about improving this functionality in next MyDAC version. As temporary solution you can write SQLUpdate manually.

swierzbicki · Post by **swierzbicki** » Thu 26 Jan 2006 15:26

Thank you for the feed back.
As you said, I write the SQLUpdate manualy (and it seems to works).

FYI here is my both SQL Statement

Code: Select all

-- Creation Temp table
DROP TEMPORARY TABLE IF EXISTS ReperesTmp;
CREATE TEMPORARY TABLE IF NOT EXISTS ReperesTmp
Select 
 IdRepereAffaireFCI, -- integer autoinc
 IdAffaireFCI, -- integer
 (QuantiteProduit - QuantiteProduction) as QuantiteRepere, -- float
 (QuantiteProduit - QuantiteProduction) as QuantiteAAssigner, -- float
 (PVFUnitairePoseHT + PVFUnitairePRoduitHT + ProrataUnitairePoseHT +ProrataUnitaireProduitHT) as PVFOriginalUnitaireHT, --float
 (PVFUnitairePoseHT + PVFUnitairePRoduitHT + ProrataUnitairePoseHT +ProrataUnitaireProduitHT) as PVFUnitaireHT,  -- float
 (PVFUnitairePoseHT + PVFUnitairePRoduitHT + ProrataUnitairePoseHT +ProrataUnitaireProduitHT) as PVFForfaitaireUnitaireHT,  -- float
 idAffaireFCI as id, -- integer
 IdRepereClient, -- Varchar
 IdDevis, -- integer
 Avenant, -- tinyint
 IdGroupeProduit, -- char
 largeur, -- float
 hauteur, -- float
 Designation, -- varchar
 TauxTVA, -- float
 IdAvenant, -- tinyint
 '' as IdVarianteTechnique, -- char
 QuantitePose -- float
 
from 
 Reperesaffairesfci
where 
 IdRepereAffaireFCI in ( 1,2,3 ) -- integer
 And ((QuantiteProduit - QuantiteProduction)>0) -- float;

The Select query to display my Records from the temp table

Code: Select all

SELECT
   IdRepereAffaireFCI,
   IdAffaireFCI,
   QuantiteRepere,
   QuantiteAAssigner,
   id,
   QuantitePose,
   PVFOriginalUnitaireHT,
   PVFUnitaireHT, 
   PVFForfaitaireUnitaireHT, 
   IdRepereClient, 
   IdDevis, 
   Avenant,
   IdGroupeProduit,
   Largeur,
   Hauteur,
   Designation,
   TauxTVA,
   IdAvenant,
   IdVarianteTechnique
FROM
 reperestmp

As said, with some records i'm getting the Update Error : 0 Records found.
It is funny because i'm fetching distinct records

Ikar · Post by **Ikar** » Thu 26 Jan 2006 16:57

> IdRepereAffaireFCI, -- integer autoinc
> IdRepereAffaireFCI in ( 1,2,3 ) -- float

Does IdRepereAffaireFCI have integer or float data type?
If float it may be source of the problem.

swierzbicki · Post by **swierzbicki** » Thu 26 Jan 2006 21:29

only integer data type

Ikar · Post by **Ikar** » Fri 27 Jan 2006 07:41

We couldn't reproduce the problem.
Please send us (mydac*crlab*com) a complete small sample to demonstrate it and include script to create and fill table.

Also supply us following information

- Exact version of Delphi, C++ Builder or Kylix.
- Exact version of MyDAC.
- Exact version of MySQL server.

swierzbicki · Post by **swierzbicki** » Fri 27 Jan 2006 08:14

I'll will send you (next week) a demo project + Database.