SQLScreens: a simple SQL screen generator


Table of Contents

1. Preface
1.1. Quick description
2. Installation
2.1. External software needed
2.2. Installation
2.2.1. Generating a wish interpreter with MySQL support:
3. Using SQLScreens
3.1. Environment variables
3.2. Buttons
3.3. Keyboard shortcuts
3.4. Special characters in fields
4. Programming Interface
4.1. Overview
4.2. Initialization and termination
4.2.1. sqlscreen
4.2.2. sqlscreendelete
4.2.3. Setting fonts for SQLScreens applications
4.3. Interface array entries
4.3.1. General parameters
4.3.2. Screen attributes
4.3.3. Field attributes
4.3.4. Auxiliary list window
4.3.5. Miscellaneous array entries
4.4. Global customization variables
4.4.1. sqlscshowstmts
4.4.2. sqlscnobell
4.5. Linking screens
4.5.1. sqlmasterslave
4.5.2. sqlslavemaster
4.6. Controlling the number of button sets
4.7. Callback routines:
4.8. Visible internal interfaces
4.9. Small utility routines
5. SQL generation
5.1. Query
5.2. Add
5.3. Update
5.4. Delete
5.5. Update issues
6. The tcsq low level database access layer
6.1. Environment variables
6.2. API calls
6.2.1. tcsqconnect
6.2.2. tcsquse
6.2.3. tcsqconuse
6.2.4. tcsqopensel
6.2.5. tcsqrew
6.2.6. tcsqclosel
6.2.7. tcsqnext
6.2.8. tcsqexec
6.2.9. tcsqdiscon
6.2.10. tcsqtabinfo
6.2.11. tcsqcolinfo
6.2.12. tcsqinsertid
6.2.13. tcsqquotequote
6.2.14. tcsqquoteblob
6.3. Programming example
7. Sample scripts

Chapter 1. Preface

This document describes SQLScreens release 1.2.1

SQLScreens is a TCL/TK package allowing the easy creation of screen forms, for querying and updating a relational database.

SQLScreens was primarily designed to work with MySQL as a backend. It also works with SQLite, and ODBC. It might still work with INFORMIX and MSQL (untested for a looong time).

SQLScreens is no match for commercial application development tools. It is a very simple tool to create ad-hoc query screens. We found it very handy for creating our data-entry utilities in CDKIT/MusicMaker (R.I.P), which is why we decided to publish it, partly also because we use so much free software that we felt compelled to contribute a little.

If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the MySQL grant tables, you may find SQLScreens useful :-)

Still interested ? Details follow. If you are reading this online, you can have a look at the screen dumps.

1.1. Quick description

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:

  • Create multiline text widgets to edit text blobs.

  • Display query results as a list linked to a detail screen.

  • And do many other things described a little further.

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:

  • You have little control over field placement. Fields are placed in a row-column grid managed by Tk's grid geometry manager.

  • The generated SQL is very basic, and you have little control over it.

  • The package may be dependant on assumptions that we made, which may not match your environment. Please try on a test database, not your production one ! When there are no primary keys, the package is crippled.

  • There is no real support for structured fields like date/time (that is, you can use date fields, but the package will not check the format). As we mainly used text and number fields in CDKIT, there are probably more bugs with other types of fields (less testing).

  • You can't specify null values when querying (the fields with no data are just not used). Operators like '>' or '<' can only be used for non-text fields .

  • The screens are not pretty !

  • Etc... Etc...

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

Chapter 2. Installation

2.1. External software needed

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

  • TCL/TK. Don't try to use anything earlier than 8.0. All later releases are supposed to work. If you are running a recent FreeBSD or Linux, you just need to install the packages. Else, you can get the source distributions from the main TCL site . TCL and TK are very easy to build.

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

  • For MySQL, a modified version of the msqltcl package by Hakan Soderstrom is included in the distribution (mysqltcl.c).

  • The original msqltcl can be used for accessing MSQL databases.

  • For UNIX ODBC you will need tclodbc, and:

    • An ODBC driver manager: under UNIX, we tested iODBC, but unixODBC should probably be OK too.

    • The driver for your database. For MySQL, this would be myodbc.

  • For INFORMIX you will need the isqltcl package by Srinivas Kumar. It has become a little difficult to find lately and there is a copy on the download page.

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.

