Incorrect error "Update Failed On Complex Query"

duzenko · Post by **duzenko** » 22.06.2009, 12:09

There are 2 tables in my DB: PLUSA and PLU__

In program, there is TZTable connected to PLU__. When I try to post updates to it, it gives "Cannot update a complex query with more then one table"

Under debugger, I can see that in TZGenericCachedResolver.DefineTableName some columns have tablename = PLU__ and some are PLUSA.

How can I resolve this problem (except that try to rename PLU__ table)?

P.S. When opening table, in TZAbstractResultSetMetadata.ReadColumnByName table names are already wrong

That is, some columns are from PLUSA (those with the same name with PLU__ table)

I suspect that there is a select from a system table, something like "select field_name where table_name like plu__" and it returns plusa fields

duzenko · Post by **duzenko** » 22.06.2009, 12:43

Inserting

Result := true; Exit;

into the beginning of TZInterbase6DatabaseMetadata.HasNoWildcards seems to help

Post by **mdaems** » 23.06.2009, 23:17

Yes, I suppose that would help for your case...

Can you find some time to find out where exactly TZAbstractResultSetMetadata.GetTables and TZAbstractResultSetMetadata.GetColumns are called from. I think it might be sufficient to use the AddEscapeCharToWildcards metadatafunction before passing the tablename to the lookup functions. My wild guess is that this might happen in the parsesql or zdbcresultsetmetadata units.
You can find more information on this topic at http://fisheye2.atlassian.com/changelog/zeos?cs=266 and the link to the bug report you find there.

I have no time at the moment to look into this myself. Sorry.
If you don't wan us to forget about it and you can't spend the time yourself, please file a bug report at http://zeosbugs.firmos.at/

Mark

duzenko · Post by **duzenko** » 30.06.2009, 10:41

mdaems,

I got a screenshot for you

Post by **seawolf** » 01.07.2009, 21:50

Hi, I suppose you are using Zeos 6.6.5 .. today I tried a small test
with the lastest svn version and it looks like to work correctly.
Have you tried with that version (doing a quick test)?

duzenko · Post by **duzenko** » 02.07.2009, 15:03

seawolf, do you suggest to update to svn version? I got a few other patches done by myself and I don't want to lose them

Post by **seawolf** » 02.07.2009, 22:33

That problem is database consider char like '_' and '%' like wildchar so procedure detect 2 of '_' like a wildchar and then do a search for all the tables which begin with the name 'plu'. You can:

1. rename table plu__ or plusa
2. change FillWildcards procedure, contained in ZdbcMetadata.pas

procedure TZAbstractDatabaseMetadata.FillWildcards;
begin
try
SetLength(WildcardsArray,2);
WildcardsArray[0]:='_';
WildcardsArray[1]:='%';
except
WildcardsArray:=nil;
end;
end;

removing line WildcardsArray[0]:='_';

But bear in mind by this way you avoid a problem that could be raised in
other parts

Post by **mdaems** » 08.07.2009, 22:13

duzenko,

I think the problem is in the way TZAbstractResultSetMetadata.GetTableColumns calls Metadata.GetColumns. Try changing the call

Code: Select all

    Result := Metadata.GetColumns(TableRef.Catalog,
      TableRef.Schema, TableRef.Table, '');

to

Code: Select all

    Result := Metadata.GetColumns(TableRef.Catalog,
      TableRef.Schema, Metadata.AddEscapeCharToWildcards(TableRef.Table), '');

or something similar. (This is untested code but you may get the idea...)

Please, test it. If it works I'll add your patch to the official Zeoslib code base.

Mark

duzenko · Post by **duzenko** » 06.01.2011, 16:30

Hello everyone
Just updated to the latest testing sources, and the problem is still there
Can we change '_' to any other character in TZAbstractDatabaseMetadata.FillWildcards?

Post by **mdaems** » 13.01.2011, 21:46

Please, test it. If it works I'll add your patch to the official Zeoslib code base.

I didn't get an answer from your tests, so we didn't change anything.
Please provide a nice and working solution for this specific problem. As there's no real development team at the moment, this will have to be fixed by the community.

Mark

duzenko · Post by **duzenko** » 17.01.2011, 12:51

My solution was to replace "_" with "&" - works fine for me so far!

Initially I was thinking about "$" but then I remembered about the rdb$ stuff and switched to "&"

Post by **mdaems** » 26.01.2011, 23:48

duzenko,

Did you try this?

I think the problem is in the way TZAbstractResultSetMetadata.GetTableColumns calls Metadata.GetColumns. Try changing the call
Code:
Result := Metadata.GetColumns(TableRef.Catalog,
TableRef.Schema, TableRef.Table, '');

to
Code:
Result := Metadata.GetColumns(TableRef.Catalog,
TableRef.Schema, Metadata.AddEscapeCharToWildcards(TableRef.Table), '');

or something similar. (This is untested code but you may get the idea...)

Because this function *should* escape the '_' in a way it's not handled as a pattern anymore.
Have a look at http://zeosbugs.firmos.at/view.php?id=28, and more specifically to comment 111 there.

Mark