Example 1. Create a new connection (MySQL).

Notes:

1.You should have already installed the gb.db.mysql Component and all MySQL related packages.
2.This is not an example about MySQL statements.
3.The privileges in a database a granted by MySQL not for Gambas.
4.The gb.db component have to by selected for your program could access the DataBase Engine.

Recommendations:

1.The database connection of your program should be placed in a Module, so it could be called from anywhere.
2.The gambas-database-manager is great!, but if you want to create a good database in MySQL you should use the MySQL console, it will prevent some problems dealing with Data Types.

Steps:

1.Create a new Module for you program, I'm going to use MODMain as the name for my Module.

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 = "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 data base 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 in the 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, or course you can't save a String into a Integer.