Ex:
SqlMapFac.setPropFile("properties/sql-map-async.xml");
.V
David Putnam wrote:
All,
I am trying to perform an operation using SqlMaps. I am performing one query to get a list of modified rows in one table, then for each row I want to perform an update or insert on another table within a transaction. I am used to transactions only existing when I say they should, so I am using explicit transactions.
What seems to be occurring is that once the inner transaction starts, the outer query stops behaving like a cursor and fetches the entire result set into memory. In one version, SqlMap.startTransaction() complained that another transaction was already in progress.
So here is a snippet of my current main:
Date laststart = new GregorianCalendar(2005,0,1,0,0,0).getTime();
CustomerLinkTask newLinkTask = new CustomerLinkTask(sqlMap, process, maxDeadlockRetry);
try {
sqlMap.queryWithRowHandler("modifiedCustomers", laststart, newLinkTask);
} catch (SQLException e) {
logger.error(e);
}
return 0;
The modifiedCustomers query returns all customers modified since the timestamp specified. The sqlMap, and a deadlock retry are passed on to the CustomerLinkTask. Here is the handleRow function for the CustomerLinkTask:
public void handleRow(Object row) { Customer customer = (Customer) row;
try { sqlMap.startTransaction(); }catch (SQLException e) { logger.error(e); }
Investor investor = null;
investor = getLinkedInvestor(customer);
if (investor == null) {
// if the investor is not linked, see if we can find an existing one
investor = findExistingInvestor(customer);
if (investor == null) {
// if we can find an existing investor we will need to create one
investor = createInvestor(customer);
createInvestorLink(investor, customer);
} else {
// create a link to the investor we found
createInvestorLink(investor, customer);
updateInvestor(investor, customer);
}
} else {
// update the linked investor
updateInvestor(investor, customer);
}
process.setLastItem(process.getLastItem() + 1); try { sqlMap.commitTransaction(); } catch (SQLException e) { logger.error(e); } finally { try { sqlMap.endTransaction(); } catch (SQLException e) { logger.error(e); } } }
getLinkedInvestor and findExistingInvestor are queries, createInvestor, createInvestorLink and updateInvestor insert or update the database and should be within a transaction.
When I have done similar things in perl with DBI, I opened multiple connections to the database. However, when I tried using connections (SqlMapClient.openConnection()), I did not get two independent connections to the database.
Am I approaching this wrong? I cannot have the transaction started outside the rowHandler, as I expect to process 1.4 million rows. Much to large for a single transaction.
Thanks
David Putnam Wells Fargo Funds Management, LLC One Hundred Heritage Reserve Menomonee Falls, WI 53051-4400 MAC: N9882-030 Voice: 414-359-3678
******DISCLOSURE STATEMENT******
*This transmission may contain information which is confidential, proprietary and privileged. If you are not the individual or entity to which it is addressed, note that any review, disclosure, copying, retransmission or other use is strictly prohibited. If you received this transmission in error, please notify the sender immediately and delete the material from your system. This transmission is for informational purposes only, and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. Any information regarding specific investments or other products is not warranted as to completeness or accuracy and is subject to change without notice.*
