You are in Home page » Products » Opensource » WebGUI » SQLEdit W(eb)Object

Soluzione integrata di telefonia su internet a banda larga.
Search on this site:  

Are you interested to be updated about all things happens to me day by day?
Visit my new blog

SQLEdit: a WebGUI W(eb)object.

Current version is: 06.03.03/2005-01-25 (beta)

Announce: I received many requests about a new version of SQLEdit for latest version of WebGUI that is a conversion of this Wobject in the new format of WebGUI objects: Assets

This module has been developed in my free time and although this page have, at the bottom, a modulo to make a voluntary donation to support this project, I received nothing. Unfortunately I have, in the near future, no free time and so I must to consider this update request as a fund-a-feature.

So, when I reach, starting from today May, 2 2005, the amount of at least 100 EUROS of donation for this project I'll begin the conversion of this module to WebGUI 6.5.x.

Do you need of this module for WebGUI 6.5.x? Donate 5 €, 10 €, 20 €, 100 € and send me your e-mail. When I reach 100 EUROS of donation, you will be notify of the start and release of a new version of SQLEdit for WebGUI. Help me to help you.

When I saw WebGUI for the first time I was very impressed about its simplicity to create website. When I looked at the programming mode under WebGUI, I made pleasantly an impression of the simplicity of building of WebGUI plugins, also called Wobject.

To that time, I was looking for a content managment system to give to inexperienced users that have necessity to manage a complex website. I found that WebGUI was the correct solution. This website must have the necessity to access some database tables and WebGUI replies me with SQLReport Wobject.

The problem raised when website manager asked me for having the possibility to modify database records. WebGUI doesn't offer this feature, and so, I have had built it.

What's SQLEdit?

