How to specify schema for SQL Server in JDBC Spring configuration

Question

We are using the org.apache.commons.dbcp.BasicDataSource as parent class for our datasources in Spring.

Is it possible to specify which schema to use in either the "url" or in another property in the DBCP datasource?

Shogun

It has been answered since. Please follow this pattern: https://mcmap.net/q/610929/-how-can-i-set-the-current-schema-for-db2-using-hibernate-jdbc – Prajna

That is for DB2, I don't think it works for SQL Server, as currentSchema is not listed in the available properties: msdn.microsoft.com/en-us/library/ms378988.aspx – Cannibalism

Answer 1

I don't think it's possible to specify the schema since this is a function of the user, not the JDBC session. You should probably create a user with their default schema set to each schema you want to use, and then specify the user in the dbcp configuration.

Clog

Answer 2

I believe that what you want is not really an schema, but a database. If this works for you, you can use the option ....;databaseName= at the jdbc url.

Colombes

Answer 3

Just give schema=SCHEMANAME

For example jdbc:sqlserver://ipaddress:port;database=DBName;schema=SCHEMANAME;selectMethod=cursor;sendStringParametersAsUnicode=false;IntegratedSecurity=true

Dollarbird

This example can not work for sql server as it doesn't support schemas in that way, please remove this solution. – Sticktight