Gambas speeds database development

Gambas, designed by Beno�t Minisini, is a new graphical development environment for Linux that aims to improve database development times.

Gambas installation is straightforward — simply download the tar file from the Web site and use tar xzvf to unpack the required directories and files, then follow the instructions in the README file.

Once the software is installed, any developers who have dipped into Windows will immediately recognize the look and feel — it’s very like Visual Basic, and that’s not a coincidence. Gambas is by no means a Visual Basic clone for Linux, but it does take many of the better features to provide a true rapid development environment. It has the standard components — a project window, a toolbox, etc. — and it takes very little effort for even a brand new programmer to build a form.

You can move between form design and coding by clicking on a form in the project window or its associated class file (in which the code is stored). A second way is to double-click on an object on the form (such as a button) or on the form itself. Doing so opens the default function of the object. For instance, the default method of a form is Form_Load. It is here that we would place any code that should run as soon as the form is first opened. For instance, a method to load a set of data into a combo box would be placed here to ensure that it will be loaded immediately:

PUBLIC SUB Form_Open() load_combo END

The subroutine to load the combo box could be as simple as:

PRIVATE SUB load_combo() combobox1.Add("Home Address") combobox1.Add("Office Address") combobox1.Add("Postal Address") END

As you enter the code the application informs you about the inputs that each method expects, and helps you by giving you the possible methods and properties of objects. It also automatically adds required words, such as the END statement. You can see your end result by clicking on the green triangle in the project window.

Grids are just as easy to handle, but have no Add method. Instead, the numbers of rows and columns have to be define, then the grid must be written to as if it were a multidimensional array. For instance, the following code sets the grid to be two columns wide by three high and writes data to the top left cell:

gridview1.columns.count=2 gridview1.rows.count=3 gridview1[0,0]="Hello World"

The number of rows and columns can set in Form_Load or they can be varied from within the code, as required. For instance, the code for a method to add an extra row to the grid and to write to the first column of the new row would be:

PRIVATE SUB update_grid (newData AS String) gridview1.rows.count=gridview1.rows.count+1 gridview1[gridview1.rows.count-1,0]=newData END

The advantage of putting this code into a method is that you can call it from other subroutines. For example, you could load the grid with the contents of a text box by the click of a button:

PUBLIC SUB Button1_Click() update_grid(textbox1.Text) END

In the combo box above we’ve hardcoded the contents into the program, but this, of course, is not a good idea. It is much more effective to store such lists in a database. Why? Imagine if we have to add something to the list. If the list is in a central database it can be modified easily, so that when a user logs on, the new list is immediately available. If not, users have to wait for you to recompile a new version of your software.

Gambas supports MySQL and PostgreSQL. Data loads need to take place in two stages — first, the creation of the data structure, and second, the loading of any default data.

The first job is to create the database. You can log onto the default database as root and create the new database, but instead I recommend creating a file (e.g. schema.txt) containing the correct commands. This approach provides a record of what you’ve done. The following example is suitable for MySQL:

/*Start of file*/ drop database if exists customer_details; /*Create the database*/ create database if not exists customer_details; /*Create the data structure*/ create table if not exists customer_details.address_type ( id int auto_increment, title varchar(255), primary key (id) ); /*Load default data*/ insert into customer_details.address_type (title) values ('Home Address'); insert into customer_details.address_type (title) values ('Office Address'); insert into customer_details.address_type (title) values ('Postal Address'); /*Create user accounts*/ GRANT select,insert,delete,update ON customer_details.* TO bainm@localhost IDENTIFIED BY 'mypassword'; /*End of file*/

Assuming that you’re in the right directory, you can now create the database by typing the following in a console window:

cat schema.txt | mysql -uroot -p<the right password> mysql

With the database in place you can read from it and write to it through the form as soon as you add the components that will allow the application to communicate with a
database. To do this, go to the project window and click on the Project menu and then Properties. Go to the Components tab, tick gb.db, and press OK.

You now need to connect to the database from within the code. To do that, you need a global variable that will represent the actual connection object, and a function to set up the connection. You’ll also need to rewrite Load_Combo to obtain its data from the database.

PRIVATE conn AS NEW Connection PUBLIC SUB Form_Open() IF make_connection()=TRUE THEN load_combo END IF END PRIVATE FUNCTION make_connection() AS Boolean WITH conn .Type = "mysql" .Host = "localhost" .Login = "bainm" .Password = "mypassword" .Name = "customer_details" TRY conn.Open IF ERROR THEN Message ("Cannot Open Database. Error = " & Error.Text) RETURN FALSE END IF RETURN TRUE END PRIVATE SUB load_combo() DIM res AS Result DIM sql AS String sql="select title from address_type" res= conn.Exec(sql) FOR EACH res ComboBox1.Add (res!title) NEXT END

In the code above we created a connection object at a global level that is available to all of the methods. Form_Load runs a function that tries to connect to the database and returns true or false according to whether or not it was successful. Load_combo now runs a query using a SQL statement on the database, returning a set of results that can be loaded into the combo box.

In the example above the user details are hardcoded, but this is not necessarily a good idea. Instead it might be better to request these from the user through a second form. It really depends on the levels of security that you require.

Writing to the the database is as easy as reading from it. Simply build either an update or insert SQL statement and send it to the Exec method of the connection. For example, a subroutine to add an extra item to the list could be:

PRIVATE SUB addToList (newItem AS String) DIM res AS Result DIM sql AS String sql="insert into address_type (title) values ('" & newItem & "')" res=conn.Exec(sql) END

Equally easy is a subroutine to change the text of an item in the list:

PRIVATE SUB changeListItem (newText AS String,oldText AS String) DIM res AS Result DIM sql AS String sql="update address_type set title='" & newText & "' where title ='" & oldText & "'" res=conn.Exec(sql) END

As with the update_grid method, these subroutines can be used in conjunction with buttons and textboxes to make them completely interactive.

Gambas still has a number of areas in which it needs to be developed. For instance, it is not yet multiplatform — it works only on Linux at the moment. However, the simplicity of its GUI development and database access are both impressive. It is worth considering Gambas the next time that you need a new application.