How To Open a MySQL connection and use it

Example 1. Create a new MySQL connection

Notes

  1. You should have already installed the gb.mysql and all MySQL related packages.

  2. This is not an example about SQL (MySQL or otherwise) statements.

  3. The privileges to a database are granted by the MySQL administrator to the MySQL user and are not controlled by Gambas.

  4. The gb.db component has to be selected for use in your project before your program can access the DataBase Engine.

Recommendations

  1. The database connection of your program should be placed in a Module, so it can be called from anywhere.

  2. The gambas-database-manager is great, However if you want to create a good database in MySQL you should use the MySQL console or MySQL Workbench, these will help prevent potential problems dealing with Data Types.

Steps

1.Create a new Module for you program, this example uses MODMain as the Module name.

2.Create a new variable for the connection:

PUBLIC $Con AS NEW Connection

3.Now create a Procedure to make the connection:

PUBLIC PROCEDURE Connect() $Con.Close() ' Close the connection $Con.Type = "[..../def/mysql]" ' Type of connection $Con.Host = "localhost" ' Name of the server $Con.Login = "root" ' User's name for the connection $Con.Port = "3306" ' Port to use in the connection, usually 3306 $Con.Name = "Sophia" ' Name of the database we want to use $Con.Password = "root123" ' User's password $Con.Open() ' Open the connection END
4.Now create a Procedure to start the program:

PUBLIC SUB Main() Connect() ' Run the Procedure to connect FRMStart.Visible = TRUE ' The main form of your program END

5.If you want to execute a query to return everything database only write:

MODMain.$Con.Exec(“SELECT * FROM mysql.user”)

6.You can create queries with information supplied by the user, just do something like this:

PUBLIC PROCEDURE SearchName() DIM $Query AS String $Query = “SELECT * FROM Friends WHERE Name = '” & TBXName.Text & “'” MODMain.$Con.Exec($Query) END

Example 2. Dealing with results (SELECTS).

Notes:

1.You should have read the Notes and Recommendations from Example 1.
2.Gambas has a special Data Type to deal with query's results, it is called Result.

Steps:

1.Let's suppose the Table Friends has the following fields: 

       FirstName
       SecondName
       Address
       Phone

2.Store the query's result into a variable:

PUBLIC PROCEDURE SearchName() DIM $Query AS String DIM $Result AS Result DIM $Phone AS String

$Query = “SELECT * FROM Friends WHERE Name = '” & TBXName.Text & “'” $Result = MODMain.$Con.Exec($Query) $Phone = $Result!Phone Message.Info($Phone) END

3.If you want to create a printable report, you can put the query's result into a File using the html format, so you can open it using a Web Browser.

Example 3. Transactions.

Notes:

1.You should have read the Notes and Recommendations from Examples 1 and 2.

Steps:

1.Let's suppose we want to add a new record into the Table Friends:

DIM $Result AS Result

MODMain.$Con.Begin() $Result = MODMain.$Con.Create(“Friends”) $Result!FirstName = TBXName.Text $Result!SecondName = TBXName2.Text $Result!Address = TBXAddress.Text $Result!Phone = TBXPhone.Text $Result.Update]() MODMain.$Con.Commit()

2.You should be careful with the Data Types, of course you can't save a String into a Integer.