Thurgood Alex wrote:
Hi everyone,
I'm having a conceptual problem with managing autoincrements and I was
hoping that the list could point me in the right direction ;-)
I've migrated a FMPro database which turned out to be a nightmare for
various reasons, including the use of multivalue fields in FMPro that
didn't export well. However, the real problem that I have is that FMPro
allows you to use multiple autoincrement fields in the same table.
The mysql server I've migrated to is at version 4.0.23. The
autoincrement fields were exported as text and are unfortunately not
sequential (in other words, there are gaps) due to data entry errors and
a certain lack of rigor in the use of the previous FMPro db. The data in
this field serves as a foreign key for another table, thus enabling
groups of data to be created in the other table that all reference a
same, and in theory, unique, id. To help with understanding, I'll give a
sample below :
Table1 :
field1 int unsigned zerofill auto_increment primary key
field2 varchar
field3 date
field4 int unsigned zerofill (references second table)
Table2 :
field1 int unsigned zerofill auto_increment primary key
field2 int unsigned zerofill (corresponds to the values that need to
appear in field4 of Table1)
I have populated Table2.field2 with the data that was migrated from the
original FMPro table. This has given me a list something like the
following :
+-------------+--------------+
| field1 | field2 |
+-------------+--------------+
| 0000000001 | 0000000007 |
| 0000000002 | 0000000009 |
| 0000000003 | 0000000010 |
| 0000000004 | 0000000012 |
| 0000000005 | 0000000013 |
| 0000000006 | 0000000014 |
| 0000000007 | 0000000016 |
| 0000000008 | 0000000017 |
| 0000000009 | 0000000019 |
| 0000000010 | 0000000022 |
| 0000000011 | 0000000024 |
| 0000000012 | 0000000026 |
| 0000000013 | 0000000028 |
| 0000000014 | 0000000030 |
| 0000000015 | 0000000031 |
+-------------+--------------+
As you can see, the values in field2 are not sequential. Unfortunately,
I can not regenerate a new listing, since this would require modifying
by hand all of the files in the office that have changed. So the first
condition is to maintain these values.
Now, I need to be able to continue generating values for field2 in a
sequential manner i.e. field2 +1, each time a new group reference is
created.
This is where I am stuck. I have tried using last-insert_id() to help me
with adding one to field2 of the last generated record, but this
functionality is session and user specific, which means that when the
user disconnects from the db and reconnects later, the initial reference
count for last_insert_id is reset to zero
I also tried changing my autoincrement value in field1 to start from a
value that would be one more than the last value of field2 actually
present in Table2. This works fine, but I don't know which SQL command
to enter to get it to copy the value from the autoinc generation into
field2 at the same time as it creates the autoinc value.
To top it all, data entry is achieved through a form in OpenOffice.org
via MyODBC, so any proposed solutions would have to be ODBC compliant.
The main form corresponds to Table1, and links to the value of field2 in
Table2 via a drop down list that executes a select SQL statement on the
list of values present in field2. That way, the user can choose the
value they want. I'm using a button activated macro to try and generate
a new reference, but I'm failing with the necessarySQL command.
Any help most greatly appreciated,
TIA,
Alex Thurgood
Alex,
I'm not sure I understand your dilemma; it sounds like you have made it
more complex than it needs to be. If you need to import data that
was an auto_increment field (but is no longer contiguous) and then,
after the import, you wish for that field to continue to be
auto_increment.... that is simple. An auto_increment field in MySQL does
not need to store contiguous integers (ie, you can delete rows
previously inserted, or insert rows with higher values than the current
max). Here is a demonstration (table "t1" represents your old data,
table "t2" demonstrates importing the old data and continuing to insert
data into it).
mysql> create table t1 (a int auto_increment primary key, b int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2 (a int auto_increment primary key, b int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 (b) values (10),(20),(30),(40),(50);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into t1 (a,b) values (10,90);
Query OK, 1 row affected (0.01 sec)
mysql> delete from t1 where b in (20,30);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t1 order by a;
+----+------+
| a | b |
+----+------+
| 1 | 10 |
| 4 | 40 |
| 5 | 50 |
| 10 | 90 |
+----+------+
4 rows in set (0.00 sec)
mysql> insert into t2 select * from t1 order by a;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t2 (b) values (100),(200);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2 order by a;
+----+------+
| a | b |
+----+------+
| 1 | 10 |
| 4 | 40 |
| 5 | 50 |
| 10 | 90 |
| 11 | 100 |
| 12 | 200 |
+----+------+
6 rows in set (0.00 sec)
You can see that in table "t2", the last insert statement (which
inserted values 100 and 200) continued to use the auto_increment based
on the largest value in column a (not how many rows there were).
In short, it sounds like you should be able to simply copy the
auto_increment field data from your old DBMS into MySQL, and continue to
use it as such, without problem. Have you tried this in a test
environment yet?
Perhaps I have completely misunderstood what you are trying to do. If
that's the case, I apologize for the long winded and useless answer!
Best Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
