Zeos 7.2.6 + Oracle - set schema on TZConnection?

[aehimself]

Hello,

I have an application connecting to an Oracle database with multiple schemas, containing the same table names. I dynamically create a TZQuery and try to execute a simple command:
INSERT INTO TABLE (COLUMN) VALUES ('ColumnValue')

This fails with an error message (cancelled by the user I think...). SQLMonitor shows that it's because TZQuery attempted to update a table with the same name, but in a different schema that I'd like. No surprise, if I change the query to
INSERT INTO SCHEMA.TABLE (COLUMN) VALUES ('ColumnValue')

everything works fine.

There is a Schema property on TZSQLMetaData; is there anything like this on a TZConnection / TZQuery so it keeps everything using it within it's boundaries?

Thanks!

[marsupilami]

The connection has a catalog property. Usually that one should do the trick. For a query I would expect that the automatically generated SQL is generated using schema names. It seems it is not?

[aehimself]

And you were right, as ever Setting the "Catalog" property to the schema name forced the queries using that connection to the right table.

The thing which confused me was that it's called Schema in the Metadata and was not brave enough to experiment.

@ Jan, I really owe you a beer now.

[marsupilami]

And you were right, as ever Setting the "Catalog" property to the schema name forced the queries using that connection to the right table.

Good to know.

The thing which confused me was that it's called Schema in the Metadata and was not brave enough to experiment.

Hmmm - the problem for me is that different vendors seem to have different opionions on what a schema and what a catalog is. *Sigh* - we need docs that tell everybody how Zeos works...

[aehimself]

the problem for me is that different vendors seem to have different opionions on what a schema and what a catalog is

This is exactly what I started to wonder about - and the deeper you dig, the worse it gets

[aehimself]

So... bump. Unfortunately it seems that it does not work - always. Now using 7.3 latest snapshot, when I did a

Code: Select all

ZQuery.Edit;
ZQuery.FieldByName('xxx').AsBytes := bb;
ZQuery.Post;

I get a "0 records updated" exception. I investigated a little bit and it turns out that the statement generator is simply trying to figure out the schema and is inserting the first available maybe; but not considering what is set on the ZConnection.

Same thing happens when I executed a SELECT query:

Code: Select all

[2019.12.10 18:43:00.602] SQL trace: '2019-12-10 18:12:00' cat: Prepare, proto: oracle, msg: Statement 3 : SELECT FIELD1, FIELD2, FIELD3 FROM MYTABLE
[2019.12.10 18:43:00.635] SQL trace: '2019-12-10 18:12:00' cat: Execute prepared, proto: oracle, msg: Statement 3
[2019.12.10 18:44:37.902] SQL trace: '2019-12-10 18:12:37' cat: Prepare, proto: oracle, msg: Statement 4 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER  WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2019.12.10 18:44:37.989] SQL trace: '2019-12-10 18:12:37' cat: Execute prepared, proto: oracle, msg: Statement 4

If you execute the metadata fetch query Zeos is executing, you'll quickly see that it does not filter for a schema at all; it returns all columns in all schemas for the table MYTABLE.

I don't know if it works the same way in other drivers, but in TZOracleDatabaseMetadata.UncachedGetColumns the SchemaPattern variably is empty (thus no schema filtering); and that's because TZAbstractResultSetMetadata.LoadColumns is tying to get schema name from the SQL command. It simply doesn't seem to care what is requested on the TZConnection

Edit: The same thing seems to be the reason for why posting a query succeeds with a query like "SELECT FIELD FROM TABLE" but fails with "SELECT * FROM TABLE" (EZDatabaseError was raised with the message Cannot update a complex query with more then one table). TZGenericCachedResolver.DefineTableName finds the table in all schemas, and it can not choose which to choose from. Exception is raised here:

Code: Select all

      else if (Result <> '') and (Temp <> '') and (Temp <> Result) then
        raise EZSQLException.Create(SCanNotUpdateComplexQuery);

(where Result is the table name to use and Temp is the next possible table name returned from ALL SCHEMAS from Metadata)

[Fr0sT]

I'm not familiar with Oracle but shouldn't Schema be rather a prop of a dataset than of a connection? Connection could access several schemas or am I missing something?

[aehimself]

Well, yes and no. For me it makes sense that a connection is "bound" to a schema and it does not let anything to go out of it; but again - in this case it should. In a real world scenario (especially if we consider network layer / RDBMS protocol) you are right - an established TCP tunnel can transport data to and from all schemas the connection has access to; the query will decide which schema it manipulates.

Unfortunately though, schema can only be defined on TZMetaData (& TZConnection) but not on TZAbstractRODataSet.

[marsupilami]

Schema in this context is more like the default schema that is set connection wide on the server. One can change the default schema from public to some other schmea on PostgreSQL using this option. It is the same for Oracle.

[aehimself]

...but alas, it doesn't seem to work Or at least I am missing something.

[marsupilami]

Sorry, I didn't see this post more early..

So... bump. Unfortunately it seems that it does not work - always. Now using 7.3 latest snapshot, when I did a

Code: Select all

ZQuery.Edit;
ZQuery.FieldByName('xxx').AsBytes := bb;
ZQuery.Post;

I get a "0 records updated" exception. I investigated a little bit and it turns out that the statement generator is simply trying to figure out the schema and is inserting the first available maybe; but not considering what is set on the ZConnection.

Unfortunately Zeos is not perfect Honestly - I assume you have two tables with the same name in your databases, one living in schema A and one living in schema B? It might be that our oracle code doesn't read the metadata correctly...

Same thing happens when I executed a SELECT query:

Code: Select all

