Cache problem with SQLContainer?

Let’s preface this with a disclaimer - I’m brand new to Vaadin, and also to Eclipse, so I could be off base here…

I have 2 database tables with basically identical formats (user_id, category_id). They are join tables for my
User table.

I wrote routines to add items to the tables and to remove items. The adds work, but the remove only works on one of the 2 tables. I am using SQLContainer and I wrote a Query Delegate class so I’m using basically identical code on each table. After almost 2 days of beating my head against the wall, it looks like the problem is that table 1 has 139 elements in it and table 2 has 235 elements. And the SQLContainer cache size is 200… When I reduced the size of table 2 to 180 elements, my remove code worked.

The following code blows up:

try {
            v_Param_SG.add("uid");
	v_Param_SG.add("sgid");
	qdSG = createQD("SELECT COUNT(*) from user_join_sg",
			"INSERT INTO user_join_sg VALUES(?, ?)",
			"SELECT * from user_join_sg",
			"DELETE FROM user_join_sg where uid = ? and sgid = ?",
                            " where uid = ?",
			v_Param_SG);

	ffq = new FreeformQuery("Select * from user_join_sg", Arrays.asList("uid", "sgid"), cPool);
	ffq.setDelegate(qdSG);
	sqc_UserJoinSG = new SQLContainer(ffq);

	for(oid = sqc_UserJoinSG.firstItemId(); oid != null; oid = sqc_UserJoinSG.nextItemId(oid)){
		Property p1 = sqc_UserJoinSG.getContainerProperty(oid, "uid");
                              // ******** p1 comes back NULL, so the stmt I really want (curUid = .... ) below segfaults

		Logger.info("OID = " + oid + "   Property = " + p1);
		curUid = (Integer) sqc_UserJoinSG.getContainerProperty(oid, "uid").getValue();

		if(curUid == uid){
			sqc_UserJoinSG.removeItem(oid);
		}
	}
	sqc_UserJoinSG.commit();

As I said, the code works if there are less than 200 items in the database table. Using the Eclipse debugger (for the first time in my life, I might add…) it looks like the SQLContainer tries to reset/reload the Cache when it discovers that the oid I’m looking for is not currently cached - but the routine appears to exit without actually reloading the first page of the table.

So my question - is this really a bug in the SQLContainer or am I using it incorrectly - that certainly would not surprise me… Is there a way to increase the cache size programmatically so I can work around this problem?

Thanks,

nbc

Hi Neil,

Did you figure the problem out already? I’m sorry I’m late to the game.

You should check that your FreeformStatementDelegate implementation does the correct thing in getQueryStatement() and actually builds a query that will return the items that are in the next page. See the source of the SQLContainer demos for an example implementation.

If everything looks to be correct in your delegate, please provide more information about possible exceptions or where in the flow of reloading the cache that SQLContainer returns without doing so.

HTH,
/Jonatan

Very late reply - my apologies… Wasn’t tracking this thread. I have not been using the SQLContainer recently - I shifted to the JPAContainer which has been working very well for me… If I have a chance, I’ll revisit the SQLContainer and see if I can work around the original problem…