No announcement yet.
  • Filter
  • Time
Clear All
new posts

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

    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.

    Gary O'Donnell

    Just wondering if anyone has had time to consider this?