Page 1 of 1
Field Name same as Table Name
Posted: 13.02.2008, 13:25
by 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 ?
Posted: 24.02.2008, 14:14
by 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
Posted: 24.02.2008, 17:06
by Choppers
Thanks for the confirmation, i'll do as you suggest.
Posted: 25.02.2008, 14:38
by mdaems
Please try the attached source. Can you confirm if this solves your problem?
Mark
Posted: 25.02.2008, 15:34
by 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.
Posted: 25.02.2008, 16:20
by 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
Posted: 25.02.2008, 16:52
by 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.
Posted: 03.03.2008, 23:38
by 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)...
Posted: 04.03.2008, 00:01
by 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.
Posted: 27.07.2008, 11:35
by 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
Posted: 27.07.2008, 18:53
by Choppers
Fine with me.
Thanks.