Temporary tables and MyQuery issues ?

Temporary tables and MyQuery issues ?

Postby swierzbicki » Mon 26 Sep 2005 07:25

Hi,

On my main TMyQuery , I put this SQL Statement :
Code: Select all
Select * from ReperesFE2000Tmp


ReperesFE2000Tmp is a temporary table.
Before opening my TMyQuery, I execute this method (so that tmp table will be created) :

Code: Select all
//ReperesFE2000.CommaText is a Stringlist where id are stored
Procedure CreateReperesFE2000Tmp;
Var MyQuery : TMyquery;
begin
  MyQuery := TMyQuery.create(application);
  MyQuery.Connection := AffairesFCI.MyConnection;
  try
    MyQuery.close;
    MyQuery.SQL.clear;
    MyQuery.SQl.Add('DROP TEMPORARY TABLE IF EXISTS ReperesFE2000Tmp;');
    MyQuery.Execute;

    MyQuery.SQL.clear;
    MyQuery.SQl.Add('CREATE TEMPORARY TABLE IF NOT EXISTS ReperesFE2000Tmp');
    MyQuery.SQl.Add('Select IdRepereFE2000,Designation,(QuantiteProduit - QuantiteOrdreService) as QuantiteReference,0 as QuantiteAAssigner ');
    MyQuery.SQl.Add('from Reperesfe2000 where IdRepereFE2000 in ( '+ReperesFE2000.CommaText+' )');
    MyQuery.Execute;

    MyQuery.SQL.clear;
    MyQuery.SQl.Add('select * from   ReperesFE2000Tmp');
    MyQuery.open;


  finally
    MyQuery.free;
  end;
end;


Now when trying to open my main TMyQuery, I get this error message :

MyDatabaseName.ReperesFE2000Tmp doesn't exists


Any idea ?
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby Ikar » Mon 26 Sep 2005 07:28

Do you use FetchAll = False mode?
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby swierzbicki » Mon 26 Sep 2005 07:47

Hello Ikar :D ,

No Fetchall is set to true.
It looks like connection is "somehow" closed !

FYI , I have 2 forms.
The first forms with my TMyConnection / some TMyQueries / some Grids
The 2nd forms with one TMyquery (with the TempTable SQL statement).

All queries are linked to the TMyConnection located on the main form (1st form).

When displaying the second form I :

- execute my CreateReperesFE2000Tmp method (for creating my temp table)
- open the form2.Myquery (calling the Temporary Table)

I'm geting then : #42S02Table 'MyDatabase.ReperesFE2000Tmp doesn't exists'


Notice that in the CreateReperesFE2000Tmp method, I do a select statement on the temporary table (for testing purpose) :

Code: Select all
    MyQuery.SQL.clear;
    MyQuery.SQl.Add('select * from   ReperesFE2000Tmp');
    MyQuery.open;
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby swierzbicki » Mon 26 Sep 2005 08:12

I just trace the connected user on the MySQL server :

- I launch my program :
1 user is created with connection ID #35

- I execute my createReperesFE2000Tmp method :
The user list doesn't change.
- I open the TMyQuery present on 2nd form :
A new connection ID is created with ID #36

This is the probem ! This new connection shouldn't be created !

When changing the TMyConnection properties :

- Pooling := True;
- Maxpoolsize = 1;


I can do form2.Myquery.open but the application hangs....
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby Ikar » Mon 26 Sep 2005 11:29

Most likely you have two instances of TMyConnection. Please check it.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby swierzbicki » Mon 26 Sep 2005 12:54

HI Ikar.

I haven't Two instance of TMyConnection.

I use one Calculted Field.
You may know that Calculated fields works with Field Definitions.

So, after creating my temporary table, and before opening my main TMyQuery , I need to update the fields definitions.

So, on the OnBefore Open event, I wrote this :

Code: Select all
 
[b] DataSet.FieldDefs.Update  ; <== This create a new connection[/b]
  for i := 0 to DataSet.FieldDefs.Count - 1 do
  //create persistant field that do not exist
  if DataSet.FindField(DataSet.FieldDefs[i].Name) = nil then

      DataSet.FieldDefs.Items[i].CreateField(DataSet);
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby swierzbicki » Tue 27 Sep 2005 10:15

any idea how to solves the DataSet.FieldDefs.Update bug ?
This method create another connection for the given dataset !
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59


Return to MySQL Data Access Components