Copying blob fields in Scriptella

Question

I'm using Scriptella for database migration. What would be the best approach to copy a longblob field from table A to table B ?

When I do this job like this:

<query ...
  SELECT FL_DATA as data FROM A where FL_DATA IS NOT NULL
  <script ...>
    INSERT B (FL_DATA) VALUES ('$data');
  </script>
</query>

then it will just write 'BLOB: java.io.IOException: Content too long to fit in memory' into the destination field (btw: the size of the longblob is just a few kBytes).

Answer 1

When $variable syntax is used, a text value of the variable is simply inserted into statement's text. A recommended approach would be to use prepared statements syntax. In this case values will be transferred separately from the SQL statement. Please try the following:

<query ...
  SELECT FL_DATA as data FROM A where FL_DATA IS NOT NULL
  <script ...>
    INSERT B (FL_DATA) VALUES (?data);
  </script>
</query>

Answer 2

Sorry, this is not a answer, but I don't how to comment ejboy's answer.

Ejboy, it doesn't work for me. I've tried prepared statement with (in my case) ?description and ?2. The same error I get when I try to show the content on the console:

<query connection-id="database">
    SELECT id, description FROM table;
    <script connection-id="copy_database">
        INSERT INTO table VALUES (?1, ?2);
    </script>
    <script connection-id="text">
      $id, $description
    </script>
</query>

I get the same error for both, for showing and inserting (here example for showing):

BRA0735401, CLOB: java.io.IOException: Content too long to fit in memory

My main task to make a copy from one database to another. Like in Jan's case the blob/clob content is about 40 kB (a list of key/value, each in the separate line, from database point of view this is MySQL MEDIUMTEXT).I know, that is possible to insert a blob/clob from a file (scriptella example with mp3 files), and it's possible to write content of a blob to a file (scriptella example with odbc and pictures with janino), so I will to try to use a solution with a temporary file, but it's not a beautiful solution. Am I right?

Is there any simpler solution?

Regards, Jacek