INT value Auto Increment ?

[Thotheolh]

Hi. I was wondering if it's possible to allow an INT value object to
automatically increment? This function is found in MySQL database
where INT value objects can be specified upon creation so that the INT
values would auto increment everytime a new row is added. It is used
for ID generation.

Also, may I know how to get the data from the last row ? The ASCII art
below is the table. I would like to get the last row which is '4', how
do I put it in SQL ?

+---------------------+
| ID |
+---------------------+
| 1 |
+---------------------+
| 2 |
+---------------------+
| 3 |
+---------------------+
| 4 |
+---------------------+

[Yaroslav Kavenchuk]

Thotheolh wrote:
> Hi. I was wondering if it's possible to allow an INT value object to
> automatically increment? This function is found in MySQL database
> where INT value objects can be specified upon creation so that the INT
> values would auto increment everytime a new row is added. It is used
> for ID generation.
>
> Also, may I know how to get the data from the last row ? The ASCII art
> below is the table. I would like to get the last row which is '4', how
> do I put it in SQL ?

For automatical autoincrement use AUTO_INCREMENT | IDENTITY
http://www.h2database.com/html/grammar.html#createtable

For manual autoincrement use DEFAULT and SEQUENCE

--
WBR, Yaroslav Kavenchuk.

[Thotheolh]

Here's a SQL statement: create table numbers (ID int primary key
auto_increment, Passcode varchar(20) not null)

Error: create table numbers (ID int primary key auto_increment,
Passcode varchar(20) not null);
Syntax error in SQL statement CREATE TABLE NUMBERS (ID INT PRIMARY KEY
AUTO_INCREMENT[*], PASSCODE VARCHAR(20) NOT NULL) ; expected ); SQL
statement:
create table numbers (ID int primary key auto_increment, Passcode
varchar(20) not null) [42001-64]

[Yaroslav Kavenchuk]

2008/4/3, Thotheolh wrote:
> Here's a SQL statement: create table numbers (ID int primary key
> auto_increment, Passcode varchar(20) not null)

Broken order.
file:///C:/JVM/H2/docs/html/grammar.html#createtable
create table numbers (ID int auto_increment primary key, Passcode
varchar(20) not null)

--
WBR, Yaroslav Kavenchuk.

[Thomas Mueller]

Hi,

This is supported in H2. See IDENTITY, Statement.getGeneratedKeys(),
and CALL IDENTITY().

> create table numbers (ID int primary key auto_increment, Passcode varchar(20) not null)

Unfortunately this is not supported at the moment. This should work however:

create table numbers (ID int auto_increment primary key, Passcode
varchar(20) not null)

I will add support for this special MySQL syntax in the next release.

Regards,
Thomas

[Thotheolh]

Thanks for all the help, Yarosalv and Thomas.

I don't think the special MySQL syntax need to be added in the next
release thus causing too much trouble. I think I just need to become
familiar and refresh my rusty memory on SQL statement which I haven't
touched for nearly a year.

Thomas, try to keep the jar file small and light while improving H2.
Looking forward to the next release.

[Thomas Mueller]

Hi,

> Syntax error in SQL statement
>

> CREATE TABLE IF NOT EXISTS COMPANY (
> CNAME VARCHAR(50) NOT NULL PRIMARY KEY,
> )[*] ; expected identifier; SQL statement:

There is an extra ',' just after 'PRIMARY KEY' and before ')'.

> CREATE TABLE IF NOT EXISTS company (
> cname varchar(50) NOT NULL primary key,
> ) [42001-64]

Here as well.

> I wonder if the semi colon I placed after each statement is causing
> the problem ?

No, the semicolon is required.

Regards,
Thomas