read out an AUTO_INCREMENT-ed value

[Bart Van der Donck]

Hello,

The first column of my table is AUTO_INCREMENT.
I fill my table with 5 records with a blanco value in their first
field.
The first column of my table will then hold the values 1,2,3,4,5
(top>down).

Then I erase those 5. Then I add a new record with a blanco first
value. It will get number 6.

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

Thanks,
Bart

[Aggro]

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

[Aggro]

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

[Aggro]

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

[Bart Van der Donck]

Aggro <sp**********@yahoo.com> wrote in message news:<jb***************@read3.inet.fi>...

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

I am working with binary objects that I store in a directory. I let
the application take the ID of a row, then put a file extension behind
this ID, and finally put a fixed directory path before the ID. That
way the application knows which object belongs to which row in my
table.

Now when adding new records, I have to know though what the name of
the object must be. Obviously the same as the ID of my row that I am
about to insert. So that the application can find it back later.

So basically I was hoping for something like:
"SELECT CURRENT_AUTO_INCREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

regards
Bart

[Bart Van der Donck]

Aggro <sp**********@yahoo.com> wrote in message news:<jb***************@read3.inet.fi>...

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

I am working with binary objects that I store in a directory. I let
the application take the ID of a row, then put a file extension behind
this ID, and finally put a fixed directory path before the ID. That
way the application knows which object belongs to which row in my
table.

Now when adding new records, I have to know though what the name of
the object must be. Obviously the same as the ID of my row that I am
about to insert. So that the application can find it back later.

So basically I was hoping for something like:
"SELECT CURRENT_AUTO_INCREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

regards
Bart

[Bart Van der Donck]

Aggro <sp**********@yahoo.com> wrote in message news:<jb***************@read3.inet.fi>...

Bart Van der Donck wrote:

My problem in this scenario:
I want to read out this number 6 before I will insert that record. Is
that possible?

May I ask why? I can't imagine any good reason for that.

I am working with binary objects that I store in a directory. I let
the application take the ID of a row, then put a file extension behind
this ID, and finally put a fixed directory path before the ID. That
way the application knows which object belongs to which row in my
table.

Now when adding new records, I have to know though what the name of
the object must be. Obviously the same as the ID of my row that I am
about to insert. So that the application can find it back later.

So basically I was hoping for something like:
"SELECT CURRENT_AUTO_INCREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

regards
Bart

[Aggro]

Bart Van der Donck wrote:

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

Atleast in PHP, C and C++ API for MySQL have a function that will return
the last inserted auto_increment value. You didn't mention which
language you are using so it's hard to give any specific instructions.

But usually there is always a method that will return the auto_increment
id value for previously inserted row. If there isn't you can get it by
committing a second query to database and using LAST_INSERT_ID() SQL
function.

"You can retrieve the most recent AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.
These functions are connection-specific, so their return value is not
affected by another connection also doing inserts."

More information:
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

[Aggro]

Bart Van der Donck wrote:

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

Atleast in PHP, C and C++ API for MySQL have a function that will return
the last inserted auto_increment value. You didn't mention which
language you are using so it's hard to give any specific instructions.

But usually there is always a method that will return the auto_increment
id value for previously inserted row. If there isn't you can get it by
committing a second query to database and using LAST_INSERT_ID() SQL
function.

"You can retrieve the most recent AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.
These functions are connection-specific, so their return value is not
affected by another connection also doing inserts."

More information:
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

[Aggro]

Bart Van der Donck wrote:

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','data2') RETURN
AUTO_INCREMENT"
and that it would return the value that was inserted in the first
field (that was an AUTO_INCREMENT)

Atleast in PHP, C and C++ API for MySQL have a function that will return
the last inserted auto_increment value. You didn't mention which
language you are using so it's hard to give any specific instructions.

But usually there is always a method that will return the auto_increment
id value for previously inserted row. If there isn't you can get it by
committing a second query to database and using LAST_INSERT_ID() SQL
function.

"You can retrieve the most recent AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.
These functions are connection-specific, so their return value is not
affected by another connection also doing inserts."

More information:
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html