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 ...");
是否有任何约定/JDBC 转义语法来内联 BLOB 数据类型?我知道 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
-- 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屋!