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:peter.brawley@stripped]
Enviado el: Viernes, 10 de Febrero de 2006 11:01 a.m.
Para: Patrick Duda
CC: mysql@stripped
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/mysql?unsub=1