2.2. Installation

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:

  • By statically linking the database access module (e.g. mysqltcl or isqltcl) with the interpreter.
  • By using the TCL load facility and a shared library. The dynamic version sometimes need some manual tweaking to work.
  • By loading an external package that itself does whatever is needed (e.g. package require sqlite3)

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.

2.2.1. Generating a wish interpreter with MySQL support:

You do not need this if you are working with SQLite only. Just give a --disable-mysql argument to configure.

Both the static and dynamic load methods are supported by the SQLScreens build tools. Only Linux, SOLARIS, and FreeBSD have been tested, things are not guaranteed to work on other systems. The Makefile generated by configure is small, it should be easy to adjust if needed.

The configuration script use the mysql_config command to locate the MySQL client library and include files. It should be accessible in your PATH.

When you are ready:

  1. cd to the SQLScreens directory, and type ./configure.

  2. Type make to compile and link the mysqlwish interpreter and the shared library. The shared library link may produce error messages, see below.

  3. Type make install to install the package. This will create a $TK_PREFIX/lib/sqlsc directory and copy the shared library and TCL code there. It will also copy mysqlwish to $TK_PREFIX/bin. TK_PREFIX is taken from the tkConfig.sh script for your wish interpreter. You can change it by typing

    make install TK_PREFIX=yourdest
    

    instead, but you might then have to adjust your TCLLIBPATH for the package to be found. If the shared library link failed at the previous step, or if you get error messages about unfound symbols during installation, either type make install-static to just install the static version, or review the README-DYNAMIC file where there is some more information about dynamic libraries issues. If you are in a hurry or/and are not used to building shared libraries, you might just want to use the static version. And yes, I should use modern TCL extension tools, and if someone wants to fix this, I'll gladly welcome a patch.

If you use TCL with other statically linked extensions, and want to use the same interpreter with SQLScreens, you will have to add the Mysqltcl_Init call to your usual tkAppInit.c file and modify your Makefile to link with mysqltcl.o. You have probably been through this already. Have a look at the included tkAppinit.c

Chapter 3. Using SQLScreens

The following is organized more like a reference manual, there is little tutorial material. You may want to look at the sample scripts to get a quick idea.

3.1. Environment variables

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

  • MYSQL to access a MySQL database.

  • SQLITE3 to access a SQLite database.

  • ODBC to use an ODBC driver manager.

  • INFORMIX to access an INFORMIX database.

  • MSQL to access an MSQL database.

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.

3.2. Buttons

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

Query

starts a SELECT. The WHERE clause is built with the values currently shown on the screen and the nodisplay fields).

Next

fetches the next record in the current query

Rewind

gets back to the first record in the current query. This has different effects depending on the database: in INFORMIX this actually reruns the query so that changes in the database will be visible. With MySQL, this just rewinds the local result buffer.

Reset

clears all visible and hidden fields in the screen.

Add

inserts the current values. There is special handling for auto_increment fields, see SQL generation

Update

updates the row according to the current values (how the WHERE clause is built is described later, see updateindex and Sql generation).

Delete

deletes the row(s) selected by the current values. It will prompt for confirmation if more than one row would be affected.

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.

3.3. Keyboard shortcuts

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:

<TAB>

goes to the next field in the screen.

<CR>

in any field will start a SELECT.

<ESC>n

will fetch the next row.

<ESC>r

will rewind the query.

<ESC>a

will start an INSERT.

<ESC>u

will start an UPDATE.

<ESC>w

will reset the current screen (clear all fields).

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

3.4. Special characters in fields

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

Chapter 4. Programming Interface

4.1. Overview

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

4.2. Initialization and termination

4.2.1. sqlscreen

To create a screen, you set values inside a TCL array (See the following section: Interface array entries ), then perform creation as follows:

sqlscreen yourarrayname
        

Note that sqlscreen will create and pack the screen's window, but not its parents, so that the screen will not be necessarily visible at this point. Ex:

frame .f
set myarray(window) .f.scr
 ... set other fields
sqlscreen myarray
# screen still not visible
pack .f     #screen appears
        

This can be useful if you do not want the screen to be visible at all times: you can use 'pack ' and 'pack forget' to make it appear and disappear as you wish.

sqlscreen optionally takes a second parameter. If the value is h, the fields will be arranged horizontally instead of vertically. There are other ways to do this(see columns), but it can still be useful in some cases.

4.2.2. sqlscreendelete

This procedure will destroy all resources associated with an sqlscreen (windows, database connections and the array itself). Call it as:

sqlscreendelete arrayname
        

4.2.3. Setting fonts for SQLScreens applications

The font used by the screen can be set by setting the font array entry. This will only adjust the font for the specific screen. It may be more convenient to set the font at the start of the application script, with a variation on the following example:

option add *font {Arial 10}
option add *Button*font  {Arial 10 bold}
         

Alternatively, the font could be set in the option database (ie: .Xdefault under Unix). Example:

wines*font: Arial 10
wines*Button*font: Arial 10 bold
         

In the latter case, the program name should not include a .tcl extension, else the dot seems to cause problems in the options database (use wines, not wines.tcl).

4.3. Interface array entries

4.3.1. General parameters

4.3.1.1. window

This defines the TK frame name where the screen will be created. Example:

frame .f1
set customer(window) .f1.cust
          

or just the following to create the window in the top one:

set customer(window) .cust
          

This entry must be a valid TK window name: for exemple it cannot start with an upper case character.

The window must not exist before calling sqlscreen, which will create it. Its parents must exist.

4.3.1.2. database

This defines the database name.

set customer(database) orderdb
          

4.3.1.3. sqlcpasswd, sqlschost, sqlscuser

These define the user name, host and password for the connection to the database server. These are all optional.

4.3.1.4. table

This defines the table name. Example:

set customer(table) customers
          

It is also possible to display fields from several tables in one screen:

set custorder(table) {customers orders}
          

If you are using several tables, you will also need a join clause (see the following paragraph), and you will not be able to modify data through the screen. (You CAN update several tables in one application, but each table will need a separate screen, and the screen links will be through cascaded queries, not join clauses - See Linking screens ).

4.3.1.5. joinclause

In case fields from several tables are displayed in a screen, SQLScreens needs to know how to join the tables when performing a SELECT. This is defined by the joinclause array entry. Exemple:

set custorder(joinclause) {customers.custid = orders.ordercustid}
          

4.3.1.6. columns

This is a list to define the columns that you want included. If it is not set, sqlscreen will query the database for all the column names in the table, and build the screen with the result.

Example for specifying the column names:

set customer(columns) {custid custname}
          

If several tables are used, it may be necessary to qualify the column names if they are not unique:

set custorder(columns) {customers.custid customers.custname}
          

By default, all fields will be displayed in one column. You can get them to be displayed in one line by calling sqlscreen as

sqlscreen arrayname h 

You can also insert line breaks by inserting newline caracters in the column list, like:

set arrayname(columns) {
    host "\n"
    user db "\n"
    select_priv insert_priv update_priv "\n"
    delete_priv create_priv drop_priv
}
          

The field positions will be arranged by the grid geometry manager. In lines with less fields, the last field (and only the last) spans the remaining columns. You will probably need several tries to get it right (at least I usually do).

4.3.2. Screen attributes

4.3.2.1. queryonly

If this is set, the screen will not have “add” and “update” buttons, you will only be able to select data. Example:

set customer(queryonly) {}
          

The value has no importance, just setting the array entry (even to no) creates a screen for query only.

4.3.2.2. allowdelete

If this entry is set, and queryonly is not set a Delete button will be created.

4.3.2.3. font

If this entry is set, the value will be used as a font definition for the screen elements. Any TK font definition can be used. There are several other ways to set the application font.

4.3.2.4. notitle

Suppresses the screen title. This spares a little space if your screen is crowded.

4.3.2.5. nobuttons

If this is set, no buttons will be created in this particular screen. Note that this does not change what you can do in the screen, because the keyboard shortcuts are still available.

4.3.2.6. graphicbuttons

If this is set, and the Tix package is available, the buttons will be created with icons instead of textual labels.

4.3.3. Field attributes

4.3.3.1. Column type and length

The sqlsc_colname_len and sqlsc_colname_type entries are normally created by the package, you do not need to set them. For character columns, you can set sqlsc_colname_len if you want the entry field to be of a size different from the column width (for example if the column is very wide). Example:

set customer(sqlsc_custname_len) 20
          

would create a 20 characters field even if custname is actually 100 characters wide. This does not constrain what you can enter because TK fields can scroll.

