在Derby和Hsqldb中转义表和字段名称的问题 [英] Problems with escaping table and field names in Derby and Hsqldb

问题描述

我的 ORMLite套餐存在问题。当我为表生成模式时，我认为逃避所有实体名称是一个好习惯。这将保护某些Java类或字段名称不是SQL保留字：

I'm having a problem with my ORMLite package. When I generate the schema for a table, I thought it would be a good practice to escape all entity names. This would protect some Java class or field name from being a SQL reserved word:

CREATE TABLE "footable" ("stuff" VARCHAR(255))

我现在正在添加原始查询支持，以便ORMLite可以帮助用户执行他们自己的查询。但是，我发现使用Derby和Hsqldb时，实体名称不能在没有转义的情况下使用。例如，以下查询：

I'm now adding "raw" query support so that ORMLite can help users perform their own queries. However, I find that with Derby and Hsqldb, the entity names cannot be used without escaping. For example, the following query:

SELECT * FROM footable

生成以下错误：

Derby: ERROR 42X05: Table/View 'FOOTABLE' does not exist.
Hsqldb: Table not found in statement [select * from footable]

如果select表也作为footable进行转义。 ORMLite支持的其他数据库可以在有或没有转义的情况下正常工作：MySQL，Postgres，Microsoft SQL Server，H2和Sqlite。

It works fine if the select table is also escaped as "footable". The other databases supported by ORMLite work fine with or without the escaping: MySQL, Postgres, Microsoft SQL Server, H2, and Sqlite.

是否有更好的方法来逃避保留字在德比和Hsqldb？关于如何以便携方式做到这一点的其他想法？

Are there better ways to escape reserved words in Derby and Hsqldb? Other ideas about how to do this in a portable manner?

谢谢。

推荐答案

所以对布莱恩的赞扬让我走上了前进的道路，尽管他的回答并不完全正确。

So kudos to Bryan for leading me down the path although his answer wasn't quite right.

原来这是因为我正在创建数据库footable然后，正如Bryan所述，它将以敏感的方式创建。但是，当我在 footable （没有引号）上进行选择时，Derby和Hsqldb正在将它推广为大写，所以我实际上在做：

Turns out that because I am creating the database as "footable" then, as Bryan states, it will be create case sensitively. However, when I did the select on footable (without quotes) Derby and Hsqldb are promoting it to be all uppercase so I'm in effect doing:

SELECT * FROM FOOTABLE

如果没有引号（这可能有效），它不是不区分大小写，而是在没有引号然后按案例匹配时将实体名称提升为所有大写字母。我认为这里有一个错误...

It's not about being case insensitive without the quotes (which would have worked) but about promoting the entity names to be all capitals when there are no quotes and then matching by case. I'd argue there was a bug here...

无论如何，我已经改变了我的Derby和Hsqldb来大写 ORMLite ，事情正在发挥作用。丑陋的国际海事组织，但工作。

In any case, I've changed my Derby and Hsqldb to capitalize all entity names in ORMLite and things are working. Ugly IMO, but working.

这篇关于在Derby和Hsqldb中转义表和字段名称的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！