Yesterday, I blogged about inlining boolean literals in SQL. This goes on with other data types. Let’s look at BLOBs and BINARY data in general. This is also defined in SQL standards, not in SQL 1992, though:
<binary string literal> ::=
X <quote> [ <space>... ]
[ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
But is that supported as such in all databases? Of course, as usual, the answer goes “NO”. Here’s a little summary to see how binary literals are handled in which database:
SQL Server, Sybase ASE, Sybase SQL Anywhere
-- T-SQL databases treat binary literals as other languages
-- treat hexadecimal numbers
INSERT INTO lob_table VALUES (0x01FF);
DB2
-- DB2 follows the standard, but you may need to 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
-- Most databases follow the standard, especially
-- the Java databases
INSERT INTO lob_table VALUES (X'01FF');
Oracle
-- Oracle Omit's the standard's "X"
INSERT INTO lob_table VALUES (hextoraw('01FF'));
Postgres
-- For some odd reason Postgres, is really off this time
-- using octal representation of bytes in literals.
-- There is also hex encoding but I didn't get that to work
-- The explicit cast is important, though
INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);
-- T-SQL databases treat binary literals as other languages
-- treat hexadecimal numbers
INSERT INTO lob_table VALUES (0x01FF);
-- DB2 follows the standard, but you may need to use
-- a blob constructor. This is not needed for VARCHAR
-- FOR BIT DATA types
INSERT INTO lob_table VALUES (blob(X'01FF'));
-- Most databases follow the standard, especially
-- the Java databases
INSERT INTO lob_table VALUES (X'01FF');
-- Oracle Omit's the standard's "X"
INSERT INTO lob_table VALUES (hextoraw('01FF'));
-- For some odd reason Postgres, is really off this time
-- using octal representation of bytes in literals.
-- There is also hex encoding but I didn't get that to work
-- The explicit cast is important, though
INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);