[Gambas-user] Creating an Auto Increment Field

[Øystein Selbekk]

> I'm writing a routen to create a database if it doesn't exist.  I need 
> to make the primary key an auto increment integer (MySQL database)
> 
> I use the following to create an integer primary key:
> 
>   WITH hConnection
>     hTable = .Tables.Add("CALL")
>     hTable.Fields.Add("id", gb.Integer, 11)
>     hTable.PrimaryKey = ["id"]
>     hTable.Update 
>   END WITH
> 
> But how do I make it an auto increment?  (Without Exec a SQL alter table 
> statement later.)
> 
> Thanks
> End of Gambas-user Digest

Hi there....
I would have to say that this is not by far any good way to create a table. You should much rather use a ANSI SQL statement for MySQL. EX: 

PRIVATE $hConn as Connection
PRIVATE $rData as Result
PRIVATE sName as string

$hConn = NEW Connection
sName = "mysql"
WITH $hConn
    .Type = "mysql"
    .Host = "localhost"
    .Login = "root"
    .Password = ""
END WITH
$hConn.Name = sName
$hConn.Open
$rData = $hConn.Exec("create table CALL (id int(11) auto_increment,myfirstdatafield varchar(50) not null,myseconddatafield varchar(5) not null,PRIMARY KEY (id))")
$hConn.Close

This should work if the MySQL server resides on your local PC and you dont use a password on the mysql root account.
Happy coding!

Regards Oystein Selbekk