Page 1 of 1
How to add quotes `` to the field name
Posted: 07.05.2007, 05:28
by softshape
Hi all,
I have a table with a field called 'limit'. Zeos causes an exception on insert action because 'limit' is a reserved word and I need to quote it as `limit`. But how to do that ? I use DBEdit linked with the 'limit' field and can't add quotes to its FieldName property, as it causes 'field not found' error.
Preferably, I'd like to quote all the field names in SQL generated by Zeos. Is there such an option ?
Thanks,
Jury.
Posted: 07.05.2007, 10:03
by kmr
Couldn't you use TZQuery instead of TZTable?
You could build an SQL-statement like:
SELECT ...., 'limit' [as] new_limit
FROM ....
WHERE ...
So your field name would be new_limit instead of 'limit'.
If you have to use [as] (without the [ ]) depends on the SQL dialekt.
This idea is really a workaround for only some fields, if want to quote all fields, I have no idea how to solve that.
Posted: 07.05.2007, 13:34
by softshape
It really looks like a workaround; TZTable, in my application and in general, is quite convenient in other aspects and I don't want to drop it.
Posted: 08.05.2007, 01:04
by mdaems
There is a 'workaround' to quate all field names at once. It's used by the HeidiSql team :
File ZDbcMetaData
Code: Select all
function TZDefaultIdentifierConvertor.Quote(Value: string): string;
var
QuoteDelim: string;
begin
Result := Value;
- if IsCaseSensitive(Value) then
- begin
- QuoteDelim := Metadata.GetIdentifierQuoteString;
- if Length(QuoteDelim) > 1 then
- Result := QuoteDelim[1] + Result + QuoteDelim[2]
- else if Length(QuoteDelim) = 1 then
- Result := QuoteDelim[1] + Result + QuoteDelim[1];
- end;
+ QuoteDelim := Metadata.GetIdentifierQuoteString;
+ if Length(QuoteDelim) > 1 then
+ Result := QuoteDelim[1] + Result + QuoteDelim[2]
+ else if Length(QuoteDelim) = 1 then
+ Result := QuoteDelim[1] + Result + QuoteDelim[1];
end;
This is a manual diff, so don't try to apply it using a tool.
Also : this is not a zeoslib team recommendation. I think it just works.
It should be possible to add the reserved mysql words somewhere, so that they are escaped as well. No idea how.
Mark
Posted: 08.05.2007, 02:16
by softshape
I've found the reason looking at IsCaseSensitive function. It has inernal list of reserved MySQL words, and 'limit' was not there. The reserved word list is much bigger actually -
http://dev.mysql.com/doc/refman/5.0/en/ ... words.html
- about 250 words, while this function lists only 27. I'm afraid this is a mistake and should be corrected by developer.
So far I've simply added 'limit' to this list and my program works fine now.
Posted: 08.05.2007, 09:55
by mdaems
Can you please track this issue in the bugtracker (and please include the link to the manual).
I want to have a look at it as soon as I have some time.
Mark
Posted: 09.05.2007, 14:42
by mdaems
I had a look at it. I updated the reserved words and functions lists for mysql up to version 5.1. Committed in SVN Testing Branch Revision 244.