How do I get dbunit to play nice with MySQL enum data types?

Question

I'm trying to use dbunit to test some our database access code and I'm running into a problem. We are using MySQL 5 something or other as the database itself. I exported a small set of data to a FlatXmlDataSet and when I setup the test case, it throws an exception which says "Data truncated for column 'FHEIGHT_FLAG' at row 1". The column FHEIGHT_FLAG is defined as

enum('t','f') default NULL

The way the enum data is inserted to the database, if the real value for the source of the data is not true, then the column actually contains "" (empty string). The code using the database is just making the assumption that if the value provided from the database is not 't', then it must be false. How can I make this go? I can't fix the source data, so I have to try to do something with the test case.

Answer 1

Export a new dataset, using "select case when FHEIGHT_FLAG = 't' then 't' else 'f' end as FHEIGHT_FLAG, ....(all other columns) .... from tablename";

(But order the columns as they are in the real table, of course.)

final String select = "select case when FHEIGHT_FLAG = 't' then 't' else 'f' end as FHEIGHT_FLAG, ....(all other columns) .... from tablename";
// database connection
Class driverClass = Class.forName("package.of.jdbcDriver");
Connection jdbcConnection = DriverManager.getConnection(
        "jdbc:url:of:db", "user", "pass");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

// partial database export
QueryDataSet partialDataSet = new QueryDataSet(connection);
partialDataSet.addTable("Tablename", select);

FlatXmlDataSet.write(partialDataSet, new FileOutputStream("mydataset.xml"));

Answer 2

I ended up modifying the test database FHEIGHT_FLAG column to be

enum('','t','f') default NULL

that way all of the empty string values were allowed to be inserted back.