#79 too strict net.sf.mpxj.mpd.ResultSetRow

[Vladimír Oraný]

net.sf.mpxj.mpd.ResultSetRow is too strict, when getting value from result set.
exception is thrown when trying to connect mysql database, because lot of types are not supported.
setting default switch case to rs.getObject(name) should works, but is then needed to find better way how to get boolean value (f.e. in mysql they are saved as number and then MapRow.getBoolean() throw casting exception)

[Jon Iles]

I'm curious - this functionality was designed to read from MPD files, via a JDBC-ODBC bridge. Have you set up MySQL with the same database schema used in the MPD file? If so how are you writing data to it?

[Vladimír Oraný]

Well, I'was just finding out what this project can do for me. Here's an example "test" I've tried to run.

//--------------------------------------------------------------------------------------------------------//
public class MPXJTest {
private static final String STEP = " ";
@Test
public void testTest() throws Exception {
MPDDatabaseReader r = new MPDDatabaseReader();
Class.forName("org.gjt.mm.mysql.Driver");
r.setConnection(DriverManager.getConnection("jdbc:mysql://localhost:3306/mspDb?autoReconnect=true","user","pass"));
r.setProjectID(1);
ProjectFile pf = r.read();

Assert.assertNotNull(pf);

System.out.println(pf.getProjectHeader().getName());

List<Task> childTasks = pf.getChildTasks();
String step = STEP;
printChildTasks(childTasks, step);
}

private void printChildTasks(List<Task> childTasks, String step) {
for (Task task : childTasks) {
System.out.println(step + task.getOutlineNumber() + " " + task.getName());
printChildTasks(task.getChildTasks(), odsazeni + STEP);
}
}
}

I haven't tried to write any data into db yet. They are already there created by MSP. I was trying to find some object-based api (prehaps based on JPA) to read project from db so I don't need to write selects based on magical msp_text_fields constants.
As you see I just connect right db and get project with correct ID.
Tip to improvement: There would by fine to have some object/method which will list all availible projects.

[Jon Iles]

OK, I think we can do this. I'm going to need a little assistance from you if that's OK? The first step is that I need to understand that actual types that are coming back in the result set. To do this, if you run the original code which throws the exception when a unknown type is found, take a note of the type numbers you are seeing, look them up in java.sql.Types and add a case clause to the switch statement for them using your suggested rs.getObject(name). Once you've got a list, I'll integrate these types into the code in CVS.

Once that's in place, we can work on the MapRow methods to ensure that things like the Boolean type extraction are handled correctly.

I will also implement a method on the MPDReader to allow you to list the projects availabl in the database. I should be able to do it this evening.

Thanks!

Jon

[Vladimír Oraný]

-3 (Types.VARBINARY) PROJ_PROP_COMPANY => This I handle same as LONGVARBINARY
-5 (Types.BIGINT) PROJ_OPT_MINUTES_PER_DAY => added new case to the switch and handled as Long

than I fixed in MapRow:

public final boolean getBoolean(String name)
{
Object o = getObject(name);
if (o instanceof Number) {
Number n = (Number) o;
if (n.intValue() == 1) {
return Boolean.TRUE;
} else {
return Boolean.FALSE;
}
}
return (BooleanUtility.getBoolean((Boolean) getObject(name)));
}

I hope this helps you. But there can be more tricky types like this. Maybe you should more rely on rs.getObject() or you can inspire in hibernate dialects source code.

[Jon Iles]

I've made the changes to support the new columns types, and as requested I've also added a method on the MPD reader class to list the project names and IDs available in the database. All of this is now in CVS.

Jon