In any case, SQLScreens checks that the input can fit in the database column and will not allow entering more data in a field (except for the special 'text' fields described further).

4.3.3.2. autopercent

The autopercentboth, autopercentleft, autopercentright lists can be set for character columns where you want '%' to be automatically added before a query (all char field queries are done with the LIKE operator). Example:

set customer(autopercentright) {custname}
          

would let you query by entering just the beginning of the name, without having to reach for the shift key to type '%'.

4.3.3.3. texts

This is a list of columns (typically text blobs) that should be displayed in multiline text widgets. Each entry is a triplet or quadruplet listing the column name, the width and height of the text widget, and a possible option field. Ex:

set product(texts) {{description 20 70} {notice 10 70 t}}
          

If the option field is present, it should be a string where each character will select an option. There are currently 2 possible (and mutually exclusive) options:

t

will display a label (column name) above the text area

l

will display a label on the left of the text area

By default, no label will be displayed for text fields.

SQLScreens will handle quoting and unquoting the blob contents.

Text entries will NOT be validated for maximum length against the database field width.

There is an exemple of texts use in the wines.tcl sample application.

4.3.3.4. choices

This list defines columns where entries should come from a menu instead of being free form. It is very useful, but the interface could be nicer.

The choices entry is a list. There are two list elements for every column. The first element is the column name, the second element the name for the list of possible values. For example:

set customer(choices) {
  custtype custtypelist
  custgender custgenderlist
}
          

Would specify that the custtype and custgender columns will have values coming from custtypelist and custgenderlist. These lists would typically have been created beforehand (possibly by querying another table). The list of values can in turn be of two types: either a simple list or a list of pairs.

A simple list lists the possible values (would you believe this ?). Ex:

set custtypelist {normal distributor internal}
          

normal, distributor and internal will be both displayed on the screen and used for querying or updating the database.

In a list of pairs, each pair defines the value that should be shown and the value that should be entered in the database. Example:

set custgenderlist {{unknown 0} {female 1} {male 2}}
          

With this list, the menu would display unknown, male, female, but the values used for the database would be 0, 1, 2.

Note that when using MySQL, a choice menu will be automatically generated for 'enum' columns. You can still set your own list, which will override the automatically built one. This can be useful if the displayed values are different from the stored ones.

There are exemples of use (both automatic and explicit) in the wines.tcl sample program.

4.3.3.5. ordercols

This list defines column names that will be added in an ORDER BY clause each time a query is run. It has the format of a normal ORDER BY column list. Ex:

set customer(ordercols) "custid desc, custname"
          

4.3.3.6. updateindex

This defines a column name (or a list of column names) that will be used in the WHERE clause of an UPDATE statement. It should provide a way to uniquely identify a row.

If neither columns nor updateindex are set before calling sqlscreen, SQLScreens will try to make up an updateindex by using a serial column or primary key if one is found.

If columns is set, and not updateindex, the latter is automatically generated only if the primary key is completely included in the column list.

If updateindex is set to an empty list by the caller, it is expanded to include all the screen's columns (no checks against a possible primary key in this case).

If no updateindex list finally exists, the screen will have no Update button.

See Sql generation for a more complete discussion of update row selection.

4.3.3.7. upshiftcols

This is a list of fields for which values should be automatically changed to upper case before inserting or querying. This is very useful with INFORMIX which is case-sensitive, not very useful with MySQL. Example:

set customer(upshiftcols) {custname custfirstname}
          

4.3.3.8. noentry

This is a list of columns for which data entry is forbidden. They are displayed differently, and will not allow typing. This is sometimes useful for fields that should only be updated by the program or on which searching is forbidden.

4.3.3.9. nodisplay

This is a list of columns for which no fields will be shown. The corresponding values are present in the array. This is used for fields which link several screens, or which the application wants to use, but which don't need to be displayed.

4.3.4. Auxiliary list window

4.3.4.1. list_columns, list_window

If list_columns and list_window are set, sqlscreen will create an auxiliary list for the screen, in the specified window. The list screen will display one line for each result row, the data displayed will be taken from the list_columns columns. This is useful to get a compact display of a query's results.

Clicking on a line in the list with mouse button 1 will display the corresponding row in the main screen.

