gb.mysql

Le but de gb.mysql est de créer des routines MySQL spécifiques et de les envoyer à gb.db pour exécution.

Par exemple, le type de donnée Date dans Gambas représente une valeur de temps et date (06-08-1981 04:25:08), dans MySQL le type Date représente uniquement les dates dans une gamme de '1000-01-01' à '9999-12-31'.

L’une des capacités de gb.db est d’être “indépendante du type” ce qui signifie que si vous créez une base de données avec des tables utilisant gb.db, le composant sera capable de les créer dans n’importe quel gestionnaire de Database supporté par gb.db (c.a.d. ODBC, sqlite, postgresql, etc), c’est puissant, mais si vous devez créer une base de données spécifique avec des types de données spécifique dans MySQL vous devriez utiliser gb.mysql.

Classe	Description
Connection
DB

Exemple

L’exemple suivant illustre la manière d’utiliser gb.mysql :

Public Procedure CreateDatabase()

  Dim $hConnection As New Connection

  With $hConnection
    .Type = "mysql"
    .Port = "3306"
    .Host = "localhost"
    .User = "root"
    .Password = "mypass"
    .Name = "Gambas"
    .Open()
  End With

$hConnection.MySQL.Charset = "utf8" 'Fixe le jeu de caractères par défaut : utf8
  $hConnection.MySQL.Collation = "utf8_general_ci" 'Fixe l’interclassement par défaut : utf8_general_ci
  $hConnection.MySQL.Engine = "InnoDB" 'Fixe le moteur, défaut : InnoDB
  $hConnection.MySQL.DataBase.Delete(Gambas, True) 'Efface une base de données
  $hConnection.MySQL.DataBase.Add(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation) 'Ajoute une base de données
  $hConnection.MySQL.DataBase.Modify(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation)

  'Affiche les informations de la base de données
  Message(("Information for: ") & $hConnection.Name & GB.NewLine & $hConnection.MySQL.DataBase.Info(txtDatabase.Text))

  'Créons des tables

  'Table acteur
  $hConnection.MySQL.Field.Add("actor_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("first_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
  $hConnection.MySQL.Field.Add("last_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["actor_id"])
  $hConnection.MySQL.Field.Index("idx_actor_last_name", "last_name")
  $hConnection.MySQL.Table.Add("actor", "InnoDB", $hConnection.MySQL.Charset)
  'Instruction exécutée
  ' Crée la TABLE `actor` (
  '   `actor_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `first_name` varchar(45) NOT NULL,
  '   `last_name` varchar(45) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`actor_id`),
  '   KEY `idx_actor_last_name` (`last_name`)
  ' )ENGINE

'Table pays
  $hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("country", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["country_id"])
  $hConnection.MySQL.Table.Add("country", "InnoDB", $hConnection.MySQL.Charset)
  'Instruction exécutée
  ' Crée la TABLE `country` (
  '   `country_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `country` varchar(50) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`country_id`)
  ' )ENGINE = InnoDB Default CHARSET = utf8

  'Table ville
  $hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("city", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["city_id"])
  $hConnection.MySQL.Field.Index("idx_fk_country_id", "country_id")
  $hConnection.MySQL.Field.ForeignKey("country_id", "country", "country_id", "RESTRICT", "CASCADE")
  $hConnection.MySQL.Table.Add("city", "InnoDB", $hConnection.MySQL.Charset)
  'Instruction exécutée
  ' Crée la TABLE `city` (
  '   `city_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `city` varchar(50) NOT NULL,
  '   `country_id` smallint(5) unsigned NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`city_id`),
  '   KEY `idx_fk_country_id` (`country_id`),
  '   CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
  ' )ENGINE = InnoDB Default CHARSET = utf8

  'Table addresse
  $hConnection.MySQL.Field.Add("address_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("address", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("address2", $hConnection.MySQL.DataTypes.VarChar(50), True, $hConnection.MySQL.DataTypes.Null)
  $hConnection.MySQL.Field.Add("district", $hConnection.MySQL.DataTypes.VarChar(20), False)
  $hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
  $hConnection.MySQL.Field.Add("postal_code", $hConnection.MySQL.DataTypes.VarChar(10), True, $hConnection.MySQL.DataTypes.Null)
  $hConnection.MySQL.Field.Add("phone", $hConnection.MySQL.DataTypes.VarChar(20), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["address_id"])
  $hConnection.MySQL.Field.Index("idx_fk_city_id", "city_id")
  $hConnection.MySQL.Field.ForeignKey("city_id", "city", "city_id", "RESTRICT", "CASCADE")
  $hConnection.MySQL.Table.Add("address", "InnoDB", $hConnection.MySQL.Charset)
  'Instruction exécutée
  ' Crée la TABLE `address` (
  '   `address_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `address` varchar(50) NOT NULL,
  '   `address2` varchar(50) default 'NULL',
  '   `district` varchar(20) NOT NULL,
  '   `city_id` smallint(5) unsigned NOT NULL,
  '   `postal_code` varchar(10) default 'NULL',
  '   `phone` varchar(20) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`address_id`),
  '   KEY `idx_fk_city_id` (`city_id`),
  '   CONSTRAINT `address_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
  ' )ENGINE = InnoDB Default CHARSET = utf8

End