如何在jboss数据源URL中指定sql server的当前架构？ [英] How can I specify the current schema for sql server in a jboss data source url?

问题描述

我有一些像这样的SQL查询

I've got some SQL queries like this

select user_id from 
table_user where lower(email_address)=? and password=?

最近更新了应用程序的架构，但我真的不想更新每个SQL查询应用程序。有没有办法从JBOSS连接端指定当前架构？

The schema for the application was recently updated but I don't really want to update every SQL query in the application. Is there a way to specify the current Schema from the JBOSS connection end?

旧连接： jdbc：sqlserver：// myserver：1433; DatabaseName = db
尝试： jdbc：sqlserver：// myserver：1433; DatabaseName = db; currentSchema = abc

我尝试使用 currentSchema 但这没有帮助，我在运行查询时遇到了一个丢失的对象异常（因为我假设这些正在寻找dbo）。是否有任何方法可以更新查询，因为我知道所有查询都将在架构上运行 abc ？

I tried using currentSchema but that didn't help, I get a missing object exception when I run the queries (since I assume these are looking under dbo). Is there any way around updating the queries since I know that all the queries will run on schema abc?

推荐答案

这些是可用的连接属性对于Microsoft JDBC 4.0驱动程序。我在这个列表中没有看到 currentSchema ，并且没有看到任何允许您在连接字符串中指定特定模式的驱动程序。

These are the available connection properties for Microsoft JDBC 4.0 driver. I don't see currentSchema in this list, and haven't seen any driver that allows you to specify a particular schema in the connection string.

由于您不想使用架构更新SQL，因此可以创建每个对象的默认（dbo）模式中的同义词。例如：

Since you don't want to update SQL with the schema, you could create synonyms in default (dbo) schema for each object. For example:

USE tempdb;
GO
-- create test schema
CREATE SCHEMA test AUTHORIZATION dbo;
GO

-- create table in test schema
CREATE TABLE test.tablename (columnname int null);

-- select from tablename in default schema will fail
SELECT * FROM tablename;
GO

-- create synonym mapping test.tablename to dbo.tablename
CREATE SYNONYM [dbo].[tablename] FOR [server].[tempdb].[test].[tablename]

-- -- select from tablename synonym will succeed
SELECT * FROM tablename;

-- cleanup
DROP SYNONYM [dbo].[tablename];
DROP TABLE [test].[tablename];
DROP SCHEMA [test];

您可以使用以下代码生成 CREATE SYNONYM 用户对象的语句。如果使用它，则需要在执行前更新变量值和查看语句。没有明示或暗示的保证：）

You can use the below code to generate CREATE SYNONYM statements for user objects. If you use it, you'll need to update variable values and review statements before executing. No warranty express or implied :)

-- generate create synonym statements for user objects
DECLARE @FromSchema SYSNAME = 'abc',
        @ToSchema SYSNAME = 'dbo',
        @ServerName SYSNAME = 'server',
        @DatabaseName SYSNAME = 'database';

SELECT  'CREATE SYNONYM ' + QUOTENAME(@ToSchema) + '.' + QUOTENAME(name) +
        ' FOR ' + QUOTENAME(@ServerName) + '.' + QUOTENAME(@DatabaseName) +
        '.' + QUOTENAME(@FromSchema) + '.' + QUOTENAME(name) + ';'
FROM    sys.objects
WHERE   is_ms_shipped = 0;

这篇关于如何在jboss数据源URL中指定sql server的当前架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！