Date: 2015-10-16 22:55:05 +0200
From: Dann Corbit <>
To: clients devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: martin.van.dinther

Last updated: 2018-03-29 15:39:19 +0200

Comment 21344

Date: 2015-10-16 22:55:05 +0200
From: Dann Corbit <>

User-Agent: Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0
Build Identifier:

using monetdb account, I created the following table:
CREATE TABLE "sys"."all_data_types" (
"charnotnull" CHAR(10) NOT NULL,
"charnull" CHAR(10),
"varcharnotnull" VARCHAR(10) NOT NULL,
"varcharnull" VARCHAR(10),
"textnotnull" CHARACTER LARGE OBJECT NOT NULL,
"textnull" CHARACTER LARGE OBJECT,
"blobnotnull" BINARY LARGE OBJECT NOT NULL,
"blobnull" BINARY LARGE OBJECT,
"decimalnotnull" DECIMAL(18,9) NOT NULL,
"decimalnull" DECIMAL(18,9),
"tinyintnotnull" TINYINT NOT NULL,
"tinyintnull" TINYINT,
"smallintnotnull" SMALLINT NOT NULL,
"smallintnull" SMALLINT,
"intnotnull" INTEGER NOT NULL,
"intnull" INTEGER,
"bigintnotnull" BIGINT NOT NULL,
"bigintnull" BIGINT,
"realnotnull" REAL NOT NULL,
"realnull" REAL,
"doublenotnull" DOUBLE NOT NULL,
"doublenull" DOUBLE,
"booleannottnull" BOOLEAN NOT NULL,
"booleantnull" BOOLEAN,
"datenotnull" DATE NOT NULL,
"datenull" DATE,
"timenotnull" TIME(3) NOT NULL,
"timenull" TIME(3),
"timewithtimezonenotnull" TIME(3) WITH TIME ZONE NOT NULL,
"timewithtimezonenull" TIME(3) WITH TIME ZONE,
"timestampnotnull" TIMESTAMP(3) NOT NULL,
"timestampnull" TIMESTAMP(3),
"timestampwithtimezonenotnull" TIMESTAMP(3) WITH TIME ZONE NOT NULL,
"timestampwithtimezonenull" TIMESTAMP(3) WITH TIME ZONE,
"intervalnotnull" INTERVAL DAY TO SECOND NOT NULL,
"intervalnull" INTERVAL DAY TO SECOND,
"bigintnotnullauto" BIGINT NOT NULL DEFAULT next value for "sys"."seq_7877",
"jsonnotnull" JSON NOT NULL,
"jsonnull" JSON,
"urlnotnull" URL NOT NULL,
"urlnull" URL,
"uuidnotnull" UUID NOT NULL,
"uuidnull" UUID,
"inetnotnull" INET NOT NULL,
"inetnull" INET,
CONSTRAINT "all_data_types_bigintnotnullauto_pkey" PRIMARY KEY ("bigintnotnullauto")
);

When I try to access the table using ODBC, it is not visible in the list of tables.

Reproducible: Always

Steps to Reproduce:

