sql - SQL/JDBC 中的内联 BLOB/BINARY 数据类型

Question

假设我想避免在 JDBC 中使用绑定(bind)变量并使用“ad-hoc”语句运行 SQL，例如:

connection.createStatement().executeQuery("SELECT ...");

是否有任何约定/JDBC 转义语法来内联 BLOB 数据类型？我知道 H2 has this syntax :

INSERT INTO lob_table VALUES (X'01FF');

但这不是一个标准。有什么通用的解决方案吗？请注意，我对一般方法感兴趣。我知道这可能会变得非常低效。

Answer 1

可能没有 JDBC 转义语法，所以我搜索了一下，发现并成功测试了以下内容:

SQL Server、Sybase ASE、Sybase SQL Anywhere

INSERT INTO lob_table VALUES (0x01FF);

DB2

-- Use a blob constructor. This is not needed for VARCHAR FOR BIT DATA types
INSERT INTO lob_table VALUES (blob(X'01FF'));

Derby 、H2、HSQLDB、Ingres、MySQL、SQLite

INSERT INTO lob_table VALUES (X'01FF');

甲骨文

-- As mentioned by a_horse_with_no_name, keep in mind the relatively low
-- limitation of Oracle's VARCHAR types to hold only 4000 bytes!
INSERT INTO lob_table VALUES (hextoraw('01FF'));

Postgres

-- There is also hex encoding as of Postgres 9.0
-- The explicit cast is important, though
INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);

见 A.H.'s answer有关 Postgres 十六进制编码的更多详细信息

SQL 标准

-- SQL actually defines binary literals as such 
-- (as implemented by DB2, Derby, H2, HSQLDB, Ingres, MySQL, SQLite):
<binary string literal> ::=
  X <quote> [ <space>... ] 
  [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>

<hexit> ::=
  <digit> | A | B | C | D | E | F | a | b | c | d | e | f