How To Open a MySQL connection and use it

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.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 MySQLyou 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 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).

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 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

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 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()

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s