/ howto / database
How To Open a MySQL connection and use it

## 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 red 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 red 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.