Bug 6800

Summary: Please add information_schema (ANSI SQL norm)
Product: SQL Reporter: Simon AUBERT <simon.aubert>
Component: allAssignee: Martin van Dinther <martin.van.dinther>
Status: NEW ---    
Severity: enhancement CC: martin.van.dinther
Priority: Normal    
Version: 11.35.3 (Nov2019)   
Hardware: All   
OS: All   

Description Simon AUBERT 2019-12-11 21:04:03 CET

According to wikipedia :
"In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database"

I think such a feature would help the support of monetdb by more tools (such as Alteryx.. ) and it's never a bad thing when you follow the SQL norm.

Best regards,

Comment 1 Martin van Dinther cwiconfidential 2019-12-19 13:56:40 CET
Hello Simon,
Thanks for reporting this request.
This feature is on our ToDo-list, so we will let you know once it is available.

Question: I guess Alteryx connects to MonetDB via the ODBC interface. ODBC (and JDBC) support standardised programming interfaces to retrieve schema information (such as SQLTables(), SQLColumns()). Usually database tools use those APIs to get the database metadata. Does Alteryx not use those APIs to retrieve schema information?
Comment 2 Simon AUBERT 2019-12-19 20:58:08 CET
Hello Martin,

First of all, I'm very happy this feature is on your todo. Thanks :)

To answer your question, from what I have understood to Alteryx in this point, it throws a set of query to guess what database it is. Something like : 
Mar 01 11:37:21.318 INFO  5264 HardyDataEngine::Prepare: Incoming SQL: select USER(), APPLICATION_ID() from system.iota

Mar 01 11:37:22.863 INFO  5264 HardyDataEngine::Prepare: Incoming SQL: select USER as USER_NAME from SYSIBM.SYSDUMMY1

Mar 01 11:37:23.454 INFO  5264 HardyDataEngine::Prepare: Incoming SQL: select * from rdb$relations

Mar 01 11:37:23.546 INFO  5264 HardyDataEngine::Prepare: Incoming SQL: select first 1 dbinfo('version', 'full') from systables
etc, etc, etc...

 Then, there is also the Visual Query Builder that's supposed to show a treemap of databases/schemas/tables/columns. As of today, this Visual Query Builder doesn't retrieve the columns in MonetDB. Logically, if information_schema is implemented, as it's in the norm, it should work without having the guys @ Alteryx hard coding the sys schema.

I don't know about SQLColumns(), I guess it isn't used.

For my information, do you plan to add tables in the information_schema or just views  such as SQL Server?

Best regards,

Comment 3 Martin van Dinther cwiconfidential 2020-01-14 18:13:04 CET
Hello Simon,

The information_schema will contain system views.

Can you provide us some SQL queries which actually query the information_schema as generated by Alteryx?

Does Visual Query Builder show you currently the database?
And in it the schemas?
And in those the available tables?
So only the columns (of a table or view) are not shown?

FYI: SQLTables() and SQLColums() are C functions standardised in the ODBC API, not SQL tables or functions. So you do not call them using SQL but from compiled C code.
If the schemas and tables and shown Visual Query Builder, but the columns not, it may be a problem in the MonetDB ODBC driver SQLColumns() implementation.
Can you confirm that Alteryx is using MonetDB ODBC driver to connect to MonetDB server?
If so, could you make a log file of the ODBC calls made. This log file can be created by turning logging on in the ODBC Driver Manager.
Comment 4 Simon AUBERT 2020-01-14 21:46:48 CET
Hello Martin,

Thanks for the answer.

1/Yes, Alteryx uses the ODBC driver. It's some kind of what they call "generic odbc connection". Some databases (PSQL, MSQL, Vertica, Oracle..) have a dedicated entry in the menu.

2/Indeed, I can see the schemas and the tables, not the columns.

3/I will get back to you as soo as I can on the queries/log you ask

Best regards,