Underscoring the underscore :)

[MJFShark]

Hi All!

Since the various "gettables" , "getprocedures", "getcolumns" and such metadata calls allow passing in a pattern, and the ConstructNameCondition call is often used in creating the data dictionary queries, we run into an issue where any table with an underscore causes a "like" clause instead of an equals clause (which could lead to some incorrect results and possibly slower queries, though I suspect that would be rare.) There are some notes in the code about it in the FillWildCards method, that point to viewtopic.php?f=40&t=13184 and I think we should implement the suggested change (or some other way if it's a problem) and document that only '%' wildcards are allowed in the metadata tablepattern-like arguments.

-Mark

[marsupilami]

Hello Mark,

I am not sure how this popped up for you. Normally the user is expected to escape wildcards if they are part of the object name. Meaning that if you want to use GetColumns and you want to search for 'TABLE_NAME' then you should call it with something like this:

Code: Select all

GetColumns('%', '%', 'TABLE\_NAME', '%').

This assumes that the '\' character is the default escape character for wildcard searches. If you need to generalize this, the use of IZDatabaseMetadata.AddEscapeCharToWildcards is suggested. IZDatabaseInfo.GetSearchStringEscape should return the default escape character for the database in use. Usually ConstructNameCondition can detect these cases (by using HasNoWildcards) and remove the wildcards (by using StripEscape) to form a regular comparision using the '=' operator.

So - usually it is not necessary to disable wildcards. If using escape characters doesn't solve the problem, this might be a bug in Zeos.

On the driver side things can get tricky too. MS SQL Server (and probably others) don't have a default escape character. On these systems one has to add an escape clause in searches. TZMsSqlDatabaseMetadata.UncachedGetColumns does that in the second part. Queries could look like this:

Code: Select all

select ... from COLUMNSTABLE T where T.COLUMNNAME like 'TABLE\_NAME' escape '\';

Best regards,

Jan

[MJFShark]

Hi Jan,

On looking further I see that "internal" metadata calls use AddEscapeCharToWildcards on those arguments, I wasn't aware of that and so was having the issue only in my own testing. I'll look into doing something similar for my "external" metadata calls that don't need wildcards. Thanks for the clarification!

-Mark

[marsupilami]

Erm - you could use AddEscapeCharToWildcards too? This is a service function. It should be available from the Component layer as TZConnection.DbcConnection.GetMetadata.AddEscapeCharToWildcards().

[MJFShark]

Ha! Yes, "something similar" may just be calling that function!

-Mark

[MJFShark]

It looks like there's no workaround for Oracle (and I assume SQLServer?) I suppose it's not that big of an issue, it would be pretty rare to have multiple tables that would match just based on a single underscore wildcard. I guess it is what it is.

-Mark

[marsupilami]

According to https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions016.htm Oracle also doesn't have a default escape character:

If esc_char is not specified, then there is no default escape character.

So, to be able to correctly search we have to modify the oracle driver to use an escape character and specify it when querying for data. On MS SQL Server we use system stored procedures for most of the data. UncachedGetColumns already specifies an escape character when looking up additional data on fields. I don't remember how SQL Server workw with wildcard characters and escaping in the stored procedures. I can only assume that they use '\' automatically there...

[EgonHugeist]

Don't make it so complicated. It's just dblib which uses this syntax. Use StripEscape instead and test with HasNoWildCards if a like or equal search needs to be made...

[MJFShark]

It's certainly an interesting issue (and yes you're correct about Oracle.) Really the metadata 'get' functions all serve double duty. They're either 'get' or 'search' depending on if wildcards are there. It's just that detecting the wildcards is iffy due to SQL's underscore issues lol. I suppose the only real solution (if it needs to handle '_' wildcards that is) is to include a UseWildcards parameter that defaults to true (for backwards compatibility.) But, that's just one possible solution off the top of my head, I'm not sure it's worth it.

By the way, looking through some of this code it seems odd that the Oracle metadata's escape string is set to '\\', and that the default in metadata is '%'.

-Mark
Also, I hope that my comments here aren't seen as negative... this is an amazing library!

[marsupilami]

Don't make it so complicated. It's just dblib which uses this syntax. Use StripEscape instead and test with HasNoWildCards if a like or equal search needs to be made...

Asuming the following:
You have six tables: TABLE_NAME_A, TABLE_NAME_B, TABLE_NAME_AA, TABLE_NAME_BB, TABLExNAME_A, TABLExNAME_B. You want to get metadata for TABLE_NAME_A and TABLE_NAME_B only in one go. So your pattern could look something like this:

'TABLE\_NAME\__'

The second underscore is there to make sure, that you only get tables with A and B and not AA or BB...

How is that going to work without using

... like 'TABLE\_NAME\__' escape '\'

on Oracle?

[MJFShark]

It's also odd that the Oracle SupportsNonEscapedSearchString has a client version check. I don't think it ever supported an escape string without the escape '\' clause, regardless of the server or client version.

-Mark

[EgonHugeist]

@Jan
seems correct. I just thought about SQL Server: https://docs.microsoft.com/en-us/sql/t- ... rver-ver15 Escape:

Code: Select all

[_] or [%]

this syntax is known on any Sybase too. But if a server does'nt support it.. We need the escape syntax, of course.