SQL Server: Support explicit table schema prefix in queries

[robvari]

Hi.

We have an issue when using Ktorm with SQL Server. The queries do not work with a db user which has no default schema, since the schema defined in the connection (e.g. via spring data source) is ignored / not supported by the SQL server driver.

It would be good to be able to enforce a schema prefix in the queries / pass a schema in the entity definition.

E.g.

object DimDate : Table<Nothing>("DimDate", "MySchema") { ... }

=> 

SELECT * FROM MySchema.DimDate

It seems this is not possible right now. Is there a suggested work around?

[vincentlauvlwj]

The same issue as #89

[robvari]

Thanks for checking my ticket. Yes, it is the same root issue. It is a bit more severe for SQL Server since the work around via connection URL is not working. Its not supported by SQL Server.

See e.g. https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-4-1-compliance-for-the-jdbc-driver?view=sql-server-ver15

[hangingman]

@vincentlauvlwj
Currently, this schema issues are opened by name of SQL Server(#154) and PostgreSQL(#89).
But I want this feature for MySQL also.
So, I think it may be core feature if there is no problem.

EDIT
In my case, I needed to set transaction for multiple tables on different schemas.
It's better to switch database schema before query or require user to specify schema if there is no schema specified.

[vincentlauvlwj]

@hangingman Yes, will be a core feature.

[vincentlauvlwj]

Supported in Ktorm 3.1.0