Null Pointer Exception in CREATE LINKED TABLE for MS SQL Server

[Pavel]

Hi Thomas,

For the following test case I got the error:
The proposed fix is at the end of the E-mail

=========================
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.convert(Message.java:257)
at org.h2.command.Command.executeUpdate(Command.java:225)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:163)
at org.h2.samples.Test.mixedCase(Test.java:111)
at org.h2.samples.Test.main(Test.java:15)
Caused by: java.lang.NullPointerException
at org.h2.util.StringUtils.toLowerEnglish(StringUtils.java:64)
at org.h2.table.TableLink.convertColumnName(TableLink.java:252)
at org.h2.table.TableLink.readMetaData(TableLink.java:240)
at org.h2.table.TableLink.connect(TableLink.java:82)
at org.h2.table.TableLink.<init>(TableLink.java:65)
at org.h2.schema.Schema.createTableLink(Schema.java:470)
at org.h2.command.ddl.CreateLinkedTable.update(CreateLinkedTable.java:
78)
at org.h2.command.CommandContainer.update(CommandContainer.java:69)
at org.h2.command.Command.executeUpdate(Command.java:206)
... 3 more

==========================
public static void mixedCase() throws Exception {

Class.forName("net.sourceforge.jtds.jdbc.Driver");
Class.forName("org.h2.Driver");

Connection linked =
DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433",
"myuser", "mypassword");

Statement mssql = linked.createStatement();
try {
mssql.execute("DROP TABLE testtable");
} catch (SQLException e) {
}
mssql.execute("CREATE TABLE testtable(lower INT, UPPER INT,
MiXeD INT)");

Connection h2 = DriverManager.getConnection("jdbc:h2:mem:ms");

Statement sb = h2.createStatement();
sb.execute("CREATE LINKED TABLE
two('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://
localhost:1433','myuser','mypassword','testtable')");
ResultSet rs = sb.executeQuery("SELECT * from two");
ResultSetMetaData md = rs.getMetaData();
/*
Assert.assertEquals(md.getColumnName(1), "LOWER");
Assert.assertEquals(md.getColumnName(2), "UPPER");
Assert.assertEquals(md.getColumnName(3), "MIXED");
*/

rs = sb.executeQuery("SELECT lower,upper,mixed from two");

rs = sb.executeQuery("SELECT LOWER,UPPER,MIXED from two");

linked.close();
h2.close();


}
================

The fix would be to add the following lines in TableLink.java line
220, to ignore some indexes returned by MS Sql Server

if (rs.getShort("TYPE") ==
DatabaseMetaData.tableIndexStatistic) {
continue;
}

[Pavel]

After the fix I also noticed the following effect. If I do not specify
schema name everything works OK

sb.execute("CREATE LINKED TABLE
two('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://
localhost:1433','myuser','mypassword','testtable')");

but I do specify schema name

sb.execute("CREATE LINKED TABLE
two('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://

localhost:1433','myuser','mypassword','', 'testtable')");

I will get a different error.

=======
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Column LOWER
not found; SQL statement:
SELECT lower,upper,mixed from two [42122-100]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:
130)
at org.h2.command.dml.Select.prepare(Select.java:710)
at org.h2.command.Parser.prepareCommand(Parser.java:233)
at org.h2.engine.Session.prepareLocal(Session.java:285)
at org.h2.engine.Session.prepareCommand(Session.java:246)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1035)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:70)
at org.h2.samples.Test.mixedCase(Test.java:120)
at org.h2.samples.Test.main(Test.java:15)
=====

The reason for the error is that columns are not converted to upper
case anymore. The fix would be in TableLink.java line 150 delete the
following lines
if (storesLowerCase &&
n.equals(StringUtils.toLowerEnglish(n))) {
n = StringUtils.toUpperEnglish(n);
}

and use
n=convertColumnName(n);
instead as you use it in other places

[Thomas Mueller]

Hi,

Thanks for the patches! I have changed it, the newest version is
committed to the trunk at:
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/table/TableLink.java

Regards,
Thomas