[2019.12.10 18:43:00.602] SQL trace: '2019-12-10 18:12:00' cat: Prepare, proto: oracle, msg: Statement 3 : SELECT FIELD1, FIELD2, FIELD3 FROM MYTABLE
[2019.12.10 18:43:00.635] SQL trace: '2019-12-10 18:12:00' cat: Execute prepared, proto: oracle, msg: Statement 3
[2019.12.10 18:44:37.902] SQL trace: '2019-12-10 18:12:37' cat: Prepare, proto: oracle, msg: Statement 4 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER  WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2019.12.10 18:44:37.989] SQL trace: '2019-12-10 18:12:37' cat: Execute prepared, proto: oracle, msg: Statement 4

If you execute the metadata fetch query Zeos is executing, you'll quickly see that it does not filter for a schema at all; it returns all columns in all schemas for the table MYTABLE.

Ok - this is a bug. This is what really should happen: Upon getting a result set, the Oracle result set should try to fill in the information of TZColumnInfo. This should contain the table name - possibly including the schema. Metadata lookups may happen, if the the oracle library doesn't provide all information that is necessary. In Firebird and PostgreSQL this works very well, so we (almost) don't need to query other metadata. On other drivers your mileage may vary...

I don't know if it works the same way in other drivers, but in TZOracleDatabaseMetadata.UncachedGetColumns the SchemaPattern variably is empty (thus no schema filtering); and that's because TZAbstractResultSetMetadata.LoadColumns is tying to get schema name from the SQL command. It simply doesn't seem to care what is requested on the TZConnection

TZAbstractResultSetMetadata.LoadColumns is part of the loading mechanism. It is meant to be a kinda fallback mechanism to provide metadata information if the client library doesn't provide enough information.

Edit: The same thing seems to be the reason for why posting a query succeeds with a query like "SELECT FIELD FROM TABLE" but fails with "SELECT * FROM TABLE" (EZDatabaseError was raised with the message Cannot update a complex query with more then one table). TZGenericCachedResolver.DefineTableName finds the table in all schemas, and it can not choose which to choose from. Exception is raised here:

Code: Select all

      else if (Result <> '') and (Temp <> '') and (Temp <> Result) then
        raise EZSQLException.Create(SCanNotUpdateComplexQuery);

(where Result is the table name to use and Temp is the next possible table name returned from ALL SCHEMAS from Metadata)

Yes - this is the same bug.

But it looks lile we have a chance to fix this by modifying TZAbstractResultSetMetadata.LoadColumns to use the default schema name if a table has no specific schema assigned?

[aehimself]

Unfortunately Zeos is not perfect Honestly - I assume you have two tables with the same name in your databases, one living in schema A and one living in schema B? It might be that our oracle code doesn't read the metadata correctly...

Yes, you are right; almost. We have about 18 schemas with same table names, because someone decided to separate test databases by schemas only

TZAbstractResultSetMetadata.LoadColumns is part of the loading mechanism. It is meant to be a kinda fallback mechanism to provide metadata information if the client library doesn't provide enough information.

But it looks lile we have a chance to fix this by modifying TZAbstractResultSetMetadata.LoadColumns to use the default schema name if a table has no specific schema assigned?

I don't think we are talking about the same thing here. There is no metadata, all information is travelling in a IZSelectSchema object, which is manually assembled by tokenizing the SQL query (probably in TZGenericStatementAnalyzer.DefineSelectSchemaFromQuery).

I don't yet have enough knowledge in Zeos to see if this is a "global" or Oracle-specific issue; also I don't really know where to put the decision logic. There's also a question on how we should detect incorrect schemas, as in my first example (0 records updates) Zeos did fill the schema name, only with an incorrect one! Unfortunately this means we can not rely on checking if it's empty.

[marsupilami]

The TZColumnInfo objects are created in ZDbcOracleResultSet.pas in TZOracleResultSet_A.Open. There is a line in there that already sets the schema information:

Code: Select all

    ColumnInfo.SchemaName := AttributeToString(P, TempColumnNameLen);

Unfortunately it seems that only the ColumnLabel gets set there but not the ColumnName. So in a scenario like thefollowing:

Code: Select all

select ColA as ColB from SchemaC.TableD

We should know that the column label is ColB but we don't (yet?) know the original column name ColA and the original table name TableD. It seems Oracle doesn't deliver that information. This is a dilemma that Zeos tries to solve by using LoadColumns.

[aehimself]

If we could make the connections default schema available to the parser, this could be solved easily there. What if we add the schema property in IZSelectSchema; and fill it in LoadColumns if it was not returned by the parser? Can we access the schema set on the connection from LoadColumns somehow?

[aehimself]

Sooooo... it seems I can fix it after all You remember the previous query? This is it now:

Code: Select all

[2019.12.13 09:05:12.097] SQL trace: '2019-12-13 09:12:12' cat: Prepare, proto: oracle, msg: Statement 3 : SELECT FIELD1, FIELD2, FIELD3 FROM MYTABLE
[2019.12.13 09:05:12.111] SQL trace: '2019-12-13 09:12:12' cat: Execute prepared, proto: oracle, msg: Statement 3
[2019.12.13 09:05:12.114] SQL trace: '2019-12-13 09:12:12' cat: Prepare, proto: oracle, msg: Statement 4 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER  WHERE [b]ALL_TAB_COLUMNS.OWNER = 'CONNECTIONSCHEMA' [/b]AND ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2019.12.13 09:05:12.969] SQL trace: '2019-12-13 09:12:12' cat: Execute prepared, proto: oracle, msg: Statement 4

I can confirm that the original code (.Edit, .FieldByName.AsBytes, .Post) also works, neither the "Cannot update complex query" is thrown when I'm editing a SELECT * FROM resultset.

I'll strip down the code a little bit and then send a pull request via Git.