[ale] Getting the next index of a table in mysql

[Chris Fowler]

I'm going to control deletion.  Once a row is inserted it can not be
changed. 

There is a problem if the user does do the following in an empty DB.

insert into sensor_533 values (533, now());

Now max will retrieve the 533 as the max with only one row in the DB.

> Checking the new Row ID after an insert is the only real way to go
about it.

This might be the best way but how do I do this?  "select serial_no from
table_533 where ....."

The table is very simple for now.

 CREATE TABLE sensor_533 (
  serial_no INTEGER(9) NOT NULL AUTO_INCREMENT,
  issued DATETIME NOT NULL,
  PRIMARY KEY(serial_no),
);

CREATE TABLE device (
  device_id INTEGER(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL,
  table_name VARCHAR(20) NULL,
  PRIMARY KEY(device_id),
);

insert into device values (0, "533", "sensor_533");

Evan that method has the same problem as select max(...)  In the MySQL
documents it stated that the auto_increment value was stored in memory
at startup and the program simply does a select max(...) to get the
correct value.



On Thu, 2005-06-09 at 09:05 -0400, Michael B. Trausch wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
> 
> Chris Fowler wrote:
> >
> > I've attached a perl program where I hacked out a way to get the next
> > logical entry.  This program does not do the insert yet.
> > 
> 
> Just note that if entries are added and deleted and so forth, that this
> will not be accurate.  The program is selecting the highest current
> serial number, when in fact, I could add 500 rows, and then delete them
> all.  The max serial would then be (next_incremented_serial-501), and
> then you'd be out of sync with the DB.  In addition, when you next
> INSERT using a non-explicit ID count, you'll be out of sync.
> 
> 	- Mike
> 
> - --
> Michael B. Trausch                                     fd0man at gmail.com
> Web: http://fd0man.theunixplace.com/        Jabber: mtrausch at jabber.com
> Phone: +1-(678)-522-7934                  FAX (US Only): 1-866-806-4647
> =======================================================================
>   Do you have PGP or GPG?  Key at pgp.mit.edu, Please Encrypt E-Mail!
> 
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
> 
> iD8DBQFCqD59PXInbkqM7nwRA6caAJ0Qs0pX9X9raz660ftuYrg7GulNGACfX8MH
> LkBCry3rMa+g/697ijbmr4Q=
> =KkgU
> -----END PGP SIGNATURE-----
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale