Sql – Inline BLOB / BINARY data types in SQL / JDBC


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

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.

Best Solution

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

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

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

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

  • SQL Standard

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