Home > Articles > Data > MySQL

Using GTK+ to Build GUI MySQL Applications—A Tabular Report

  • Print
  • + Share This
Matt Stucky, author of MySQL: Designing User Interfaces, shows you how to use two very popular GPL tools to create Linux database applications the way you may be used to with SQL Server and Visual Basic. That is, there is a "backend database," MySQL in this case, and an "Integrated Development Environment" (IDE) that creates the User Interface (UI). Finally, the programmer must write code to connect to be executed when an event is triggered.
This article is excerpted from MySQL: Designing User Interfaces, by Matt Stucky.
From the author of

Using GTK+ to Build GUI MySQL Applications—A Tabular Report

This report in Listing 1 is a simple tabular report that, for lack of a better term, will be called a "data display control." It is called a "data display control" because it queries a MySQL database table and displays the resulting rows in tabular format. It is read-only as presented here; the user cannot make changes and write them to the database in this application.

Be aware that although they are not covered here, several resources went into the creation of this application:

  • Glade, the GTK+ GUI builder, is an IDE to allow the developer to "point and click" the UI into form in a graphical manner. This follows along the same basic lines as VB or VC++.

  • The application uses the MySQL system tables, which requires that MySQL be installed. MySQL is a GPL database that is very popular in the Linux community. It is small, fast, and robust. It operates along the same lines as SQL Server or other databases in that it runs a daemon (or "service" in the Win32 world) that processes and responds to incoming requests for data.

  • Also note that GTK+ uses "widgets" instead of the term "controls" that you may be familiar with. Their function is the same.

Listing 1 ddc.c for the tabular report

#ifdef HAVE_CONFIG_H
# include <config.h>
#endif
#include <gdk/gdkkeysyms.h>
#include <gtk/gtk.h>
#include <mysql.h>
#include "callbacks.h"
#include "interface.h"
#include "support.h"
GtkWidget *create_ddc (gchar *table_name, gchar *server_name,
            gchar *user_name, gchar *user_pwd,
            gchar *db_name, GtkWidget *frm_target)
{
 MYSQL   *conx;
 GtkWidget *scrolledwindow1;
 GtkWidget *clist_table;
 GtkWidget *label;
 gint   counter;
 gint   cols = 3;
 gchar   *sql;
 MYSQL_RES *result_set;
 MYSQL_ROW db_row;
 MYSQL_FIELD *field;
 gchar   *row[20] = {"", "", "",
"", "", 
            "", "", "", "",
"",
            "", "", "", "",
"",
            "", "", "", "",
""};
 scrolledwindow1 = gtk_scrolled_window_new (NULL, NULL);
 gtk_widget_show (scrolledwindow1);
 conx = mysql_init(0L);
 if (conx == 0L) 
  {
    g_print("mysql_init failure...\n");
    return 0L;
  }
 mysql_real_connect (conx, server_name, user_name, 
           user_pwd, db_name, 0, 0L, 0);
 if (conx == 0L)
  {
    g_print("mysql_real_connect failure...\n");
    return 0L;
  }
 sql = g_strconcat("select * from ", table_name, 0L);
 g_print("sql is: %s\n", sql);
 if (mysql_query (conx, sql) != 0)
  {
   g_print("query failure...\n");
   return 0L;
  }
 
 result_set = mysql_store_result (conx);
 cols = mysql_num_fields (result_set);
 clist_table = gtk_clist_new (cols);
 gtk_object_set_data_full(GTK_OBJECT(frm_target),
"clist_table",
       clist_table, 0L);
 gtk_widget_show (clist_table);
 gtk_container_add (GTK_CONTAINER (scrolledwindow1),
clist_table);
 gtk_clist_column_titles_show (GTK_CLIST (clist_table));
 /* First iterate through the columns. */
 for (counter = 0; counter < cols; counter++)
  {
    mysql_field_seek(result_set, counter);
    field = mysql_fetch_field(result_set);
    label = gtk_label_new (field->name);
    gtk_widget_show (label);
    gtk_clist_set_column_widget (GTK_CLIST (clist_table), counter,
label);
    gtk_clist_set_column_width (GTK_CLIST (clist_table), counter,
80);
  }
 /* Next iterate through the rows. */
 while ((db_row = mysql_fetch_row (result_set)) != 0L)
   { 
    for (counter = 0; counter < cols; counter++)
     {
       row[counter] = db_row[counter];
     }
    gtk_clist_append(GTK_CLIST(clist_table), row);
   }
 mysql_close(conx);
 return scrolledwindow1;
}

