Vscode-sqltools: ODBC/JDBC support

[FireFighter1017]

This is a guideline to help us to understand SQLTools Issues.

You don't need to stick to this, but please try to guide us to reproduce the errors or understand your feature requests.

Issue Type

• [X] Feature Request
• [ ] Bugfix/Errors
• [ ] Questions
• [ ] Other

Prerequisites (For bugfixes)

• [ ] Are you running the latest version?
• [ ] Did you check the logs?
• [ ] Did you check the Setup?

Description

I would like to connect not only to MSSQL or MySQL but also to DB2, CSV, Access, etc. using ODBC or JDBC.

Steps to Reproduce (For bugfixes)

1. [First Step]
2. [Second Step]
3. [and so on...]

Expected behavior: [What you expected to happen]
Connection config that allows for JDBC connection and URL
No need to define supported drivers, as this would come along with PATH or CLASSPATH

Actual behavior: [What actually happened]

Versions

• Version: vX.Y.Z
• OS: ...
• SGDB: MySQL, PostgresSQL...

You can get this information from executing SQLTools: Version from VSCode Quick Open.

[mtxr]

Hello @FireFighter1017!

Could you provide some queries to:

• fetch all database columns;
• fetch limited records of a table;
• fetch all database tables;

Like in mysql.ts file?

With that, it will help me to start this FT.

[mtxr]

@BuZZ-dEE, @SimonDever and @dakaraphi. Feel free to help too.

[FireFighter1017]

DB2 for i5OS (using sql naming convention):

• To get all table columns : Select * from qsys2.syscolumns
• To get all tables : Select * from qsys2.systables
• Fetch first 100 records : Select * from qsys.syscolumns fetch first 100 rows only

MS Access:

• Stack Overflow post: How can I get table names from an MS Access Database?

As for CSV, I was just fishing. I'm not sure it is possible to query CSV files using SQL without having some sort of interpreter in between that can execute SQL statements using python, ruby or R. Most obvious way would be to import the CSVs in MSSQL and run queries from there. Some sort of script that would handle this would be nice though.

Though this is not written in code like in your mysql.ts file, I can have a look later today and see if I can contribute to create one for DB2 at very least.

[cmarin1983]

Will you have support for Sybase?

[ZuluagaSD]

Will this help with connecting to an Impala DB using the Impala ODBC?

[BrettMoan]

Any ideas on what kind of work would need done to add this functionality? It something I could see myself using at work if you were able to get it working. Depending on what skills would be needed to implement I could try and set aside some personal time to contribute.

[mtxr]

I'm currently working to add Oracle and SQLite. If we succeed, I will be able to add this functionality too :pray:

[adainz]

If you could also add support for Teradata that would be amazing!

[mtxr]

Hey guys, could you please submit a pull request of this feature?

As you are a user that understands the needs for ODBC/JDBC, you could follow this structure (https://github.com/mtxr/vscode-sqltools/tree/master/packages/core/dialect/sqlite) to accomplish the same result. Is it ok?

As you can see, it's not difficult, it's almost to define the queries it needs to run.

That's the first step. After creating the dialect, we just need to add it to the UI.

Thanks!

[mtxr]

Does anyone know a good npm package, supporting all platforms and widely used?

I'll work on this issue soon, so I want to get all information I can for that.

[BrettMoan]

This one looks promising for the odbc side:

https://github.com/wankdanker/node-odbc/blob/master/README.md

[ChrisTuckerNM]

How is the pull request coming?

[ChrisTuckerNM]

Any movement on local repos for DB2?

[mtxr]

Didn't have time to work on it yet. But PRs are welcome, for someone with knowledge on DB2 would be basic add the driver, add the queries and some copy & paste from other dialects you could use as an example

[mtxr]

For those who want to work in this feature request, here is a guide of how to add a new dialect.

https://vscode-sqltools.mteixeira.dev/contributing/support-new-drivers

[mtxr]

@FireFighter1017 @ChrisTuckerNM DB2 support was developed and release by @EpicEric .

This issue covers many things and as we need specific queries for each RDBMS, I'll close it for now.

if you still need drivers to connect to CSV, Access and etc. it's better to open individual issues for each driver, and if possible write the queries regarding that driver to display tables, columns and functions.

All driver can use OBDC / JDBC behind it, but it needs to be individually written.

Closing.

[SimonDever]

Where was it released? His fork is behind yours and I can't find where you've done a PR for it.

[mtxr]

It was release a few weeks ago. It's part of SQLTools extension. Just install version v0.21.2 and you should be able to see it in the connection assistant

[EpicEric]

It wasn't me that added DB2 support, it was @snyang :sweat_smile:

The PR is #391

[SimonDever]

Thank you

[mtxr]

It wasn't me that added DB2 support, it was @snyang

The PR is #391

Lol. That's true. Thanks @EpicEric and @snyang

[a019647]

It's sad that this was never supported. JDBC and ODBC are highly useful and utilized ways to connect to databases generically without the need to customize the client. It sounds like you've got a tight coupling between the dialect and the driver as a whole. This isn't normal or necessary. Products like Hibernate allow you to connect to any JDBC source generically. Since Hibernate uses canned query approaches, it also has need of a dialect definition, but does not couple this to the driver API in any way. Dialects are specified independently. In addition, Hibernate defines more generic dialects that can often be used across multiple databases for which no specific dialects have yet been defined.