如何使用Java（和SQL Server）中的Statement.execute（）更改数据库模式？

I need to execute a SQL Server system stored procedure, programmatically, and since it executes in the current schema, I need to change it on the fly.

我需要以编程方式执行SQL Server系统存储过程,并且由于它在当前架构中执行,我需要动态更改它。

Like this

Statement st = connection.createStatement(); st.execute("EXEC SP_ADDUSER ' ', ' '");

Statement st = connection.createStatement(); st.execute(“EXEC SP_ADDUSER'',''');

But SP_ADDUSER only executes on the current schema set for the connection, so if I wanted to create users in various schemas, I'd need to change it, and that's what I am looking for.

但是SP_ADDUSER只在连接的当前模式集上执行,所以如果我想在各种模式中创建用户,我需要更改它,这就是我要找的东西。

2 个解决方案

#1

I don't believe it's possible to change which database a connection points to.

我不相信可以更改连接所指向的数据库。

You'll probably need to create a separate DataSource/Connection for each database (schema).

您可能需要为每个数据库(架构)创建单独的DataSource / Connection。

#2

EXEC <DatabaseName>..sp_adduser can be run from a connection to any database (even master, say). The connection will not be affected.

EXEC .. sp_adduser可以从任何数据库的连接运行(比如说master)。连接不会受到影响。

For instance, the following appears to work fine on my system:

例如,以下似乎在我的系统上正常工作:

USE master
EXEC sp_addlogin 'test1'
EXEC SandBox..sp_adduser 'test1'

The same things works fine through the client. Is your client connection altering your SQL?

同样的事情通过客户端工作正常。您的客户端连接是否会改变您的SQL?

using System;
using System.Data.SqlClient;

namespace TestUse
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection cn = new SqlConnection("Server=(local);Database=master;Trusted_Connection=True;");
            cn.Open();
            SqlCommand cmd = new SqlCommand("USE master; EXEC sp_addlogin 'test1'; EXEC SandBox..sp_adduser 'test1'", cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }
}

#1