Hi all! I'm having problems upgrading my database from 4.0.x to 5.0.x, specially with date fields
For example with 4.0.x I do: "select startDate from TEvent" and in my application it is displayed ok(managed with controls), but the same code, same odbc, same control in delphi, etc with mysql 5.0.x it displays: byte instance of the date storaged in my table My versions are: Delphi 5 MyODBC-3.51.11-2-win MySQL 5.0.18 MySQL 4.0.26 Regards! -----Mensaje original----- De: Peter Brawley [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Febrero de 2006 11:01 a.m. Para: Patrick Duda CC: mysql@lists.mysql.com Asunto: Re: question about locking Patrick, >I thought, that if I turned off autocommit I would enter a transaction. >Then, by using the "select...for update", that I would take and hole a lock on the table. With a transaction-capable table, eg InnoDB, otherwise neither setting autocommit off nor adding FOR UPDATE has any effect. PB ----- Patrick Duda wrote: > Hi, > > I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems > trying to figure out why I am not getting the results I am expecting. > > I have a table that is used for generating primary keys. It only has > one item, an int that is incremented each time a key is needed. This > is not my code or my design so using something like auto_incrament is > not an option. > > The code runs under a container and our desire is to have several > different containers running at the same time, all accessing the same > database. Each container is independent so the controls need to be on > the database side. > > The solution also needs to be portable to other databases so I am > trying to stay with standard JDBC or SQL options. > > The code for generating a new key is this: > > try { > c = DatabaseSetup.getDBConnection(); > > c.setAutoCommit(false); > > statement = > c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, > ResultSet.CONCUR_UPDATABLE); > > rs = statement.executeQuery("select transfer_id from transferid for > update"); > > if (!rs.next()) { > nextTransferId = nextTransferId + 1; > StringBuffer query = new StringBuffer(); > query.append("insert into transferid(transfer_id) > values ("); > query.append(nextTransferId); > query.append(")"); > tempStatement = c.createStatement(); > // Now Update the old value with new value > tempStatement.executeUpdate(query.toString()); > } else { > rs.previous(); > while( rs != null && rs.next() ) { > nextTransferId = rs.getInt(1); > // Get the transfer Id and increment it instead of > using > // Db Specific sequence > nextTransferId = nextTransferId + 1; > // Now Update the old value with new value > tempStatement = c.createStatement(); > tempStatement.executeUpdate("update transferid set "+ > "transfer_id= "+ nextTransferId); > } > } > } catch (SQLException e) { > > if( c != null ) > { > try > { > c.rollback(); > c.setAutoCommit(true); > } > catch( SQLException ex ) > { > } > } > throw new DBException(i18n.getMessage("dbInsertErr"), > e); > } finally { > try { > c.commit(); > c.setAutoCommit(true); > if (statement != null) { > statement.close(); > } > if (tempStatement != null) { > tempStatement.close(); > } > if (rs != null) { > rs.close(); > } > if (c != null) { > DatabaseSetup.returnDBConnection(c); > } > } catch (SQLException sql) { > logger.warn(i18n.getMessage("dbStatementErr"), sql); > } > } > return nextTransferId; > } > > I thought, that if I turned off autocommit I would enter a > transaction. Then, by using the "select...for update", that I would > take and hole a lock on the table. That no other transaction would be > able to read the table until I released the lock. However, this is > not what I am seeing when I run some tests. I start up a number of > containers and then fire off a bunch of jobs to each. Each of these > jobs will hit the above code. The problem is that every so often I > see the following error message. > > Error inserting records into database [Caused by: Duplicate entry > '131' for key 1] > > What am I doing wrong? > > How am I suppose to be doing this via JDBC? I know it should work... > > Thanks > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.5/256 - Release Date: 2/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
