如何切换数据库模式？
我正在开发一个 Delphi/WIN32 应用程序，该应用程序使用 SQL Server 数据库作为后端，使用 ADO 访问数据。有许多用户使用此应用程序，但有一个用户正在使用特殊的设置：他们有多个数据库模式，每个模式都包含应用程序的完整数据模型。每个模式还有一个默认为特定模式的数据库用户。他们还为每个数据库用户提供单独的登录帐户，使他们只需在连接字符串中使用不同的登录帐户即可控制要使用的架构。 他们使用此设置来拥有一个支持多个办公室的单一集中式数据库。通常，每个办公室都有自己的数据库，但在这里，每个办公室都有自己的架构。
我喜欢他们使用的这个解决方案。我以前没有考虑过这一点，因为该应用程序通常由单个办公室使用。只有这个客户需要一个集中式数据库。即使应用程序不知道这些架构，应用程序也可以正常工作，这仅仅是因为登录帐户将默认为正确的架构。
但现在他们询问是否可以更改代码，以便用户可以选择他们想要连接的模式。因此，用户需要能够在应用程序中的模式之间进行切换。我不想重写代码来支持这些模式，因为我需要保持 SQL 代码数据库中立。因此，我正在寻找一种将用户切换到另一个模式而不会对代码本身产生太大影响的方法。
有什么建议吗？
I'm working on a Delphi/WIN32 application that uses an SQL Server database as back-end, using ADO to access the data. There are many users who use this application, but one user is using a special setup: they have multiple database schema's and every schema contains the complete datamodel for the application. Every schema also has a database user which defaults to the specific schema. They also have a separate login account for every database user, allowing them to control which schema to use simply by using a different login account in the connection string.
They use this setup to have a single, centralized database which supports multiple offices. Normally, every office would have it's own database but here, every office has their own schema.
I like this solution that they're using. I haven't thought about this before simply because the application is normally used by single offices. Only this customer had a need to have a centralized database. The application works just fine, even though it's unaware of these schema's, simply because the login account will default to the correct schema.
But now they've asked if it's possible to change the code in a way that the user can select the schema to which they want to connect. Thus, a user needs to be able to switch between schema's in the application. And I don't want to rewrite the code to support these schema's simply because I need to keep the SQL code database neutral. So I'm looking for a way to switch a user to another schema without much impact on the code itself.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
更改用户的默认架构怎么样？
当然，您需要在升级的权限下执行此操作，因为我确信您并不拥有所有具有 ALTER USER 功能的用户。
How about changing the default schema of the user?
Of course you will need to execute this under escalated privileges as I'm sure you don't have all users with ALTER USER capabilities.