sqlscreens.lyx
3033 lines (2358 with data), 62.2 kB
#LyX 1.2 created this file. For more info see http://www.lyx.org/
\lyxformat 220
\textclass docbook-book
\language english
\inputencoding default
\fontscheme default
\graphics dvips
\paperfontsize 12
\spacing single
\papersize Default
\paperpackage a4wide
\use_geometry 0
\use_amsmath 0
\use_natbib 0
\use_numerical_citations 0
\paperorientation portrait
\secnumdepth 3
\tocdepth 3
\paragraph_separation indent
\defskip medskip
\quotes_language english
\quotes_times 2
\papercolumns 1
\papersides 1
\paperpagestyle default
\layout Title
\added_space_top vfill \added_space_bottom vfill
SQLScreens: a simple SQL screen generator
\layout FirstName
Jean-Francois
\layout Surname
Dockes
\layout Surname
jean-francois.dockes@wanadoo.fr
\layout Date
25 May 1999
\layout Chapter
Preface
\layout Standard
This document describes SQLScreens release 1.1.7
\layout Standard
With SQLScreens you can easily create screen forms, for querying and updating
a relational database.
\layout Standard
SQLScreens is written in TCL/TK.
It is primarily designed to work with MySQL as a backend.
It also works with ODBC.
It might still work with INFORMIX and MSQL (untested for some time).
\layout Standard
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 (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.
\layout Standard
If you've struggled with (Y,N,Y,N,Y,Y,...) lists in the MySQL grant tables,
you may find SQLScreens useful :-)
\layout Standard
Still interested ? Details
\begin_inset LatexCommand \ref[follow]{QuickDescription}
\end_inset
.
If you are reading this in a web browser, you can have a look at the
\begin_inset LatexCommand \htmlurl[screen dumps]{sqlscdumps.html}
\end_inset
.
\layout Section
Quick description
\begin_inset LatexCommand \label{QuickDescription}
\end_inset
\layout Standard
A typical SQLScreens application will have a number of screens, each with
several fields.
Each screen will be linked to one or several database tables, each field
to a column.
Impatient people using a web browser can look at the
\begin_inset LatexCommand \url[screen dumps]{sqlscdumps.html}
\end_inset
right now.
\layout Standard
You create each screen by listing the column names that you want to use.
\layout Standard
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 (
\emph on
tablescreen.tcl
\emph default
).
\layout Standard
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).
\layout Standard
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 relationsh
ips), or so that it will just update the join column.
\layout Standard
You can also:
\layout Itemize
Create multiline text widgets to edit text blobs.
\layout Itemize
Display query results as a list linked to a detail screen.
\layout Itemize
And do many other things described a little further.
\layout Standard
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.
\layout Standard
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.
\layout Standard
There are many other bells and whistles, but also a few drawbacks:
\layout Itemize
You have little control over field placement.
Fields are placed in a row-column grid managed by Tk's grid geometry manager.
\layout Itemize
The generated SQL is very basic, and you have little control over it.
\layout Itemize
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.
\layout Itemize
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).
\layout Itemize
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 .
\layout Itemize
The screens are not pretty !
\layout Itemize
Etc...
Etc...
\layout Standard
This said, the software is free and we are open to suggestions to improve
it.
\layout Standard
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, and point it to any table, like:
\layout Code
tablescreen.tcl dbname tablename
\layout Standard
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).
\layout Chapter
Installation
\layout Section
External software needed
\layout Standard
To use SQLScreens, you will need a number of external software packages:
\layout Itemize
TCL/TK.
Don't try to use anything earlier than 8.0.
All later releases are supposed to work.
If you're running a recent FreeBSD or Linux, you just need to install the
packages.
Else, you can get the source distributions from
\begin_inset LatexCommand \htmlurl[the main TCL site]{http://tcl.activestate.com/}
\end_inset
.
TCL and TK are very easy to compile.
\layout Standard
In order to access the databases, the basic TCL interpreter must be augmented
with a database access module:
\layout Itemize
For MySQL, a modified version of the
\series bold
msqltcl
\series default
package by Hakan Soderstrom is included in the distribution (
\series bold
mysqltcl.c
\series default
).
\layout Itemize
The original
\series bold
msqltcl
\series default
can be used for accessing MSQL databases.
\layout Itemize
For UNIX ODBC you will need
\begin_inset LatexCommand \url[tclodbc]{http://www.solagem.fi/~rnurmi/tclodbc.html}
\end_inset
, and:
\begin_deeper
\layout Itemize
An ODBC driver manager: under UNIX, we tested
\begin_inset LatexCommand \url[iODBC]{http://www.iodbc.org/}
\end_inset
, but
\begin_inset LatexCommand \url[unixODBC]{http://www.unixodbc.org/}
\end_inset
should probably be OK too.
\layout Itemize
The driver for your database.
For
\series bold
MySQL
\series default
, this would be
\series bold
\series default
\begin_inset LatexCommand \url[myodbc]{http://www.tcx.se/download.html}
\end_inset
.
\end_deeper
\layout Itemize
For INFORMIX you will need the
\series bold
isqltcl
\series default
package by Srinivas Kumar.
You can get it
\begin_inset LatexCommand \url[there]{ftp://ftp.neosoft.com/languages/tcl/sorted/databases/isqltcl3.2}
\end_inset
.
\layout Standard
If you're reading this in a browser, there is a
\begin_inset LatexCommand \url[download page]{http://perso.wanadoo.fr/dockes/sqlscreens/sqlscdownload.html}
\end_inset
where you can get the SQLScreens package and which also points to the other
pieces.
\layout Section
Installation
\layout Standard
The package comes as a gzipped tar file named something like
\shape italic
sqlscreens-X.Y.Z.tar.gz
\shape default
.
\layout Standard
Unpacking the file (gunzip < sqlscreens-X.Y.Z.tar.gz | tar xvf -) will create
a top directory named like
\shape italic
sqlscreens-X.Y.Z.
\layout Standard
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...
\layout Standard
First, if needed, compile and install TCL and TK (untar; cd tcl8.../unix; configure
; make; make install, same for tk).
\layout Standard
The next step is to add database-access capability to the standard TCL/TK
\series bold
wish
\series default
interpreter.
This can be done in two ways, either by statically linking the database
access module (
\series bold
mysqltcl
\series default
or
\series bold
isqltcl
\series default
) with the interpreter or by using the TCL
\series bold
load
\series default
facility and a shared library.
The dynamic version sometimes need some manual tweaking to work.
\layout Standard
The Makefile in the
\series bold
sqlscreens
\series default
directory only supports
\series bold
MySQL
\series default
internally.
If you will be using
\series bold
isqltcl
\series default
and INFORMIX, follow the installation instructions in the
\series bold
isqltcl
\series default
package to generate the interpreter.
\layout Standard
For
\series bold
ODBC
\series default
, you should first install the driver manager, the driver(s) you need and
the
\series bold
tclodbc
\series default
TCL extension.
Follow the instructions in each package.
\layout Subsection
Generating a wish interpreter with MySQL support:
\layout Standard
Both the static and dynamic load methods are supported by the build tools
in the
\series bold
sqlscreens
\series default
directory.
Only Linux, SOLARIS, and FreeBSD have been tested, it is not guaranteed
to work on other systems.
The Makefile generated by
\series bold
configure
\series default
is small, it should be easy to adjust if needed.
\layout Standard
The configuration script only looks for the MySQL library in /usr/local/mysql/li
b,/usr/local/lib/mysql, /usr/lib/mysql, /usr/lib.
If your installation is different, please make symbolic links or modify
the script.
\layout Standard
When you're ready:
\layout Enumerate
\begin_inset Quotes eld
\end_inset
cd
\begin_inset Quotes erd
\end_inset
to the sqlscreens directory, and type
\begin_inset Quotes eld
\end_inset
./configure
\begin_inset Quotes erd
\end_inset
.
\layout Enumerate
Type
\begin_inset Quotes eld
\end_inset
make
\begin_inset Quotes erd
\end_inset
to compile and link the
\series bold
mysqlwish
\series default
interpreter and the shared library.
The shared library link may produce error messages, see below.
\layout Enumerate
Type
\begin_inset Quotes eld
\end_inset
make install
\begin_inset Quotes erd
\end_inset
to install the package.
This will create a
\begin_inset Quotes eld
\end_inset
$TK_PREFIX/lib/sqlsc
\begin_inset Quotes erd
\end_inset
directory and copy the shared library and TCL code there.
It will also copy
\series bold
mysqlwish
\series default
to
\begin_inset Quotes eld
\end_inset
$TK_PREFIX/bin.
TK_PREFIX is taken from the tkConfig.sh script for your wish interpreter.
You can change it by typing
\begin_inset Quotes eld
\end_inset
make install TK_PREFIX=yourdest
\begin_inset Quotes erd
\end_inset
instead, but you might then have to adjust your TCLLIBPATH for the package
to be found.
\newline
If the shared library link failed at the previous step, or if you get error
messages about unfound symbols during installation, either type
\begin_inset Quotes eld
\end_inset
make install-static
\begin_inset Quotes erd
\end_inset
to just install the static version, or review the README-DYNAMIC file where
there is some more information about dynamic libraries issues.
If you're in a hurry or/and are not used to building shared libraries,
you might just want to use the static version.
\layout Standard
If you use TCL with other statically linked extensions, and want to use
the same interpreter with
\series bold
SQLScreens
\series default
, you will have to add the
\shape italic
Mysqltcl_Init
\shape default
call to your usual
\shape italic
tkAppInit
\shape default
file and modify your Makefile to link with
\series bold
mysqltcl.o
\series default
.
You've probably been through this already.
Have a look at the included tkAppinit.c
\layout Standard
Because the dynamic version is sometimes a pain to build, the sample scripts
are now set up to be executed by
\series bold
mysqlwish
\series default
by default (for MySQL).
If you managed to get the dynamic version to build, you'll certainly find
it easy to change the first line in the scripts...
\layout Chapter
Using SQLScreens
\layout Standard
The following is organized more like a reference manual, there is little
tutorial material.
You may want to look at the
\begin_inset LatexCommand \ref[sample scripts]{samplescripts}
\end_inset
to get a quick idea.
\layout Section
Environment variables
\begin_inset LatexCommand \label{EnvVars}
\end_inset
\layout Standard
The
\series bold
SQLDBTYPE
\series default
environment variable decides what database code is going to be used.
It has several possible values:
\layout Itemize
\shape italic
MYSQL
\shape default
to access a MySQL database.
\layout Itemize
\shape italic
INFORMIX
\shape default
to access an INFORMIX database.
\layout Itemize
\shape italic
MSQL
\shape default
to access an MSQL database.
\layout Itemize
ODBC to use an ODBC driver manager.
\layout Standard
The default if the variable is not set is to use MYSQL.
Don't depend on it.
\layout Standard
This variable can be set inside the script, before the first call to
\series bold
sqlscreen
\series default
.
(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).
\layout Standard
The
\series bold
SQLSCLOG
\series default
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.
\layout Standard
The
\series bold
SQLSCHOST
\series default
,
\series bold
SQLSCUSER
\series default
, and
\series bold
SQLSCPASSWORD
\series default
variables can be used to set the connection parameters.
These are only used by the sample scripts, not the core package.
\layout Standard
\series bold
MYSQL_TCP_PORT
\series default
should be used if you need to change the default MySQL connection port.
\layout Section
Buttons
\layout Standard
Each screen has a set of buttons to perform the following operations:
\layout Description
Query starts a SELECT.
The WHERE clause is built with the values currently shown on the screen
and the
\series bold
nodisplay
\series default
fields).
\layout Description
Next fetches the next record in the current query
\layout Description
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.
\layout Description
Reset clears all visible and hidden fields in the screen.
\layout Description
Add inserts the current values.
There is special handling for auto_increment fields, see
\begin_inset LatexCommand \ref[SQL generation]{sqlGeneration}
\end_inset
\layout Description
Update updates the row according to the current values (how the where clause
is built is described later, see
\begin_inset LatexCommand \ref[updateindex]{updateindex}
\end_inset
and
\begin_inset LatexCommand \ref[Sql generation]{sqlGeneration}
\end_inset
).
\layout Description
Delete deletes the row(s) selected by the current values.
It will prompt for confirmation if more than one row would be affected.
\layout Standard
The
\series bold
Update
\series default
and
\series bold
Add
\series default
buttons may not exist on all screens (some screens may be set up only for
querying).
\layout Standard
\series bold
Delete
\series default
is not created by default (See
\begin_inset LatexCommand \ref[allowdelete]{allowdelete}
\end_inset
).
\layout Standard
The
\begin_inset LatexCommand \ref[SQL generation]{sqlGeneration}
\end_inset
section describes how the SQL statements are generated for the different
actions.
\layout Section
Keyboard shortcuts
\layout Standard
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:
\layout Description
<TAB> goes to the next field in the screen.
\layout Description
<CR> in any field will start a SELECT.
\layout Description
<ESC>n will fetch the next row.
\layout Description
<ESC>r will rewind the query.
\layout Description
<ESC>a will start an INSERT.
\layout Description
<ESC>u will start an UPDATE.
\layout Description
<ESC>w will reset the current screen (clear all fields).
\layout Standard
The Update and Add shortcuts will have no effects in query only screens.
There is no Delete shortcut.
\layout Section
Special characters in fields
\layout Standard
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:
\layout Standard
If you enter
\begin_inset Quotes eld
\end_inset
>10
\begin_inset Quotes erd
\end_inset
in a field named
\begin_inset Quotes eld
\end_inset
quantity
\begin_inset Quotes erd
\end_inset
, a
\begin_inset Quotes eld
\end_inset
quantity > 10
\begin_inset Quotes erd
\end_inset
condition will be inserted in the WHERE clause.
You could also enter
\begin_inset Quotes eld
\end_inset
>10 and quantity < 20
\begin_inset Quotes erd
\end_inset
, or whatever condition you need.
(See also
\begin_inset LatexCommand \ref[the paragraph about SQL generation]{sqlGeneration}
\end_inset
).
\layout Chapter
Programming Interface
\layout Section
Overview
\layout Standard
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.
\layout Standard
The basic idea is that you set values in the array and then call
\begin_inset Quotes eld
\end_inset
\series bold
sqlscreen
\shape italic
\SpecialChar ~
arrayname
\series default
\begin_inset Quotes erd
\end_inset
\shape default
to create the screen.
\layout Standard
The values set in the array define what tables/columns will be used, how
the screen will look like, etc...
You can 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.
\layout Standard
In the following, we shall use the example of a database named
\begin_inset Quotes eld
\end_inset
orderdb
\begin_inset Quotes erd
\end_inset
, with a table named
\begin_inset Quotes eld
\end_inset
customers
\begin_inset Quotes erd
\end_inset
, with columns named
\begin_inset Quotes eld
\end_inset
custid
\begin_inset Quotes erd
\end_inset
,
\begin_inset Quotes eld
\end_inset
custname
\begin_inset Quotes erd
\end_inset
, and
\begin_inset Quotes eld
\end_inset
custfirstname
\begin_inset Quotes erd
\end_inset
, and a table named
\begin_inset Quotes eld
\end_inset
orders
\begin_inset Quotes erd
\end_inset
with
\begin_inset Quotes eld
\end_inset
orderid
\begin_inset Quotes erd
\end_inset
and
\begin_inset Quotes eld
\end_inset
ordercustid
\begin_inset Quotes erd
\end_inset
.
\layout Standard
\series bold
SQLScreens
\series default
stores the values for the field corresponding to a column as
\begin_inset Quotes eld
\end_inset
arrayname(sqlsc_
\shape italic
column
\shape default
_value)
\begin_inset Quotes erd
\end_inset
(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).
\layout Standard
The first release used to store the values as arrayname(
\shape italic
column
\shape default
).
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
\begin_inset Quotes eld
\end_inset
sqlsc_names_compat_old
\begin_inset Quotes erd
\end_inset
to 1 before the first call to get a compatible behaviour (this will go
away in the near future).
\layout Standard
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.
\layout Standard
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.
\layout Standard
When you're finished with the screen, you can call
\series bold
sqlscreendelete
\series default
\shape italic
arrayname
\shape default
to cleanup and release all resources (array, windows, database connections).
Most applications will exit instead.
\series bold
sqlscreendelete
\series default
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).
\layout Section
Initialization and termination
\layout Subsection
sqlscreen
\layout Standard
To create an sqlscreen, you set values inside a TCL array (See the following
section:
\begin_inset LatexCommand \ref[Interface array entries]{ifarray}
\end_inset
), then create the screen as follows:
\layout Code
sqlscreen yourarrayname
\layout Standard
Note that
\series bold
sqlscreen
\series default
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:
\layout Code
frame .f
\layout Code
set myarray(window) .f.scr
\layout Code
...
set other fields
\layout Code
sqlscreen myarray
\layout Code
# screen still not visible
\layout Code
pack .f #screen appears
\layout Standard
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.
\layout Standard
\series bold
sqlscreen
\series default
optionnally 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
\begin_inset LatexCommand \ref[columns]{ifColumns}
\end_inset
) , but it can still be useful in some cases.
\layout Subsection
sqlscreendelete
\layout Standard
This procedure will destroy all resources associated with an sqlscreen (windows,
database connections and the array itself).
Call it as:
\layout Code
sqlscreendelete arrayname
\layout Section
Interface array entries
\begin_inset LatexCommand \label{ifarray}
\end_inset
\layout Subsection
General parameters
\layout Subsubsection
window
\layout Standard
This defines the TK frame name where the screen will be created.
Example:
\layout Code
frame .f1
\layout Code
set customer(window) .f1.cust
\layout Standard
or just the following to create the window in the top one:
\layout Code
set customer(window) .cust
\layout Standard
This entry must be a valid TK window name: for exemple it cannot start with
an upper case character.
\layout Standard
The window must not exist before calling
\series bold
sqlscreen
\series default
, which will create it.
Its parents must exist.
\layout Subsubsection
database
\layout Standard
This defines the database name.
\layout Code
set customer(database) orderdb
\layout Subsubsection
sqlcpasswd, sqlschost, sqlscuser
\layout Standard
These define the user name, host and password for the connection to the
database server.
These are all optional.
\layout Subsubsection
table
\layout Standard
This defines the table name.
Example:
\layout Code
set customer(table) customers
\layout Standard
It is also possible to display fields from several tables in one screen:
\layout Code
set custorder(table) {customers orders}
\layout Standard
If you are using several tables, you'll 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
\begin_inset LatexCommand \ref[Linking screens]{linkingscreens}
\end_inset
).
\layout Subsubsection
joinclause
\layout Standard
In case fields from several tables are displayed in a screen,
\series bold
SQLScreens
\series default
needs to know how to join the tables when performing a SELECT.
This is defined by the
\series bold
joinclause
\series default
array entry.
Exemple:
\layout Code
set custorder(joinclause)
\backslash
\layout Code
{customers.custid = orders.ordercustid}
\layout Subsubsection
columns
\begin_inset LatexCommand \label{ifColumns}
\end_inset
\layout Standard
This is a list to define the columns that you want included.
If it is not set,
\series bold
sqlscreen
\series default
will query the database for all the column names in the table, and build
the screen with the result.
\layout Standard
Example for specifying the column names:
\layout Code
set customer(columns) {custid custname}
\layout Standard
If several tables are used, it may be necessary to qualify the column names
if they are not unique:
\layout Code
set custorder(columns) {customers.custid
\backslash
customers.custname...}
\layout Standard
By default, all fields will be displayed in one column.
You can get them to be displayed in one line by calling sqlscreen as
\begin_inset Quotes eld
\end_inset
sqlscreen arrayname h
\begin_inset Quotes erd
\end_inset
instead of
\begin_inset Quotes eld
\end_inset
sqlscreen arrayname
\begin_inset Quotes erd
\end_inset
.
You can also insert
\begin_inset Quotes eld
\end_inset
line breaks
\begin_inset Quotes erd
\end_inset
by inserting newline caracters in the column list, like:
\layout Code
set arrayname(columns) {
\layout Code
host "
\backslash
n"
\layout Code
user db "
\backslash
n"
\layout Code
select_priv insert_priv update_priv "
\backslash
n"
\layout Code
delete_priv create_priv drop_priv
\layout Code
}
\layout Standard
The field positions will be arranged by the
\series bold
grid
\series default
geometry manager.
In lines with less fields, the last field (and only the last) spans the
remaining columns.
You'll probably need several tries to get it right (at least I do).
\layout Subsection
Screen attributes
\layout Subsubsection
queryonly
\layout Standard
If this is set, the screen will not have
\begin_inset Quotes eld
\end_inset
add
\begin_inset Quotes erd
\end_inset
and
\begin_inset Quotes eld
\end_inset
update
\begin_inset Quotes erd
\end_inset
buttons, you will only be able to select data.
Example:
\layout Code
set customer(queryonly) {}
\layout Standard
The value has no importance, just setting the array entry (even to
\begin_inset Quotes eld
\end_inset
no
\begin_inset Quotes erd
\end_inset
) creates a screen for query only.
\layout Subsubsection
allowdelete
\begin_inset LatexCommand \label{allowdelete}
\end_inset
\layout Standard
If this entry is set, and queryonly is not set a
\series bold
Delete
\series default
button will be created.
\layout Subsubsection
notitle
\layout Standard
Suppresses the screen title.
This spares a little space if your screen is crowded.
\layout Subsubsection
nobuttons
\layout Standard
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.
\layout Subsection
Field attributes
\layout Subsubsection
Column type and length
\layout Standard
The
\series bold
sqlsc_
\shape italic
colname
\shape default
_len
\series default
and
\series bold
sqlsc_
\shape italic
colname
\shape default
_type
\series default
entries (like
\shape italic
sqlsc_custid_len
\shape default
,
\shape italic
sqlsc_custid_type
\shape default
) are normally created by the package, you do not need to set them.
For character columns, you can set
\series bold
sqlsc_
\shape italic
colname
\shape default
_len
\series default
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:
\layout Code
set customer(sqlsc_custname_len) 20
\layout Standard
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.
\layout Standard
Beware:
\series bold
SQLScreens
\series default
does NOT check that the input can fit in the database column.
This can produce unexpected truncation.
\layout Subsubsection
autopercent
\layout Standard
The
\series bold
autopercentboth
\series default
,
\series bold
autopercentleft
\series default
,
\series bold
autopercentright
\series default
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:
\layout Code
set customer(autopercentright) {custname}
\layout Standard
would let you query by entering just the beginning of the name, without
having to reach for the shift key to type '%'.
\layout Subsubsection
texts
\layout Standard
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:
\layout Code
set product(texts) {{description 20 70}
\backslash
\layout Code
{notice 10 70 t}}
\layout Standard
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:
\layout Description
t will display a label (column name) over the text area
\layout Description
l will display a label on the left of the text area
\layout Standard
By default, no label will be displayed for text fields.
\layout Standard
\noindent
\series bold
SQLScreens
\series default
will handle quoting and unquoting the blob contents.
\layout Standard
\noindent
There is an exemple of
\series bold
texts
\series default
use in the
\series bold
wines.tcl
\series default
sample application.
\layout Subsubsection
choices
\layout Standard
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.
\layout Standard
The
\series bold
choices
\series default
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:
\layout Code
set customer(choices) {
\layout Code
custtype custtypelist
\layout Code
custsex custsexlist
\layout Code
}
\layout Standard
Would specify that the
\shape italic
custtype
\shape default
and
\shape italic
custsex
\shape default
columns will have values coming from
\shape italic
custtypelist
\shape default
and
\shape italic
custsexlist
\shape default
.
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.
\layout Standard
A simple list lists the possible values (would you believe this ?).
Ex:
\layout Code
set custtypelist {normal distributor internal}
\layout Standard
\family typewriter
normal
\family default
\shape italic
,
\family typewriter
\shape default
distributor
\family default
and
\family typewriter
internal
\family default
will be both displayed on the screen and used for querying or updating
the database.
\layout Standard
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:
\layout Standard
set custsexlist {{unknown 0} {female 1} {male 2}}
\layout Standard
With this list, the menu would display
\family typewriter
unknown
\family default
,
\family typewriter
male
\family default
,
\family typewriter
female
\family default
, but the values used for the database would be
\family typewriter
0, 1, 2.
\layout Standard
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.
\layout Standard
There are exemples of use (both automatic and explicit) in the
\series bold
wines.tcl
\series default
sample program.
\layout Subsubsection
ordercols
\layout Standard
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:
\layout Code
set customer(ordercols) "custid desc, custname"
\layout Standard
\begin_inset LatexCommand \label{updateindex}
\end_inset
\layout Subsubsection
updateindex
\layout Standard
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.
\layout Standard
If neither
\series bold
columns
\series default
nor
\series bold
updateindex
\series default
are set before calling
\series bold
sqlscreen
\series default
,
\series bold
SQLScreens
\series default
will try to make up an
\series bold
updateindex
\series default
by using a serial column or primary key if one is found.
\layout Standard
If
\series bold
columns
\series default
is set, and not
\series bold
updateindex,
\series default
the latter is automatically generated only if the primary key is completely
included in the column list.
\layout Standard
If
\series bold
updateindex
\series default
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).
\layout Standard
If no
\series bold
updateindex
\series default
list finally exists, the screen will have no
\series bold
update
\series default
button.
\layout Standard
See
\begin_inset LatexCommand \ref[Sql generation]{sqlGeneration}
\end_inset
for a more complete discussion of update row selection.
\layout Subsubsection
upshiftcols
\layout Standard
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:
\layout Code
set customer(upshiftcols) {custname custfirstname}
\layout Subsubsection
noentry
\layout Standard
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.
\layout Subsubsection
nodisplay
\layout Standard
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.
\layout Subsection
Auxiliary list window
\layout Subsubsection
list_columns, list_window
\layout Standard
If
\series bold
list_columns
\series default
and
\series bold
list_window
\series default
are set,
\series bold
sqlscreen
\series default
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
\series bold
list_columns
\series default
columns.
This is useful to get a compact display of a query's results.
\layout Standard
Clicking on a line in the list with mouse button 1 will display the correspondin
g row in the main screen.
\layout Standard
This capability will only be available if an updateindex list has been defined
for the screen (either implicitely or explicitely), see
\begin_inset LatexCommand \ref[the updateindex section]{updateindex}
\end_inset
.
The updateindex columns must be part of
\series bold
list_columns
\series default
, so that we can uniquely link back from the list to the detail screen.
\layout Standard
Example:
\layout Code
set customers(list_columns) {custname custid}
\layout Code
set customers(updateindex) custid
\layout Code
toplevel .custlist
\layout Code
set customers(list_window) .custlist
\layout Subsubsection
list_colwidths:
\layout Standard
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
\series bold
list_colwidths
\series default
entry, as a list specifying the width in characters for each column.
Exemple:
\layout Code
set customers(list_colwidths) {40 5}
\layout Standard
The widths must be specified in the same order as the columns in
\series bold
list_columns
\series default
.
\layout Subsubsection
list_lineproc:
\layout Standard
When displaying the list, SQLScreens will alternate the line's background
between white and light grey to facilitate reading.
If defined, the
\series bold
list_lineproc
\series default
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).
Exemple:
\layout Code
set customers(list_lineproc) custlineproc
\layout Standard
\series bold
list_lineproc
\series default
will then be called for each line with 3 parameters:
\layout Enumerate
The name of the
\series bold
TK
\series default
\series bold
text
\series default
window where the line is displayed.
\layout Enumerate
The
\series bold
TK text tag
\series default
name for the area associated with the line.
\layout Enumerate
The list of column values for this line.
\layout Standard
\series bold
list_lineproc
\series default
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
\series bold
wines.tcl
\series default
):
\layout Code
proc setlinecolor {w tag res} {
\layout Code
# Get the bottle count from the value list
\layout Code
set botcnt [lindex $res 0]
\layout Code
# Set the background color accordingly
\layout Code
switch $botcnt {
\layout Code
1 {$w tag configure $tag -background red}
\layout Code
2 {$w tag configure $tag -background orange}
\layout Code
3 {$w tag configure $tag -background yellow} default {$w
tag configure $tag
\backslash
\layout Code
-background green}
\layout Code
}
\layout Code
}
\layout Subsection
Miscellaneous array entries
\layout Subsubsection
hdl
\layout Standard
The package uses this entry to store the database handle.
\layout Subsubsection
initfocus
\layout Standard
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.
\layout Subsubsection
tabcolserial
\layout Standard
If there is a serial or auto_increment column,
\series bold
sqlscreen
\series default
sets its name in there.
\layout Standard
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.
\layout Standard
If the field is modifiable by the user, no special action will be taken.
\layout Standard
If the
\series bold
beforeinsert
\series default
procedure is defined for the screen, any modification is performed before
calling it, to allow for a local value allocation scheme.
\layout Subsubsection
querynum
\layout Standard
This is the select result handle.
\layout Subsubsection
sqlsc_colname_valsaved
\layout Standard
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
\begin_inset LatexCommand \ref[Sql generation]{sqlGeneration}
\end_inset
section).
\layout Section
Global customization variables
\layout Subsection
sqlscshowstmts
\layout Standard
You can set this variable to 1 or 0 to print the SQL statements to stdout
(or
\series bold
SQLSCLOG
\series default
) when they are executed.
This is not an array element but a global variable.
\layout Subsection
sqlscnobell
\layout Standard
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...
\layout Section
Linking screens
\begin_inset LatexCommand \label{linkingscreens}
\end_inset
\layout Standard
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.
\layout Subsection
sqlmasterslave
\layout Standard
This links the first screen to the second one so that a query in the first
will run a query in the second.
Example:
\layout Code
sqlmasterslave customer custid order ordercust
\layout Standard
would link the
\shape italic
customer
\shape default
and
\shape italic
order
\shape default
screens so that the
\shape italic
order
\shape default
screen is reset, the
\shape italic
ordercust
\shape default
field is set to the value of the
\shape italic
custid
\shape default
field and a query is run every time a query is run in
\shape italic
customer
\shape default
.
\layout Standard
Things are set up so that it is possible to have reciprocal links without
creating an infinite loop.
Example:
\layout Code
sqlmasterslave customer custid order ordercust
\layout Code
sqlmasterslave order ordercust customer custid
\layout Standard
is ok and would both show a customer's orders after querying in the
\shape italic
customer
\shape default
screen and an order's customer after querying in the
\shape italic
orders
\shape default
screen.
\layout Subsection
sqlslavemaster
\layout Standard
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:
\layout Code
sqlslavemaster customer custid order ordercust
\layout Standard
could be used to set the
\shape italic
ordercust
\shape default
field by querying
\shape italic
customer
\shape default
, typically while entering orders.
\layout Section
Controlling the number of button sets
\layout Standard
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
\series bold
sqcommonbuttons
\series default
routine.
\series bold
sqcommonbutons
\series default
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:
\layout Code
sqcommonbuttons .f1.buttons
\layout Standard
Will create the
\shape italic
.f1.buttons
\shape default
frame and buttons inside there.
\layout Standard
It is possible to create several sets of buttons (useful when there are
several top level frames in the application) by calling
\series bold
sqcommonbuttons
\series default
several times.
Any of these sets will control the screen which currently has the keyboard
focus.
\layout Standard
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.
\layout Standard
It would be quite easy to use completely custom buttons for an application
by setting the
\begin_inset Quotes eld
\end_inset
usecommonbuttons
\begin_inset Quotes erd
\end_inset
variable, and creating custom buttons with appropriate callbacks (look
at the sqcommonbuttons code in sqlscreens.tcl).
\layout Section
Callback routines:
\layout Standard
The following callback routine names can be defined in the array:
\layout Itemize
afterinsert
\layout Itemize
afterquery
\layout Itemize
afterupdate
\layout Itemize
afterdelete
\layout Itemize
beforeinsert
\layout Itemize
beforequery
\layout Itemize
beforeupdate
\layout Itemize
beforedelete
\layout Standard
Example:
\layout Code
set customer(beforeinsert) checkcustfields
\layout Standard
The different routines will be invoked in the following manner:
\layout Standard
For beforexxx routines:
\layout Code
routinename optype arrayname
\layout Standard
For afterxxx routines:
\layout Code
routinename optype txt arrayname
\layout Standard
Where
\family typewriter
optype
\family default
defines what's happening (like
\family typewriter
beforeinsert
\family default
,
\family typewriter
afternext
\family default
, etc...),
\family typewriter
arrayname
\family default
is the affected screen's array name, and
\family typewriter
txt
\family default
is the SQL text for afterxxx routines.
We can't pass the text to the beforexxx routines, because they may be responsib
le to modify some field values that will affect the statement !
\layout Standard
\series bold
beforeinsert
\series default
,
\series bold
afterinsert
\series default
,
\series bold
beforeupdate
\series default
,
\series bold
afterupdate, beforedelete
\series default
and
\series bold
afterdelete
\series default
will be called before and after inserting or updating data.
\layout Standard
\series bold
beforequery
\series default
and
\series bold
afterquery
\series default
will be called before and after doing a select, and
\series bold
afterquery
\series default
will also be called after the user fetches the next record, rewinds the
query, or resets the screen.
\layout Standard
If one of the beforexxx routines returns anything but 0 , the operation
will be canceled (not run).
\layout Section
Visible internal interfaces
\layout Standard
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.
\layout Itemize
\series bold
sqlscinsert
\series default
: Generate and run an INSERT statement.
\layout Itemize
\series bold
sqlscupd
\series default
: Generate and run an UPDATE statement.
\layout Itemize
\series bold
sqlscquery
\series default
: Generate and run a SELECT statement.
\layout Itemize
\series bold
sqlscdelete
\series default
: Generate and run a DELETE statement.
\layout Itemize
\series bold
sqlscnext
\series default
: Fetch the next row in the current query.
\layout Itemize
\series bold
sqlscreopen
\series default
: Rewind the current query.
\layout Itemize
\series bold
sqlscreset
\series default
: Reset all data values for the screen.
\layout Standard
In all cases, the effect will be exactly the same as the corresponding button
press.
\layout Section
Small utility routines
\layout Itemize
\series bold
sqlsc_entrywidget
\series default
\SpecialChar ~
\shape italic
arnm
\shape default
\SpecialChar ~
\shape italic
colname
\shape default
Return the name for the entry widget for
\shape italic
arnm
\shape default
and
\shape italic
colname
\shape default
.
\layout Itemize
\series bold
sqlsc_labelwidget
\series default
\SpecialChar ~
\shape italic
arnm
\shape default
\SpecialChar ~
\shape italic
colname
\shape default
Return the name for the label widget for
\shape italic
arnm
\shape default
and
\shape italic
colname
\shape default
.
\layout Chapter
SQL generation
\begin_inset LatexCommand \label{sqlGeneration}
\end_inset
\layout Standard
Every time the user presses a button like
\series bold
query
\series default
or
\series bold
update
\series default
, the program will generate a SQL statement to perform the appropriate operation
on the database.
The following paragraphs describe how the statement is generated.
\layout Section
Query
\layout Standard
The
\series bold
Query
\series default
button generate a SELECT statement.
The list of columns comes from the
\series bold
columns
\series default
entry in the input array (all the columns by default).
\layout Standard
The WHERE clause is built from all the fields that hold data (including
the hidden ones if there are any).
\layout Standard
For non character columns, the comparison operator used is =.
For character columns, it is LIKE.
\layout Standard
Example: for a screen with custid, custname, custfirstname, custsomenumber
fields, where data was entered in custname (xxx) and custsomenumber (yyy),
the statement would be:
\layout Standard
SELECT custid,custname,custfirstname,custsomenumber from customers
\newline
WHERE custname LIKE 'xxx' AND custsomenumber = yyy
\layout Standard
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
\begin_inset Quotes eld
\end_inset
>10
\begin_inset Quotes erd
\end_inset
or
\begin_inset Quotes eld
\end_inset
<>1234
\begin_inset Quotes erd
\end_inset
, or
\begin_inset Quotes eld
\end_inset
>10 and custsomenumber<20).
\layout Section
Add
\layout Standard
The
\series bold
Add
\series default
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:
\layout Standard
INSERT INTO customers(custname,custsomenumber) VALUES('xxx',yyy)
\layout Standard
There is no explicitely provided way to insert a NULL value (and certainly
none for a char field).
\layout Standard
If the table's primary key is a serial or auto_increment field, and the
corresponding field is set as
\begin_inset Quotes eld
\end_inset
noentry
\begin_inset Quotes erd
\end_inset
, the value for the field is reset to
\begin_inset Quotes eld
\end_inset
\begin_inset Quotes erd
\end_inset
before inserting to let auto_increment do its job.
\layout Standard
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.
\layout Section
Update
\layout Standard
The
\series bold
Update
\series default
button generates an UPDATE statement.
There are two issues: the WHERE clause and the values.
\layout Standard
The WHERE clause is built from the columns that were designated in the
\series bold
updateindex
\series default
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).
\layout Standard
The values in the WHERE clause are taken from those that were saved when
the last
\series bold
Query
\series default
(or
\series bold
Next
\series default
,
\series bold
Rewind
\series default
,
\series bold
Reset
\series default
) 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'll
get strange error messages about missing array entries.
\layout Standard
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).
\layout Standard
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.
\layout Section
Delete
\layout Standard
The
\series bold
Delete
\series default
button generates a DELETE statement.
The WHERE clause is built like the SELECTs, except that no LIKE operators
are used.
\layout Standard
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.
\layout Standard
For this reason, the program will create a dialog screen and ask for confirmatio
n if more than one row would be affected by a DELETE statement.
\layout Section
Update issues
\layout Standard
Any application that displays database values and allows the user to update
them has two problems:
\layout Enumerate
It must ensure that the generated UPDATE statement will really update the
row that was displayed and not many other rows in the database.
\layout Enumerate
It must ensure that the affected row has not changed since it was displayed.
\layout Standard
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
\series bold
SQLScreens
\series default
insists on having an
\shape italic
updateindex
\shape default
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.
\layout Standard
For the second issue,
\series bold
SQLScreens
\series default
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
\begin_inset Quotes eld
\end_inset
nodisplay
\begin_inset Quotes erd
\end_inset
attribute).
\layout Chapter
The tcsq low level database access layer
\layout Standard
\series bold
SQLScreens
\series default
uses an intermediate code layer to access the different databases in a
consistent fashion.
This intermediate layer is called
\series bold
tcsq
\series default
.
\layout Standard
The
\series bold
tcsq
\series default
calls which are documented here may be useful as a database access layer
for non-
\series bold
SQLScreens
\series default
applications (for any TCL script accessing the supported databases), or
in auxiliary routines inside an
\series bold
SQLScreens
\series default
application (for example, for building lists of values by querying a table).
\layout Section
Environment variables
\layout Standard
\series bold
tcsq
\series default
uses the same SQLDBTYPE environment variable as the
\series bold
SQLScreens
\series default
layer to define the database type.
\layout Section
API calls
\layout Subsection
tcsqconnect
\layout Code
tcsqconnect [host [user [passwd]]]
\layout Standard
Returns a server connection handle (
\emph on
hdl
\emph default
in the following).
Depending on the database type, it may actually connect to a server, or
do nothing (Ex: informix).
\layout Subsection
tcsquse
\layout Code
tcsquse hdl dbname
\layout Standard
Associates the connection handle
\emph on
hdl
\emph default
with database
\emph on
dbname
\emph default
.
\layout Subsection
tcsqconuse
\layout Code
tcsqconuse database [host]
\layout Standard
Utility function: connect and use.
\layout Subsection
tcsqopensel
\layout Code
tcsqopensel hdl stmt
\layout Standard
Opens a query operation.
\emph on
stmt
\emph default
is a string holding an SQL SELECT statement.
\newline
Returns a select handle (
\emph on
selhdl
\emph default
in the following).
\layout Subsection
tcsqrew
\layout Code
tcsqrew selhdl
\layout Standard
Rewinds the query associated with
\emph on
selhdl
\emph default
.
This may actually rerun the query (INFORMIX) or be purely local (MYSQL).
\layout Subsection
tcsqclosel
\layout Code
tcsqclosel selhdl
\layout Standard
Closes a query, and frees the associated resources.
\layout Subsection
tcsqnext
\layout Code
tcsqnext selhdl
\layout Standard
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.
\layout Subsection
tcsqexec
\layout Code
tcsqexec hdl stmt
\layout Standard
Executes a non-SELECT SQL statement (Ie, INSERT, DELETE, etc...)
\layout Subsection
tcsqdiscon
\layout Code
tcsqdiscon hdl
\layout Standard
Disconnects and frees resources associated with
\emph on
hdl
\emph default
.
\layout Subsection
tcsqtabinfo
\layout Code
tcsqtabinfo hdl
\layout Standard
Returns a list of the user tables in the database referenced by hdl.
\layout Subsection
tcsqcolinfo
\layout Code
tcsqcolinfo hdl tbl arnm
\layout Standard
Returns information about table
\emph on
tbl
\emph default
into the array the name of which is specified by
\emph on
arnm
\emph default
.
\layout Subsection
tcsqinsertid
\layout Standard
tcsqinsertid hdl
\layout Standard
Returns the auto_increment value for the last inserted row.
\layout Subsection
tcsqquotequote
\layout Code
tcsqquotequote s
\layout Standard
Returns a suitably escaped string, for use in sql statements.
\layout Subsection
tcsqquoteblob
\layout Code
tcsqquoteblob s
\layout Standard
Same for blobs.
\layout Section
Programming example
\layout Standard
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.
\layout Code
#!/usr/local/cdkit/isqltcl
\layout Code
package require tcsq
\layout Code
set env(SQLDBTYPE) MYSQL
\layout Code
\layout Code
set hdl [tcsqconuse mysql localhost]
\layout Code
set uname [tcsqquotequote "John O'Connell"]
\layout Code
set qry [tcsqopensel $hdl "SELECT host,user
\backslash
\layout Code
FROM user WHERE user LIKE '$uname'"]
\layout Code
\layout Code
while {[set res [tcsqnext $qry]] != {}} {
\layout Code
set host [lindex $res 0]
\layout Code
set user [lindex $res 1]
\layout Code
puts "Host: $host User: $user"
\layout Code
}
\layout Code
\layout Code
tcsqclosel $qry
\layout Code
tcsqdiscon $hdl
\layout Code
exit 0
\layout Chapter
Sample scripts
\begin_inset LatexCommand \label{samplescripts}
\end_inset
\layout Standard
Three sample scripts are provided with the package (in the
\shape italic
samples
\shape default
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.
\layout Description
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.
\layout Description
mysqldb.tcl creates a screen to access the MySQL
\begin_inset Quotes eld
\end_inset
db
\begin_inset Quotes erd
\end_inset
table.
The main goal is to show how you can arrange the fields in row-column.
Otherwise, it does nothing more than
\series bold
tablescreen.tcl.
\layout Description
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).
\layout Standard
To try
\series bold
wines.tcl
\series default
, you will have to create tables and load the sample data in a database
named
\begin_inset Quotes eld
\end_inset
wines
\begin_inset Quotes erd
\end_inset
.
The
\begin_inset Quotes eld
\end_inset
\shape italic
createloadwines.sh
\shape default
\begin_inset Quotes erd
\end_inset
shell-script will do this for you.
\layout Standard
\series bold
wines.tcl
\series default
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.
\the_end