Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DatabaseMetaData.getColumns() doesn't work correctly when using index-based getters #3356

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2013-09-02 18:49:39 +0200
From: monetdb-bugzilla
To: clients devs <>
Version: 11.15.15 (Feb2013-SP4)
CC: radovan.biciste

Last updated: 2013-10-22 14:42:08 +0200

Comment 19108

Date: 2013-09-02 18:49:39 +0200
From: monetdb-bugzilla

User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:23.0) Gecko/20100101 Firefox/23.0
Build Identifier:

When retrieving details about the columns of a table, the driver does not return the correct information when the index based getters are used rather than the getters that provide a column name.

Example:

ResultSet rs = connection.getMetaData().getColumns("demo", "voc", "invoices", "%");
rs.next();
String isNullable = rs.getString("IS_NULLABLE"); // returns "YES"
String isNullable = rs.getString(18); // returns a null value

The same is true for getInt("DECIMAL_DIGITS") vs. getInt(9)

Reproducible: Always

Steps to Reproduce:

  1. use DatabaseMetaData.getColumns() to obtain details about table columns
  2. use getString(18) --> null value return

Actual Results:

getString(18) returns null
getInt(9) returns 0

Expected Results:

getString(18) should return the same value as getString("IS_NULLABLE")
getInt(9) should return the same value as getInt("DECIMAL_DIGITS)

I have observed this behaviour with the JDBC driver versions 2.8 and 2.9 against a MonetDB Server 11.15.3

Comment 19109

Date: 2013-09-02 19:42:45 +0200
From: @grobian

from http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.htmlgetColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29 I cannot deduce that the column mapping should be exactly as the specification says

Comment 19112

Date: 2013-09-02 20:23:42 +0200
From: monetdb-bugzilla

Next to each column name there is the index number where that column is supposed to be.

  1. TABLE_CAT String => table catalog (may be null)
  2. TABLE_SCHEM String => table schema (may be null)
  3. TABLE_NAME String => table name
  4. ....
  5. ....

Using the index works in all other DBMS (Oracle, Postgres, SQL Server, DB2, H2, HSQLDB, Derby, Teradata, Cubrid, MySQL, Firebird, Ingres, Informix, NuoDB, SQLite, Vertica, even Excel and Access through ODBC)

Comment 19115

Date: 2013-09-03 09:54:08 +0200
From: Radovan Bičiště <<radovan.biciste>>

Created attachment 229
modified MonetDatabaseMetaData.java class

I reordered columns in metadata query in getColumns method and fixed couple column number (adding 1) in metadate methods.
Please consider changes for adding into distribution.
Thank you.

Attached file: MonetDatabaseMetaData.java (text/plain, 113534 bytes)
Description: modified MonetDatabaseMetaData.java class

Comment 19116

Date: 2013-09-03 10:03:04 +0200
From: @grobian

Can you please attach a patch? I think the changes are fine, but can't easily check from here/this way.

Comment 19117

Date: 2013-09-03 10:09:16 +0200
From: Radovan Bičiště <<radovan.biciste>>

Created attachment 230
proposed patch

Hopefully the patch is in the right direction.
Created by command:
diff MonetDatabaseMetaData.java.ORG MonetDatabaseMetaData.java > MonetDatabaseMetaData.java.diff

Attached file: MonetDatabaseMetaData.java.diff (text/plain, 1589 bytes)
Description: proposed patch

Comment 19124

Date: 2013-09-04 10:44:18 +0200
From: @grobian

Changes look OK to me. I assume they pass the tests?

Comment 19126

Date: 2013-09-04 15:14:18 +0200
From: Radovan Bičiště <<radovan.biciste>>

(In reply to comment 6)

Changes look OK to me. I assume they pass the tests?

I did not run any tests included in the source code tree.

Comment 19129

Date: 2013-09-05 16:40:45 +0200
From: Radovan Bičiště <<radovan.biciste>>

Created attachment 231
patch for getImportedKeys method

I'm sorry for bugging. I found another glitch in the MetaData. Method keyQuery did not return the columns in the other described in JDBC specification. It caused method getImportedKeys and others to malfunction in SQL Workbench.
Please validate the proposed patch. I only run make clean and make in java folder. My quick tests in SQL Workbench seems to work fine.

Attached file: MonetDatabaseMetaData.java.diff (text/plain, 2806 bytes)
Description: patch for getImportedKeys method

Comment 19130

Date: 2013-09-05 18:20:06 +0200
From: @grobian

I cannot apply your patch:

patch unexpectedly ends in middle of line
patch: **** Only garbage was found in the patch input.

Comment 19131

Date: 2013-09-05 18:24:07 +0200
From: @grobian

(In reply to comment 9)

I cannot apply your patch:

patch unexpectedly ends in middle of line
patch: **** Only garbage was found in the patch input.

ignore that, I no longer understand BugZilla's buttons/links/output

Comment 19132

Date: 2013-09-05 18:53:57 +0200
From: @grobian

(In reply to comment 8)

Created attachment 231 [details]
patch for getImportedKeys method

I'm sorry for bugging. I found another glitch in the MetaData. Method
keyQuery did not return the columns in the other described in JDBC
specification. It caused method getImportedKeys and others to malfunction in
SQL Workbench.
Please validate the proposed patch. I only run make clean and make in java
folder. My quick tests in SQL Workbench seems to work fine.

your patch breaks Test_JdbcClient on the foreign key constraint dumping, so something is no longer matching

Comment 19133

Date: 2013-09-05 21:50:01 +0200
From: Radovan Bičiště <<radovan.biciste>>

I'm not able to compile tests with Java 1.7.0_25:
rbiciste@rbiciste-suse:~/MonetDB-11.13.9/java> sudo make install
make install-recursive
make[1]: Entering directory /home/rbiciste/MonetDB-11.13.9/java' Making install in tests make[2]: Entering directory /home/rbiciste/MonetDB-11.13.9/java/tests'
make install-am
make[3]: Entering directory /home/rbiciste/MonetDB-11.13.9/java/tests' "/usr/bin/ant" -f "readlink -f ./build.xml" -Dbuilddir="readlink -f /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests" -Djardir="readlink -f /home/rbiciste/MonetDB-11.13.9/java/tests" -Dbasedir="readlink -f .`" jar_jdbctests
Buildfile: /home/rbiciste/MonetDB-11.13.9/java/tests/build.xml

prepare:

find_driver:

jdbc:

compile:
[echo] Compiling JDBC tests
[javac] Compiling 1 source file to /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests
[javac] ----------
[javac] 1. ERROR in /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests/src/nl/cwi/monetdb/jdbc/MonetDriver.java (at line 1)
[javac] /*
[javac] ^
[javac] The type java.sql.Wrapper cannot be resolved. It is indirectly referenced from required .class files
[javac] ----------
[javac] 2. ERROR in /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests/src/nl/cwi/monetdb/jdbc/MonetDriver.java (at line 29)
[javac] import java.sql.SQLFeatureNotSupportedException;
[javac] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[javac] The import java.sql.SQLFeatureNotSupportedException cannot be resolved
[javac] ----------
[javac] 3. ERROR in /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests/src/nl/cwi/monetdb/jdbc/MonetDri
[javac] ver.java (at line 377)
[javac] public Logger getParentLogger() throws SQLFeatureNotSupportedException {
[javac] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[javac] SQLFeatureNotSupportedException cannot be resolved to a type
[javac] ----------
[javac] 4. ERROR in /home/rbiciste/MonetDB-11.13.9/java/tests/jar_jdbctests/src/nl/cwi/monetdb/jdbc/MonetDriver.java (at line 378)
[javac] throw new SQLFeatureNotSupportedException("java.util.logging not in use", "0
[javac] A000");
[javac] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[javac] SQLFeatureNotSupportedException cannot be resolved to a type
[javac] ----------
[javac] 4 problems (4 errors)

BUILD FAILED
/home/rbiciste/MonetDB-11.13.9/java/tests/build.xml:60: Compile failed; see the compiler error output for details.

Total time: 1 second
make[3]: *** [jar_jdbctests_ant_target] Error 1
make[3]: Leaving directory /home/rbiciste/MonetDB-11.13.9/java/tests' make[2]: *** [install] Error 2 make[2]: Leaving directory /home/rbiciste/MonetDB-11.13.9/java/tests'
make[1]: *** [install-recursive] Error 1
make[1]: Leaving directory `/home/rbiciste/MonetDB-11.13.9/java'
make: *** [install] Error 2
rbiciste@rbiciste-suse:~/MonetDB-11.13.9/java>

Comment 19244

Date: 2013-10-04 15:42:39 +0200
From: MonetDB Mercurial Repository <>

Changeset 868919fa3005 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=868919fa3005

Changeset description:

Applied patch from bug #3356.
The patch looks good, applies cleanly, and the tests pass.

Comment 19245

Date: 2013-10-04 15:44:25 +0200
From: @sjoerdmullender

Assuming that the patch does indeed fix the problem, I'm closing this.
We do still need a test (hence the keyword).

Comment 19247

Date: 2013-10-04 21:02:00 +0200
From: @grobian

(In reply to comment 14)

Assuming that the patch does indeed fix the problem, I'm closing this.
We do still need a test (hence the keyword).

as noted in comment 11, the patch breaks the constraint dumping test, e.g.

http://monetdb.cwi.nl/testweb/web/showtestoutput.php?serial=49143:868919fa3005&target=GNU-Darwin-i386-propcheck&module=sql&test=sql%2Fjdbc%2Ftests%2FTest_JdbcClient&which=out

please revert.

Comment 19248

Date: 2013-10-04 21:03:20 +0200
From: @grobian

(http://monetdb.cwi.nl/testweb/web/testchange.php?test=49143:868919fa3005/GNU-Darwin-i386-propcheck/sql/mTests/sql%2Fjdbc%2Ftests%2FTest_JdbcClient)

Comment 19249

Date: 2013-10-05 10:55:28 +0200
From: MonetDB Mercurial Repository <>

Changeset 4e1be37a5d65 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=4e1be37a5d65

Changeset description:

Hopefully better fix for bug #3356.
The only thing this patch does is to rearrange columns to comply with
the specification (unlike the previous attempt at a fix which did a
bit more).
All JDBC tests pass.

Comment 19250

Date: 2013-10-07 17:32:29 +0200
From: MonetDB Mercurial Repository <>

Changeset 6ba6b5e2f37f made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6ba6b5e2f37f

Changeset description:

Added test for bug #3356.

Comment 19251

Date: 2013-10-07 17:33:06 +0200
From: @sjoerdmullender

Fixed with a smaller patch than the original.
Also added a test.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant