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

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

Postby 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 !
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby 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 :?
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby swierzbicki » Thu 26 Jan 2006 21:29

only integer data type
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby swierzbicki » Fri 27 Jan 2006 08:14

I'll will send you (next week) a demo project + Database.
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59


Return to MySQL Data Access Components