Listing 2 contains the callbacks for the tabular form. Listings 1 and 2 are the most important parts of the "tabular" report presented here.

Listing 2 callbacks.c for the tabular report

#ifdef HAVE_CONFIG_H
# include <config.h>
#endif
#include <gtk/gtk.h>
#include <mysql.h>
#include "callbacks.h"
#include "interface.h"
#include "support.h"
#include "ddc.h"
GtkWidget *frm_tabular;
void
on_frm_tabular_show          (GtkWidget    *widget,
                    gpointer     user_data)
{
 gchar   *sql;
 MYSQL   *conx;
 GtkWidget *scrolledwindow1;
 GtkWidget *statusbar1;
 /* When frm_tabular opens, it needs to show the
top
  * salespeople in descending order of commissions paid.
  */
 g_print("on_frm_tabular_show.\n");
 /* First, connect to the database. */
 conx = mysql_init((MYSQL *)0L);
 conx = mysql_real_connect(conx, "localhost", 0L,
              0L, "mysql", 0, 0L, 0);
 if (conx == 0L) 
   {
     g_print("Unable to connect to database.\n");
     gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_tabular,
           "statusbar1")), 1, "Unable to Connect to
database.");
     return;
   }
 g_print("connected to mysql db.\n");
 mysql_close (conx);
 g_print("First connection closed.\n");
 sql = " db";
 gtk_widget_destroy(GTK_WIDGET(lookup_widget(frm_tabular,
        "scrolledwindow1")));
 g_print("Calling create_ddc.\n");
 scrolledwindow1 = create_ddc(sql, "localhost", 0L, 
                0L, "mysql", frm_tabular);
 g_print("Returned from create_ddc.\n");
 
 gtk_widget_ref(scrolledwindow1);
 gtk_object_set_data_full(GTK_OBJECT(frm_tabular),
        "scrolledwindow1", scrolledwindow1,
        0L);
 gtk_box_pack_start(GTK_BOX(lookup_widget(frm_tabular,
        "vbox1")), scrolledwindow1, TRUE, TRUE, 0);
 gtk_widget_show(scrolledwindow1);
 /* Unfortunately, the packing box widgets don't have
any
  * way to insert a child widget at a certain position;
it
  * can be inserted only at the start and end. Therefore,
  * destroy the statusbar widget
  * created in Glade and create a new one.
  *
  * Remember, however, that the statusbar was needed
prior
  * to this point to communicate with the user.
  */
 gtk_widget_destroy(GTK_WIDGET(lookup_widget(frm_tabular,
     "statusbar1")));
 statusbar1 = gtk_statusbar_new();
 gtk_box_pack_start(GTK_BOX(lookup_widget(frm_tabular,
        "vbox1")), statusbar1, FALSE, FALSE, 0);
 gtk_widget_show(statusbar1);
 gtk_statusbar_push(GTK_STATUSBAR(lookup_widget(frm_tabular,
           "statusbar1")), 1, "Done.");
}
gboolean
on_frm_tabular_delete_event      (GtkWidget    *widget,
                    GdkEvent    *event,
                    gpointer     user_data)
{
 g_print("on_frm_delete_event.\n");
 
 gtk_main_quit();
 return FALSE;
}

Figure 1 shows the finished tabular report running in its own process space.

Figure 1 The tabular report is a very rough "grid control."

