为什么Derby不使用WAS数据源中的属性“ currentShema”？ [英] Why Derby does not use property 'currentShema' from my WAS datasource?

问题描述

我想知道为什么derby不考虑在给定数据源（当前示例为WAS）上定义的属性'currentSchema'吗？

我的问题是与Nastya在这篇文章中的内容完全相同：在DERBY查询中省略架构
但是他/她的最后评论没有答案。

因此，我有一个应用程序在不同的数据源上工作（相同的数据库，但每个数据源一个模式） 。
我以为如果Derby告诉他使用数据源，则Derby将使用在数据源上定义的属性 currentSchema，但是我所有的命名查询都在模式 DEV（数据库用户名）上执行，而不是在定义的模式下执行在我的数据源属性 currentSchema中。我知道当查询未提供任何模式并且之前未执行SET CURRENT SCHEMA语句时，这是德比默认行为，但是...

Derby是否有任何现有解决方案或即将进行改进以解决此用例？
（我不认为这是一个问题，因为我认为Derby被设计为每个用户都使用架构）

预先感谢您的任何建议。

PS ：要了解我为什么需要这样做：我编写了脚本，可以将derby数据源自动配置到开发人员的工作站中（本地Websphere），这样他们就可以将应用程序部署/运行到本地数据库上，而不是通用的DB2开发数据库上。但是我会避免在应用程序代码中的每个请求之前仅执行SET CURRENT SCHEMA语句来解决此问题...（对其他实际环境的性能影响）
当然，我不能更改本地数据库以使用其他内容比Derby：）

解决方案

我已经找到了解决方案，该解决方案特定于我的用例，因为该修复是通过WebSphere配置完成的。 / p>

解决方案：将数据源配置为自动执行SQL查询，以验证从该数据源中池化的新连接。 SQL查询是

 设置当前模式MY_SCHEMA 
  

其中 MY_SCHEMA是每个数据源所特有的。

这当然可以通过更新以下内容使用wsadmin脚本进行配置：

• 数据源上的J2EEResourceProperty'preTestSQLString'，值= SQL查询


• 数据源池的'testConnections'属性值为'true'

没有像WebSphere这样的特定服务器环境，可以在合并的新连接上自动执行SQL查询，我担心这不是可能，除非在代码中使用EntityManager生产者执行此操作。但这意味着您必须编写特定于您的环境的代码，但这并不……不错。

I'd like to know why derby does not take into account property 'currentSchema' defined on a given datasource (WAS for current example) ?

My problem is quite the same that Nastya have in this post: Omit schema in the DERBY Query But there is no answer to his/her last comment.

So, i have an application working on different datasources (same database but one schema per datasource). I thought that Derby would use the property 'currentSchema' defined on a datasource if i tell him to use this datasource, but all my named queries are executed on schema 'DEV' (database user name)instead of the schema defined in my datasource property 'currentSchema'. I know that's derby default behaviour when no schema is provided with the query and if no SET CURRENT SCHEMA statement executed before, but...

Is there any existing solution or coming improvement into Derby to solve this use case ? (i do not consider this as an issue as Derby has been designed to use a schema per user i think)

Thanks in advance for any suggestion.

PS: To understand why i need this: i have written scripts to automatically configure derby datasources into developer's workstation (local websphere) so that they can deploy / run their applications onto a local database instead of a common DB2 developement one. But i would avoid doing a SET CURRENT SCHEMA statement before every request in the application code just to solve this issue... (performance impact on the other real environments) And of course i cannot change local database to use something else than Derby :)

解决方案

I have found a solution, specific to my use case as the fix is done with WebSphere configuration.

Solution: Configure the datasource to automatically execute an SQL query to validate a new connection pooled from this datasource. The SQL query is

SET CURRENT SCHEMA MY_SCHEMA

where 'MY_SCHEMA' is specific to each datasource.

This is of course configurable with wsadmin scripting by updating:

• J2EEResourceProperty 'preTestSQLString' on the datasource, with value = SQL query
• 'testConnections' property of datasource pool with value 'true'

Without specific server environment like WebSphere to auto execute SQL query on a fresh connection pooled, i fear this is not possible, unless doing it in the code with an EntityManager producer. But that mean you have to write code specific to your environments, and this is not... nice.

这篇关于为什么Derby不使用WAS数据源中的属性“ currentShema”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！