JDBC 规范是否阻止了 '?'被用作运算符(引号之外)? [英] Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?
问题描述
来自山姆麦克白的问题:><块引用>
JDBC 规范中是否有任何内容允许 ?被转义并成为参数占位符以外的任何东西?
例如，Postgres 允许你使用 ? 作为操作符:
SELECT * FROM tbl WHERE tbl.data ?'ABC'可以让您使用 JDBC 驱动程序吗?作为运营商仍然符合 JDBC 吗?
我认为如果 JDBC 驱动程序允许 ? 操作符不被转义并按原样使用，那将是完全可以接受的，但它可能 1) 使您的解析器复杂化以实际识别来自该运算符的参数，并且 2) 可能会使习惯于 ? 仅表示参数占位符的人(可能还有工具)感到困惑.
所以我的建议是提供某种转义(或替代运算符).然而，查看 JDBC 规范，驱动程序应该只使用 JDBC 转义语法来实现 JDBC 规范中定义的转义(13.4.2:转义语法不应用于调用用户定义或供应商特定的标量函数."; 虽然这是专门针对 {fn ...} 转义的.
所以要么你需要使用替代的转义，要么打破"规则(我认为没有人会介意).如果你想要一个更权威的答案，你可以将你的问题发送到 jdbc-spec-讨论邮件列表.我相信 Lance Andersen(JDBC 规范负责人)会提供答案.
编辑:
同样有趣的是，JDBC 规范第 6.2 节(指南和要求)说:
<块引用>驱动程序应该提供对底层数据源实现的每个功能的访问，包括扩展 JDBC API 的功能.目的是让使用 JDBC API 的应用程序能够访问与本机应用程序相同的功能集.
因此，基于您应该(不是必须)支持 ?-operator，您只需要找到一种实用的方法即可.
更新基于讨论在 jdbc-spec-discuss 上
根据 Lance Andersen 的说法，JDBC 规范在问号方面遵循 SQL 规范:它们只能用作查询文本中的参数占位符(当然在注释和引用文本中除外)，例如使用 ? 不允许使用 PostgreSQL hstore 运算符.(请参阅此消息)
可用的选项是为操作员提供别名或转义，前提是这与未来的更改不冲突(如果没有千里眼，这很难做到；).最好的解决方案 - 防止未来 JDBC 更改出现问题 - 可能是自定义转义.
JDBC 实际上并没有定义供应商转义，但 Lance Andersen 确实建议了一种类似于 JDBC 转义的转义:{postgres ;在此转义中使用供应商名称或驱动程序名称将提供一种命名空间形式，以防止与规范发生冲突.(请参阅此消息)
为了与普通"JDBC 函数转义保持一致，我建议定义一个转义，以允许您的问题中的查询表述为:
SELECT * FROM tbl WHERE {postgres containskey(tbl.data, 'abc')}我根据hstore 文档.?& : containsallkeys) 和 ?| 的类似建议:containsanykey.为了保持一致性，您也可以考虑对其他 hstore 运算符执行此操作.
您也可以决定只转义问号本身.例如使用 {postgres '?'} 或 {postgres qm} (qm 表示问号)转义.我确实认为可读性不如我的函数转义建议:
SELECT * FROM tbl WHERE tbl.data {postgres '?'} 'abc'<小时>
根据 JDBC v.Next (4.4?) 的新提案更新
下一个 JDBC 规范(可能编号为 4.4)，可能会添加一个显式的 JDBC 转义来完全转义查询的片段，明确的意图是能够为原生不使用问号的数据库系统转义问号用于参数标记并需要支持问号的其他用途.
建议的语法是 { (Oracle JDBC 驱动程序已经支持作为非标准转义).使用这种语法，参数标记可以用 {?} 转义(在字符串文字中:{\?\})，或者转义更大的片段以提高可读性.
另见 SQL 2016 MATCH RECOGNIZE JDBC参数标记/转义字符和之前在 jdbc-spec-discuss 邮件列表中的讨论.
From Sam Macbeth's question:
Is there anything in the JDBC spec which allows a ? to be escaped and be anything other than a parameter placeholder?
For example, Postgres allows you to use ? as an operator:
SELECT * FROM tbl WHERE tbl.data ? 'abc'
Would a JDBC driver that lets you use ? as an operator still be JDBC-compliant?
I think it would be perfectly acceptable if the JDBC driver would allow for the ? operator to be unescaped and used as it is, but it might 1) complicate your parser to actually identify parameters from this operator and 2) might confuse people (and maybe tools) who are used to ? only meaning parameter placeholder.
So my suggestion would be to provide some sort of escape (or alternative operator). However looking at the JDBC spec, drivers should only use the JDBC escape syntax to implement the escapes defined in the JDBC spec (13.4.2: "The escape syntax is not intended to be used to invoke user-defined or vendor specific scalar functions."; although this is specifically about the {fn ...} escape).
So either you need to use an alternative escape, or 'break' the rules (I don't think anyone would mind). If you want a more authorative answer, you could send your question to the jdbc-spec-discuss mailinglist. I am sure Lance Andersen (JDBC spec lead) will provide an answer.
Edit:
Also interesting to note that the JDBC spec section 6.2 (Guidelines and Requirements) says:
Drivers should provide access to every feature implemented by the underlying data source, including features that extend the JDBC API. The intent is for applications using the JDBC API to have access to the same feature set as native applications.
So based on that you should (not must) support the ?-operator, you just need to find a practical way to do it.
Update based on the discussion on jdbc-spec-discuss
According to Lance Andersen the JDBC specification follows the SQL specification with regard to question marks: they can only be used as parameter placeholders in query text (except of course in comments and quoted text), as such use of ? as in the PostgreSQL hstore operators wouldn't be allowed. (see this message)
The option available is to either provide an alias or an escape for the operator, provided this does not conflict with future changes (which is rather hard to do without clairvoyance ;). The best solution - to prevent problems with future JDBC changes - is probably a custom escape.
JDBC does not actually define a vendor escape, but Lance Andersen does suggest an escape that is similar to the JDBC escapes: {postgres <thing to be escaped>}; use of the vendorname or drivername in this escape will provide a form of namespacing that should prevent conflict with the specification. (see this message)
To be in line with 'normal' JDBC function escapes, I would suggest to define an escape that will allow the query in your question to be stated as:
SELECT * FROM tbl WHERE {postgres containskey(tbl.data, 'abc')}
I chose containskey based on the meaning of ? in the hstore documentation. Similar suggestions for ?& : containsallkeys) and for ?| : containsanykey. For consistency you could consider to do this for the other hstore operators as well.
You could also decide to only escape the question mark itself. For example escape with {postgres '?'} or {postgres qm} (qm for question mark). I do think readability is less than my function-escape suggestion:
SELECT * FROM tbl WHERE tbl.data {postgres '?'} 'abc'
Updated based on new proposal for JDBC v.Next (4.4?)
The next JDBC specification (likely numbered 4.4), will probably add an explicit JDBC escape to entirely escape fragments of a query, with the express intent to be able to escape question marks for database systems that don't natively use question marks for parameter markers and need to support other uses of question marks.
The proposed syntax would be { <thing-to escape> } (which is already supported by the Oracle JDBC driver as a non-standard escape). With this syntax, a parameter marker could then be escaped with {?} (in a string literal: {\?\}), or escape a larger fragment for improved readability.
See also SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters and earlier discussion on the jdbc-spec-discuss mailing list.
这篇关于JDBC 规范是否阻止了 '?'被用作运算符(引号之外)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
