[CAY-1661] Ingres interactions with transactions

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Won't Fix
• Affects Version/s: 3.0.2
• Component/s: Database integration
• Environment: Ingres 10 64 bit for Linux, client is freebsd 9 with openjdk 6, cayenne 3.0.2
• Assignee: Unassigned
• Reporter: Lucas Holt
• Created: 03/Feb/12 16:15
• Updated: 22/Feb/12 08:51
• Resolved: 22/Feb/12 08:51

Description

I've written some code to insert new records using a SQLTemplate (as I can't use new object, see other bug) and noticed that I must explicitly create a new transaction in order for the code to work. The code block starts with a result iterator and if i do not explicitly create transactions inside the loop, the program terminates early with the result iterator after about 24 rows (there are over a million).

Ingres doesn't have auto commit by default and I'm wondering if this is the problem. (Even select's need commit)

The insert piece is just a normal sqltemplate call on the same thread. I'll include the code below that loops:

public void generate() throws CayenneException, ServiceException {
DataContext context;
context = (DataContext) BaseContext.getThreadObjectContext();

context.setValidatingObjectsOnCommit(false);
DataDomain domain = Configuration.getSharedConfiguration().getDomain();

SelectQuery query;
Expression qualifier;

if (!update)

{ qualifier = Expression.fromString("variationMeta+.lastName = $vm and msnNameVarList.indivId > 0"); HashMap<String, String> map = new HashMap<String, String>(); map.put("vm", null); qualifier = qualifier.expWithParameters(map); }

else

{ qualifier = Expression.fromString("msnNameVarList.indivId > 0"); }

query = new SelectQuery(MsnVariation.class, qualifier);

query.setStatementFetchSize(1000);
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);

ResultIterator it = context.performIteratedQuery(query);

try {
while (it.hasNextRow()) {
Transaction tx = domain.createTransaction();
Transaction.bindThreadTransaction(tx);
tx.setStatus(Transaction.STATUS_ACTIVE);

try {
MsnVariation variation = context.objectFromDataRow(MsnVariation.class, (DataRow) it.nextRow(), false);
if (variation == null)

{ log.error("variation is null?"); continue; }

if (verbose)

{ System.out.println("Var: " + DataObjectUtils.intPKForObject(variation) + ": " + variation.getNameTxt()); }

if (variation.getVariationMeta() == null)

{ variationService.createMeta(variation); }

else if (update)

{ variationService.updateMeta(variation); }

tx.commit();
} catch (Exception ex)

{ log.error(ex); tx.setRollbackOnly(); }

finally {
if (tx.getStatus() == Transaction.STATUS_MARKED_ROLLEDBACK) {
try

{ tx.rollback(); }

catch (Exception rollbackEx) {
}
}
}
}
} catch (Exception s)

{ log.error(s); }

finally {
try

{ // explicit closing of the iterator is required !!! it.close(); }

catch (CayenneException closeEx)

{ log.error(closeEx); }

}
}