Cannot update temporary tables

[firmos]

Hello Mark,

I just wanted to thank you! - because our time is somewhat limited :-(
I am aware that your "Patch" is not the golden solution.
We have a Test Suite (DUnit), wich tests side-effects (best effort).

thanks,

helmut

[zippo]

It seems to work - I'll report eventual bugs here. Thank you, you saved my life!

[anse123]

Thanks a lot for trying to debug to all guys! But sorry I have to disappoint some of you. I have applied the hack from mdaems and get another errormessage when opening a simple resultset with a ZQuery, TDataSource and a TDBGrid :

SQL Error: Unknown table 'channels' in information_schema.

Maybe I'm still doing something wrong, but I don't believe so. I have tried some debugging in the ZDbcGenericResolver.pas and in ZDbcResultSetMetadata.pas but with no effort. I finally had to make a diff of the original 6.5.1 sources with those I tried to debug in order to get the originals back.

What I found curious is this:
There is a function in the ZdbResultSetMetadata which should deliver the tablename, but it doesn't.

Code: Select all

// In ZdbcGenericResolver (Line 253):
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;

Code: Select all

// In ZDbcResultSetMetadata (Line 419):
function TZAbstractResultSetMetadata.GetTableName(Column: Integer): string;
begin
  if not Loaded then LoadColumns;
  Result := TZColumnInfo(FResultSet.ColumnsInfo[Column - 1]).TableName;
end;

In my case, the result of the first function is always a empty string so that I get the SCanNotUpdateThisQueryType exception . The first func calls the second one "GetTableName" and even here the property TableName of TZColumnInfo is empty. So far, so frustrating... Maybe more tomorrow evening.

hmmmm.... maybe this could have something to do with the mysql-version (5.0.18-log) as I saw a db called "INFORMATION_SCHEMA" on my localhost? I will update the mysql-server and let you know...

[anse123]

I just found this in my mysql-query.log:

...
78 Query SHOW TABLES FROM information_schema LIKE 'channels'
78 Query SHOW COLUMNS FROM information_schema.channels LIKE '%'

These queries are fired by ZDbcMysqlMetadata funcs "GetTables" and "GetColumns" and I think "GetKeys". Looks like the current database was set to "information_schema", which is not what I did and this db doesn't have the table called "channels"...

I just discovered that Zeos always uses the first database which is present for all formed updates, deletes and so on. Although the ZConnection.Database is set to the correct dbname, all queries are fired with "insert into firstdbname.correcttablename ...". strange... why does Zeos use a DBname in its statements at all? It could also rely on the ZConnection.Database property? Now I have patched the TZGenericCachedResolver.ComposeFullTableName to never use a DBname. Just commented the 4 lines 242 to 245 out.

[mdaems]

Hi Anse,

Maybe you should check where he's changing the current database to Information_schema. Quick scanning, the code information_schema is only used in PostGresSql.My patch concerns mysql only.

If you find the solution, please send a patch to the testing SVN branch for zeoslib. So we can integrate it.

Mark

[anse123]

ok... ist seems more simple than I thought
- My ZConnection initially has the database-property set to "Information_schema" (this is the first database on the server)
- then a ZQuery executes ('USE ' + secondDBname)
- then another ZQuery executes ('SELECT * FROM ' + tableFromSecondDB)
- here I always got the exception because Zeos always does additional queries when I execute a simple query like "select * from table". For example it wants to fetch the fieldnames and keys from the table ("show keys from db.table"). But this "db" is still set to "Information_schema".

I assume this is a bug in ZeosLib - it should recognize which DB is currently used. But I don't know if my suggestions for not using a dbname at all for the internal queries has no negative side-effects... So should I check this into CVS?

BTW: I don't see a SVN repository, only CVS .
http://sourceforge.net/svn/?group_id=35994 says: "This Project Has Turned Off SVN"

Regards,
Anse

[mdaems]

Hi Anse,

The Sourceforge CVS is not longer in use for development. For the moment we use a SVN repository (svn://www.firmos.at/zeos/).
Most recent patches can be found in the 'testing' branch. (Read more about his in other threads)

Concerning your problem : Probably it's a bug, but why don't you use the database property of the connection component? Also, did you try putting the database name in front of the table name to avoid the prefixing with the wrong database name.

Mark

[anse123]

thanks for the reply, mdaems.
I am setting the database property before I execute the "select ...", but it doesn't seem to effect something. For the moment I have fixed the problem by commenting the 4 lines out and removing database from the internal queries (as described above).

By the way... I just released a first "HeidiSQL" version 3.0 BETA. HeidiSQL ist based on the old MySQL-Front 2.5, which is now using ZeosLib as connector! Have a look here : http://www.heidisql.com/

Regards,
Anse

[mdaems]

I'm watching the mysqlplanet forum, so i've seen HeidiSql and remembered your name. Are you using this alpha version of zeos? Probably you modified it a little to comment out problems as the one above.

Mark

[anse123]

Yes, I am using the alpha 6.5.1 for HeidiSQL

I noticed the ZConnection-property "Catalog". What does it stand for? Is it probably the represention of the current database, which I can set and get rid of all problems I had ? I always thought I have to set the "Database"-property but now I'm not sure.

Anse

[mdaems]

Not sure. It seems a little confusing to me as well.
Is the hierarchy catalog/database/table or database/catalog/tabel or (database or catalog)/table?
Anybody knows?