select count(*) from myTable; ???
before inserting the next record. In this way I know how many records i have.
how can I do this with JDBC...
When using sequences supplied by the databse, I tend to select the next value before doing the insert rather than doing both at the same time.
I can't remember the exact way do do this in a dummy select, but I can have a look if you want.
Your original question was:
I have a table in my database with an auto-increment field, which is the primary key. What I'd like to know is how I can get this number after I insert a record into the table.
I'm suggesting you pull the primary key value BEFORE you do the insert.
the problem is the following. My application enables a user to upload an image to the server. My upload class (jspsmartUpload) always overwrites the file in the server, so if a guy posts an image called computer.jpeg and another guy chooses the same name for his picture (could be a computer, but different image), now I get into trouble. the file is replaced and the first image uploaded is lost.
So I was thinking about renaming the image with the secuence number. In this way I'm curing myself. There's not going to be the same number.
Now, i insert the product information WITHOUT the image (inserting null in the image path). Then I do some sort of select to retrieve the information I have just inserted, rename the file I have uploaded with the secuence number i get and update the record with the full image path.
Sometimes I think I'm complicated all the things but I can't think of any other way to do it. If I have a category called computers, I know people will have their image called computer.jpeg and will want to upload it, replacing other people's files.
uff... so this is my problem. maybe you can give me any suggestions or ideas...
Then use the sequence number.
I'm not sure on the exact sytax, but usually something like "SELECT nextVal() as seq FROM <dummy table>"
All you are doing here is pretending to read a value from the database, where what you really want is the output from a database function (no idea what the function is called though)
I do this which is essentially an imitation of the internal operation of auto increment
Note that you cant include the +1 in the select statement because if no records are found the +1 will not happen. Now with the max ID I can safely insert new elements into the table on my own. Of course this is an exclusive access DB
Basically what you do is after the insert issue the command "SELECT LAST_INSERT_ID()" from the same Statement object.
Try it from the mysql cmd line.
Issue an insert statement, then run "select last_insert_id()".
As for the jdbc code above, the reason you need to use the "same" Statement is that it returns the last id you have just inserted, other solutions listed here (such as selecting the max(id)) will return the last id inserted into the table, but that might NOT have been issued by you, if there is a multi-user situation going on.
[ June 25, 2002: Message edited by: James Swan ]
This is not thread safe and would more than likely break uniqueness.
This may be safer but I'm still dubious. LAST_INSERT_ID() wasn't designed to be a sequence generator but can be used a sequence generator...
It's more important to make sure that the keys are unique than the keys be sequential. Don't be fooled by the word 'sequence', I don't believe there is any promise of actual sequantial values.
Generally sequences are managed on a per-connection basis rather than per table. When you create a connection, it gets a bunch of unique numbers it is allowed to use. No other connection gets these numbers, so uniqueness is guuaranteed. If the connection closes there is no promise that unused numbers are returned hence continuous ranges are unlikely.
If we're still talking about a MySQL-specific solution, have a look at the docs for LAST_INSERT_ID at http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
are we getting any closer to a solution?
its only a sequence damnit
Of course if all you really want is a unique value so that images don't collide, don't use the database at all. You can build your own in several different ways, System.currentTimeMillis() being one (evil) way.
I'm starting to like evil solutions....
If your only requirement is to give unique name to the uploaded image and you are not very particular about the sequence numer then the solution is very simple. Your product name is going to be a unique one. So rename the image file like productname_image.
hhmmm.. i don't think so mate. What happens if you upload an image named computer.jpeg, but another user has already upload his computer.jpeg file? the file will be overwritten, and I do not want that to happen.