SQL/JDBC中的内联BLOB/BINARY数据类型 [英] Inline BLOB / BINARY data types in SQL / JDBC

问题描述

假设我要避免在JDBC中使用绑定变量，而要使用"ad-hoc"语句运行SQL，例如:

Let's say I want to avoid using bind variables in JDBC and run SQL using "ad-hoc" statements, e.g:

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

内联BLOB数据类型是否有任何约定/JDBC转义语法?我知道 H2具有以下语法:

Is there any convention / JDBC escape syntax to inline BLOB data types? I know that H2 has this syntax:

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

但这不是标准.有什么一般的解决方案吗?注意，我对通用方法感兴趣.我知道这可能效率很低.

But that's not a standard. Any general solutions? Note, I'm interested in a general approach. I know that this can turn out to be terribly inefficient.

推荐答案

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

There probably isn't a JDBC escape syntax, so I searched around a bit and found and successfully tested the following:

• SQL Server，Sybase ASE，Sybase SQL Anywhere

• SQL Server, Sybase ASE, Sybase SQL Anywhere

INSERT INTO lob_table VALUES (0x01FF);

DB2

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

Derby, H2, HSQLDB, Ingres, MySQL, SQLite

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

Oracle

Oracle

-- 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

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);

有关更多信息，请参见 AH的答案 Postgres十六进制编码的详细信息

See A.H.'s answer for more details about Postgres' hex encoding

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

这篇关于SQL/JDBC中的内联BLOB/BINARY数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！