>> This is less of a performance hit, but
>> still has a version skew problem. Whatever the scheme, however, you
>> have to solve the problem somehow or break most tools and the ODBC and
>> JDBC drivers.
>
> May i ask our ODBC\JDBC developers if it is required to return information
> from external schemas ? Roman ?

There are two issues in JDBC:

a) general metadata information available in the DatabaseMetaData
interface. Those are information about schema, tables, columns,
procedures, etc.

b) result set metadata available for each result set at runtime.

In general I see no problem with a), since we're not supposed to provide
all schema information for an external database, even people can queue
it. Or better to say, the only possibility to make it work in JDBC is to
treat external database as catalog. In this case we have to be prepared
to handle "dotted" SQL queries like
"some_catalog.some_schema.some_table". That also means that we have to
support schemas (at least some default name).

The issue b) is more problematic, since the result set might refer to
the external table, but the SQLDA does not contain enough information
for the ResultSetMetaData interface. At the moment Jaybird queries
information from the system tables, but that is just a solution for the
current case.

The ResultSetMetaData interface must provide:

- name of the catalog (empty at the moment);
- column class name (can be derived from XSQLVAR);
- column count (XSQLDA);
- column display size (XSQLVAR, for numeric columns see precision);
- column label (XSQLVAR);
- column type (XSQLVAR);
- column type name (XSQLVAR);
- precision (->system table query!!!);
- scale (XSQLVAR);
- schema name (empty);
- table name (XSQLVAR);
- is auto-increment (not yet impl., but would require system table query);
- is currency (not yet impl., we have no such data type);
- is definitely writable (not yet impl., requires information about
permissions);
- is nullable (XSQLVAR);
- is read-only (not yet impl., maybe system queries needed);
- is searchable (something is impl., not sure it is 100% correct);
- is signed (XSQLVAR);
- is writable (not yet impl., maybe system queries needed).

The ParameterMetaData has only one problem - the precision. This is
clear requirement for XSQLVAR extension.

I cannot tell a lot about ODBC, but I suspect that the requirements are
similar.

>>> In-memory metadata for remote relations will be created at query prepare time
>>> when engine will able to extract query string and prepare it at remote data source
>>>
>> Too little too late. You have to solve the general metadata problem to
>> be standard conforming.
>
> Not sure it is too late, wait what Roman say.

If we implement catalogs (e.g. external data source is a catalog) and
engine provides a possibility to access the needed information in the
remote database via system tables or selectable system procedures (in
other words the parser would somehow distinguish the query involving
catalog name), that would work. For sure, we (driver developers) will
have to update drivers to support it, but that's ok.

Same applies to the result set meta data - we need such information only
after preparing the query.

Roman