1.Create a table like this:
CREATE TABLE "sys"."all_data_types" (
"charnotnull" CHAR(10) NOT NULL,
"charnull" CHAR(10),
"varcharnotnull" VARCHAR(10) NOT NULL,
"varcharnull" VARCHAR(10),
"textnotnull" CHARACTER LARGE OBJECT NOT NULL,
"textnull" CHARACTER LARGE OBJECT,
"blobnotnull" BINARY LARGE OBJECT NOT NULL,
"blobnull" BINARY LARGE OBJECT,
"decimalnotnull" DECIMAL(18,9) NOT NULL,
"decimalnull" DECIMAL(18,9),
"tinyintnotnull" TINYINT NOT NULL,
"tinyintnull" TINYINT,
"smallintnotnull" SMALLINT NOT NULL,
"smallintnull" SMALLINT,
"intnotnull" INTEGER NOT NULL,
"intnull" INTEGER,
"bigintnotnull" BIGINT NOT NULL,
"bigintnull" BIGINT,
"realnotnull" REAL NOT NULL,
"realnull" REAL,
"doublenotnull" DOUBLE NOT NULL,
"doublenull" DOUBLE,
"booleannottnull" BOOLEAN NOT NULL,
"booleantnull" BOOLEAN,
"datenotnull" DATE NOT NULL,
"datenull" DATE,
"timenotnull" TIME(3) NOT NULL,
"timenull" TIME(3),
"timewithtimezonenotnull" TIME(3) WITH TIME ZONE NOT NULL,
"timewithtimezonenull" TIME(3) WITH TIME ZONE,
"timestampnotnull" TIMESTAMP(3) NOT NULL,
"timestampnull" TIMESTAMP(3),
"timestampwithtimezonenotnull" TIMESTAMP(3) WITH TIME ZONE NOT NULL,
"timestampwithtimezonenull" TIMESTAMP(3) WITH TIME ZONE,
"intervalnotnull" INTERVAL DAY TO SECOND NOT NULL,
"intervalnull" INTERVAL DAY TO SECOND,
"bigintnotnullauto" BIGINT NOT NULL DEFAULT next value for "sys"."seq_7877",
"jsonnotnull" JSON NOT NULL,
"jsonnull" JSON,
"urlnotnull" URL NOT NULL,
"urlnull" URL,
"uuidnotnull" UUID NOT NULL,
"uuidnull" UUID,
"inetnotnull" INET NOT NULL,
"inetnull" INET,
CONSTRAINT "all_data_types_bigintnotnullauto_pkey" PRIMARY KEY ("bigintnotnullauto")
);

2. Try to access the table using ODBC

Actual Results:

The table does not show up

Expected Results:

The table should be visible.

Perhaps not visible due to non-odbc types, the non-odbc types (such as UUID, JSON) could be mapped as varchar() for ODBC purposes.

Comment 21345

Date: 2015-10-16 23:08:10 +0200
From: Dann Corbit <>

Command to populate the table with a single row:

sql>insert into "all_data_types"
more>(
more>"charnotnull",
more>"charnull",
more>"varcharnotnull",
more>"varcharnull",
more>"textnotnull",
more>"textnull",
more>"blobnotnull",
more>"blobnull",
more>"decimalnotnull",
more>"decimalnull",
more>"tinyintnotnull",
more>"tinyintnull",
more>"smallintnotnull",
more>"smallintnull",
more>"intnotnull",
more>"intnull",
more>"bigintnotnull",
more>"bigintnull",
more>"realnotnull",
more>"realnull",
more>"doublenotnull",
more>"doublenull",
more>"booleannottnull",
more>"booleantnull",
more>"datenotnull",
more>"datenull",
more>"timenotnull",
more>"timenull",
more>"timewithtimezonenotnull",
more>"timewithtimezonenull",
more>"timestampnotnull",
more>"timestampnull",
more>"timestampwithtimezonenotnull",
more>"timestampwithtimezonenull",
more>"intervalnotnull",
more>"intervalnull",
more>"jsonnotnull",
more>"jsonnull",
more>"urlnotnull",
more>"urlnull",
more>"uuidnotnull",
more>"uuidnull",
more>"inetnotnull",
more>"inetnull"
more>)
more>values (
more>'a',
more>NULL,
more>'b',
more>NULL,
more>'c',
more>NULL,
more>'',
more>NULL,
more>123456789.123456789,
more>NULL,
more>-127,
more>NULL,
more>-32767,
more>NULL,
more>-2147483647,
more>NULL,
more>-9223372036854775807,
more>NULL,
more>-1e38,
more>NULL,
more>-1e308,
more>NULL,
more>true,
more>NULL,
more>current_date(),
more>NULL,
more>current_time(),
more>NULL,
more>current_timestamp(),
more>NULL,
more>current_timestamp(),
more>NULL,
more>current_timestamp(),
more>NULL,
more>1,
more>NULL,
more>'{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}',
more>NULL,
more>'http://www.connx.com',
more>NULL,
more>uuid(),
more>NULL,
more>'192.168.100.128/25',
more>NULL
more>);
1 affected row, last generated key: 2 (28.589ms)

