SQLScreens: a simple SQL screen generator

A typical SQLScreens application will have a number of screens, each with several entry/display fields. Each screen will be linked to one or several database tables, each field to a column.

You create each screen by listing the column names that you want to use.

If you do not even list the column names, all columns are used, so that creating a screen to look at a table is 5 lines of TCL code. There is a sample program in the package to do just this: give the database and table name and up comes the screen (tablescreen.tcl).

Once the screens are created, you can query, insert, update, and delete records by entering data in the fields and clicking on the appropriate button (or using a keyboard shortcut).

SQLScreens provides an easy method to link the screens so that a change in one screen will trigger a query in another one (for master-detail relationships), or so that it will just update the join column.

You can also:

As all values for the fields are stored in an accessible TCL array, it is quite easy to add code for data validation or to show computed fields. There are provisions in the package for calling external routines before and after the database operations.

SQLScreens can be used to build standalone database access applications, or to embed a database-access screen in another application. For example, in CDKIT, we managed a big musical database. We used SQLScreens mainly for data-entry screens, but we also embedded it in the audio-acquisition application, to establish the link between the database and the audio files.

There are many other bells and whistles, but also a few drawbacks:

This said, the software is free and we are open to suggestions to improve it.

If you want a quick idea of what it does, do the installation, have a look at the tablescreen.tcl file in the samples directory, set the host and user name (depending on the backend type), and point it to any table, like:

tablescreen.tcl dbname tablename
      

This will create a screen with fields for all columns in the table (you may need to adjust the host and user names in the script or the environment to get the right permissions).

To use SQLScreens, you will need a number of external software packages:

In order to access the databases, the basic TCL interpreter must be augmented with a database access module:

The SQLScreens download page has pointers or copies for some of these elements.

Only the direct MySQL and SQLite backends have been tested lately, and there may be minor problems with the others.

The package comes as a gzipped tar file named something like sqlscreens-X.Y.Z.tar.gz.

Unpack the file:

gunzip < sqlscreens-X.Y.Z.tar.gz | tar xvf -

This will create a top directory named sqlscreens-X.Y.Z.

X is the major release number. Y is the minor release. Z is the bug fix release number. Don't make too much of it...

First, if needed, compile and install TCL and TK (untar; cd tcl8.../unix; configure; make; make install, same for tk).

The next step is to add database-access capability to the standard TCL/TK wish interpreter. This can be done in several ways:

MySQL support is managed by the Makefile in the SQLScreens directory. You can disable MySQL support (and the need to install the client library) by using option --disable-mysql to the configure script.

For SQLite, just install the SQLite TCL package (which may be named something like libsqlite3-tcl).

For ODBC, you should first install the driver manager, the driver(s) you need and the tclodbc TCL extension. Follow the instructions in each package.

For using isqltcl and INFORMIX, follow the installation instructions inside the isqltcl package to generate the interpreter.

The SQLDBTYPE environment variable decides what database code is going to be used. It has several possible values:

The default if the variable is not set is to use MySQL. Don't depend on it.

This variable can be set inside the script, before the first call to sqlscreen. (It is set to MYSQL inside the sample scripts). Of course, the value of this variable must be consistent with what extensions are available to the TCL interpreter (See installation).

The SQLSCLOG environment variable can be set to the name of a file where SQLScreens will log the SQL statements it executes. The default is to log to stdout.

The SQLSCHOST, SQLSCUSER, and SQLSCPASSWORD variables can be used to set the connection parameters. These are only used by the sample scripts, not the core package.

MYSQL_TCP_PORT should be used if you need to change the default MySQL connection port.

Each screen has a set of buttons to perform the following operations:

The Update and Add buttons may not exist on all screens (some screens may be set up only for querying).

Delete is not created by default (See allowdelete ).

The SQL generation section describes how the SQL statements are generated for the different actions.

Often, when doing data entry, it is inconvenient to have to reach for the mouse to perform an action. A set of keyboard shortcuts is provided to make things smoother:

The Update and Add shortcuts will have no effects in query only screens. There is no Delete shortcut.

The '<' and '>' characters will be interpreted when entered as the first character in a non-string field. Actually, if such a field begins with '<', '>', or '=', whatever is entered in it will be included in the WHERE clause when querying. For example:

If you enter >10 in a field named quantity, a quantity > 10 condition will be inserted in the WHERE clause. You could also enter >10 AND quantity < 20, or whatever condition you need. (See also the paragraph about SQL generation ).

