PgDatabaseMetaData should return column names as described in specification.

[bilak]

Hello,
In java specification for getTables are returned column names uppercased. As many implementations depends on this, would it be possible to convert values to uppercase and provide it as described in specification? I know that it's possible to retrieve resultset metadata with ignoringcase but when you don't have this in mind, it could produce errors.

Thanks

[davecramer]

PostgreSQL by default folds to lower case. This would probably break everyone elses code. Pretty sure this is a non-starter

[bilak]

Then this issue could be closed.

[vlsi]

@davecramer , technically speaking, "TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ..." are declared to be in upper case (not the contents, but the column names itself)

rs.getString("TABLE_TYPE") works since getString is case-insensitive.

Do you still consider it is not possible to adjust that?

[davecramer]

I assumed he wanted the actually names of the columns returned in upper case ?

ie create table (id int, a text) would return column names id, and a in lower case.

Is this not the case ?

[bilak]

@davecramer no, as @vlsi described specifications is telling about "TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ..." as uppercase. So I expect column names to be returned in upper case also.

[vlsi]

Implementation-wise, getTables works off a SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM..., that causes lower-case conversion.

[davecramer]

This would not be difficult to fix. Just double quote the table names above. At this point I'm wondering what it would break ?

[vlsi]

At this point I'm wondering what it would break ?

Lower case is visible via getTables().getMetaData().getColumName(1). However, I don't think one would ever rely on that level of meta-information. They would either use getTables().getString("TABLE_NAME") and/or column position getTables().getString(3).

However, metadata-API is always a pain, so we might want to add a kill switch. That is: make upper case the default (e.g. in 42.1.2+), add a connection property to flip back the case, and remove the switch in (42.2.0).

[vlsi]

@davecramer , do you think a kill switch is required?

[davecramer]

@vlsi yes, I do, as this is a behavioural change.

[EcoFreak]

This should be changed. Some applications use jdbc drivers and read the columns from the metadata.
Adding quotes to the getTables query and such will solve this problem and will make the methods completely compliant with the jdbc specs.

[davecramer]

@EcoFreak if I'm understanding this problem this #1350 fixes it, no?

[EcoFreak]

Hi @davecramer , I'm trying to integrate a postgresql database with SAP JAVA BI (currently have MySQL, MSSQL and others running with JDBC drivers).

After some digging I found that the code used to determine database tables:

 ResultSet rs = connSQL.getMetaData().getTables(null, null, null, null);
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();
    
    while (rs.next())
    {
      try
      {
        for (int i = 1; i <= cols; i++)
        {

          if (rsmd.getColumnName(i).equals("TABLE_NAME")) {
            tableList.add(rs.getString(i));
          }
        }
      }
      catch (Exception e) {}
    }

As you can see, the column names are fetched using rs.getMetaData(); wich returns lower cased column names instead of TABLE_NAME, the column is name table_name.

Unfortunately I can't control the existing SAP code.

Any thoughts?

Thanks

[davecramer]

@EcoFreak it would appear we need to fix the ResultSetMetaData as well. Pull requests are welcome