Auto-number
Author
I am writing a Java application that uses JDBC to work with a database - The idea is that the app should be able to access any database engine (for instance MySQL).
Therefore I don''t wanna use stored procedures, but then a problem arises - What about Counter / AutoNumber type of fileds:
I insert a row into the db, the db engine will give it a unique number - How do I get this number back without using a stored procedure - Is there any good way, or shall I skip using the "Counter" functionallity of the database and do the numbering myself?
any input appreciated,
Jens
I am not sure exactly which of the following scenarios you are trying to use, so I will answer both. 
Case 1 - Unique Number
If you only need the column of data to store a unique number, then you can use a random GUID as the number. Since they are quite large, 32-bytes, you can usually guarantee uniqueness. You can always append the current time of day and date to it if you are really concerned.
If you are not using Windows, then you may want to pull the NIC identification number and append the time of day(in milliseconds), date, and a random number to it. NICs are supposed to have unique IDs. You may not even need the NIC id if you use time, date, and a random number.
Case 2 - Counted Number
If you are trying to keep a tally (count) then you can use mutexes or critical sections to lock the update. This prevents multiple threads from calling it at the same time. In a function I have written that increments a number in a database, it does the following:
1. Enters a critical section (or mutex if in UNIX).
2. Opens the database and uses the following call: select max(counter_columnname_here) from table_name;
3. It pulls the column returned, this gives me the latest number stored. The MAX() function is used to get the largest value of the column specified in it. This is SQL standard-95 I believe (not as in Windows 95, mind you.)
4. It then adds one to the number I got back. This new number will be the identifier for the new record.
5. It then submits the new record to the database like so: insert counter_columnname_here=incremented_number, data1=x, data2=x, data3=x into table_name;
6. Closes the database.
7. Releases the critical section (or mutex if UNIX).
I actually use mutexes, since they are process safe as well as thread safe, but you can use whichever meets your needs.
Hope that helps!
LostLogic
www.lostlogic.com
Author, Multiplayer Game Programming
Case 1 - Unique Number
If you only need the column of data to store a unique number, then you can use a random GUID as the number. Since they are quite large, 32-bytes, you can usually guarantee uniqueness. You can always append the current time of day and date to it if you are really concerned.
If you are not using Windows, then you may want to pull the NIC identification number and append the time of day(in milliseconds), date, and a random number to it. NICs are supposed to have unique IDs. You may not even need the NIC id if you use time, date, and a random number.
Case 2 - Counted Number
If you are trying to keep a tally (count) then you can use mutexes or critical sections to lock the update. This prevents multiple threads from calling it at the same time. In a function I have written that increments a number in a database, it does the following:
1. Enters a critical section (or mutex if in UNIX).
2. Opens the database and uses the following call: select max(counter_columnname_here) from table_name;
3. It pulls the column returned, this gives me the latest number stored. The MAX() function is used to get the largest value of the column specified in it. This is SQL standard-95 I believe (not as in Windows 95, mind you.)
4. It then adds one to the number I got back. This new number will be the identifier for the new record.
5. It then submits the new record to the database like so: insert counter_columnname_here=incremented_number, data1=x, data2=x, data3=x into table_name;
6. Closes the database.
7. Releases the critical section (or mutex if UNIX).
I actually use mutexes, since they are process safe as well as thread safe, but you can use whichever meets your needs.
Hope that helps!
LostLogic
www.lostlogic.com
Author, Multiplayer Game Programming
Is there any reason you want to stay completely DB independent? Stored procedures would drastically speed up DB access, though I realize that MySQL doesn''t support them (and the format changes from DB to DB).
Personally, I''ve always found autonumber sql fields to be more trouble than they''re worth. Ever since we once tried to deal with replicating a database with autonumber fields I''ve sworn them off forever. Also, I just checked the JDBC documentation and it doesn''t seem like there''s much there that would help.
If you can perform multiple queries in a single transaction in JDBC (and you don''t mind writing code that uses transactions) you could lock the table, determine the next valid number (using max()), do the insert, and commit. This will slow down insterts if you''ll be doing them from a ton of processes simultaneously, but should work.
Personally, I''ve always found autonumber sql fields to be more trouble than they''re worth. Ever since we once tried to deal with replicating a database with autonumber fields I''ve sworn them off forever. Also, I just checked the JDBC documentation and it doesn''t seem like there''s much there that would help.
If you can perform multiple queries in a single transaction in JDBC (and you don''t mind writing code that uses transactions) you could lock the table, determine the next valid number (using max()), do the insert, and commit. This will slow down insterts if you''ll be doing them from a ton of processes simultaneously, but should work.
Author
The reason I don''t wanna use SP:s are that I plan to run this on a Linux server (kinda low end), and I don''t have the funding to go out and buy a DB:server for Linux.
So I guess the issue isn''t really to be DB independant, but rather that I probably won''t have access to a DB server that supports SP:s.
That raises a new question - Does anyone know of a "free" Linux DB that supports SPs - Or any other free DB than MySQL?
cheers,
Jens
So I guess the issue isn''t really to be DB independant, but rather that I probably won''t have access to a DB server that supports SP:s.
That raises a new question
- Does anyone know of a "free" Linux DB that supports SPs - Or any other free DB than MySQL?cheers,
Jens
quote:Original post by Kensai
Is there any reason you want to stay completely DB independent? Stored procedures would drastically speed up DB access, though I realize that MySQL doesn''t support them (and the format changes from DB to DB).
What''s wrong with MySQL? How time critical are your operations? How many operations/sec? If MySQL isn''t fast enough, then you probably need to either re-engineer the way you are handling you data, or spend more money on better server and/or software.
In my experience, Microsoft SQL Server using stored procs can be pretty speedy, but it isn''t *that* much faster than MySQL with Apache and mod_PHP.
But to answer your question... check out PostGreSQL (I think?) for another free SQL DB.
In my experience, Microsoft SQL Server using stored procs can be pretty speedy, but it isn''t *that* much faster than MySQL with Apache and mod_PHP.
But to answer your question... check out PostGreSQL (I think?) for another free SQL DB.
You might try using PostgreSQL. It''s free as well. Although I''ve never actually developed with it, it''s derived from Ingres, which has treated me well.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
