Is it possible to add support for MSSQL?

[lundeen-bryan]

Hello, I know we can connect to mysql and postgresql but is it possible to add support for MSSQL?

Thank you so much for your hard work on this extension! ⭐

[l-v-yonsama]

Hi, It's possible to support for MSSQL. It's going to take a little while, so just wait and see.

[lundeen-bryan]

Thank you so much!

[l-v-yonsama]

I've changed to be able to use SQL Server in Ver 0.4.0.

Please try it after upgrading.

[lundeen-bryan]

It does connect, and I can run queries to SQLServer, however I just wanted to report a few things that might be improved at some point. Here is how it looks for me. This may be because I use an Integrated windows authentication to connect to SQLServer, but in this case I created a login just for DB Notebook.

Then here is the connection settings:

And to test it, I had to write fully qualified FROM statement like this:

This fully qualified FROM statement returns results as expected, but it would be nice if I didn't have to write the full path.

But I really appreciate you adding this feature. I'm still learning SQL but SQLServer is what they use at work. I'm hoping to get a job that pays more. I'm just a clerk at the office and I hope to get into IT using SQL. So I'm teaching myself.

Anyway, you helped me and when I get a better job, I owe you a coffee, so let me know if you have one of those "buymeacoffee" features on your github.

If you think I should setup SQLServer differently let me know. Maybe there is another way I can set it up so that I don't have to type in full paths in the FROM statement.

Thank you.

[lundeen-bryan]

I will wait to see if you have more things you want me to try when creating the SQLServer login. If you don't have any suggestions, it's ok to close this thread. Thanks again.

[lundeen-bryan]

UPDATE I unchecked the "Show only default schema" and now I can browse the database and see the different tables. I still have to enter a fully qualified path to return results. I even tried "dbo.Person" but that didn't work. Only Person.Person or Person.Address or HumanResources.Employee for example will work.

[l-v-yonsama]

@lundeen-bryan Person.Person's first is the schema name, right?

I think the problem of not being able to SELECT by only table name that is a matter of specifying the default-schema for the connecting user.

As an example of my environment, access to the DEPT table that I have in the default schema of the connecting user does not require schema modification. In contrast, the CUSTOMER table is owned by schema0, so access to it requires schema0 qualification.

• DB: testdb
  • SCHEMA: schema0
    • TABLE: customer
  • SCHEMA: schema1 ← My Default Schema!!
    • TABLE: DEPT

Sample of creating a user by specifying a default schema

• https://github.com/l-v-yonsama/db-drivers/blob/main/__tests__/setup/mssql.ts#L88-L121

[l-v-yonsama]

I don't know any clerk in the office who can write SQL statements. That's great! I'm sure you'll become an IT engineer, so good luck.

[lundeen-bryan]

Thank you so much. Yes, I am basically hired for administrative support, but I made friends with one of the programmers and he let me help out on SQL projects when he doesn't have time. I really appreciate the hard work you put into this Extension.

The problem may be the way I created a user in SSMS. So I will compare your screenshots and try to figure out the difference.

[lundeen-bryan]

I really appreciate your work on this. I had a problem opening a SQL Server database and got the error "STRING_AGG" is not a recognized built-in function name.

This only happens in one database table, but other tables in this database (also using SQL Server will work fine. It also worked in a view in the same database. So I'm not sure what is different about this table. It's at my work so I can't allow you access to look directly at it. If there is an error log I can upload you can let me know where to find that. thank you!

[l-v-yonsama]

Both MySQL and SQLServer use postgresql's for parsing SQL statements. (I should really change it for each DB product I use, but I don't have time, so I use postgresql's.)

SQL is programmed to be rewritten to match the Postgres syntax before parsing, but not all of it is supported. In this case, I assumed the error was in the WITHIN GROUP clause of "STRING_AGG". And we have addressed that part of the problem, so please try to fix it when you have time.

Where to upload the error log? Is it this Issue? What is everyone else doing about it? My personal Email?

[lundeen-bryan]

Ok I think I understand. It only happens because postresqul doesn't use WITHIN GROUP clause. No problem, I will try to work around this. I appreciate you addressing this so quickly! At some point I will learn typescript and maybe I can help people like you when you are loaded with work. I just want to emphasize I appreciate you very much.

[lundeen-bryan]

Microsoft SQL Server support tested and works with only some features not working such as using "WITHIN GROUP" clause which is not supported in postgressql - the basis for most sql transactions.