H2 Console MODE=MYSQL fails to edit ResultSets

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
2. INSERT INTO TEST VALUES (1, 'JAMES');
3. @edit SELECT * FROM TEST;
4. Change the value of NAME and Save.

What is the expected output? What do you see instead?
For connection MODE=MYSQL this fails with following message:

The result set is not updatable. The query must select all columns from a 
unique key. Only one table may be selected. [90127-144]

For default MODE this works.  For real MySQL DB connection this works.

What version of the product are you using? On what operating system, file
system, and virtual machine?

1.2.144

Do you know a workaround?

Don't run H2 console in MySQL mode.  :(

How important/urgent is the problem for you?

In your view, is this a defect or a feature request?

Defect.

Please provide any additional information below.

Original issue reported on code.google.com by James.Mo...@gmail.com on 2 Nov 2010 at 6:55

[GoogleCodeExporter]

 UpdateableRow() {
 ...

 ResultSet rs = meta.getTables(null,
                JdbcUtils.escapeMetaDataPattern(schemaName),
                JdbcUtils.escapeMetaDataPattern(tableName),
                new String[] { "TABLE" });
        if (!rs.next()) {
            return;
        }
...

}

the !rs.next() return; clause triggers in MODE=MYSQL
This check passes in default mode.

Original comment by James.Mo...@gmail.com on 2 Nov 2010 at 7:26

[GoogleCodeExporter]

Further investigation yields that "show tables" returns nothing in MODE=MYSQL 
which is probably another symptom of the same problem.

That problem is here.....
 PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "TABLE_TYPE, "
                    + "REMARKS, "
                    + "TYPE_NAME TYPE_CAT, "
                    + "TYPE_NAME TYPE_SCHEM, "
                    + "TYPE_NAME, "
                    + "TYPE_NAME SELF_REFERENCING_COL_NAME, "
                    + "TYPE_NAME REF_GENERATION, "
                    + "SQL "
                    + "FROM INFORMATION_SCHEMA.TABLES "
                    + "WHERE TABLE_CATALOG LIKE ? ESCAPE '\\' "
                    + "AND TABLE_SCHEMA LIKE ? ESCAPE '\\' "
                    + "AND TABLE_NAME LIKE ? ESCAPE '\\' "
                    + "AND (" + tableType + ") "
                    + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
            prep.setString(1, getCatalogPattern(catalogPattern));
            prep.setString(2, getSchemaPattern(schemaPattern));
            prep.setString(3, getPattern(tableNamePattern));

schemaPattern and tableNamePattern work if they are UPPERCASE and fail if they 
are lowercase - which happens in MODE=MYSQL.

So either LIKE needs to be case-insensitive OR those two fields should be 
lowercase?
I'm guessing that LIKE needs to be case-insensitive.

Original comment by James.Mo...@gmail.com on 2 Nov 2010 at 8:08

[GoogleCodeExporter]

LIKE is case-insensitive IF the left type == Value.STRING_IGNORECASE (14), but 
the metadata columns are type Value.STRING (13) so the mixed-case comparison 
fails.

Original comment by James.Mo...@gmail.com on 2 Nov 2010 at 9:44

[GoogleCodeExporter]

You are right, it doesn't work as it should. Maybe the following change solves 
the problem (I'm currently testing it):

MetaTable.createColumns
dataType = database.getMode().lowerCaseIdentifiers ? 
  Value.STRING_IGNORECASE : Value.STRING;
instead of:
dataType = Value.STRING;

Original comment by thomas.t...@gmail.com on 5 Nov 2010 at 10:39

• Changed state: Accepted

[GoogleCodeExporter]

There are a few more things to change, but I think I can do that for the next 
release.

Original comment by thomas.t...@gmail.com on 5 Nov 2010 at 11:18

[GoogleCodeExporter]

I eventually found that same code too in MetaTable and patched it with 
something very similar - yours makes more sense: lowerCaseIdentifiers.

But that only works for system tables.  i.e. it will make "show tables" work, 
but editing a row in the console still won't work because the non-system tables 
have the same problem (I think).  I haven't found where the local table 
metadata is instantiated yet.

I'm pretty sure this bug also makes SquirrelSQL fail for me if I connect with 
MODE=MYSQL.  I will confirm that.

Original comment by James.Mo...@gmail.com on 5 Nov 2010 at 11:53

[GoogleCodeExporter]

It is now fixed in the trunk, see 
http://code.google.com/p/h2database/source/detail?r=3084

Original comment by thomas.t...@gmail.com on 5 Nov 2010 at 12:00

[GoogleCodeExporter]

This is working nicely for:
 * H2 Console editing
 * show tables

I think there is still an issue with MODE=MYSQL for schema.
If I connect in MODE=MYSQL with Squirrel I can not browse database tables.
The Squirrel log says:

org.h2.jdbc.JdbcSQLException: Schema "testdatabase" not found; SQL statement:
select count(*) from "testdatabase"."public"."testtable" [90079-145]

If I remove MODE=MYSQL from the connection string, Squirrel can properly browse 
tables, etc.

Original comment by James.Mo...@gmail.com on 5 Nov 2010 at 12:46

[GoogleCodeExporter]

I see. This is still a problem. In this regard, the MySQL compatibility feature 
is still a "hack" and not the correct solution. I believe the DatabaseMetaData 
methods stores*Case*Identifiers need to be changed as well.

Original comment by thomas.t...@gmail.com on 8 Nov 2010 at 7:00

[GoogleCodeExporter]

I'm a little confused on case-sensitivity in H2.  I'm trying to massage my app 
to run in native mode instead of relying on MODE=MYSQL.  It appears that H2 
supports case-insensitive table names and case-insensitive column names, by 
default, for queries/statements.... but NOT in the metadata.

i.e. in my code I have a table exists check...

ResultSet rs = connxn.getMetaData().getTables(null, null, tableName, null);
boolean hasTable = false;
while (rs.next())
    hasTable = true;
rs.close();

In native mode, this check fails unless I uppercase tableName first. (This is 
because metadata columns are Value.STRING in MetaTable.createColumns - comment 
#4).

Should MetaTable.createColumns always use Value.STRING_IGNORECASE instead of 
checking lowerCaseIdentifiers?

Original comment by James.Mo...@gmail.com on 9 Nov 2010 at 2:25

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 14 Dec 2010 at 8:30

• Changed state: Started

[GoogleCodeExporter]

Please Contact me, regarding my Source Code, which is being used. Thank you. 

Original comment by haladelphia@halsegal.com on 5 Nov 2012 at 7:51

[GoogleCodeExporter]

> Please Contact me, regarding my Source Code, which is being used. Thank you. 

Sorry I don't understand.

Original comment by thomas.t...@gmail.com on 5 Nov 2012 at 8:13

[GoogleCodeExporter]

Should be fixed in H2 version 1.3.171

Original comment by thomas.t...@gmail.com on 17 Mar 2013 at 4:06

• Changed state: Fixed