Field Name same as Table Name

[Choppers]

I have a MySQL 5.0 database called "TestDatabase", which contains a table called "TestDatabase Info". When I try to open with a TZTable, I get the following error:

SQLError : Table 'TestDatabase.TestDatabase' does not exist.

I've checked with several other databases/tables and the always get the same problem i.e

tables names in the format "x y", where x = database name, cannot be opened.

Any ideas ?

[mdaems]

Problem is the space. If you really had a table called Testdatabase the problem would be even worse. Then you would query this table instead of the 'Testdatabase Info' table. Actually, a TZTable is a ZQuery where the 'select * from Testdatabase Info' is generated automatically. As you see : this table name should be quoted.

Please file a bug report at http://zeosbugs.firmos.at/ to make sure we don't forget to fix it. I could do this myself, but in that case you don't get follow up reports.

Mark

[Choppers]

Thanks for the confirmation, i'll do as you suggest.

[mdaems]

Please try the attached source. Can you confirm if this solves your problem?

Mark

[Choppers]

Problem solved.

Although the problem recurs later when I open the table with a TZQuery, but i'd guess you probably knew that.

Don't go out of your for for me, this isn't a mission critical problem, I only noticed it when I was messing around with a sample database that comes with an old version of Crystal Reports.

Thanks.

[mdaems]

Wrong!!
If you open the table with a TZQuery it's your responsibility to do the right quoting as required by mysql.
Use `tablename with spaces` in your query. And that really works. In fact : a ZTable IS a ZQuery and I resolved your problem by adding the backticks to the automatically generated SQL.Text.
From now on you can follow further processing of the patch in the bug tracker.

Mark

[Choppers]

Indeed !!

You've just found a bug in my software for me, Thanks!!. It only appeared because I was testing the updated 'ZAbstractTable.pas', glad I mentioned it now.

Thanks again.

[zippo]

Suggestion: Do not use spaces in table names. Could be a real pain in the ass in database migration (especially to a different type of server)...

[Choppers]

I couldn't agree more zippo.

The database in question was a sample from an old copy of Crystal Report, version 7, to be precise, written way back in 1998. The database was called 'xtreme', and was an MDB.

[mdaems]

Choppers,

I'll have to UNDO this patch. It causes problems when one has to select from a table in an other schema (eg. Postgres) or database (eg. mysql). I'll fix this problem in an other way. When showing this kind of tables in the 'tablename' dropdown, they will be quoted in the list. If somebody wants to fill the field manually or in code, the quoting should be done by the programmer, just like in the case of a normal query.
Something to help you adding quotes when necessary : Connection.DbcConnection.GetMetadata.GetIdentifierConvertor.Quote('x y').

Here's the correcting patch:

Code: Select all

Index: ZAbstractTable.pas
===================================================================
--- ZAbstractTable.pas	(revision 387)
+++ ZAbstractTable.pas	(working copy)
@@ -74,7 +74,6 @@
     procedure SetTableName(const Value: string);
 
   protected
-    procedure SetActive (Value : Boolean); override;
   {$IFDEF WITH_IPROVIDER}
     function PSIsSQLBased: Boolean; override;
     function PSGetTableName: string; override;
@@ -119,29 +118,12 @@
   if FTableName <> Value then
   begin
     FTableName := Value;
-    SQL.Text := '';
+    if Value <> '' then
+      SQL.Text := Format('SELECT * FROM %s', [FTableName])
+    else SQL.Text := '';
   end;
 end;
 
-procedure TZAbstractTable.SetActive(Value: boolean);
-var
-  TempSql : string;
-begin
-  if Value then
-    if FTableName <> '' then
-      begin
-        CheckConnected; // we need a connection to decide about proper table name quoting
-        if not Connection.DbcConnection.GetMetaData.GetIdentifierConvertor.IsQuoted(FTableName) then
-          TempSQL := Format('SELECT * FROM %s', [Connection.DbcConnection.GetMetaData.GetIdentifierConvertor.Quote(FTableName)])
-        else  TempSQL := Format('SELECT * FROM %s', [FTableName]);
-        if SQL.Text <> TempSQL then
-          SQL.Text := TempSQL;
-      end
-    else SQL.Text := '';
-
-  inherited;
-end;
-
 {$IFDEF WITH_IPROVIDER}
 
 {**
Index: ZPropertyEditor.pas
===================================================================
--- ZPropertyEditor.pas	(revision 382)
+++ ZPropertyEditor.pas	(working copy)
@@ -343,7 +343,7 @@
         // Look for the Tables of the defined Catalog and Schema
         ResultSet := Metadata.GetTables(Catalog, Schema, '', nil);
         while ResultSet.Next do
-          List.Add(ResultSet.GetStringByName('TABLE_NAME'));
+          List.Add(Metadata.GetIdentifierConvertor.Quote(ResultSet.GetStringByName('TABLE_NAME')));
       finally
         ResultSet.Close;
       end;
@@ -356,7 +356,7 @@
         // Look for the Tables of the defined Catalog and Schema
         ResultSet := Metadata.GetTables(Connection.Catalog, '', '', nil);
         while ResultSet.Next do
-          List.Add(ResultSet.GetStringByName('TABLE_NAME'));
+          List.Add(Metadata.GetIdentifierConvertor.Quote(ResultSet.GetStringByName('TABLE_NAME')));
       finally
         ResultSet.Close;
       end;

Can you live with that?

Mark

[Choppers]

Fine with me.

Thanks.