3 Replies Latest reply on Jan 7, 2002 6:13 PM by ewlyahoocom

    mysql & database pooling

    neilmcc

      Hi all,

      I have an application which uses the MM mysql drivers to connect via jboss connection pool to mysql 3.23. I'm using the driver's capability to extract the value from an auto increment field with getLastInsertId like this :

      long thisID = ((org.gjt.mm.mysql.PreparedStatement) statement).getLastInsertID();
      


      This works fine un-pooled. But as soon as the pool is enabled I get a class cast exception on this line -

      java.lang.ClassCastException: org.jboss.pool.jdbc.PreparedStatementInPool


      It looks like the PreparedStatementInPool class wraps the MM driver.

      How can I extract the last inserted id using the MM driver & jBoss' pools?

      Tks,
      mcc

        • 1. Re: mysql & database pooling
          p_d_austin

          With datasources you cannot cast to what you think the prepared statement class is as the application server will generally put a facade in front of the actual preparedstatement, connection and resultset classes. So what you are trying to acheive using the mysql driver's extension to JDBC will not work.

          So that was the bad news, the good news is you can get the last id inserted by mysql into and autonumber field using a simple select statement. This is safe across multiple threads as it is tied to the current connection.

          idStatement = con.createStatement();
          idRs = idStatement.executeQuery("select image_id from nh_image where image_id is null");
          if (idRs.next()) {
          id = idRs.getLong(1);
          }

          The key here is that you are selecting the record that the id field is null.

          • 2. Re: mysql & database pooling
            davidjencks

            You can also get the underlying PreparedStatement by calling getUnderlyingPreparedStatement(), and cast that.

            • 3. Re: mysql & database pooling
              ewlyahoocom