Example 1. Create a new connection (MySQL).
1.You should have already installed the gb.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.
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 MySQLyou should use the MySQL console, it will prevent some problems dealing with Data Types.
1.Create a new Module for you program, I’m going to use MODMainas 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 = "user" ' Name of the data base we want to use $Con.Password = "password" ' 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).
1.Let’s suppose the TableFriends 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
Example 3. Transactions.
1.You should have read the Notes and Recommendations from Examples 1 and 2.
1.Let’s suppose we want to add a new record into the TableFriends:
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()