List:General Discussion« Previous MessageNext Message »
From:ISC Edwin Cruz Date:February 10 2006 6:01pm
Subject:MySQL 5.0.x and Delphi
View as plain text  
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


Thread
question about lockingPatrick Duda8 Feb
  • Re: question about lockingGleb Paharenko8 Feb
  • Re: question about lockingPeter Brawley10 Feb
    • MySQL 5.0.x and DelphiISC Edwin Cruz10 Feb
      • Re: MySQL 5.0.x and DelphiMartijn Tonies10 Feb
        • RE: MySQL 5.0.x and DelphiISC Edwin Cruz15 Feb
          • Re: MySQL 5.0.x and DelphiMartijn Tonies15 Feb
RE: question about lockingDan Burke8 Feb