All exchanges between the package and the user application are made through TCL arrays that hold all data and parameters. There is one such array for every screen. The array name is not significant except that it will be used for the screen title. We often use the table name, but this is not mandatory.

The basic idea is that you set values in the array and then call sqlscreen arrayname to create the screen.

The application can be reduced to a main program to initialize and call SQLScreens, or it may more complex and use SQLScreens as a utility module.

The array entries define what tables/columns will be used, how the screen will look like, etc... A minimal program to display a default query/entry screen for table mytable in database test on the local host might look like the following:

#!/usr/local/bin/wish8.4

package require sqlsc

set mytable(window)   .t
set mytable(database) test
set mytable(table)    mytable
sqlscreen mytable

Many more attributes and options can be set in the array. You could also define callback functions which will be called before and after the database accesses, to give you an opportunity for checking what's happening, possibly modify values, or block the operation if something is wrong.

In the following, we shall use the example of a database named “orderdb”, with a table named “customers”, with columns named “custid”, “custname”, and “custfirstname”, and a table named “orders” with “orderid” and “ordercustid”.

SQLScreens stores the values for the field corresponding to a column as arrayname(sqlsc_column_value) (Ex: customer(sqlsc_custid_value)). This makes collisions of other entries with your column names unlikely. You can access these variables to retrieve values into your application, and also to modify them (before an insert for example, if the user input needs processing, or if some values are automatically generated by the application).

The first release used to store the values as arrayname(column). If you have written code based on this, I would suggest that you modify it. If you do not want or can not, you can set the global variable “sqlsc_names_compat_old” to 1 before the first call to get a compatible behaviour (this will go away in the near future).

The following paragraphs describe the function of the different array entries, beginning with the most basic and frequently used, then the different callback functions that you can use.

I am sorry for the many naming inconsistencies (like using or not the sqlsc prefix for array entries), this came over time and would just be too much work to change.

When you are finished with the screen, you can call

sqlscreendelete arrayname

