英文:
How can I get "select into" statement to work with H2
问题
我目前正在为一个现有的Java/Spring项目创建回归测试，该项目使用Sybase数据库。有人要求我为测试启动一个H2数据库，并在其中运行现有查询以测试结果。
总之，代码调用真实的现有API，但将H2数据库作为数据库源传递，而不是使用Sybase（通过在调用托管代码的Spring应用程序时使用 -DdbServer=tcp:[host]:[port]/./testDatabase）。
我面临的问题是，一些现有的由API调用的SQL查询使用了 select COLUMNS into #tempTable，而不幸的是，这似乎不受H2支持。
我的问题是：我该如何解决这个问题？是否有另一个等效的命令可以在查询中使用，同时受到Sybase和H2的支持？是否有一种方法可以在H2数据库周围创建一个包装器，以便它可以捕获 "select into" 语句并将其转换为H2支持的语句？
我在网上搜索过，但找不到任何信息。我知道H2支持 create table NewTable as SELECT，但不幸的是，Sybase不支持这一点，我特别需要相同的SQL查询适用于两者。
非常感谢您提供任何帮助！
英文:
I am currently working on creating regression tests for an existing java/spring project that uses a sybase database. I was asked to spin an H2 database for the tests and run the existing queries on it to test the results.
In summary, the code calls the real, existing APIs but passes the H2 database as the db source instead of sybase (by using
-DdbServer=tcp:[host]:[port]/./testDatabase
when calling the spring application hosting the code).
The problem I'm facing is that a couple of the existing sql queries called by the APIs use select COLUMNS into #tempTable, which unfortunately doesn't seem to be supported by H2.
My question is: How can I go around this problem? Is there another equivalent command I could use in the queries that would be supported by BOTH sybase and H2? Is there a way to create a wrapper around the H2 database so that it could catch the "select into" statement and change it into an H2 supported statement?
I've looked online but I couldn't find anything. I know that H2 supports create table NewTable as SELECT but unfortunately Sybase does not, and I specifically need the same SQL query to work for both.
Any and all help will be appreciated!
答案1
得分: 2
这就是我最终所做的：
我将 SQL 从
select 列
into #tempTable
from OtherTable
修改为：
create #tempTable(列 列类型)
insert into #tempTable(列)
select (列) from OtherTable
这在 H2 和 Sybase 上都得到支持，并且似乎不会影响性能。不过需要略微麻烦一些，因为你必须为新表创建所有的列，但除此之外，变更非常直观。
这种方法的潜在问题是 H2 在 create 语句末尾需要一个分号，而 Sybase 不需要，所以我通过创建一个自定义的 JdbcOperations 类来解决这个问题，仅在使用 H2 时调用它，在处理查询之前添加分号（我还必须在其中处理 Sybase 使用的井号（#）来创建临时表）。
英文:
This is what I ended up doing:
I changed the sql from
select columns
into #tempTable
from OtherTable
to:
create #tempTable(columns columnsType)
insert into #tempTable(columns)
select (columns) from OtherTable
This is supported by both H2 and Sybase and doesn't seem to affect the performance. It is a bit more annoying as you have to create all the columns for the new table but otherwise the change is pretty straight forward.
The potential issue with this is that H2 wants a semi-colon at the end of the create statement, while Sybase does not, so I fixed that by creating a custom JdbcOperations class that only gets called when using H2 and adds the semicolons before processing the query (I also had to use it to deal with the hash symbol (#) that Sybase uses to create temporary tables).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论