Cannot update a complex query with more then one table

[josimarz]

Hello!

I have an Oracle database with three users/schemas. The three schemes have similar structures. I'll call them Joe, MAX and RYO.

My application access the database using the component TZConnection.
In particular form have a TZQuery with a simple SQL:

Code: Select all

SELECT * FROM EXAMPLE

The user clicks the ADD button and the query is placed in insert mode:

Code: Select all

Query.Insert;

After filling out the information the user clicks the save button and runs POST:

Code: Select all

Query.Post; 

Accessing the application with the JOE user when performing the insertion everything runs normally.

With the MAX user error is raised in the DefineTableName method of the class TZGenericCachedResolver in unit ZDbcGenericResolver:

"Can not update a complex query with more then one table"

When traveling in Metadata property, there are two schemes: JOE and MAX. The EXAMPLE table exists in both schemes.

Then I accessed the application with the user RYO. In the first test, we found the method TZGenericCachedResolver.DefineTableName JOE schemes and MAX, but not the schema RYO. And the error was raised:

"Can not update a complex query with more then one table"

After I changed the SQL statement to fetch the fields explicitly:

Code: Select all

SELECT CD_EXAMPLE, DS_EXAMPLE, NM_EXAMPLE FROM EXAMPLE;

When running the insertion error not occurred, but the record was inserted in the scheme JOE.

I tried to debug the code to identify a possible bug, but without success.

The Lords could help me?

Josimar Zimermann

[josimarz]

Hello!

Debugging the code Zeos I found some useful information. The method TZOracleDatabaseMetadata.UncachedGetColumns in ZDbcOracleMetadata unit runs on an SQL statement that fetches the information table in the SYS Schema:

Code: Select all

SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
      + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
      + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
      + ' FROM SYS.ALL_TAB_COLUMNS'
      + ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
      + ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
      + ToLikeString(ColumnNamePattern) + '''';

Debugging the code, I noticed that the SchemaPattern variable is always empty this point. So I changed the SQL statement to filter the desired SCHEMA:

Code: Select all

var
  SQL, LSchemaPattern: string;
begin
    LSchemaPattern := 'MYSCHEMA';
    Result := ConstructVirtualResultSet(TableColColumnsDynArray);

    SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
      + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
      + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
      + ' FROM SYS.ALL_TAB_COLUMNS'
      + ' WHERE OWNER LIKE ''' + ToLikeString(LSchemaPattern) + ''' AND TABLE_NAME LIKE '''
      + ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
      + ToLikeString(ColumnNamePattern) + '''';

After completing a test in my application and it worked properly.

I think some stage before the execution of this routine information SchemaName not been completed correctly. Here are the routines performed previously:

Code: Select all

TZAbstractResultSetMetadata.LoadColumns
TZAbstractResultSetMetadata.ReplaceStarColumns
TZAbstractResultSetMetadata.GetTableColumns
TZAbstractDatabaseMetadata.GetColumns
TZOracleDatabaseMetadata.UncachedGetColumns

I believe we now have a starting point to find out where the BUG.

Thanks!

Josimar Zimermann

[josimarz]

I found an apparent solution to the problem.
The UncachedGetColumns method in TZOracleDatabaseMetadata class in ZDbcOracleMetadata unit uses the ALL_TAB_COLUMNS VIEW in the SYS SCHEMA.
According to a handbook for the Oracle DBA, VIEWS that start with "ALL_" "accessible by all users, giving all information of all objects accessible by a user. "
The VIEWS that start with USER_ "quelque are accessible by the user, providing information about its objects. "

I changed the SQL to query the USER_TAB_COLUMNS VIEW. However, this VIEW does not have OWNER column. Therefore, some changes were needed in the SQL query and the call to GetString was necessary to reduce the value of all indices smaller than 3:

Code: Select all

function TZOracleDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
var
  SQL: string;
begin
    Result := ConstructVirtualResultSet(TableColColumnsDynArray);
    {
    SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
      + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
      + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
      + ' FROM SYS.ALL_TAB_COLUMNS'
      + ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
      + ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
      + ToLikeString(ColumnNamePattern) + '''';
    }
    { Changed }
    SQL := 'SELECT NULL, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
      + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
      + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
      + ' FROM SYS.USER_TAB_COLUMNS'
      + ' WHERE TABLE_NAME LIKE ''' + ToLikeString(TableNamePattern)
      + ''' AND COLUMN_NAME LIKE ''' + ToLikeString(ColumnNamePattern) + '''';

    with GetConnection.CreateStatement.ExecuteQuery(SQL) do
    begin
      while Next do
      begin
        Result.MoveToInsertRow;
        Result.UpdateNull(1);
        // Result.UpdateString(2, GetString(2)); CHANGED
        Result.UpdateString(3, GetString(2)); // Changed 3 to 2
        Result.UpdateString(4, GetString(3)); // Changed 4 to 3
        // Changed 6 to 5, 9 to 8, 10 to 9
        Result.UpdateInt(5, Ord(ConvertOracleTypeToSQLType(
          GetString(5), GetInt(8), GetInt(9))));
        Result.UpdateString(6, GetString(5)); // Changed 6 to 5
        Result.UpdateInt(7, GetInt(6)); // Changed 7 to 6
        Result.UpdateNull(8);
        Result.UpdateInt(9, GetInt(8)); // Changed 9 to 8
        Result.UpdateInt(10, GetInt(9)); // Changed 10 to 9

        if UpperCase(GetString(10)) = 'N' then
        begin
          Result.UpdateInt(11, Ord(ntNoNulls));
          Result.UpdateString(18, 'NO');
        end
        else
        begin
          Result.UpdateInt(11, Ord(ntNullable));
          Result.UpdateString(18, 'YES');
        end;

        Result.UpdateNull(12);
        Result.UpdateString(13, GetString(12)); // Changed 13 to 12
        Result.UpdateNull(14);
        Result.UpdateNull(15);
        Result.UpdateNull(16);
        Result.UpdateInt(17, GetInt(16)); // Changed 17 to 16

        Result.UpdateNull(19);   //AUTO_INCREMENT
        Result.UpdateBoolean(20, //CASE_SENSITIVE
          GetIdentifierConvertor.IsCaseSensitive(GetString(3))); // Changed 4 to 3
        Result.UpdateBoolean(21, True);  //SEARCHABLE
        Result.UpdateBoolean(22, True);  //WRITABLE
        Result.UpdateBoolean(23, True);  //DEFINITELYWRITABLE
        Result.UpdateBoolean(24, False); //READONLY

        Result.InsertRow;
      end;
      Close;
    end;
end;

Initial tests showed positive results. I'll continue testing and will communicate any new developments.

Tanks!
Josimar

[josimarz]

Hello!

I have some more useful information about this error. When told explicitly the table schema in SQL, the TZOracleDatabaseMetadata.UncachedGetColumns method receives information from the scheme in the parameter SchemaPattern:

Code: Select all

SELECT * FROM RYO.EXAMPLE

One option is to check if the variable is empty SchemaPattern this point and more convenient to run the query:

Code: Select all

function TZOracleDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
var
  SQL: string;
begin
    Result := ConstructVirtualResultSet(TableColColumnsDynArray);

    // Here check if SchemaPattern is empty
    // Query from USER_TAB_COLUMNS and OWNER column is empty
    if SchemaPattern = '' then
      SQL := 'SELECT NULL, '''' OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
      + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
      + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
      + ' FROM SYS.USER_TAB_COLUMNS'
      + ' WHERE TABLE_NAME LIKE ''' + ToLikeString(TableNamePattern)
      + ''' AND COLUMN_NAME LIKE ''' + ToLikeString(ColumnNamePattern) + ''''
    else
      SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
        + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
        + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
        + ' FROM SYS.ALL_TAB_COLUMNS'
        + ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
        + ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
        + ToLikeString(ColumnNamePattern) + '''';

    with GetConnection.CreateStatement.ExecuteQuery(SQL) do
    begin
      while Next do
      begin
        Result.MoveToInsertRow;
        Result.UpdateNull(1);
        Result.UpdateString(2, GetString(2));
        Result.UpdateString(3, GetString(3));
        Result.UpdateString(4, GetString(4));
        Result.UpdateInt(5, Ord(ConvertOracleTypeToSQLType(
          GetString(6), GetInt(9), GetInt(10))));
        Result.UpdateString(6, GetString(6));
        Result.UpdateInt(7, GetInt(7));
        Result.UpdateNull(8);
        Result.UpdateInt(9, GetInt(9));
        Result.UpdateInt(10, GetInt(10));

        if UpperCase(GetString(11)) = 'N' then
        begin
          Result.UpdateInt(11, Ord(ntNoNulls));
          Result.UpdateString(18, 'NO');
        end
        else
        begin
          Result.UpdateInt(11, Ord(ntNullable));
          Result.UpdateString(18, 'YES');
        end;

        Result.UpdateNull(12);
        Result.UpdateString(13, GetString(13));
        Result.UpdateNull(14);
        Result.UpdateNull(15);
        Result.UpdateNull(16);
        Result.UpdateInt(17, GetInt(17));

        Result.UpdateNull(19);   //AUTO_INCREMENT
        Result.UpdateBoolean(20, //CASE_SENSITIVE
          GetIdentifierConvertor.IsCaseSensitive(GetString(4)));
        Result.UpdateBoolean(21, True);  //SEARCHABLE
        Result.UpdateBoolean(22, True);  //WRITABLE
        Result.UpdateBoolean(23, True);  //DEFINITELYWRITABLE
        Result.UpdateBoolean(24, False); //READONLY

        Result.InsertRow;
      end;
      Close;
    end;