to cleanup and release all resources (array, windows, database connections). Most applications will exit instead. sqlscreendelete is mostly useful in case you want to recreate the screen with different options (most options can't be changed once a screen is created).

It is possible to link two screens so that a change in one screen will update the other one. This can be done in two ways.

sqlmasterslave customer custid order ordercust
        

sqlmasterslave customer custid order ordercust
sqlmasterslave order ordercust customer custid 
        

sqlslavemaster customer custid order ordercust
        

Each screen in an application normally has a set of control buttons. It is sometimes useful to use only one set of buttons for several screens. This is done with the sqcommonbuttons routine. sqcommonbutons will create a TK frame with a set of control buttons inside. This set of buttons will not be linked to a particular screen, but will apply to the screen which has the current keyboard focus. Example:

sqcommonbuttons  .f1.buttons
      

Will create the .f1.buttons frame and buttons inside there.

It is possible to create several sets of buttons (useful when there are several top level frames in the application) by calling sqcommonbuttons several times. Any of these sets will control the screen which currently has the keyboard focus.

In practice, this facility has not proved very useful because it is to easy to make mistake about where the current keyboard focus actually is.

It would be quite easy to use completely custom buttons for an application by setting the “usecommonbuttons” variable, and creating custom buttons with appropriate callbacks (look at the sqcommonbuttons code in sqlscreens.tcl).

The following callback routine names can be defined in the array:

Example:

set customer(beforeinsert) checkcustfields
      

The different routines will be invoked in the following manner:

For beforexxx routines:

routinename optype arrayname
      

For afterxxx routines:

routinename optype txt arrayname
      

Where optype defines what's happening (like beforeinsert, afternext, etc...), arrayname is the affected screen's array name, and txt is the SQL text for afterxxx routines. We can't pass the text to the beforexxx routines, because they may be responsible to modify some field values that will affect the statement !

beforeinsert, afterinsert, beforeupdate, afterupdate, beforedelete and afterdelete will be called before and after inserting or updating data.

beforequery and afterquery will be called before and after doing a select, and afterquery will also be called after the user fetches the next record, rewinds the query, or resets the screen.

If one of the beforexxx routines returns anything but 0 , the operation will be canceled (not run).

In some cases it may be useful to start a database operation by a program call (as opposed to a button press by the user). This is easily feasible by calling the following routines. They all take the array name as sole argument, and use the values that are currently stored/displayed in the screen.

In all cases, the effect will be exactly the same as the corresponding button press.

The Query button generate a SELECT statement. The list of columns comes from the columns entry in the input array (all the columns by default).

The WHERE clause is built from all the fields that hold data (including the hidden ones if there are any).

For non character columns, the comparison operator used is =. For character columns, it is LIKE.

Example: for a screen with custid, custname, custfirstname, custsomenumber fields, where data was entered in custname (xxx) and custsomenumber (yyy), the statement would be:

SELECT custid,custname,custfirstname,custsomenumber from customers WHERE custname LIKE 'xxx' AND custsomenumber = yyy

If a numeric field begins with '<' or '>', whatever is entered in the field will be used as a condition in the WHERE clause, and AND'ed with the rest. (Ex: you could enter “>10” or “<>1234” , or “>10 and custsomenumber<20).

The Add button generates an INSERT statement. All fields which hold data are used for the values, the others are not listed. Char fields are suitably quoted. With the same example as above, the SQL statement would be:

INSERT INTO customers(custname,custsomenumber) VALUES('xxx',yyy)

There is no explicitely provided way to insert a NULL value (and certainly none for a char field).

If the table's primary key is a serial or auto_increment field, and the corresponding field is set as “noentry”, the value for the field is reset to “” before inserting to let auto_increment do its job.

There seems to be no way to retrieve the auto_increment attribute from a mysql client program, so that, when using MySQL, we make the assumption that if an integer field is a primary key, it also has the auto_increment attribute. If the field is also set as noentry, it will be reset before inserting.

The Update button generates an UPDATE statement. There are two issues: the WHERE clause and the values.

The WHERE clause is built from the columns that were designated in the updateindex list (if no such list was explicitely indicated, SQLScreens tries to use the primary index columns for the table. If there is no primary index, no updateindex list is built, and no Update button is created, neither can you run an update by typing Esc u).

The values in the WHERE clause are taken from those that were saved when the last Query (or Next, Rewind, Reset) was performed, which means that it is possible to update the columns in the primary index. If you try an update without having performed some query before, you will get strange error messages about missing array entries.

The values for the update are taken from the screen fields (including the possible hidden ones). All fields whose value is different from the saved value are used. If no value changed, no update is performed (and an error dialog is shown).

As opposed to what happens for SELECT and INSERT, even the fields with no data are used. For char fields, the columns are set to '', for other types, they are set to NULL. This is somewhat arbitrary, but we like it like this.

The Delete button generates a DELETE statement. The WHERE clause is built like the SELECTs, except that no LIKE operators are used.

If some columns have NULL or zero-length string values, they will not be used in the WHERE clause. This means that more rows than expected could sometimes be affected by the statement.

For this reason, the program will create a dialog screen and ask for confirmation if more than one row would be affected by a DELETE statement.

Any application that displays database values and allows the user to update them has two problems:

The first issue can be solved by certifying that the values initially retrieved uniquely define the row (for example, this would be the case if a complete primary key is included in the retrieved fields). That is why SQLScreens insists on having an updateindex field list. This will be automaticaly generated from the primary key in some cases, or specified by the application in other cases. There are also other ways, such as using rowids or server-side cursors, but they are database-dependant.

For the second issue, SQLScreens takes the approach of including all the screen's fields in the WHERE clause, not only the updateindex fields. This guarantees that the UPDATE will fail if one of the fields changed in the database. It might still be possible that another field in the record (a field not used by the screen) would have changed since the query, but this change will not be affected by the new update. If this is still undesirable, you just need to include all the appropriate fields in the screen (possibly with the “nodisplay” attribute).

tcsq uses the same SQLDBTYPE environment variable as the SQLScreens layer to define the database type.

The following shows a small program to search for a name in a MYSQL 'user' table. It is not supposed to be useful for any purpose except as an example.

#!/usr/local/cdkit/isqltcl
package require tcsq
set env(SQLDBTYPE) MYSQL

set hdl [tcsqconuse mysql localhost]
set uname [tcsqquotequote "John O'Connell"]
set qry [tcsqopensel $hdl "SELECT host,user \
     FROM user  WHERE user LIKE '$uname'"]

while {[set res [tcsqnext $qry] != {}} {
   set host [lindex $res 0] 
   set user [lindex $res 1]
   puts "Host: $host User: $user"
}

tcsqclosel $qry
tcsqdiscon $hdl
exit 0