Gambas Documentation - /comp/gb.mysql

/comp/gb.mysql

# gb.mysql

The goal of gb.mysql is to create MySQL specific routines and then sent them to the gb.db to be executed.

For example, the Date DataType in Gambas represents a date and time value (06-08-1981 04:25:08), in MySQL the Date DataType represents only dates in a range from '1000-01-01' to '9999-12-31'.

One of the abilities of gb.db is to be “type independent” which means that if you create a database with some tables using gb.db, the component will be able to create it in any Database Manager supported by gb.db (i.e ODBC, sqlite, postgresql, etc), it is great, but if you have to create a specific database with specific datatypes in MySQL you should use gb.mysql.

This component is not stable yet.

Author | David Villalobos Cambronero.
Requires | gb.db gb.db.mysql

Class | Description
--- | ---
Connection |
DB |

## Example

The following example shows the way to use 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" 'Sets the charset, default: utf8
      $hConnection.MySQL.Collation = "utf8_general_ci" 'Sets the collation, default: utf8_general_ci
      $hConnection.MySQL.Engine = "InnoDB" 'Sets the engine, default: InnoDB
      $hConnection.MySQL.DataBase.Delete(Gambas, True) 'Deletes a database
      $hConnection.MySQL.DataBase.Add(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation) 'Adds a database
      $hConnection.MySQL.DataBase.Modify(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation)

      'Shows the database information
      Message(("Information for: ") & $hConnection.Name & GB.NewLine & $hConnection.MySQL.DataBase.Info(txtDatabase.Text))

      'Lets create some tables

      'Table actor
      $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)
      'Statement executed
      ' Create 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 = InnoDB Default CHARSET = utf8

      'Table country
      $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)
      'Statement executed
      ' Create 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 city
      $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)
      'Statement executed
      ' Create 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 address
      $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)
      'Statement executed
      ' Create 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