Cannot update temporary tables

zippo · Post by **zippo** » 25.03.2006, 00:34

Hi!

I have a problem with Zeos 6.5.1 and MySQL temporary tables.
If I try to post something into them via the TDataset.Edit/post method Delphi raises an exception like "Cannot update this query type".

Tables are dynamically created during run-time (on startup) and are used as temporary stockage of data. The funny thing is that if I create the same tables without the "Temporary" directive it works perfectly! Any ideas?

Ah, the code snippet of the table creatin in could help:

Code: Select all

    ExecQuery(frmMain.qryWork,'Drop Table If Exists TMP_POSTAVKE');
    ExecQuery(frmMain.qryWork,'Create '+TblType+' Table If Not Exists TMP_POSTAVKE Select * From IZH_POSTAVKE Where DOK_ID=999999');
    ExecQuery(frmMain.qryWork,'Alter Table TMP_POSTAVKE Add Primary Key (DOK_TIP,DOK_ID,ARTIKEL_ID,UNIQ)');

Notes:
- TblType can be "" or " Temporary ". if "" no problem, if "Temporary", exception raises after data post in dataset.
- ExecQuery() is just a shortage for Query.SQL.Text := X; Query.ExecSQL or Open (depends on the query type);

I think it's an important feature so please developers, could you take a look on this, please. I need it very much, so I'll try to debug, but I'm not an expert in Zeos, so help is needed very much.

zippo · Post by **zippo** » 25.03.2006, 19:21

No one has this problem?

I diiscovered the problem in this procedure (unit ZDbcGenericResolver)

Code: Select all

function TZGenericCachedResolver.DefineTableName: string;
var
  I: Integer;
  Temp: string;
begin
  Result := '';
  for I := 1 to Metadata.GetColumnCount do
  begin
    Temp := ComposeFullTableName(Metadata.GetCatalogName(I),
      Metadata.GetSchemaName(I), Metadata.GetTableName(I));
    if (Result = '') and (Temp <> '') then
      Result := Temp
    else if (Result <> '') and (Temp <> '') and (Temp <> Result) then
      raise EZSQLException.Create(SCanNotUpdateComplexQuery);
  end;
  if Result = '' then
    raise EZSQLException.Create(SCanNotUpdateThisQueryType);
end;

The variables Temp and Result never changes from '' (empty string), so the exception is raised at the end of the procedure - why are the vars always empty? Why are the temporary tables invisible to Zeos?

zippo · Post by **zippo** » 25.03.2006, 20:00

I also found a bugreport http://sourceforge.net/tracker/index.ph ... tid=415824

where the object Metadata is filled with table/schema names? I just can't find it..

zippo · Post by **zippo** » 26.03.2006, 22:19

I would really need help here - if any Zeos developer is reading this, please give me a hand. ported a project from 5.x to 6.5.1 and I'm stuck because of this bug.

Hope there's a quick fix for this.

anse123 · Post by **anse123** » 30.03.2006, 21:19

I just wanted to say that I still got the same problem and I wonder why so few others seem to have this problem with the "Cannot update this query type". So please, if someone knows something more on that, please help us!

Anse

zippo · Post by **zippo** » 30.03.2006, 22:18

@Anse123: I solved some of the situations, try to send be a piece of code (+- 30 rows around the problem) and I can analyze it, if you want. Maybe i can help you (but not if temporary tables)..

PLEASE can anyone help me with Temp tables?

mangwills · Post by **mangwills** » 31.03.2006, 04:18

I've encountered the same problem, and everytime I work with temporary mysql tables, I have to use TZUpdateSQL to update them.

Is this somehow related to the problem that Mysql does not show temporary tables when issued the "SHOW TABLES" statement? (i.e. no metadata for temporary tables were obtained...)

Post by **firmos** » 31.03.2006, 06:52

Hi,

we are still in the phase of forming a new development team
- so things are really progressing too slowly right now :-(
but i hope i can have a look at this issues in the next days.

For now: If mysql can't deliver metadata for the table - zeos is not able to do
the update automatically - so the usage of the TZupdateSQL Component is mandatory !

greets,

firmos

zippo · Post by **zippo** » 31.03.2006, 07:42

MySQL command "Show Tables" doesn't show temporary tables - this could be the source for the problem, right?

anse123 · Post by **anse123** » 31.03.2006, 19:28

Hi there,

ok, my code is as follows, a little bit simplified:

Code: Select all

ZQuery2.Close;
ZQuery2.SQL.Clear;
ZQuery2.SQL.Add( 'SELECT * FROM ' + ActualTable );
ZQuery2.SQL.Add( 'WHERE ' + trim(Memo3.Text) );
ZQuery2.SQL.Add('LIMIT ' + intToStr(mainform.UpDownLimitStart.Position) + ', ' + intToStr(mainform.UpDownLimitEnd.position) );
try
  ZQuery2.Open;
except
  on E:Exception do
  begin
    MessageDlg(E.Message , mtError, [mbOK], 0);
    ZQuery2.Active := false;
    Screen.Cursor := crDefault;
    exit;
  end;
end;

This code is working fine so far. Further I have a TDataSource and a TDBGrid attached to ZQuery2. Now I have attached a TZUpdateSQL to the ZQuery2. I try to post some update by editing in the DBGrid and I get the message "SQL Query: Query was empty." ... ???

Anse

anse123 · Post by **anse123** » 01.04.2006, 20:50

mangwills wrote:I've encountered the same problem, and everytime I work with temporary mysql tables, I have to use TZUpdateSQL to update them.

OK, now I am also using a TZUpdateSQL for UPDATE-Statements in order to get rid of the "cannot update this query type"-problem. But the paramater-substitution in UpdateSQL is not done automatically. I set the ModifySQL for example to this:

Code: Select all

Update table1 set foo = :foo where bar = :OLD_bar

At the time when I apply an update through the DBGrid, I get an SQL-error because the executed command has still these parameters in it. (foo = :foo and so on). If I am not misunderstanding the documentation parameter substitution should be done automatically?

Notice: ZQuery.CachedUpdates is set to false

Anse

zippo · Post by **zippo** » 02.04.2006, 08:11

Hey, developers, is there any way to disable table existance checking in Zeos - this could be a temporary solution. Just show where to disable it, we will do it, test it and report any suggestions!

Post by **mdaems** » 02.04.2006, 21:51

Zippo,

Probably I have a (more or less dirty) patch for your problem. I have taken the source from the SVN directory and added 1 line to ZDbcMySqlMetadata.pas. I'll try to attach the diff file and the full version.(I don't know what version you are using) I hope it works for you. It did in my example.

The reason of this failure definitely is the missing temporary tables in the 'show tables' command. Mysql bug? Some would say feature I suppose...

Mark

Post by **firmos** » 02.04.2006, 22:45

Thanks for supplying a patch mdaems !

firmos

Post by **mdaems** » 03.04.2006, 08:23

Firmos,
It's not a real patch!! It's a dirty way to shortcut code.
What I essentially do is telling ZEOS 'if you can't find a table, well... believe it's there, honestly'.

So, if you wan't to integrate it in the source tree, I would advise to add some configuration parameter like 'USE_MYSQL_TEMP_TABLES' to enable my trick.
Also, if you have somebody who can verify the effects on the whole component suite, you'd better ask him to check.

Zippo,
Do you confirm if it's working for you? So Firmos knows if it's worth the effort of integrating.

Mark