Inline BLOB / BINARY data types in SQL / JDBC

Question

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.

Solution 1

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
  

Solution 2

I'd like to add some PostgreSQL specific stuff to Lukas' answer:

The shortest and most easiest solution would be (since PostgreSQL 9.0 at least):

insert into lob_table (data) values( E'\\x0102030405FF' )

without any cast (if the column is already a bytea one) and only one \\x mark right at the beginning. This is the "hex format" documented in the section Binary Data Types.

Regarding the X'01FF' syntax: According to the string constant documentation PostgreSQL does support it - for bit strings. And it seems, that there is no standard conversion from bit to bytea.

Solution 3

public String binaryLiteral(Connection con, byte[] bytes) {
    String databaseName = con.getMetaData().getDatabaseProductName();
    String binary = DatatypeConverter.printHexBinary(bytes);
    switch (databaseName) {
        case "Microsoft SQL Server":
        case "Sybase Anywhere": case "ASE": case "Adaptive Server Enterprise": // Sybase
            return "CONVERT(VARBINARY(MAX), '0x" + binary + "', 1)";
        case "Oracle":
            if (binary.length() <= 4000) {
                return "HEXTORAW('" + binary + "')";
            } else {
                // https://stackoverflow.com/questions/18116634/oracle-10-using-hextoraw-to-fill-in-blob-data/62162036#62162036
                return "CONCAT_RAWS(RAWS(" +
                        Streams.stream(Splitter.fixedLength(4000).split(binary))
                                .map(chunk -> "HEXTORAW('" + chunk + "')")
                                .collect(Collectors.joining(",")) +
                        "))";
            }
        case "PostgreSQL":
            return "E'\\\\x" + binary + "'";
        case "H2":
        // the following list is mostly untested
        case "HSQL Database Engine":
        case "Apache Derby":
        case "Ingres":
        case "MySQL":
        case "MariaDB":
        case "SQLite":
        case "Informix Dynamic Server":
        case "DB2":
        case "Firebird":
        default: // SQL Standard
            return "X'" + binary + "'";
    }

The source for CONCAT_RAWS and RAWS is given in Oracle 10: Using HEXTORAW to fill in blob data.

Comments

[a_horse_with_no_name]

Very good compilation. Note that Oracle's has a limit of 4000 characters for character literals which also applies for the hextoraw() function. That limits the max. size of the blob that you can create like that.

[Lukas Eder]

@a_horse_with_no_name: Nice hint. That probably applies to some of the other databases too, which tend to have similar (yet larger) limits for varchar

[Lukas Eder]

Nice, thanks for the addition. I don't know what I did wrong, but in my Postgres instance (9.0), the hex variant didn't seem to work when running select E'\\x01FF'. But maybe I was missing some detail

[A.H.]

Does is work now? I tested on 9.1. My guess: You used E'\\x01\\xFF' which indeed does not work :-)

[Lukas Eder]

It actually works. It's just my nonsensical hex code 01FF which has no useful correspondance in utf-8, which is why it was still escaped in the output. DUH! ;-)

[Prashant]

Did you actually used/suceeded in using the derby version? It does not work for me. I tried X'AABB' and (CAST(X'AABB' AS BLOB))

[Lukas Eder]

@eckes: Yes this works as documented above and is implemented this way in jOOQ. In what context did you try this (i.e. what's the full statement)?

[Prashant]

@LukasEder Yes you are right, INSERT INTO tBlob(cID, cBlob) VALUE ("1",CAST(X'010203' AS BLOB)) actually works with Derby. My problems seems to be Flyway related. BTW: without the cast it does not work, it returns: ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR () FOR BIT DATA'. which is expected according to the Derby spec.

[Lukas Eder]

@eckes: "BTW: without the cast it does not work": Yes that might be true. Derby is very picky with data types...

[tanius]

Note of the obvious: At least in MySQL, BLOBs are a specific type of string. So where useful, "normal" string literals also work as BLOB literals: INSERT INTO lob_table VALUES ('abc')

[Vadzim]

Beware that VALUES (0x01FF) is applicable only for tiny values fitting in about 8 bytes of numeric data type. Any longer values would require string literals and explicit casts like in insert varbinary value from hex literal in Microsoft SQL Server.