end;

However, I believe there is an error on a routine prior to this I should pass the information to the schema variable SchemaPattern.

I await guidance from the development team to make the right decision.

Thanks!

[josimarz]

Hello!

Primarily I want to apologize for the inconvenience of the previous posts. I'm starting to develop in Oracle + Delphi and I'm finding some difficulties.

In this case mentioned I assumed to be a failure because Zeos had sought help in the Oracle fóruns and according to the responses I received I finally deduced that it was a failure to Zeos. However, I kept looking for more information on the Oracle data dictionary and came to the conclusion that a privilege of the server that allowed one user to visualize the objects in a schema of others.

Thus, a query ALL_TAB_COLUMNS exhibited objects from other schemas.

After some testing I found out what the privileges that such permits:

DELETE ANY TABLE
INSERT ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
UPDATE ANY TABLE

After excluding these privileges, the Zeos started to behave correctly.

I apologize for any inconvenience and thank you for the excellent library available to the community.

Josimar Zimermann

[mdaems]

No need to apologize, Josimar.
Professionally I'm an oracle developer myself, so I know about the mess these different views are.
I'm afraid there may be multiple options
- Using the 'USER_'-view : the disadvantage is this breaks existing code for users who use the principle of a data schema with public synonyms for the other users pointing to the data schema tables. (I'm working on a project using this principle myself, but not using zeoslib)
- Overwrite GetColumns for Oracle in such way it searches for the logged in user before searching before any user when the schema isn't known
- Changing the code that calls GetColumns to make sure it works correctly
- Make sure the rows for the currently connected user come first in the 'all_'query by adding a order by clause on 'decode(owner,user(),0,1) ASC'. Not sure if this works, however
- The ideal solution would be to write a query and all_tab_columns taking into account synonyms and public synonyms for tables that make the foreign tables visible to the logged on user. The you could select only the columns from those tables a user sees as if they are his own and treat them as if they effectively are his own tables (so even substituting his own user name as the table owner)

If the third solution works as expected, I would go for that one.
It seems like that would not break existing code so a patch could be committed to make it available for all users.

Mark