MSSQL and FreeformQueryDelegate; Possible newbie mistake?

Hi,

I’m trying to use SQL Server 2005 with the SQLContainer to make a suggestion box application; I’ve used the SQLContainer with a MySQL database before and had success. I’ve created a custom FreeformQueryDelegate and the error seems to be only after I add it as a delegate to the FreeformQuery. If I don’t add it, the interactions with the database will not work as desired. I copied almost directly off the trunk example and changed the fields to match mine. This is the error I’ve been getting

Apr 6, 2011 11:53:31 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet myPublicApp Application threw exception
java.lang.NullPointerException
at com.vaadin.addon.sqlcontainer.SQLContainer.getPropertyIds(SQLContainer.java:1076)
at com.vaadin.addon.sqlcontainer.SQLContainer.(SQLContainer.java:116)
at com.example.mypublicapp.DatabaseAdmin.initContainer(DatabaseAdmin.java:145)
at com.example.mypublicapp.DatabaseAdmin.(DatabaseAdmin.java:37)
at com.example.mypublicapp.MypublicappApplication.initMainWindow(MypublicappApplication.java:107)
at com.example.mypublicapp.MypublicappApplication.init(MypublicappApplication.java:37)
at com.vaadin.Application.start(Application.java:554)

I changed the queries to the appropriate names and SQL syntax, and this is my custom delegate code where I changed field names and data types to match my database. Could I be using the wrong datatypes for SQL Server? Is it because of an empty database? Thanks for any input or help in advance!


public int storeRow(Connection conn, RowItem row)
			throws UnsupportedOperationException, SQLException {
        PreparedStatement statement = null;
        if (row.getId() instanceof TemporaryRowId) {
            statement = conn
                    .prepareStatement("INSERT INTO suggestions (issue, resolution, date_received) VALUES(?, ?, ?)");
            statement.setString(1, (String) row.getItemProperty("issue")
                    .getValue());
            statement.setString(2, (String) row.getItemProperty("resolution")
                    .getValue());     
            statement.setTimestamp(3, (java.sql.Timestamp) row.getItemProperty("date_received")
                    .getValue());
        } else {
        	statement = conn
            .prepareStatement("UPDATE suggestions SET response = ?, date_responded = ?," +
                 " is_public = ?, issue_public = ? WHERE idsuggestions = ?");
    
        	statement.setString(1, (String) row.getItemProperty("response")
        			.getValue());
        	statement.setTimestamp(2, (java.sql.Timestamp) row.getItemProperty("date_responded")
        			.getValue());     
        	statement.setInt(3, (Integer) row.getItemProperty("is_public")
        			.getValue());   
        	statement.setInt(4, (Integer) row.getItemProperty("issue_public")
        			.getValue());    
        	statement.setInt(5, (Integer) row.getItemProperty("idsuggestions")
        			.getValue());
        }

        int retval = statement.executeUpdate();
        statement.close();
        return retval;
}

public boolean removeRow(Connection conn, RowItem row)
			throws UnsupportedOperationException, SQLException {
        PreparedStatement statement = conn
        	.prepareStatement("DELETE FROM suggestions WHERE idsuggestions = ?");
        statement.setInt(1, (Integer) row.getItemProperty("idsuggestions").getValue());
        int rowsChanged = statement.executeUpdate();
        statement.close();
        return rowsChanged == 1;
}

I finally figured things out. Since I was using multiple tables, I didn’t realize I needed to create different FreeformQueryDelegates for each new SQLContainer. Also binary booleans needed to be treated as Integers and for the timestamp dates, I needed to import java.sql.Timestamp. I hope this helps anyone else who’s having similar problems!