Gambas Documentation
Application Repository
Code Snippets
Compilation & Installation
Components
gb
gb.args
gb.cairo
gb.chart
gb.clipper
gb.complex
gb.compress
gb.crypt
gb.data
gb.db
gb.db.form
gb.db.mysql
gb.db.odbc
gb.db.postgresql
gb.db.sqlite2
gb.db.sqlite3
gb.dbus
gb.dbus.trayicon
gb.debug
gb.desktop
gb.desktop.gnome.keyring
gb.desktop.x11
gb.eval
gb.eval.highlight
gb.form
gb.form.dialog
gb.form.editor
gb.form.htmlview
gb.form.mdi
gb.form.print
gb.form.stock
gb.form.terminal
gb.gmp
gb.gsl
gb.gtk
gb.gtk.opengl
gb.gtk3
gb.gtk3.opengl
gb.gtk3.webview
gb.gui
gb.gui.opengl
gb.gui.qt
gb.gui.qt.ext
gb.gui.qt.opengl
gb.gui.qt.webkit
gb.gui.trayicon
gb.gui.webview
gb.hash
gb.highlight
gb.image
gb.image.effect
gb.image.imlib
gb.image.io
gb.inotify
gb.jit
gb.libxml
gb.logging
gb.map
gb.markdown
gb.media
gb.media.form
gb.memcached
gb.mime
gb.mongodb
gb.mysql
_DataBase
_DataTypes
_Field
_Index
_MySQL
_Table
_User
_View
Connection
DB
gb.ncurses
gb.net
gb.net.curl
gb.net.pop3
gb.net.smtp
gb.openal
gb.opengl
gb.opengl.glsl
gb.opengl.glu
gb.opengl.sge
gb.openssl
gb.option
gb.pcre
gb.pdf
gb.poppler
gb.qt4
gb.qt4.ext
gb.qt4.opengl
gb.qt4.webkit
gb.qt4.webview
gb.qt5
gb.qt5.ext
gb.qt5.opengl
gb.qt5.webkit
gb.qt5.webview
gb.qt6
gb.qt6.ext
gb.qt6.opengl
gb.qt6.webview
gb.report
gb.report2
gb.scanner
gb.sdl
gb.sdl.sound
gb.sdl2
gb.sdl2.audio
gb.settings
gb.signal
gb.term
gb.test
gb.util
gb.util.web
gb.v4l
gb.vb
gb.web
gb.web.feed
gb.web.form
gb.web.gui
gb.xml
gb.xml.html
gb.xml.rpc
gb.xml.xslt
Controls pictures
Deprecated components
Developer Documentation
Development Environment Documentation
Documents
Error Messages
Gambas Playground
How To's
Language Index
Language Overviews
Last Changes
Lexicon
README
Search the wiki
To Do
Topics
Tutorials
Wiki License
Wiki Manual

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