SQLEdit is a W(eb)object for WebGUI ( one of the most popular open source content management systems (CMS). Currently supports WebGUI release 5.5, 6.0, 6.1 and 6.2 under Linux and Windows. SQLEdit fully supported MySQL database.

Hot news: Starting from 06.03.01, SQLEdit supports Microsoft® SQL Server database. This support is currently in beta and may be some bugs or unsupported features. As an example, binary data via upload, shouldn't work.

Microsoft® SQL Server support has been tested with these DBD drivers:

  • DBD:Sybase via freeTDS under Linux
    Example DSN:
    with these entries in freetds.conf
        host = your_server
        port = 1433
        tds version = 8.0

  • DBD:ODBC using system DSN under Windows
    Example DSN:

  • DBD:ADO using standard autentication
    Example DSN:
       dbi:ADO:Provider=sqloledb;Data Source=(local);Initial Catalog=sqledit;

All these tests have been made with Microsoft® SQL Server 2000 under Windows 2000.

This module allows to build a web form to add, to edit and to delete data from database tables using the WebGUI philosophy to generate it.

By giving the database link property (DSN,username and password) and a table name, this module builds a web form with all table columns by trying to set the best field type. As an example, if a column is of type "date", a field with a calendar button and javascript to validate date input in this field, will be placed on form, if a column is an auto_incremental column, i.e. automatically its numerical value increases when new records is added, a visible and not writable field will be placed on web form.

However, properties of web fields can be changed by content manager so, e.g., you can change a text field into a select list field to force users to enter data selecting it from some possible values.

Layout of web form generated by this module is personalized because it uses templates, following WebGUI philosophy.

Key features

With SQLEdit Wobject you can

  • Easy create database web forms.
  • Insert, edit and delete records from a database table.
  • Upload files into BLOB fields
  • Modify and delete records from one-to-one or one-to-many related tables with foreing keys.
  • Personalized layout of every columns table, setting visibility and HTML Form field module type.
  • Check and restriction for possible values of fields assignable by users
  • Notification, via e-mail, of changes in the database records.

How to install.

Take a look at docs/SQLEdit/install.txt file for detailed installation instructions. Remember that to use this module (and SQL Report standard module too) it's necessary to be an user with UId Level 9 (guru) otherwhise this module doesn't show in the available modules.

How to use.

Let's show how to use SQLEdit with some examples.

First of all, create a new table in WebGUI database. We can use tables in whatever database, but now, for semplicity, we use the same database where are WebGUI tables.

Execute this from a shell console to create a new table called test_anagr to WebGUI database:

	root@devel# mysql -p WebGUI
	mysql> CREATE TABLE `test_anagr` (
		`name` VARCHAR( 255 ) NOT NULL ,
		`birthday` DATE NOT NULL ,
		`sex` ENUM( 'M', 'F' ) NOT NULL ,
		PRIMARY KEY ( `id` )

Now open WebGUI site in administrative mode and add a new page, call it test and set "Cache Timeout" and "Cache Timeout (Visitors)" property to 0. Add it an SQL Report.

Now add this string in the description field (if you are using Internet Explorer enter in code-mode)

and enter this query in query field

Now, switch to layout tab.

If you have WebGui 5.x, enter this code directly in the report template field:

	<table border=1>
	<td><a href="test_edit?exec=edit&id=^0;">Edit</td>
	<td><a href="test_edit?exec=delete&id=^0;">Delete</td>

If you have WebGUI 6.x, you must create a new template. Click on "manage" button of the template, copy default SQLReport template and named "SQLReport test_anagr" the new template. Edit this template so that the <tmpl_loop rows_loop> is as:

	<tmpl_loop rows_loop>
	<tmpl_loop row.field_loop>
	<td class="tableData"><tmpl_var field.value></td>
	<td><a href="test_edit?exec=edit&id=<tmpl_var>">Edit</td>
	<td><a href="test_edit?exec=delete&id=<tmpl_var>">Delete</td>

Use this new template as the template for our SQLReport element.

Obviously in every case, the edit and delete link must be adapted to your table and they must be such to having all primary keys with their relative values of the record you wish to edit or to delete.

Switch to privileges and enter DSN,username, password to WebGUI database or select it from database link field, if available.

After save you should have something like this:

Untill now, nothing strange, we have create an SQLReport that lists all "id" and "name" columns presents in "test_anagr" table.

It's now time to create test_edit.

Add a new page to WebGUI and call it test_edit, set "Cache Timeout" and "Cache Timeout (Visitors)" property to 0. Now add an SQLEdit Wobject to this page. Let's go directly to the priviliges tabs that looks like this:

Here select the same database connection insert in SQLReport. As you can see, there is another field: "table name". Here you must set the name of table that you with to edit. You can type it now or you can select it from a list of database tables if you have a correct DSN database link. Enter only the correct database link, save it and then edit again the Wobject, you can see this list.

select test_anagr and press save (module can warn you that it's required to reload database schema. Press Yes to reload it). If you see this....the module works :-)

If you come back to "test" page and try to add a new record, edit and delete records you can see that all works fine.

Add a new record:

To "save and come back" you must enter the referer page in the Wobject property tab. Set it to the page that have SQLReport

Save and come back:

now edit this record

and we can delete it from this page or from previous page

If you "save and continue to edit" you continue to edit current record after save it. If you "save and come back" you come back to test page after save current record. If you "save and add new" you save current record and go to a new page for adding a new record.

As you can see SQLEdit try to understand fields type from database schema. Let's show how to change this. Press the edit button at left of sex field. We can give more meaning to "M" and "F" database keys in this way:

As you can see, you can choose in which kind of order, fields possible values will be listed: preserving input order, ordered by key or by values.

We can, if you wish, change fields type and label:

You can also use macros to set possible values and default value for fields. To set possible values is important that result macro returns values in format "key => value\n" or "key\n".

As an example you can configure a field like this (using some programmer macros)

and having, as result, something like this:

Macros can be used also in "default value" field. In the example above, the selected item will depend from your login name.

Note that the newline before the end of SQL macro is very important because, with it, the result of the macro will be

1 => Visitor
3 => Admin


You can also "lookup"an external database using my SQLExt WebGui macro.

For every field it is moreover possible to set up several check or restriction for the possible values assignable by users. It's possible, as an example, to assign the minimal and/or maximum number of characters, the range of valid characters, the range of valid values. All this with an easy configuration page.

In the manual you can find how i can create one-to-one or one-to-many relation tables editing/deleting records.

To DO.

  • Permit to send a different email when new records have been added or records are updated or deleted
  • Binary uload file on Microsoft® SQL Server
  • "Come back" feature doesn't work on Windows
  • PostgreSQL database support

Make a donation

This free software has been developed during my spare time, and therefore, during some nights lost to develop this for you instead sleeping. If you found this product useful, feel free to send me a donation. You will be remember in my prayers and you will help my soul to feel less alone during long the long nights of coding. Of course, if you make a donation and have a feature request, I'll try to place your feature with a high priority.

You can fast and free donate using a credit card, bank account, or existing Paypal balance.  After you push the button, you'll be taken to Paypal's secure website, where you'll supply a username and password (if you already have a Paypal account) or you'll sign up for an account (it's easy). You get to choose the amount you'd like to donate.  Once you've authorized the transaction, you're done. Paypal does take a cut of what you send (so if you send me $20, I get $19.12; if you send $5, I actually get $4.55; if you send me $3, I get $2.61).

JavaScript Menu Courtesy of

 Copyright© 1997-2006 Emiliano Bruni Online from 16/08/1998 with visitors Write me to: