getBestRowIdentifier not supported?

[RavenAtSafe]

My attempts to use metadata's getBestRowIdentifier() to find primary key columns were not successful (no columns ever returned). Are there plans to implement this feature?

[Marco Amadei]

getBestRowIdentifier() is already implemented, so it might be a bug. In my test case all works fine, may you post a code sample?

[RavenAtSafe]

This is not a standalone repro unfortunately, but the best I can do at the moment. I am pretty sure that schemaName == null in this invocation.

 result = jdbcConnection_.getMetaData().getBestRowIdentifier(
       jdbcConnection_.getCatalog(), // Current catalog
       schemaName,
       unqualifiedTableName,
       DatabaseMetaData.bestRowSession,
       false // don't accept nullable columns
       );

[Marco Amadei]

Hi Raven, just a break here. I let inalterated the hsqldb logic behind this method(even if I changed the query to provide metadata in the exact case). I've just done some test with hsqldb, and I can confirm the following behaviour: the least restrictive value for the "scope" argument seems to be bestRowTemporary whereas the most restrictive is DatabaseMetaData.bestRowSession.
So, please try with bestRowTemporary in your code.
Some doubts it's a correct implementation and I would change it.
But because I'm querying the hsqldb system tables, I would like be sure before doing it: I'll contact the hsqldb team.
Here's my hsqldb only code:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Hsqldb {
public static void main(String[] s) throws SQLException{
    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");
    Statement st=c.createStatement();
    st.execute("create table doctors(  badgeno INTEGER primary key,  fname char(15),  lname varchar(25) not null,  specialty varchar(15) not null  )");
    c.commit();
    //ResultSet rs= c.getMetaData().getBestRowIdentifier("PUBLIC", "PUBLIC", "DOCTORS", DatabaseMetaData.bestRowTemporary, true);
    ResultSet rs= c.getMetaData().getBestRowIdentifier("PUBLIC", "PUBLIC", "DOCTORS", DatabaseMetaData.bestRowSession, true);
    while(rs.next()){
        System.out.println("here");
    }
}
}

[RavenAtSafe]

Hi Marco,

We have found that bestRowTemporary (but not bestRowTransaction) does allow us to find the column we want. Do you expect to make changes to allow bestRowSession to work, or should we add an exception for the UCanAccess driver?

(I find the JDBC doc very thin on getBestRowIdentifier, and it's kind of ambiguous as to what scope is really appropriate for various scenarios. Perhaps I am being too conservative by asking for bestRowSession...)

Thanks!

[Marco Amadei]

I've read the JDBC doc, I didn't understand it in depth and I prefered adapting the hsqldb code. If the behaviour of other drivers is actually different(and it's what we expected), I'll fixit in ucanaccess regardless the original and current hsqldb behaviour(but I will report this issue to them).

[RavenAtSafe]

Of the drivers we have tested, almost all will find primary key columns when given the bestRowSession scope. Examples of drivers I have tested that work with bestRowSession scope:

• Postgres
• SAP HANA
• Microsoft SQL Server
• Teradata
• H2