#1387 Error message and empty Metadata tab when connecting to H2 v1.4.197

[Chris B]

If you connect to a H2 database of version 1.4.197, Squirrel will show an error message right after connecting:

"Error occured during task execution:
For input string: "127.0.0.1:9092"

Additionally, the Metadata tab is empty.

As far as I found out, this has to do with how the H2 driver reports its metadata in a "non standard way":

"non standard way" means:

According to the Java API description getClientInfoProperties() of a java.sql.DatabaseMetaData is meant to return a 4 column resultset:

1. NAME String
2. MAX_LEN int
3. DEFAULT_VALUE String
4. DESCRIPTION String

Despite that getClientInfoProperties() in org.h2.jdbc.JdbcDatabaseMetaData of version 1.4.197 just adds two string/varchar columns:

result.addColumn("Name", Types.VARCHAR, 0, 0);
result.addColumn("Value", Types.VARCHAR, 0, 0);

In Squirrel:

Method generateLine(...) in net.sourceforge.squirrel_sql.fw.sql.MetaDataDataSet expects the second column to be an Integer:

:
while (rs.next()) {
tmp.append(rs.getString(1)).append("\t");
tmp.append(rs.getInt(2)).append("\t");
tmp.append(rs.getString(3)).append("\t");
tmp.append(rs.getString(4)).append("\n");
}
:

... which results in an exception as soon as H2 returns its non-Integer data of column 2.

So I wonder if it would be a good idea to check the datatype for Integer first ?
Or would it work to always "getString(2)" ?

[Gerd Wagner]

Sorry, I can't reproduce this problem with SQuirreL's latest snapshot version and H2 version 1.4.199 (2019-03-13).
Maybe the problem was fixed lately.

[Chris B]

Yes, you're right, the problem does not occur with H2 version 1.4.199.
Thats why I wrote H2 v1.4.197 ;-)

Sorry I forgot to point out that I already looked up the H2 Git, and that indeed they changed the behaviour of getClientInfoProperties() in October 2018.
(https://github.com/h2database/h2database/commit/97cd4f5dda65b782e753a448104c586f3bc4ef16#diff-889a16be31dac86bd862ddeb3d7525eb)

However, sometimes it's not up to me to decide which version of a DB server is used.
In that particular situation a H2 v1.4.197 is used, no matter if I like it or not.
For those situations a less "sensitive" way of Squirrel dealing with "non-standard ClientInfoProperties" could a) avoid the (not really helpful) error message and b) allow the Metadata tab to be filled, I think.

And I guess the same problem could occur with other database implementations as well.
Since the DatabaseMetaData interface can not guarantee that getClientInfoProperties() really returns a resultset of string/int/string/string, or can it ?

[Gerd Wagner]

Ok, I added a check when reading the ClientInfoProperties.
If you get the chance please try it out and let me know if it works.

[Chris B]

Hi Gerd,

now it works better in the way that the NumberFormatException is avoided and most of the Metadata tab is filled and displayed correctly.
But "rs.getString(3)" fails with exception since the Resultset as returned by getClientInfoProperties() in org.h2.jdbc.JdbcDatabaseMetaData (version 1.4.197) contains just two colums instead of four.
Therefore, property "getClientInfoProperties" has value "null" in the Metadata tab.

I suggest an additional modification to check the number of columns to be prepared for "non-standard" ClientInfoProperties with less than 4 columns:

:
  ResultSet rs = (ResultSet) obj;
  try
  {
    int clientInfoColumnCount = rs.getMetaData().getColumnCount();

    StringBuilder tmp = new StringBuilder();
    while (rs.next())
    {
      tmp.append(rs.getString(1));

      if (clientInfoColumnCount > 1)
      {
        if (rs.getMetaData().getColumnType(2) == Types.INTEGER)
        {
          tmp.append("\t").append(rs.getInt(2));
        }
        else
        {
          // To cope with the issue discussed in bug #1387
          tmp.append("\t").append(rs.getString(2));
        }
      }

      if (clientInfoColumnCount > 2)
      {
        tmp.append("\t").append(rs.getString(3));
      }

      if (clientInfoColumnCount > 3)
      {
        tmp.append("\t").append(rs.getString(4));
      }

      tmp.append("\n");
    }
    line[1] = tmp.toString();
  }
  catch (SQLException ex)
:

Hope that helps.

[Gerd Wagner]

Is committed as suggested.
Thanks Gerd