<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="../../style.css">
<title>
Gambas Documentation - How To Open a MySQL connection and use it
</title>
</head>
<div class="path">&nbsp;<a href="../help.html"><img class="flag" valign="middle" src="../../img/lang/en.png" alt="Home"></a>&nbsp;/&nbsp;<a href="../howto.html">howto</a>&nbsp;/&nbsp;database&nbsp;</div>
<div>
<div class="title">
How To Open a MySQL connection and use it
</div>
<h2>Example 1. Create a new connection (MySQL).</h2>
<p>
<h3>Notes:</h3>
1.You should have already installed the gb.db.mysql <a href="../comp/gb/component.html">Component</a> and all MySQL related packages.<br>
2.This is not an example about MySQL statements.<br>
3.The privileges in a database a granted by MySQL not for <a href="../def/gambas.html">Gambas</a>.<br>
4.The <a href="../comp/gb+db.html">gb.db</a> <a href="../def/component.html">component</a> have to by selected for your program could access the DataBase Engine.<br>
<p>
<h3>Recommendations:</h3>
1.The database connection of your program should be placed in a Module, so it could be called from anywhere.<br>
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.
<p>
<h3>Steps:</h3>
1.Create a new Module for you program, I'm going to use <b>MODMain</b> as the name for my Module.<br>
<p>
2.Create a new Variable for the connection:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PUBLIC $Con AS NEW Connection<br>
<p>
3.Now create a Procedure to make the connection:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PUBLIC PROCEDURE Connect()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Close()              ' Close the connection<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Type = &quot;mysql&quot;       ' Type of connection<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Host = &quot;localhost&quot;   ' Name of the server<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Login = &quot;root&quot;       ' User's name for the connection<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Port = &quot;3306&quot;        ' Port to use in the connection, usually 3306<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Name = &quot;Sophia&quot;      ' Name of the data base we want to use<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Password = &quot;root123&quot; ' User's password<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Con.Open()               ' Open the connection<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END<br>
<p>
4.Now create a Procedure to start the program:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PUBLIC SUB Main()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Connect() ' Run the Procedure to connect<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FRMStart.Visible = TRUE ' The main form of your program<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END<br>
<p>
5.If you want to execute a query in the database only write:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MODMain.$Con.Exec(“SELECT * FROM mysql.user”)<br>
<p>
6.You can create queries with information supplied by the user, just do something like this:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PUBLIC PROCEDURE SearchName()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIM $Query AS String<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Query = “SELECT * FROM Friends WHERE Name = '” & TBXName.Text & “'”<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MODMain.$Con.Exec($Query)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END<br>
<p>
<hr>
<p>
<h2>Example 2. Dealing with results (SELECTS).</h2>
<p>
<h3>Notes:</h3>
1.You should have red the Notes and Recommendations from Example 1.<br>
2.<a href="../def/gambas.html">Gambas</a> has a special Data Type to deal with query's results, it is called <a href="../comp/gb+db/result.html">Result</a>.<br>
<p>
<h3>Steps:</h3>
1.Let's suppose the <a href="../comp/gb+db/table.html">Table</a> Friends has the following fields:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FirstName<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SecondName<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Address<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Phone<br>
<p>
2.Store the query's result into a Variable:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PUBLIC PROCEDURE SearchName()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIM $Query AS String<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIM $Result AS Result<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIM $Phone AS String<br>
<p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Query = “SELECT * FROM Friends WHERE Name = '” & TBXName.Text & “'”<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result = MODMain.$Con.Exec($Query)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Phone = $Result!Phone<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Message.Info($Phone)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END<br>
<p>
3.If you want to create a printable report, you can put the query's result into a <a href="../comp/gb/file.html">File</a> using the <a href="../comp/gb+web/html.html">HTML</a> format, so you can open it using a Web Browser.<br>
<p>
<hr>
<p>
<h2>Example 3. Transactions.</h2>
<p>
<h3>Notes:</h3>
1.You should have red the Notes and Recommendations from Examples 1 and 2.<br>
<p>
<h3>Steps:</h3>
1.Let's suppose we want to add a new record into the <a href="../comp/gb+db/table.html">Table</a> Friends:<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIM $Result AS Result<br>
<p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MODMain.$Con.Begin()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result = MODMain.$Con.Create(“Friends”)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result!FirstName = TBXName.Text<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result!SecondName = TBXName2.Text<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result!Address = TBXAddress.Text<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result!Phone = TBXPhone.Text<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$Result.Update]()<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MODMain.$Con.Commit()<br>
<p>
2.You should be careful with the Data Types, or course you can't save a <a href="../comp/gb/string.html">String</a> into a <a href="../lang/type/integer.html">Integer</a>.<br>

</div>
</body>
</html>