Listing 3 is the Glade-generated file for the tabular executable. This is the output from Glade that will be compiled to create the main form of the application; the create_frm_tabular() function is the only one in the file interface.c (in this case). Note that interface.c is only one of the files output by Glade; the rest are presented in later listings.

Listing 3 interface.c for the tabular executable

/ * DO NOT EDIT THIS FILE - it is generated by Glade.
 */
#ifdef HAVE_CONFIG_H
# include <config.h>
#endif
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <string.h>
#include <gdk/gdkkeysyms.h>
#include <gtk/gtk.h>
#include "callbacks.h"
#include "interface.h"
#include "support.h"
GtkWidget*
create_frm_tabular (void)
{
 GtkWidget *frm_tabular;
 GtkWidget *vbox1;
 GtkWidget *scrolledwindow1;
 GtkWidget *clist1;
 GtkWidget *label1;
 GtkWidget *label2;
 GtkWidget *label3;
 GtkWidget *statusbar1;
 frm_tabular = gtk_window_new (GTK_WINDOW_TOPLEVEL);
 gtk_object_set_data (GTK_OBJECT (frm_tabular), "frm_tabular",
frm_tabular);
 gtk_window_set_title (GTK_WINDOW (frm_tabular), "Top Commission
Earners");
 gtk_window_set_default_size (GTK_WINDOW (frm_tabular), 280,
500);
 vbox1 = gtk_vbox_new (FALSE, 0);
 gtk_widget_ref (vbox1);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular), "vbox1",
vbox1,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (vbox1);
 gtk_container_add (GTK_CONTAINER (frm_tabular), vbox1);
 scrolledwindow1 = gtk_scrolled_window_new (NULL, NULL);
 gtk_widget_ref (scrolledwindow1);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular),
"scrolledwindow1", scrolledwindow1,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (scrolledwindow1);
 gtk_box_pack_start (GTK_BOX (vbox1), scrolledwindow1, TRUE, TRUE,
0);
 clist1 = gtk_clist_new (3);
 gtk_widget_ref (clist1);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular), "clist1",
clist1,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (clist1);
 gtk_container_add (GTK_CONTAINER (scrolledwindow1), clist1);
 gtk_clist_set_column_width (GTK_CLIST (clist1), 0, 80);
 gtk_clist_set_column_width (GTK_CLIST (clist1), 1, 80);
 gtk_clist_set_column_width (GTK_CLIST (clist1), 2, 80);
 gtk_clist_column_titles_show (GTK_CLIST (clist1));
 label1 = gtk_label_new ("label1");
 gtk_widget_ref (label1);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular), "label1",
label1,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (label1);
 gtk_clist_set_column_widget (GTK_CLIST (clist1), 0, label1);
 label2 = gtk_label_new ("label2");
 gtk_widget_ref (label2);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular), "label2",
label2,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (label2);
 gtk_clist_set_column_widget (GTK_CLIST (clist1), 1, label2);
 label3 = gtk_label_new ("label3");
 gtk_widget_ref (label3);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular), "label3",
label3,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (label3);
 gtk_clist_set_column_widget (GTK_CLIST (clist1), 2, label3);
 statusbar1 = gtk_statusbar_new ();
 gtk_widget_ref (statusbar1);
 gtk_object_set_data_full (GTK_OBJECT (frm_tabular),
"statusbar1", statusbar1,
              (GtkDestroyNotify) gtk_widget_unref);
 gtk_widget_show (statusbar1);
 gtk_box_pack_start (GTK_BOX (vbox1), statusbar1, FALSE, FALSE,
0);
 gtk_signal_connect (GTK_OBJECT (frm_tabular), "show",
           GTK_SIGNAL_FUNC (on_frm_tabular_show),
           NULL);
 gtk_signal_connect (GTK_OBJECT (frm_tabular),
"delete_event",
           GTK_SIGNAL_FUNC (on_frm_tabular_delete_event),
           NULL);
 return frm_tabular;
}

