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]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> My problem in this scenario:
> I want to read out this number 6 before I will insert that record. Is
> that possible?[/color]

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

[Aggro]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> My problem in this scenario:
> I want to read out this number 6 before I will insert that record. Is
> that possible?[/color]

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

[Aggro]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> My problem in this scenario:
> I want to read out this number 6 before I will insert that record. Is
> that possible?[/color]

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

[Bart Van der Donck]

Re: read out an AUTO_INCREMENT-ed value

Aggro <spammerdream@y ahoo.com> wrote in message news:<jbt0c.324 $GE4.261@read3. inet.fi>...[color=blue]
> Bart Van der Donck wrote:
>[color=green]
> > My problem in this scenario:
> > I want to read out this number 6 before I will insert that record. Is
> > that possible?[/color]
>
> May I ask why? I can't imagine any good reason for that.[/color]

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_IN CREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','da ta2') 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]

Re: read out an AUTO_INCREMENT-ed value

Aggro <spammerdream@y ahoo.com> wrote in message news:<jbt0c.324 $GE4.261@read3. inet.fi>...[color=blue]
> Bart Van der Donck wrote:
>[color=green]
> > My problem in this scenario:
> > I want to read out this number 6 before I will insert that record. Is
> > that possible?[/color]
>
> May I ask why? I can't imagine any good reason for that.[/color]

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_IN CREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','da ta2') 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]

Re: read out an AUTO_INCREMENT-ed value

Aggro <spammerdream@y ahoo.com> wrote in message news:<jbt0c.324 $GE4.261@read3. inet.fi>...[color=blue]
> Bart Van der Donck wrote:
>[color=green]
> > My problem in this scenario:
> > I want to read out this number 6 before I will insert that record. Is
> > that possible?[/color]
>
> May I ask why? I can't imagine any good reason for that.[/color]

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_IN CREMENT_NUMBER FROM mytable"

or even better, something like:
"INSERT INTO mytable VALUES ('','data1','da ta2') 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]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> or even better, something like:
> "INSERT INTO mytable VALUES ('','data1','da ta2') RETURN
> AUTO_INCREMENT"
> and that it would return the value that was inserted in the first
> field (that was an AUTO_INCREMENT)[/color]

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]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> or even better, something like:
> "INSERT INTO mytable VALUES ('','data1','da ta2') RETURN
> AUTO_INCREMENT"
> and that it would return the value that was inserted in the first
> field (that was an AUTO_INCREMENT)[/color]

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]

Re: read out an AUTO_INCREMENT-ed value

Bart Van der Donck wrote:
[color=blue]
> or even better, something like:
> "INSERT INTO mytable VALUES ('','data1','da ta2') RETURN
> AUTO_INCREMENT"
> and that it would return the value that was inserted in the first
> field (that was an AUTO_INCREMENT)[/color]

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