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.
How to add quotes `` to the field name
Moderators: gto, cipto_kh, EgonHugeist
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.
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.
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
There is a 'workaround' to quate all field names at once. It's used by the HeidiSql team :
File ZDbcMetaData
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
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;
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
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.
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.