Hello Michael,
I know this post is getting lengthy already and I am sorry, if I am talking to much or get to much into details you already know or anything...
There is no easy solution to this as all solutions may break some software. But on the other hand - that would be software that did not follow the specs from the start. Let me start with the Zeos source code embedded documentation for TZAbstractDatabaseMetadata.GetColumns:
ZDbcMetadata.pas wrote: @param catalog a catalog name; "" retrieves those without a
catalog; null means drop catalog name from the selection criteria
@param schemaPattern a schema name pattern; "" retrieves those
without a schema
@param tableNamePattern a table name pattern
@param columnNamePattern a column name pattern
The table name parameter can be a pattern. This way it is possible to retrieve information for more than one table at a time. This problem is mostly about this possibility.
Now we've the '_' char again in the WildCard-list which is against: ticket #63.
Hmmm - I think
ticket #63 was fixed in R3149. Back then the result of ConstructNameConditon for a Pattern looked like:
Code: Select all
Result := Format('%s like %s',
[Column, EscapeString(WorkPattern+'%')]);
Removing the '_' char from the wildcard list made ConstructNameCondition believe it was a simple comparison and made it construct its result like this:
Code: Select all
Result := Format('%s = %s', [Column, EscapeString(WorkPattern)]);
So - instead of getting something like "where TABELNAME like 'PLU__%' which also returned information for the table PLUSA, Zeos now constructed a statement whith this condition "where TABLENAME = 'PLU__'" that will return only columns for the table PLUU. In my opinion the real fix for this problem would have been to fix the caller to escape the _-chars. Removing _ from the wildchar list is more or less a violation of the specification for the function TZAbstractDatabaseMetadata.GetColumns. I tried to fix this in ZDbcResultSetMetadata.pas by adding the escaping function there.
Please note the % that was appended to the work pattern if it was a pattern at this time. This later on became the culprit for this thread: "
0 record(s) updated. Only one record should have been updated". The solution of this thread was to remove the trailing % and add a trim for Firebird.
The change of ZDbcResultSetMetadata.pas: Could this be done in another way? I wouldn't touch this peace of code.
This change adresses the main problem. Whatever the solution to this problem will be, any caller of TZAbstractDatabaseMetadata.GetColumns should escape the wildchars in the table name if it knows that it wants the columns of exactly one table, even if the table name contains a _ or a %.
Raising a Exception in this case where users are expecting a automation code sonds not so good for me.
This change in ZDbcInterbase6Metadata.pas is an examle to notify callers that there is no safe way to use a pattern for the table name with Firebird 1.5 and 1.0. If you don't like the exception you also can do a downcast of the pattern to a table name by adding the necessary escape characters:
Code: Select all
Result := Inherited ConstructnameCondition(AddEscapeCharToWildcards(Pattern), Column)
This will work under all circumstances but users might wonder why TABLE% doesn't give a result for all columns of TABLEA and TABLEB.
I was thinking about use the "old" code for FB<2.0 and for Interbase i can't judge it. Dali seems to be happy without the TRIM.
The problem with the old code is that there are corner cases for all the old codes that don't work as expected if you use a table name that is a pattern:
Imagine a database with these tables:
ABC
A_C
ABCD
_ enabled as a wildcard and Result := Format('%s like %s', [Column, EscapeString(WorkPattern+'%')]);
This code will fail on the pattern 'A_C' because it returns information about table ABCD where it should only have shown information about ABC and A_C.
_ disabled as a wildcard and Result := Format('%s like %s', [Column, EscapeString(WorkPattern+'%')]);
This code will fail on the pattern 'A%C' because it returns information about ABCD where it should only have shown information about ABC and A_C.
_ disabled as a wildcard and Result := Format('%s like %s', [Column, EscapeString(WorkPattern)]); (% removed from the WorkPattern)
This code will fail on nearly all patterns because it will not return any information at all. Firebird will padd the table names with spaces in its system tables because they use tha char(32) type there. So
"ABC" effectively becomes "ABC" + 29 spaces
"A_C" effectively becomes "A_C" + 29 spaces
"ABCD" effectively becomes "ABCD" + 28 spaces
A comparison with like will not remove these spaces, whereas a comparison with the = operator will. The only solution, I can think of is to remove them by using the trim function. But the trim function is only available for Firebird Versions 2.0 and up. So there is no simple way of doing a pattern search with these servers. Hence I opt to disable it one way or another.
But disabling the possibility to do a search by a pattern for the old Firebird and Interbase servers should not be a big issue, I think. Most users of ConstructNameCondition will most probably only look for one table at a time any way. And if they enter anything wrong the exception would inform them of the problem whereas the downcasting would silently work in a way the user doesn't expect. Usually I am the guy that opts for informing the user that he does something wrong or something is not suported...
I am sorry again for this lengthy post. I hope it will help to make good decisions...
Best regards,
Jan
