Unable to Discover Primary Keys for Tables in Non-Default Schema on MS SQL Server with autoDeriveSchema="true"

[godonnell_ip]

In SmartGWT v6.1 SQLMetaData.getPrimaryKeys() does not accept a schema. According to the DatabaseMetaData documentation this parameter is simply used to scope the search. However, SQL Server 2017 (perhaps other versions also, but I have not tested) requires the schema to be supplied in order for it to return the correct response for tables that are not contained in the user's default schema. Thus, a call to SQLMetaData.getPrimaryKeys() for tables not contained in the default schema returns no keys.

The reason is that Microsoft's implementation of DatabaseMetaData.getPrimaryKeys() (I'm using the mssql-jdbc-8.2.2.jre8.jar JDBC driver) invokes the stored procedure sys.sp_pkeys() which in turn invokes the system function sys.object_id() to identify the table in question. When the schema is supplied in a call to sys.sp_keys(), the correct object ID is returned. When null is supplied then no object ID is returned and thus no primary keys are discovered.

Since the schema is known at the time that the call to SQLMetaData.getPrimaryKeys() is made from SQLDSGenerator.getFieldsFromTable() I would imagine that this would be a straightforward fix to have it work correctly.

P.S. Although I haven't hit this yet, I notice that SQLMetaData.getColumnNames() similarly does not support the schema being specified, although the other getter methods do.

Regards,
Gary O'Donnell

[godonnell_ip]

Just wondering if anyone has had time to consider this?

Thanks.

[Isomorphic]

Sincere apologies for the delay. This was assigned right away but was lost down the road. Thank you for the research leading to a straightforward fix, which is committed and will be available in SmartGWT 6.1 nightly builds since Oct 30 (tomorrow). Please let us know how it worked for you if it is still relevant.