title: Insert BLOB in Derby Database using SQL
body: <p>Currently I'm trying to import an SQL-INSERT dump from a postgres database into my Derby development/testing database using the Eclipse's Data Tools SQL scratchpad. The export created a lot of data that looked like the following:</p>
  
  <pre><code>CREATE TABLE mytable ( testfield BLOB );
  INSERT INTO mytable ( testfield ) VALUES ('\x0123456789ABCDEF');
  </code></pre>
  
  <p>Executing it in Eclispe's SQL Scratchpad results in (translated from german):</p>
  
  <pre><code>Columns of type 'BLOB' shall not contain values of type 'CHAR'.
  </code></pre>
  
  <p>The problem seems, that the PostgreSQL admin tool exported BLOB data in a format like <code>'\x0123456789ABCDEF'</code> which is not recognized by Derby (Embedded). </p>
  
  <p>Changing this to <code>X'0123456789ABCDEF'</code> or simply <code>'0123456789ABCDEF'</code>did also not work.</p>
  
  <p>The only thing that worked was <code>CAST (X'123456789ABCDEF' AS BLOB)</code>, but I'm not yet sure, if this results in the correct binary data when read back in Java and if the <code>X'0123456789ABCDEF'</code>is 100% portable.</p>