JDBC规范是否防止“?"从用作运算符(引号之外)? [英] Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?
问题描述
来自 Sam Macbeth的问题:
JDBC规范中是否有任何允许使用的?可以转义，并且可以是参数占位符以外的其他东西?
Is there anything in the JDBC spec which allows a ? to be escaped and be anything other than a parameter placeholder?
例如，Postgres允许您将?用作运算符:
For example, Postgres allows you to use ? as an operator:
SELECT * FROM tbl WHERE tbl.data ? 'abc'
是否可以使用JDBC驱动程序?作为操作员仍符合JDBC标准?
Would a JDBC driver that lets you use ? as an operator still be JDBC-compliant?
推荐答案
我认为，如果JDBC驱动程序允许?运算符原样转义和使用，那将是完全可以接受的，但是可能是1)使解析器复杂化，以实际上无法从该运算符识别参数，并且2)可能使习惯于?仅表示参数占位符的人(甚至是工具)感到困惑.
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.
所以我的建议是提供某种转义(或替代运算符).但是，从JDBC规范来看，驱动程序应仅使用JDBC转义语法来实现JDBC规范中定义的转义(13.4.2:该转义语法不可用于调用用户定义的或特定于供应商的标量函数. "；尽管这是专门针对{fn ...}转义的内容.
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).
因此，您要么需要使用其他转义符，要么打破"规则(我想没人会介意).如果您想获得更具权威性的答案，可以将问题发送到 jdbc -spec-讨论邮件列表.我确信Lance Andersen(JDBC规范负责人)将提供答案.
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.
修改:
有趣的是，JDBC规范第6.2节(准则和要求)指出:
Also interesting to note that the JDBC spec section 6.2 (Guidelines and Requirements) says:
驱动程序应提供对由底层数据源实现的所有功能的访问，包括扩展JDBC API的功能.目的是使使用JDBC API的应用程序可以访问与本机应用程序相同的功能集.
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.
因此，基于您应该(不一定)支持? -operator的想法，您只需要找到一种实用的方法即可.
So based on that you should (not must) support the ?-operator, you just need to find a practical way to do it.
根据Lance Andersen的说法，JDBC规范遵循关于问号的SQL规范:它们只能用作查询文本中的参数占位符(当然，注释和带引号的文本除外)，例如?的用法如下:在PostgreSQL中，hstore运算符是不允许的. (请参见此消息)
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)
可用的选项是为操作员提供别名或转义符，前提是这不与将来的更改冲突(如果没有千里眼，这是很难做到的；).最好的解决方案-防止将来的JDBC更改出现问题-可能是自定义转义.
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实际上没有定义供应商转义，但是Lance Andersen确实建议了与JDBC转义类似的转义:{postgres <thing to be escaped>};在此转义中使用供应商名称或驱动程序名称将提供一种命名空间形式，应避免与规范冲突. (请参见此消息)
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)
为了与常规" JDBC函数转义保持一致，我建议定义一个转义，以使您问题中的查询可以表示为:
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')}
我根据 hstore文档.对于?&:containsallkeys)和?|:containsanykey的类似建议.为了保持一致性，您也可以考虑对其他hstore运算符执行此操作.
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.
您还可以决定仅跳过问号本身.例如，使用{postgres '?'}或{postgres qm}进行转义(问号为qm).我确实认为可读性低于我的功能转义建议:
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'
基于JDBC v.Next(4.4?)的新建议进行了更新
下一个JDBC规范(可能编号为4.4)可能会添加一个显式JDBC转义以完全转义查询的片段，并明确表示希望能够转义那些本身不使用问号的数据库系统的问号.用于参数标记，并且需要支持问号的其他用途.
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.
建议的语法为{\ <thing-to escape> \}(作为非标准转义，Oracle JDBC驱动程序已支持该语法).使用此语法，然后可以使用{\?\}(在字符串文字:{\\?\\}中)对参数标记进行转义，或者对较大的片段进行转义以提高可读性.
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.
另请参见 SQL 2016 MATCH RECOGNIZE JDBC参数标记/转义字符和 jdbc-spec-discuss邮件列表上的早先讨论.
这篇关于JDBC规范是否防止“?"从用作运算符(引号之外)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
