Loading images into MySQL via Ant

[Min Huang]

I am trying to load a bunch of images as seed data into MySQL as part of the build process. Currently, I have an image.sql file that contains binary data, which the mysql client tools will happily import into MySQL:

mysql my_database < image.sql

Unfortunately, the ant sql task fails to parse this file correctly because of the binary data and fails. I've tried combinations of keepformat, encoding, and escapeprocessing, but I still can't execute that sql file via ant.

I have a shell script that loads the data, but I'd really like to be able to use ant and not require mysql client tools or cygwin/unix to be able to load data into the database.

Here are my options:
a) Find out a way to make the ant sql task work with binary data
b) Find out a way to load binary data using mysql's load_file with ant. The load_file function requires an absolute path, so I would need some way to only specify relative paths in the sql file and have ant do the rest. I dont think this is even possible.
c) The least attractive option is to have a <exec> task execute the mysql binary with the correct args. But I dont think there's a way to do unix file redirection; it wouldn't work on Windows.

Any suggestions?

[Jeanne Boyarsky]

Min,
I don't think it is possible to do this with the sql task. Another alternative is to write a Java program that does the load through JDBC. You would be dependent on a mysql driver, but you wouldn't need the mysql client or exec.

[Min Huang]

I thought so. I ended up writing a task that read in id's and image file paths from a properties file, then executed a sql update on all of them. Here's the class:

import org.apache.commons.io.*;
import org.apache.commons.logging.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
 
import javax.sql.rowset.serial.*;
import java.io.*;
import java.sql.*;
import java.util.*;
 
public class BlobLoaderTask extends JDBCTask
{
  private static Log log = LogFactory.getLog(BlobLoaderTask.class);
  private String propsfile;
  private String basedir;
  private String sql;
 
  public void execute() throws BuildException
  {
    if (propsfile == null)
      throw new BuildException("propsfile is required");
    if (sql == null)
      throw new BuildException("sql is required");
    if (basedir == null)
      basedir = "";
 
    Properties props = load();
    Connection connection = getConnection();
    PreparedStatement statement = null;
 
    try
    {
      connection.setAutoCommit(true);
    }
    catch (SQLException e)
    {
      throw new BuildException(e);
    }
 
    for (Object key : props.keySet())
    {
      try
      {
        String id = (String) key;
        String path = props.getProperty(id);
 
        InputStream is = new FileInputStream(basedir + path);
        byte[] bytes = IOUtils.toByteArray(is);
        is.close();
        Blob blob = new SerialBlob(bytes);
 
        statement = connection.prepareStatement(sql);
        statement.setBlob(1, blob);
        statement.setInt(2, Integer.parseInt(id));
        int result = statement.executeUpdate();
 
        log.info("Executed sql with [sql=" + sql + ", param1=" + id + ", param2=" + basedir + path
            + ", length=" + blob.length() + ", result=" + result + "]");
      }
      catch (SQLException e)
      {
        throw new BuildException(e);
      }
      catch (IOException e)
      {
        throw new BuildException(e);
      }
    }
  }
 
  protected Properties load()
  {
    Properties props = new Properties();
    try
    {
      props.load(new FileInputStream(propsfile));
      return props;
    }
    catch (FileNotFoundException e)
    {
      throw new BuildException(e);
    }
    catch (IOException e)
    {
      throw new BuildException(e);
    }
  }
 
  public void setPropsfile(String propsfile)
  {
    this.propsfile = propsfile;
  }
 
  public void setSql(String sql)
  {
    this.sql = sql;
  }
 
  public void setBasedir(String basedir)
  {
    this.basedir = basedir;
  }
}
 

Target def looks like this:

    <taskdef name="bload" classname="BlobLoaderTask">
      <classpath refid="compile.classpath"/>
      <classpath path="${build.test-classes.dir}"/>
    </taskdef>
    <bload propsfile="${basedir}/sql/images.properties"
        basedir="${basedir}/sql/binary/images"
        sql="update `uploaded_files` set content = ? where id = ?"
        url="${db.url}"
        classpathref="runtime.classpath"
        driver="${db.driver}"
        userid="${db.username}"
        password="${db.password}"/>
  </target>

Unfortunately, mysql's load_file only works on the host computer (ie the computer the mysql instance is running on), so this didnt work when I tried running ant load on another database:

package com.taylor.test;
 
import java.io.PrintStream;
import java.sql.SQLException;
 
import org.apache.tools.ant.taskdefs.SQLExec;
 
 
public class RelativeLoaderSQLTask extends SQLExec
{
  private String basedir;
 
  @Override
  protected void execSQL(String sql, PrintStream stream) throws SQLException
  {
    sql = sql.replaceAll("load_file\\('", "load_file('" + basedir);
    super.execSQL(sql, stream);
  }
 
  public void setBasedir(String basedir)
  {
    this.basedir = basedir;
  }
}

[ April 18, 2007: Message edited by: Min Huang ]
[ April 18, 2007: Message edited by: Min Huang ]