Zeoslib Portal

Posted: **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.

Posted: **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?

Posted: **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..

Posted: **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.

Posted: **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

Posted: **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?

Posted: **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...)

Posted: **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

Posted: **31.03.2006, 07:42**

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

Posted: **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

Posted: **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

Posted: **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!

Posted: **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

Posted: **02.04.2006, 22:45**

Thanks for supplying a patch mdaems !

firmos

Posted: **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

Zeoslib Portal

Cannot update temporary tables

Cannot update temporary tables