Comment 21346

Date: 2015-10-16 23:54:07 +0200
From: Dann Corbit <>

I can query this table using JDBC via SQLSquirrel.

Comment 21361

Date: 2015-10-21 12:38:21 +0200
From: @sjoerdmullender

In what sense is the table not visible through ODBC?
I've tried reproducing the problem, but when I query through ODBC, I see the table. I've tried SQLTables with "%" for the table name and table type parameters, and also "%" for the table name and "TABLE" for the table type parameter. I've also tried a call to SQLExecDirect with the query "select * from all_data_types".

Comment 21383

Date: 2015-10-23 02:00:54 +0200
From: Dann Corbit <>

Created attachment 362
Attempt to perform odbc import

Table exists

Attached file: monetdb-import-attempt.png (image/png, 8052 bytes)
Description: Attempt to perform odbc import

Comment 21384

Date: 2015-10-23 02:01:19 +0200
From: Dann Corbit <>

Created attachment 363
collecting the information about the table fails

Attached file: monetdb-import-error.png (image/png, 11109 bytes)
Description: collecting the information about the table fails

Comment 21388

Date: 2015-10-23 15:12:30 +0200
From: @sjoerdmullender

Can you please create a log and attach it to this bug report.

To create a log, you need to start the application that uses the MonetDB ODBC driver with an extra environment variable. Probably the easiest way to do that is to start a cmd window (Start -> Run... -> cmd.exe) and in that window type

set ODBCDEBUG=C:....\odbc.log

and then start your application from this window.
The bit after the = should be the absolute pathname of a file you can write to. After the application is done it should contain a log of the complete interaction with the driver. Before attaching the file, please make sure it doesn't contain any sensitive information.

Comment 21500

Date: 2015-11-10 22:08:40 +0100
From: Dann Corbit <>

Created attachment 365
Import from Monetdb where the only object selected was sys.all_data_types

A listing of all objects was made, and the item sys.all_data_types was selected.

Attached file: monetdb2.7z (application/octet-stream, 28762 bytes)
Description: Import from Monetdb where the only object selected was sys.all_data_types

Comment 21501

Date: 2015-11-10 22:09:32 +0100
From: Dann Corbit <>

Created attachment 366
This import requested every object in the database, and has several additional import failures

Attached file: monetdb.7z (application/octet-stream, 27530 bytes)
Description: This import requested every object in the database, and has several additional import failures

Comment 21518

Date: 2015-11-13 02:46:54 +0100
From: Dann Corbit <>

In tracing the code, it appears that sys.all_data_types is listed as a procedure rather than as a table.

Comment 26281

Date: 2018-03-23 22:01:56 +0100
From: Martin van Dinther <<martin.van.dinther>>

Your last attachment of the monetdb.log showed there was an SQL error:
SELECT: no such column 'p.sql'
when calling SQLProceduresW.
That was a bug in the implementation of the SQL query executed by SQLProceduresW(). It contained an old invalid SQL string part "p.sql = true and ".
It has been fixed (replaced with "p.language >= %d and ") in the upcoming Mar2018 release.
Calling SQLProceduresW() should no longer return an SQL error.

Comment 26282

Date: 2018-03-23 22:17:42 +0100
From: Martin van Dinther <<martin.van.dinther>>

See also
https//devmonetdborg/hg/MonetDB?cmd=changeset;node=0a1ca816b858

Comment 26318

Date: 2018-03-29 15:39:19 +0200
From: @sjoerdmullender

The Mar2018 version has been released.