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

Monet ODBC gives wrong schema information #3337

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

Monet ODBC gives wrong schema information #3337

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-08-08 21:49:33 +0200
From: Dieter <>
To: clients devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: mayerd

Last updated: 2013-09-27 13:47:15 +0200

Comment 18983

Date: 2013-08-08 21:49:33 +0200
From: Dieter <>

User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; MALC)
Build Identifier:

Accessing MonetDB database (Fedora) via "OLEDB-Provider for ODBC" (Windows7) the application gets wrong column data because of wrong schema information for the columns in the result set.

When querying the table via Excel the output is displayed right, because Excel doesn't care about correct schema information, but requests all column data in WCHAR-Format from ODBC. Therefore the DECIMAL column and the VARCHAR column are obviously both in the right. But this is a implementation of Microsoft knowing that there were many careless implemented ODBC drivers in ealier years.

But any other application depends on correct schema information from the ODBC driver and that seems to be the reason why they get the wrong column data from the MonetDB ODBC driver. You can check it with a simple VB Script on Windows 7.

When accessing data with Visual Basic by using ADO, the "Microsoft OLEB-Provider for ODBC" strongly relies on this and also gets the wrongly coded data. A test script "monetDB.vbs" is attached. Here we can see that the schema information is not as expected by the standard.

Reproducible: Always

Steps to Reproduce:

1.create table voc.customer
( cname varchar(100),
sales decimal(15, 2) );

  1. Then I imported following customer.csv (in UTF-8 format):
    "cname";"sales"
    "Müller Österreich - Polish: ońśłąęóźż";50.78

with:
COPY 1 OFFSET 2 RECORDS INTO voc.customer from '/home/aruba/customer.csv' USING DELIMITERS ';','\r\n', '"' NULL as '' ;

  1. select * from customer;

Actual Results:

cname sales


Müller Österreich - Polish: ońśłąęóźż 50

Expected Results:

cname sales


Müller Österreich - Polish: ońśłąęóźż 50,78

A test script "monetDB.vbs" is attached. Executing this with double-click on Windows we can see that the schema information is not as expected by the standard.
It gives us:

  • for column cname the Type=129 char (but you store a NVARCHAR)
  • for column sales the Precision 15 and Scale also 15 (should 2 in our case)

"cname" should be given as NVARCHAR in SQL-term, that means Type 202=adVarWChar out of the ODBC driver
and
"sales" should be given as DECIMAL with Precision 15 and Scale 2 together with Type 14 or 131 out of the ODBC driver

I would expect that if the schema information given by the Monet ODBC driver (for Windows) to the application will be corrected, that the described problems are solved.

Comment 18984

Date: 2013-08-08 21:52:39 +0200
From: Dieter <>

Created attachment 215
MonetDB.vbs Testscript for retrieving column/schema inf.

Attached file: monetDB.vbs (application/octet-stream, 4128 bytes)
Description: MonetDB.vbs Testscript for retrieving column/schema inf.

Comment 18985

Date: 2013-08-08 22:02:18 +0200
From: Dieter <>

The Testscript should be slightly changed if using another System DSN as "MonetDB" on Windows and other user ("voc") pw ("voc") for my "voc" database.

The output file Schema.txt will be created in folder c:\temp.
So make shure it exists before executing the script.

Comment 19079

Date: 2013-08-26 16:42:24 +0200
From: MonetDB Mercurial Repository <>

Changeset 523e5550df9d 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=523e5550df9d

Changeset description:

Fix for part of bug #3337: return proper value for decimal_digits in SQLcolumns().

Comment 19087

Date: 2013-08-27 15:12:01 +0200
From: MonetDB Mercurial Repository <>

Changeset ae491bc2025a 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=ae491bc2025a

Changeset description:

Inside the ODBC driver, treat server's character data as wide characters.
This solves another part of bug #3337: the string data is now displayed
correctly.

Comment 19088

Date: 2013-08-27 15:12:03 +0200
From: MonetDB Mercurial Repository <>

Changeset a6d834db6449 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=a6d834db6449

Changeset description:

Initialize precision and scale information with data from server.
This fixes part of bug #3337: the decimal value is now displayed in full.

Comment 19089

Date: 2013-08-27 15:20:24 +0200
From: @sjoerdmullender

Most of the issues reported have been fixed.
However, one difference with the expected output remains. The type reported for the cnames column is now 130 (adWChar) and not 202 (adVarWChar). Why, I don't know. The column is now reported as being of type SQL_WVARCHAR, but something somewhere doesn't translate that to adVarWChar, but to adWChar.

Comment 19092

Date: 2013-08-27 21:31:59 +0200
From: Dieter <>

Hi Sjoerd,

thanks for your really engaged efforts. I'd like to test your corrections.
When do you expect to publish the next release of MonetDB with these changes?

Or would you recommend to compile the changed sources by myself? (Today I dont have any experience to do a compile and make with your source distribution).

Kind regards, Dieter

Comment 19093

Date: 2013-08-27 22:03:20 +0200
From: @sjoerdmullender

Depending on your platform, I could give you a precompiled version.
So, what is your platform?

Comment 19094

Date: 2013-08-27 22:29:44 +0200
From: Dieter <>

I am using the MonetDb ODBC driver on Windows 7 64bit.
Can you supply a non-debug (release compiled) version because of best runtime performance with a short explanation what I must do to install it.

Comment 19095

Date: 2013-08-28 09:11:26 +0200
From: @sjoerdmullender

(In reply to comment 9)

I am using the MonetDb ODBC driver on Windows 7 64bit.
Can you supply a non-debug (release compiled) version because of best
runtime performance with a short explanation what I must do to install it.

I'll send a mail off-list with instructions.

Comment 19137

Date: 2013-09-06 15:31:28 +0200
From: @sjoerdmullender

Things seem to work.

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