Oracle false complex query error

The forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
Post Reply
CompsSQL
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 03.03.2012, 12:48

Oracle false complex query error

Post by CompsSQL »

I have an Oracle database that contains 2 tables :
- T_ALERT
- T_ALERT_ENVIRONMENT
The first field in both tables is identical : ID (integer)

When I perform this query : select * from SYAL.T_ALERT
then I get the following error : cannot update a complex query with more then one table.
This is obviously not a complex query
(sidenote: I think the "then" in this error message should be "than")

When I debug the code (rev 3115) a little, in TZGenericCachedResolver.DefineTableName :
- for the first field, it returns that field "ID" belongs to table "T_ALERT_ENVIRONMENT" => NOT OK
- for the second field (which is unique for T_ALERT), it finds "T_ALERT" => OK
This then triggers the SCanNotUpdateComplexQuery exception.
The “TableRef.Table” in TZAbstractResultSetMetadata.GetTableColumns contains, correctly, “T_ALERT”

FYI : if I use this query : select * from SYAL.T_ALERT_ENVIRONMENT, then everything executes correctly
miab3
Zeos Test Team
Zeos Test Team
Posts: 1310
Joined: 11.05.2012, 12:32
Location: Poland

Re: Oracle false complex query error

Post by miab3 »

@CompsSQL,

I suppose that this is a problem names with underscores.

Try:
In:
ZDbcMetadata.pas

in function:
TZAbstractDatabaseMetadata.ConstructNameCondition

replace:
Format('%s like %s',

to:
Format('%s = %s',

and write what's changed !!!

It might touch:
Oracle, Firebird, PostgreSQL and MySQL/MariaDB (and it affects versions 7.1 and 7.2).

Michal
Last edited by miab3 on 17.05.2014, 10:41, edited 1 time in total.
CompsSQL
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 03.03.2012, 12:48

Re: Oracle false complex query error

Post by CompsSQL »

as far as I can tell, underscores are allowed in TableNames in all databases, but the %-sign is not (not even if you escape the table name with brackets)
So instead of changing the "like" to "=", I opted for a more subtle solution :

in ZDbcMetadata.pas, in TZAbstractDatabaseMetadata.ConstructNameCondition, there is a call to “HasNoWildcards”
This HasNoWildCards method is unique for ConstructNameCondition (I checked, it is not used anywhere else).
I have changed the TZAbstractDatabaseMetadata.HasNoWildcards implementation slightly :
changed
WildcardsSet := GetWildcardsSet;
to
WildcardsSet := ['%'];

This solution still checks for the %-sign wildcard, but no longer for the underscore-wildcard.
This did solve the problem.
miab3
Zeos Test Team
Zeos Test Team
Posts: 1310
Joined: 11.05.2012, 12:32
Location: Poland

Re: Oracle false complex query error

Post by miab3 »

@CompsSQL,

Your solution looks safer.

Michal
Post Reply