Temporary tables and MyQuery issues ?

[swierzbicki]

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 ?

[Ikar]

Do you use FetchAll = False mode?

[swierzbicki]

Hello Ikar ,

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]

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....

[Ikar]

Most likely you have two instances of TMyConnection. Please check it.

[swierzbicki]

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]

any idea how to solves the DataSet.FieldDefs.Update bug ?
This method create another connection for the given dataset !