This capability will only be available if an updateindex list has been defined for the screen (either implicitely or explicitely), see the updateindex section. The updateindex columns must be part of list_columns, so that we can uniquely link back from the list to the detail screen.

Example:

set customers(list_columns) {custname custid}
set customers(updateindex) custid
toplevel .custlist
set customers(list_window) .custlist
          

4.3.4.2. list_colwidths:

SQLScreens will try its best to compute appropriate column widths for the list and to align the columns. You may force specific values for the column widths by specifying the list_colwidths entry, as a list specifying the width in characters for each column. Example:

set customers(list_colwidths) {40 5}
          

The widths must be specified in the same order as the columns in list_columns.

4.3.4.3. list_lineproc:

When displaying the list, SQLScreens will alternate the line's background between white and light grey to facilitate reading. If defined, the list_lineproc procedure will be called for each displayed line, with parameters allowing it to change the line's display (for exemple, this would allow showing special rows needing attention in red). Example:

set customers(list_lineproc) custlineproc
          

list_lineproc will then be called for each line with 3 parameters:

  1. The name of the TK text window where the line is displayed.

  2. The TK text tag name for the area associated with the line.

  3. The list of column values for this line.

list_lineproc can then test one or several entries in the value list, and use the window and tag names to set attributes. The following exemple sets the ugly colors in the wine list according to the bottle count (from wines.tcl):

proc setlinecolor {w tag res} {
    # Get the bottle count from the value list
    set botcnt [lindex $res 0]
    # Set the background color accordingly
    switch $botcnt {
      1 {$w tag configure $tag -background red}
      2 {$w tag configure $tag -background orange}
      3 {$w tag configure $tag -background yellow}          
      default {$w tag configure $tag -background green}
    }
}
          

4.3.5. Miscellaneous array entries

4.3.5.1. hdl

The package uses this entry to store the database handle.

4.3.5.2. initfocus

This is the name of the window where the focus should go when the screen is reset. This can be useful for repetitive entry when you don't want to use the mouse.

4.3.5.3. tabcolserial

If there is a serial or auto_increment column, sqlscreen sets its name in there.

If the screen allows insertion, but this field is either not displayed or not modifiable (noentry), the value will be automatically reset to null before performing an insert, which will allow inserting a record by first querying for (and probably modifying) another one.

If the field is modifiable by the user, no special action will be taken.

If the beforeinsert procedure is defined for the screen, any modification is performed before calling it, to allow for a local value allocation scheme.

4.3.5.4. querynum

This is the select result handle.

4.3.5.5. sqlsc_colname_valsaved

The package uses these entries to save the database values when a query is performed. This is used to compute the UPDATE statements (See the Sql generation section).

4.4. Global customization variables

4.4.1. sqlscshowstmts

You can set this variable to 1 or 0 to print the SQL statements to stdout (or SQLSCLOG) when they are executed. This is not an array element but a global variable.

4.4.2. sqlscnobell

You can set this to 1 to prevent use of the bell function when the end of a query is reached. The sound can become quite ennoying...

4.5. Linking screens

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.

4.5.1. sqlmasterslave

This links the first screen to the second one so that a query in the first will run a query in the second. Example:

sqlmasterslave customer custid order ordercust
        

would link the customer and order screens so that the order screen is reset, the ordercust field is set to the value of the custid field and a query is run every time a query is run in customer.

Things are set up so that it is possible to have reciprocal links without creating an infinite loop. Example:

sqlmasterslave customer custid order ordercust
sqlmasterslave order ordercust customer custid 
        

is ok and would both show a customer's orders after querying in the customer screen and an order's customer after querying in the orders screen.

4.5.2. sqlslavemaster

This second type of link is used to just update a column in the target screen, without running a query there. It is useful to set the join column values. Example:

sqlslavemaster customer custid order ordercust
        

could be used to set the ordercust field by querying customer, typically while entering orders.

4.6. Controlling the number of button sets

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

4.7. Callback routines:

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

  • afterinsert

  • afterquery

  • afterupdate

  • afterdelete

  • beforeinsert

  • beforequery

  • beforeupdate

  • beforedelete

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

4.8. Visible internal interfaces

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.

  • sqlscinsert: Generate and run an INSERT statement.

  • sqlscupd: Generate and run an UPDATE statement.

  • sqlscquery: Generate and run a SELECT statement.

  • sqlscdelete: Generate and run a DELETE statement.

  • sqlscnext: Fetch the next row in the current query.

  • sqlscreopen: Rewind the current query.

  • sqlscreset: Reset all data values for the screen.

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

