Java Database Connectivity (JDBC)

Hello all,

This is my first topic/question in the forums. I am relatively novice with J2EE programming so please pardon my ignorance. I have spend several hours thying to figure out the following issue.

So here is the scenario. I have a JSF ManagedBean called SymbolProcessBean. Here is part of its declaration

@PersistenceContext(name = "persistence/LogicalName", unitName = "MantisPU")
public class SymbolProcessBean {
@Resource
private UserTransaction utx;
.
.
}

The Bean is callef by a h:command from a jsp page

<h:commandLink action="#{symbolProcessBean.processSymbol}" value="Process"/>

In part of the process the Bean read data from a webpage and persists them in a MySQL database. Here is the loop:

        try {
            // Create a URL for the desired page
            String urlStr = "http://ichart.finance.yahoo.com/table.csv?s=" + this.selectedSymbol.getTicker();
            if (firstNewDay != null)
                urlStr += "&a=" + firstNewDay.get(Calendar.MONTH) +
                          "&b=" + firstNewDay.get(Calendar.DATE) +
                          "&c=" + firstNewDay.get(Calendar.YEAR);

            URL url = new URL(urlStr);

            // Read all the text returned by the server
            BufferedReader in = new BufferedReader(new InputStreamReader(url.openStream()));
            String str;
            outText = in.readLine() + "<br />";    // Discard first line

            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            utx.begin();
            em = (EntityManager) ctx.lookup("persistence/LogicalName");

            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

            while ((str = in.readLine()) != null) {
                // Use togenizer to parse comma separated fields
                StringTokenizer strtok = new StringTokenizer(str, ",");

                // Read the data from the line
                Date recordDate = formatter.parse(strtok.nextToken());
                Float open = Float.valueOf(strtok.nextToken());
                Float high = Float.valueOf(strtok.nextToken());
                Float low = Float.valueOf(strtok.nextToken());
                Float close = Float.valueOf(strtok.nextToken());
                Integer volume = Integer.valueOf(strtok.nextToken());
                Float adj_close = Float.valueOf(strtok.nextToken());

                // Set the PriceRecord data members
                PriceRecord pr = new PriceRecord();
                pr.setSymbol(this.selectedSymbol);
                pr.setRecordDate(recordDate);
                pr.setOpen(open);
                pr.setHigh(high);
                pr.setLow(low);
                pr.setClose(close);
                pr.setVolume(volume);
                pr.setAdjClose(adj_close);

                // Create Record
                em.persist(pr);
                this.selectedSymbol.getPriceRecordCollection().add(pr);
                // Add record string to output text
                outText += str + "<br />";
            }
            in.close();
            this.selectedSymbol = em.merge(this.selectedSymbol);
            utx.commit();
        } catch (Exception e) {
            utx.rollback();
            throw e;
        }

All this works fine on the first invocation of the command link and the Bean reads the data from yahoo for a Stock symbol and persists into the database. When the command link is clicked again for a different stock then the utx.commit(); throws a "javax.transaction.RollbackException: Transaction marked for rollback" exception. NOTE that when I redeploy the application and run it again it will work. But it works only the first time. The second time it will throw the exception.

I am not sure why this happens. I looked in the create() methods of the autogenerated JPA Controllers (PriceRecordJpaController) and they allways commit the transaction. Why would it fail in my case? Is there a way to "refresh" the transaction?

Thank you in advance.

Edited by: kkyriako on Nov 11, 2009 10:37 PM