Listing 4 is the support.c file, again as output by Glade. Glade generates a larger file with other utility functions, but this file has been reduced to a minimum to present only the function necessary to this demonstration. The lookup_widget() function is required to find a "child" widget (such as a listbox or command button) inside a "parent" widget (such as a window or "form").

Listing 4 support.c

/* DO NOT EDIT THIS FILE - it is generated by Glade.
 */
#ifdef HAVE_CONFIG_H
# include <config.h>
#endif
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <string.h>
#include <gtk/gtk.h>
#include "support.h"
GtkWidget*
lookup_widget             (GtkWidget    *widget,
                    const gchar   *widget_name)
{
 GtkWidget *parent, *found_widget;
 for (;;)
  {
   if (GTK_IS_MENU (widget))
    parent = gtk_menu_get_attach_widget (GTK_MENU (widget));
   else
    parent = widget->parent;
   if (parent == NULL)
    break;
   widget = parent;
  }
 found_widget = (GtkWidget*) gtk_object_get_data (GTK_OBJECT
(widget),
                          widget_name);
 if (!found_widget)
  g_warning ("Widget not found: %s", widget_name);
 return found_widget;
}

Listing 5 is the main.c file for this demonstration. It is the starting point for the code. Note that it essentially has one function: to call create_frm_tabular() and display the resulting widget, which in this case happens to be a window. Because only one window widget was created in Glade, Glade automatically added that window to main(), hence the comments about "having something to show."

Listing 5 main.c

/*Initial main.c file generated by Glade. Edit as
required.
 * Glade will not overwrite this file.
 */
#ifdef HAVE_CONFIG_H
# include <config.h>
#endif
#include <gtk/gtk.h>
#include "interface.h"
#include "support.h"
GtkWidget *frm_tabular;
int
main (int argc, char *argv[])
{
 gtk_set_locale ();
 gtk_init (&argc, &argv);
 /*The following code was added by Glade to create one of each

  * component (except popup menus), just so you see something

  * after building the project. Delete any components you
don't
  * want shown initially. 
  */
 frm_tabular = create_frm_tabular ();
 gtk_widget_show (frm_tabular);
 gtk_main ();
 return 0;
}

Listing 6 is the command needed to compile the application. It will of course produce an executable with the name "a.out" by default. The quotes around the gtk-config... are actually back-tick characters; the back-tick character normally shares the key with the tilde (~) character and is generally located at the top left of the keyboard. The -I is the directory for include files; the -L is the location of the library files specified by the -l options. gcc is the GNU C compiler that comes standard with every Linux distribution (you may not have installed it, however). The –Wall flag tells gcc to output "all warnings."

Listing 6 The command line to compile the application

gcc -Wall *.c ´gtk-config --cflags --libs´ -I/usr/include/mysql

-L/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lz

Figure 2 shows the same control, displaying data from a different database on the author's system; the data shown in Figure 2 is from one of the examples in MySQL: Building User Interfaces (see the end of this article for more information).

Figure 2 The same "data display control," displaying salesperson ranking data.

My book, MySQL: Building User Interfaces, is an attempt to show that developers with GUI toolkit and database experience can make the jump to MySQL, GTK+, and Glade with minimal effort. (Most of my experience is with VB and SQL Server.)

Additionally, experienced developers will appreciate two things I cover in the book that are not demonstrated in this article:

  • First, how to use the same source code to compile for either Linux or Windows.

  • Second, using XML and glade.h to build the user interface at run time rather than compile time, still using C.

I hope you have found this article informative and that you find my book equally so.

About This Article

This article is excerpted from MySQL: Designing User Interfaces by Matt Stucky (New Riders Publishing, 2001, ISBN 073571049X). Refer to Chapter 12, "Management Reporting Construction," for more detailed information on the material covered in this article.

  • + Share This
  • 🔖 Save To Your Account