4.9. Small utility routines

  • sqlsc_entrywidget arnm colname Return the name for the entry widget for arnm and colname.

  • sqlsc_labelwidget arnm colname Return the name for the label widget for arnm and colname.

Chapter 5. SQL generation

Every time the user presses a button like query or update, the program will generate a SQL statement to perform the appropriate operation on the database. The following paragraphs describe how the statement is generated.

5.1. Query

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

5.2. Add

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.

5.3. Update

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.

5.4. Delete

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.

5.5. Update issues

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

  1. It must ensure that the generated UPDATE statement will really update the row that was displayed and not many other rows in the database.

  2. It must ensure that the affected row has not changed since it was displayed.

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

Chapter 6. The tcsq low level database access layer

SQLScreens uses an intermediate code layer to access the different databases in a consistent fashion. This intermediate layer is called tcsq.

The tcsq calls which are documented here may be useful as a database access layer for non-SQLScreens applications (for any TCL script accessing the supported databases), or in auxiliary routines inside an SQLScreens application (for example, for building lists of values by querying a table).

6.1. Environment variables

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

6.2. API calls

6.2.1. tcsqconnect

tcsqconnect [host [user [passwd]] 
        

Returns a server connection handle ( hdl in the following). Depending on the database type, it may actually connect to a server, or do nothing (Ex: informix).

6.2.2. tcsquse

tcsquse hdl dbname
        

Associates the connection handle hdl with database dbname .

6.2.3. tcsqconuse

tcsqconuse database [host]
        

Utility function: connect and use.

6.2.4. tcsqopensel

tcsqopensel hdl stmt
        

Opens a query operation. stmt is a string holding an SQL SELECT statement. Returns a select handle ( selhdl in the following).

6.2.5. tcsqrew

tcsqrew selhdl 
        

Rewinds the query associated with selhdl . This may actually rerun the query (INFORMIX) or be purely local (MYSQL).

6.2.6. tcsqclosel

tcsqclosel selhdl
        

Closes a query, and frees the associated resources.

6.2.7. tcsqnext

tcsqnext selhdl
        

Returns the next row for the query, as a list of values, in the order of the columns in the SELECT statement. The last fetch returns an empty list.

6.2.8. tcsqexec

tcsqexec hdl stmt 
        

Executes a non-SELECT SQL statement (Ie, INSERT, DELETE, etc...)

6.2.9. tcsqdiscon

tcsqdiscon hdl 
        

Disconnects and frees resources associated with hdl .

6.2.10. tcsqtabinfo

tcsqtabinfo hdl 
        

Returns a list of the user tables in the database referenced by hdl.

6.2.11. tcsqcolinfo

tcsqcolinfo hdl tbl arnm 
        

Returns information about table tbl into the array the name of which is specified by arnm .

6.2.12. tcsqinsertid

tcsqinsertid hdl

Returns the auto_increment value for the last inserted row.

6.2.13. tcsqquotequote

tcsqquotequote s 
        

Returns a suitably escaped string, for use in sql statements.

6.2.14. tcsqquoteblob

tcsqquoteblob s 
        

Same for blobs.

6.3. Programming example

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
      

Chapter 7. Sample scripts

Three sample scripts are provided with the package (in the samples directory). These are not real applications. For example you have to set the password in the environment or edit the scripts to change it. The goal was to keep things as simple as possible.

tablescreen.tcl

is a minimal application using all the automatic defaults to create a screen with all the columns in a given table. This can be a good skeleton for trying things.

mysqldb.tcl

creates a screen to access the MySQL “db” table. The main goal is to show how you can arrange the fields in row-column. Otherwise, it does nothing more than tablescreen.tcl.

wines.tcl

is something I use to manage my wine cellar. It demonstrates most of sqlscreen's features (and provides most of the incentives for new gadgets, by the way).

To try wines.tcl, you will have to create tables and load the sample data in a database named “wines”. The “createloadwines.sh” shell-script will do this for you.

wines.tcl is not the perfect cellar management application, but it is quite useful right now. It is already better than my old Excel spreadsheet, and I don't need to reboot my PC under some strange Operating System to use it.