sqlscreens.sgml
2082 lines (1617 with data), 72.8 kB
<!-- Use this header for the FreeBSD sgml toolchain -->
<!-- NOTE: the sgml version should be saved as ISO-8859-1. -->
<!DOCTYPE BOOK PUBLIC "-//FreeBSD//DTD DocBook V4.1-Based Extension//EN" [
<!-- Use this header for going XML -->
<!-- <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [ -->
<!ENTITY SQLSC "<application>SQLScreens</application>">
<!ENTITY SQLSCVERSION "1.2.1">
<!ENTITY MYSQL "<application>MySQL</application>">
<!ENTITY SQLITE "<application>SQLite</application>">
<!ENTITY SQLSCDOWN "http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html">
<!ENTITY SQLSCRDMPS "http://lesbonscomptes.com/sqlscreens/sqlscdumps.html">
]>
<book lang="en">
<bookinfo>
<title>&SQLSC;: a simple SQL screen generator</title>
<firstname>Jean-Francois</firstname>
<surname>Dockes</surname>
<surname>jean-francois.dockes@wanadoo.fr</surname>
<date>8 March 2007</date>
</bookinfo>
<chapter>
<title>Preface</title>
<para>This document describes &SQLSC; release &SQLSCVERSION;</para>
<para>&SQLSC; is a TCL/TK package allowing the easy creation of
screen forms, for querying and updating a relational
database.</para>
<para>&SQLSC; was primarily designed to work with &MYSQL; as a
backend. It also works with &SQLITE;,
and <application>ODBC</application>. It might still work
with <application>INFORMIX</application>
and <application>MSQL</application> (untested for a looong
time).</para>
<para>&SQLSC; 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.</para>
<para>If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the &MYSQL;
grant tables, you may find &SQLSC; useful :-)</para>
<para>Still interested ? Details
<link linkend="PREFACE.QUICKDESCRIPTION">follow</link>. If you are
reading this online, you can have a look at the
<ulink url="&SQLSCRDMPS;">screen dumps</ulink>.</para>
<sect1 id="PREFACE.QUICKDESCRIPTION">
<title>Quick description</title>
<para>A typical &SQLSC; 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.</para>
<para>You create each screen by listing the column names that you
want to use.</para>
<para>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
(<filename>tablescreen.tcl</filename>).</para>
<para>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).</para>
<para>&SQLSC; 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.</para>
<para>You can also:</para>
<itemizedlist>
<listitem>
<para>Create multiline text widgets to edit text blobs.</para>
</listitem>
<listitem>
<para>Display query results as a list linked to a detail
screen.</para>
</listitem>
<listitem>
<para>And do many other things described a little
further.</para>
</listitem>
</itemizedlist>
<para>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.</para>
<para>&SQLSC; 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 &SQLSC; 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.</para>
<para>There are many other bells and whistles, but also a few
drawbacks:</para>
<itemizedlist>
<listitem>
<para>You have little control over field placement. Fields are
placed in a row-column grid managed by Tk's grid geometry
manager.</para>
</listitem>
<listitem>
<para>The generated SQL is very basic, and you have little
control over it.</para>
</listitem>
<listitem>
<para>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.</para>
</listitem>
<listitem>
<para>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).</para>
</listitem>
<listitem>
<para>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 .</para>
</listitem>
<listitem>
<para>The screens are not pretty !</para>
</listitem>
<listitem>
<para>Etc... Etc...</para>
</listitem>
</itemizedlist>
<para>This said, the software is free and we are open to suggestions
to improve it.</para>
<para>If you want a quick idea of what it does, do the installation,
have a look at the <filename>tablescreen.tcl</filename> file
in the samples directory, set the host and user name
(depending on the backend type), and point it to any table,
like:</para>
<programlisting>
tablescreen.tcl dbname tablename
</programlisting>
<para>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).</para>
</sect1>
</chapter>
<chapter id="INSTALLATION">
<title>Installation</title>
<sect1 id="INSTALLATION.EXTERNAL">
<title>External software needed</title>
<para>To use &SQLSC;, you will need a number of external software
packages:</para>
<itemizedlist>
<listitem>
<para>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
<ulink url="http://tcl.activestate.com/">the main TCL
site</ulink>
. TCL and TK are very easy to build.</para>
</listitem>
</itemizedlist>
<para>In order to access the databases, the basic TCL interpreter
must be augmented with a database access module:</para>
<itemizedlist>
<listitem>
<para>For &MYSQL;, a modified version of the msqltcl package by
Hakan Soderstrom is included in the distribution
(<filename>mysqltcl.c</filename>).</para>
</listitem>
<listitem>
<para>The original msqltcl can be used for accessing MSQL
databases.</para>
</listitem>
<listitem>
<para>For UNIX ODBC you will need
<ulink url="http://sourceforge.net/projects/tclodbc">
tclodbc</ulink>, and:</para>
<itemizedlist>
<listitem>
<para>An ODBC driver manager: under UNIX, we tested
<ulink url="http://www.iodbc.org/">iODBC</ulink>, but
<ulink url="http://www.unixodbc.org/">unixODBC</ulink>
should probably be OK too.</para>
</listitem>
<listitem>
<para>The driver for your database. For &MYSQL;, this would be
<ulink url="http://www.mysql.com/products/connector/odbc/">
myodbc</ulink>.</para>
</listitem>
</itemizedlist>
</listitem>
<listitem>
<para>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 <ulink url="&SQLSCDOWN;">download
page</ulink>.</para>
</listitem>
</itemizedlist>
<para>The &SQLSC; <ulink url="&SQLSCDOWN;">download page</ulink>
has pointers or copies for some of these elements.</para>
<para>Only the direct &MYSQL; and &SQLITE; backends have been
tested lately, and there may be minor problems with the
others.</para>
</sect1>
<sect1>
<title>Installation</title>
<para>The package comes as a gzipped tar file named something like
<filename>sqlscreens-X.Y.Z.tar.gz</filename>.</para>
<para>Unpack the file:
<programlisting>gunzip < <replaceable>sqlscreens-X.Y.Z.tar.gz</replaceable> | tar xvf -
</programlisting>
This will create a top directory named
<filename>sqlscreens-X.Y.Z</filename>.</para>
<para>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...</para>
<para>First, if needed, compile and install TCL and TK (untar; cd
tcl8.../unix; configure; make; make install, same for tk).</para>
<para>The next step is to add database-access capability to the
standard TCL/TK wish interpreter. This can be done in several
ways:
<itemizedlist>
<listitem>By statically linking the database access module
(e.g. mysqltcl or isqltcl) with the
interpreter.</listitem>
<listitem>By using the <application>TCL</application> load
facility and a shared library. The dynamic version sometimes
need some manual tweaking to work.</listitem>
<listitem>By loading an external package that itself does
whatever is needed (e.g. <literal>package require
sqlite3)</literal></listitem>
</itemizedlist>
</para>
<para>&MYSQL; support is managed by
the <filename>Makefile</filename> in the &SQLSC;
directory. You can disable &MYSQL; support (and the need to
install the client library) by using
option <literal>--disable-mysql</literal> to
the <command>configure</command> script.</para>
<para>For &SQLITE;, just install the &SQLITE; TCL package (which
may be named something
like <literal>libsqlite3-tcl</literal>).</para>
<para>For <application>ODBC</application>, you should first
install the driver manager, the driver(s) you need and the
tclodbc TCL extension. Follow the instructions in each
package.</para>
<para>For using <application>isqltcl</application> and INFORMIX,
follow the installation instructions inside
the <application>isqltcl</application> package to generate the
interpreter.</para>
<sect2>
<title>Generating a wish interpreter with &MYSQL; support:</title>
<para>You do not need this if you are working with &SQLITE;
only. Just give a <literal>--disable-mysql</literal>
argument to <command>configure</command>.</para>
<para>Both the static and dynamic load methods are supported by
the &SQLSC; 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.</para>
<para>The configuration script use
the <command>mysql_config</command> command to locate the
&MYSQL; client library and include files. It should be
accessible in your <literal>PATH</literal>.</para>
<para>When you are ready:</para>
<orderedlist>
<listitem>
<para><literal>cd</literal> to the &SQLSC; directory, and type
<literal>./configure</literal>.</para>
</listitem>
<listitem>
<para>Type <literal>make</literal> to compile and link the
<command>mysqlwish</command> interpreter and the shared
library. The shared library link may produce error
messages, see below.</para>
</listitem>
<listitem>
<para>Type <literal>make install</literal> to install the package.
This will create a <filename>$TK_PREFIX/lib/sqlsc</filename>
directory and copy the shared library and TCL code there. It
will also copy <filename>mysqlwish</filename> to
<filename>$TK_PREFIX/bin</filename>.
TK_PREFIX is taken from the tkConfig.sh script for your
<command>wish</command>
interpreter. You can change it by typing
<programlisting>make install TK_PREFIX=<replaceable>yourdest</replaceable></programlisting>
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 <literal>make install-static</literal> 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.</para>
</listitem>
</orderedlist>
<para>If you use TCL with other statically linked extensions,
and want to use the same interpreter with &SQLSC;, you will
have to add the <function>Mysqltcl_Init</function> call to
your usual <filename>tkAppInit.c</filename> file and modify
your <filename>Makefile</filename> to link with
<filename>mysqltcl.o</filename>. You have probably been
through this already. Have a look at the included
<filename>tkAppinit.c</filename></para>
</sect2>
</sect1>
</chapter>
<chapter id="USING">
<title>Using &SQLSC;</title>
<para>The following is organized more like a reference manual, there
is little tutorial material. You may want to look at the
<link linkend="SAMPLES">sample scripts</link>
to get a quick idea.</para>
<sect1 id="USING.ENVIR">
<title>Environment variables</title>
<para>The <literal>SQLDBTYPE</literal> environment variable
decides what database code is going to be used. It has several
possible values:</para>
<itemizedlist>
<listitem>
<para><literal>MYSQL</literal> to access a &MYSQL; database.</para>
</listitem>
<listitem>
<para><literal>SQLITE3</literal> to access a &SQLITE; database.</para>
</listitem>
<listitem>
<para><literal>ODBC</literal> to use an ODBC driver manager.</para>
</listitem>
<listitem>
<para><literal>INFORMIX</literal> to access an INFORMIX
database.</para>
</listitem>
<listitem>
<para><literal>MSQL</literal> to access an MSQL database.</para>
</listitem>
</itemizedlist>
<para>The default if the variable is not set is to use &MYSQL;. Don't
depend on it.</para>
<para>This variable can be set inside the script, before the
first call to <function>sqlscreen</function>. (It is set to
<literal>MYSQL</literal>
inside the sample scripts). Of course, the value of this
variable must be consistent with what extensions are available
to the TCL interpreter (See <link
linkend="INSTALLATION">installation</link>).</para>
<para>The <literal>SQLSCLOG</literal> environment variable can
be set to the name of a file where &SQLSC; will log the SQL
statements it executes. The default is to log to stdout.</para>
<para>The <literal>SQLSCHOST</literal>,
<literal>SQLSCUSER</literal>, and
<literal>SQLSCPASSWORD</literal> variables can be used to set
the connection parameters. These are only used by the sample
scripts, not the core package.</para>
<para><literal>MYSQL_TCP_PORT</literal> should be used if you
need to change the default &MYSQL; connection port.</para>
</sect1>
<sect1>
<title>Buttons</title>
<para>Each screen has a set of buttons to perform the following
operations:</para>
<variablelist>
<varlistentry>
<term>Query</term>
<listitem>
<para>starts a SELECT. The WHERE clause is built with the
values currently shown on the screen and the nodisplay
fields).</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Next</term>
<listitem>
<para>fetches the next record in the current query</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Rewind</term>
<listitem>
<para>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.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Reset</term>
<listitem>
<para>clears all visible and hidden fields in the
screen.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Add</term>
<listitem>
<para>inserts the current values. There is special handling
for auto_increment fields, see
<link linkend="SQLGENERATION">SQL generation</link>
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Update</term>
<listitem>
<para>updates the row according to the current values (how the
WHERE clause is built is described later, see
<link linkend="PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX">
updateindex</link>
and
<link linkend="SQLGENERATION">Sql generation</link>).</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Delete</term>
<listitem>
<para>deletes the row(s) selected by the current values. It
will prompt for confirmation if more than one row would be
affected.</para>
</listitem>
</varlistentry>
</variablelist>
<para>The Update and Add buttons may not exist on all screens (some
screens may be set up only for querying).</para>
<para>Delete is not created by default (See
<link linkend="PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE">
allowdelete</link>
).</para>
<para>The
<link linkend="SQLGENERATION">SQL generation</link>
section describes how the SQL statements are generated for the
different actions.</para>
</sect1>
<sect1>
<title>Keyboard shortcuts</title>
<para>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:</para>
<variablelist>
<varlistentry>
<term><TAB></term>
<listitem>
<para>goes to the next field in the screen.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><CR></term>
<listitem>
<para>in any field will start a SELECT.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><ESC>n</term>
<listitem>
<para>will fetch the next row.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><ESC>r</term>
<listitem>
<para>will rewind the query.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><ESC>a</term>
<listitem>
<para>will start an INSERT.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><ESC>u</term>
<listitem>
<para>will start an UPDATE.</para>
</listitem>
</varlistentry>
<varlistentry>
<term><ESC>w</term>
<listitem>
<para>will reset the current screen (clear all fields).</para>
</listitem>
</varlistentry>
</variablelist>
<para>The Update and Add shortcuts will have no effects in query
only screens. There is no Delete shortcut.</para>
</sect1>
<sect1>
<title>Special characters in fields</title>
<para>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:</para>
<para>If you enter <literal>>10</literal> in a field named
<literal>quantity</literal>, a <literal>quantity >
10</literal> condition
will be inserted in the WHERE clause. You could also enter
<literal>>10 AND quantity < 20</literal>, or whatever
condition you need. (See also
<link linkend="SQLGENERATION">the paragraph about SQL
generation</link>
).</para>
</sect1>
</chapter>
<chapter id="PROGRAMMING">
<title>Programming Interface</title>
<sect1 id="PROGRAMMING.OVERVIEW">
<title>Overview</title>
<para>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.</para>
<para>The basic idea is that you set values in the array and then
call <literal>sqlscreen arrayname</literal> to create the
screen.</para>
<para>The <emphasis>application</emphasis> can be reduced to a
main program to initialize and call &SQLSC;, or it may more
complex and use &SQLSC; as a utility module.</para>
<para>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
<literal>mytable</literal> in database <literal>test</literal>
on the local host might look like the following:</para>
<programlisting>
#!/usr/local/bin/wish8.4
package require sqlsc
set mytable(window) .t
set mytable(database) test
set mytable(table) mytable
sqlscreen mytable
</programlisting>
<para>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. </para>
<para>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”.</para>
<para>&SQLSC; stores the values for the field corresponding to a
column as
<literal><replaceable>arrayname</replaceable>(sqlsc_<replaceable>column</replaceable>_value)</literal>
(Ex: <literal>customer(sqlsc_custid_value)</literal>). 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).</para>
<para>The first release used to store the values as
<literal><replaceable>arrayname</replaceable>(<replaceable>column</replaceable>)</literal>. 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).</para>
<para>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.</para>
<para>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.</para>
<para>When you are finished with the screen, you can call
<programlisting>sqlscreendelete arrayname</programlisting> to cleanup and release all resources
(array, windows, database connections). Most applications will exit
instead. <function>sqlscreendelete</function> 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).</para>
</sect1>
<sect1 id="PROGRAMMING.INIT">
<title>Initialization and termination</title>
<sect2>
<title>sqlscreen</title>
<para>To create a screen, you set values inside a TCL array
(See the following section:
<link linkend="PROGRAMMING.ARRAYENTRIES">Interface array entries</link>
), then perform creation as follows:</para>
<programlisting>
sqlscreen yourarrayname
</programlisting>
<para>Note that <function>sqlscreen</function> 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:</para>
<programlisting>
frame .f
set myarray(window) .f.scr
... set other fields
sqlscreen myarray
# screen still not visible
pack .f #screen appears
</programlisting>
<para>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.</para>
<para><function>sqlscreen</function> optionally takes a
second parameter. If the value is <literal>h</literal>, the
fields will be arranged horizontally instead of
vertically. There are other ways to do this(see
<link linkend="PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS">columns</link>),
but it can still be useful in some cases.</para>
</sect2>
<sect2>
<title>sqlscreendelete</title>
<para>This procedure will destroy all resources associated with an
sqlscreen (windows, database connections and the array itself).
Call it as:</para>
<programlisting>
sqlscreendelete arrayname
</programlisting>
</sect2>
<sect2 id="PROGRAMMING.INIT.FONT">
<title>Setting fonts for &SQLSC; applications</title>
<para>The font used by the screen can be set by
<link linkend="PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT">
setting the <literal>font</literal> array entry</link>. 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:</para>
<programlisting>
option add *font {Arial 10}
option add *Button*font {Arial 10 bold}
</programlisting>
<para>Alternatively, the font could be set in the option
database (ie: <filename>.Xdefault</filename> under
Unix). Example:</para>
<programlisting>
wines*font: Arial 10
wines*Button*font: Arial 10 bold
</programlisting>
<para>In the latter case, the program name should not include
a <literal>.tcl</literal> extension, else the dot seems to cause
problems in the options database (use
<literal>wines</literal>, not
<literal>wines.tcl</literal>).</para>
</sect2>
</sect1>
<sect1 id="PROGRAMMING.ARRAYENTRIES">
<title>Interface array entries</title>
<sect2 id="PROGRAMMING.ARRAYENTRIES.GENERAL">
<title>General parameters</title>
<sect3>
<title>window</title>
<para>This defines the TK frame name where the screen will be
created. Example:</para>
<programlisting>
frame .f1
set customer(window) .f1.cust
</programlisting>
<para>or just the following to create the window in the top
one:</para>
<programlisting>
set customer(window) .cust
</programlisting>
<para>This entry must be a valid TK window name: for exemple it
cannot start with an upper case character.</para>
<para>The window must not exist before calling sqlscreen, which
will create it. Its parents must exist.</para>
</sect3>
<sect3>
<title>database</title>
<para>This defines the database name.</para>
<programlisting>
set customer(database) orderdb
</programlisting>
</sect3>
<sect3>
<title>sqlcpasswd, sqlschost, sqlscuser</title>
<para>These define the user name, host and password for the
connection to the database server. These are all
optional.</para>
</sect3>
<sect3>
<title>table</title>
<para>This defines the table name. Example:</para>
<programlisting>
set customer(table) customers
</programlisting>
<para>It is also possible to display fields from several tables
in one screen:</para>
<programlisting>
set custorder(table) {customers orders}
</programlisting>
<para>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
<link linkend="PROGRAMMING.LINKING">Linking screens</link>
).</para>
</sect3>
<sect3>
<title>joinclause</title>
<para>In case fields from several tables are displayed in a
screen, &SQLSC; needs to know how to join the tables when
performing a SELECT. This is defined by the joinclause array
entry. Exemple:</para>
<programlisting>
set custorder(joinclause) {customers.custid = orders.ordercustid}
</programlisting>
</sect3>
<sect3 id="PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS">
<title>columns</title>
<para>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.</para>
<para>Example for specifying the column names:</para>
<programlisting>
set customer(columns) {custid custname}
</programlisting>
<para>If several tables are used, it may be necessary to qualify
the column names if they are not unique:</para>
<programlisting>
set custorder(columns) {customers.custid customers.custname}
</programlisting>
<para>By default, all fields will be displayed in one column.
You can get them to be displayed in one line by calling
<function>sqlscreen</function> as
<programlisting>
sqlscreen <replaceable>arrayname</replaceable> h
</programlisting>
You can also insert <emphasis>line breaks</emphasis> by
inserting newline caracters in the column list, like:</para>
<programlisting>
set arrayname(columns) {
host "\n"
user db "\n"
select_priv insert_priv update_priv "\n"
delete_priv create_priv drop_priv
}
</programlisting>
<para>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).</para>
</sect3>
</sect2>
<sect2 id="PROGRAMMING.ARRAYENTRIES.SATTRIBUTES">
<title>Screen attributes</title>
<sect3>
<title>queryonly</title>
<para>If this is set, the screen will not have “add”
and “update” buttons, you will only be able to
select data. Example:</para>
<programlisting>
set customer(queryonly) {}
</programlisting>
<para>The value has no importance, just setting the array entry
(even to <literal>no</literal>) creates a screen for query
only.</para>
</sect3>
<sect3 id="PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE">
<title>allowdelete</title>
<para>If this entry is set, and <literal>queryonly</literal>
is not set a <guilabel>Delete</guilabel> button will be
created.</para>
</sect3>
<sect3 id="PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT">
<title>font</title>
<para>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 <link
linkend="PROGRAMMING.INIT.FONT">several other
ways</link> to set the application font.</para>
</sect3>
<sect3>
<title>notitle</title>
<para>Suppresses the screen title. This spares a little space if
your screen is crowded.</para>
</sect3>
<sect3>
<title>nobuttons</title>
<para>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.</para>
</sect3>
<sect3>
<title>graphicbuttons</title>
<para>If this is set, and the <application>Tix</application>
package is available, the buttons will be created with icons
instead of textual labels.</para>
</sect3>
</sect2>
<sect2 id="PROGRAMMING.ARRAYENTRIES.FATTRIBUTES">
<title>Field attributes</title>
<sect3>
<title>Column type and length</title>
<para>The
<literal>sqlsc_<replaceable>colname</replaceable>_len</literal>
and
<literal>sqlsc_<replaceable>colname</replaceable>_type</literal>
entries are normally created by the
package, you do not need to set them. For character columns, you
can set
<literal>sqlsc_<replaceable>colname</replaceable>_len</literal>
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:</para>
<programlisting>
set customer(sqlsc_custname_len) 20
</programlisting>
<para>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.</para>
<para>In any case, &SQLSC; 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).</para>
</sect3>
<sect3>
<title>autopercent</title>
<para>The <literal>autopercentboth</literal>,
<literal>autopercentleft</literal>,
<literal>autopercentright</literal>
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:</para>
<programlisting>
set customer(autopercentright) {custname}
</programlisting>
<para>would let you query by entering just the beginning of the
name, without having to reach for the shift key to type
'%'.</para>
</sect3>
<sect3>
<title>texts</title>
<para>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:</para>
<programlisting>
set product(texts) {{description 20 70} {notice 10 70 t}}
</programlisting>
<para>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:</para>
<variablelist>
<varlistentry>
<term>t</term>
<listitem>
<para>will display a label (column name) above the text
area</para>
</listitem>
</varlistentry>
<varlistentry>
<term>l</term>
<listitem>
<para>will display a label on the left of the text
area</para>
</listitem>
</varlistentry>
</variablelist>
<para>By default, no label will be displayed for text
fields.</para>
<para>&SQLSC; will handle quoting and unquoting the blob
contents.</para>
<para>Text entries will NOT be validated for maximum length
against the database field width.</para>
<para>There is an exemple of texts use in the wines.tcl sample
application.</para>
</sect3>
<sect3>
<title>choices</title>
<para>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.</para>
<para>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:</para>
<programlisting>
set customer(choices) {
custtype custtypelist
custgender custgenderlist
}
</programlisting>
<para>Would specify that the <literal>custtype</literal> and
<literal>custgender</literal> 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.</para>
<para>A simple list lists the possible values (would you believe
this ?). Ex:</para>
<programlisting>
set custtypelist {normal distributor internal}
</programlisting>
<para><literal>normal</literal>,
<literal>distributor</literal> and
<literal>internal</literal> will be both displayed on the
screen and used for querying or updating the
database.</para>
<para>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:</para>
<programlisting>
set custgenderlist {{unknown 0} {female 1} {male 2}}
</programlisting>
<para>With this list, the menu would display
<literal>unknown</literal>, <literal>male</literal>,
<literal>female</literal>, but the values used for the
database would be 0, 1, 2.</para>
<para>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.</para>
<para>There are exemples of use (both automatic and explicit) in
the wines.tcl sample program.</para>
</sect3>
<sect3>
<title>ordercols</title>
<para>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:</para>
<programlisting>
set customer(ordercols) "custid desc, custname"
</programlisting>
</sect3>
<sect3 id="PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX">
<title>updateindex</title>
<para>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.</para>
<para>If neither <literal>columns</literal> nor
<literal>updateindex</literal> are set before calling
<function>sqlscreen</function>, &SQLSC; will try to make up
an <literal>updateindex</literal> by using a serial column
or primary key if one is found.</para>
<para>If <literal>columns</literal> is set, and not
<literal>updateindex</literal>, the latter is automatically
generated only if the primary key is completely included in
the column list.</para>
<para>If <literal>updateindex</literal> 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).</para>
<para>If no <literal>updateindex</literal> list finally
exists, the screen will have no <guilabel>Update</guilabel>
button.</para>
<para>See
<link linkend="SQLGENERATION">Sql generation</link>
for a more complete discussion of update row selection.</para>
</sect3>
<sect3>
<title>upshiftcols</title>
<para>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:</para>
<programlisting>
set customer(upshiftcols) {custname custfirstname}
</programlisting>
</sect3>
<sect3>
<title>noentry</title>
<para>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.</para>
</sect3>
<sect3>
<title>nodisplay</title>
<para>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.</para>
</sect3>
</sect2>
<sect2>
<title>Auxiliary list window</title>
<sect3>
<title>list_columns, list_window</title>
<para>If <literal>list_columns</literal> and
<literal>list_window</literal> are set,
<function>sqlscreen</function> 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
<literal>list_columns</literal> columns. This is useful to
get a compact display of a query's results.</para>
<para>Clicking on a line in the list with mouse button 1 will
display the corresponding row in the main screen.</para>
<para>This capability will only be available if an
<literal>updateindex</literal> list has been defined for the
screen (either implicitely or explicitely), see <link
linkend="PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX">the
updateindex section</link>. The
<literal>updateindex</literal> columns must be part of
<literal>list_columns</literal>, so that we can uniquely
link back from the list to the detail screen.</para>
<para>Example:</para>
<programlisting>
set customers(list_columns) {custname custid}
set customers(updateindex) custid
toplevel .custlist
set customers(list_window) .custlist
</programlisting>
</sect3>
<sect3>
<title>list_colwidths:</title>
<para>&SQLSC; 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 <literal>list_colwidths</literal> entry, as a list
specifying the width in characters for each column. Example:</para>
<programlisting>
set customers(list_colwidths) {40 5}
</programlisting>
<para>The widths must be specified in the same order as the
columns in <literal>list_columns</literal>.</para>
</sect3>
<sect3>
<title>list_lineproc:</title>
<para>When displaying the list, &SQLSC; will alternate the
line's background between white and light grey to facilitate
reading. If defined, the <literal>list_lineproc</literal>
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:</para>
<programlisting>
set customers(list_lineproc) custlineproc
</programlisting>
<para><literal>list_lineproc</literal> will then be called
for each line with 3 parameters:</para>
<orderedlist>
<listitem>
<para>The name of the TK text window where the line is
displayed.</para>
</listitem>
<listitem>
<para>The TK text tag name for the area associated with the
line.</para>
</listitem>
<listitem>
<para>The list of column values for this line.</para>
</listitem>
</orderedlist>
<para><literal>list_lineproc</literal> 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):</para>
<programlisting>
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}
}
}
</programlisting>
</sect3>
</sect2>
<sect2>
<title>Miscellaneous array entries</title>
<sect3>
<title>hdl</title>
<para>The package uses this entry to store the database
handle.</para>
</sect3>
<sect3>
<title>initfocus</title>
<para>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.</para>
</sect3>
<sect3>
<title>tabcolserial</title>
<para>If there is a serial or auto_increment column, sqlscreen
sets its name in there.</para>
<para>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.</para>
<para>If the field is modifiable by the user, no special action
will be taken.</para>
<para>If the beforeinsert procedure is defined for the screen,
any modification is performed before calling it, to allow for a
local value allocation scheme.</para>
</sect3>
<sect3>
<title>querynum</title>
<para>This is the select result handle.</para>
</sect3>
<sect3>
<title>sqlsc_colname_valsaved</title>
<para>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
<link linkend="SQLGENERATION">Sql generation</link>
section).</para>
</sect3>
</sect2>
</sect1>
<sect1>
<title>Global customization variables</title>
<sect2>
<title>sqlscshowstmts</title>
<para>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.</para>
</sect2>
<sect2>
<title>sqlscnobell</title>
<para>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...</para>
</sect2>
</sect1>
<sect1 id="PROGRAMMING.LINKING">
<title>Linking screens</title>
<para>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.</para>
<sect2>
<title>sqlmasterslave</title>
<para>This links the first screen to the second one so that a
query in the first will run a query in the second. Example:</para>
<programlisting>
sqlmasterslave customer custid order ordercust
</programlisting>
<para>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.</para>
<para>Things are set up so that it is possible to have reciprocal
links without creating an infinite loop. Example:</para>
<programlisting>
sqlmasterslave customer custid order ordercust
sqlmasterslave order ordercust customer custid
</programlisting>
<para>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.</para>
</sect2>
<sect2>
<title>sqlslavemaster</title>
<para>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:</para>
<programlisting>
sqlslavemaster customer custid order ordercust
</programlisting>
<para>could be used to set the ordercust field by querying
customer, typically while entering orders.</para>
</sect2>
</sect1>
<sect1>
<title>Controlling the number of button sets</title>
<para>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:</para>
<programlisting>
sqcommonbuttons .f1.buttons
</programlisting>
<para>Will create the .f1.buttons frame and buttons inside
there.</para>
<para>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.</para>
<para>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.</para>
<para>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).</para>
</sect1>
<sect1>
<title>Callback routines:</title>
<para>The following callback routine names can be defined in the
array:</para>
<itemizedlist>
<listitem>
<para>afterinsert</para>
</listitem>
<listitem>
<para>afterquery</para>
</listitem>
<listitem>
<para>afterupdate</para>
</listitem>
<listitem>
<para>afterdelete</para>
</listitem>
<listitem>
<para>beforeinsert</para>
</listitem>
<listitem>
<para>beforequery</para>
</listitem>
<listitem>
<para>beforeupdate</para>
</listitem>
<listitem>
<para>beforedelete</para>
</listitem>
</itemizedlist>
<para>Example:</para>
<programlisting>
set customer(beforeinsert) checkcustfields
</programlisting>
<para>The different routines will be invoked in the following
manner:</para>
<para>For beforexxx routines:</para>
<programlisting>
routinename optype arrayname
</programlisting>
<para>For afterxxx routines:</para>
<programlisting>
routinename optype txt arrayname
</programlisting>
<para>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 !</para>
<para>beforeinsert, afterinsert, beforeupdate, afterupdate,
beforedelete and afterdelete will be called before and after
inserting or updating data.</para>
<para>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.</para>
<para>If one of the beforexxx routines returns anything but 0 , the
operation will be canceled (not run).</para>
</sect1>
<sect1>
<title>Visible internal interfaces</title>
<para>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.</para>
<itemizedlist>
<listitem>
<para>sqlscinsert: Generate and run an INSERT statement.</para>
</listitem>
<listitem>
<para>sqlscupd: Generate and run an UPDATE statement.</para>
</listitem>
<listitem>
<para>sqlscquery: Generate and run a SELECT statement.</para>
</listitem>
<listitem>
<para>sqlscdelete: Generate and run a DELETE statement.</para>
</listitem>
<listitem>
<para>sqlscnext: Fetch the next row in the current query.</para>
</listitem>
<listitem>
<para>sqlscreopen: Rewind the current query.</para>
</listitem>
<listitem>
<para>sqlscreset: Reset all data values for the screen.</para>
</listitem>
</itemizedlist>
<para>In all cases, the effect will be exactly the same as the
corresponding button press.</para>
</sect1>
<sect1>
<title>Small utility routines</title>
<itemizedlist>
<listitem>
<para>sqlsc_entrywidget arnm colname Return the name
for the entry widget for arnm and colname.</para>
</listitem>
<listitem>
<para>sqlsc_labelwidget arnm colname Return the name
for the label widget for arnm and colname.</para>
</listitem>
</itemizedlist>
</sect1>
</chapter>
<chapter id="SQLGENERATION">
<title>SQL generation</title>
<para>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.</para>
<sect1>
<title>Query</title>
<para>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).</para>
<para>The WHERE clause is built from all the fields that hold data
(including the hidden ones if there are any).</para>
<para>For non character columns, the comparison operator used is =.
For character columns, it is LIKE.</para>
<para>Example: for a screen with custid, custname, custfirstname,
custsomenumber fields, where data was entered in custname (xxx) and
custsomenumber (yyy), the statement would be:</para>
<para>SELECT custid,custname,custfirstname,custsomenumber from
customers WHERE custname LIKE 'xxx' AND custsomenumber = yyy</para>
<para>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).</para>
</sect1>
<sect1>
<title>Add</title>
<para>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:</para>
<para>INSERT INTO customers(custname,custsomenumber)
VALUES('xxx',yyy)</para>
<para>There is no explicitely provided way to insert a NULL value
(and certainly none for a char field).</para>
<para>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.</para>
<para>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.</para>
</sect1>
<sect1>
<title>Update</title>
<para>The Update button generates an UPDATE statement. There are two
issues: the WHERE clause and the values.</para>
<para>The WHERE clause is built from the columns that were
designated in the updateindex list (if no such list was explicitely
indicated, &SQLSC; 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).</para>
<para>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.</para>
<para>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).</para>
<para>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.</para>
</sect1>
<sect1>
<title>Delete</title>
<para>The Delete button generates a DELETE statement. The WHERE
clause is built like the SELECTs, except that no LIKE operators are
used.</para>
<para>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.</para>
<para>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.</para>
</sect1>
<sect1>
<title>Update issues</title>
<para>Any application that displays database values and allows the
user to update them has two problems:</para>
<orderedlist>
<listitem>
<para>It must ensure that the generated UPDATE statement will
really update the row that was displayed and not many other rows
in the database.</para>
</listitem>
<listitem>
<para>It must ensure that the affected row has not changed since
it was displayed.</para>
</listitem>
</orderedlist>
<para>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 &SQLSC; 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.</para>
<para>For the second issue, &SQLSC; 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).</para>
</sect1>
</chapter>
<chapter>
<title>The tcsq low level database access layer</title>
<para>&SQLSC; uses an intermediate code layer to access the
different databases in a consistent fashion. This intermediate layer
is called tcsq.</para>
<para>The tcsq calls which are documented here may be useful as a
database access layer for non-&SQLSC; applications (for any TCL
script accessing the supported databases), or in auxiliary routines
inside an &SQLSC; application (for example, for building lists of
values by querying a table).</para>
<sect1>
<title>Environment variables</title>
<para>tcsq uses the same SQLDBTYPE environment variable as the
&SQLSC; layer to define the database type.</para>
</sect1>
<sect1>
<title>API calls</title>
<sect2>
<title>tcsqconnect</title>
<programlisting>
tcsqconnect [host [user [passwd]]
</programlisting>
<para>Returns a server connection handle (
<emphasis>hdl</emphasis>
in the following). Depending on the database type, it may actually
connect to a server, or do nothing (Ex: informix).</para>
</sect2>
<sect2>
<title>tcsquse</title>
<programlisting>
tcsquse hdl dbname
</programlisting>
<para>Associates the connection handle
<emphasis>hdl</emphasis>
with database
<emphasis>dbname</emphasis>
.</para>
</sect2>
<sect2>
<title>tcsqconuse</title>
<programlisting>
tcsqconuse database [host]
</programlisting>
<para>Utility function: connect and use.</para>
</sect2>
<sect2>
<title>tcsqopensel</title>
<programlisting>
tcsqopensel hdl stmt
</programlisting>
<para>Opens a query operation.
<emphasis>stmt</emphasis>
is a string holding an SQL SELECT statement. Returns a select
handle (
<emphasis>selhdl</emphasis>
in the following).</para>
</sect2>
<sect2>
<title>tcsqrew</title>
<programlisting>
tcsqrew selhdl
</programlisting>
<para>Rewinds the query associated with
<emphasis>selhdl</emphasis>
. This may actually rerun the query (INFORMIX) or be purely local
(MYSQL).</para>
</sect2>
<sect2>
<title>tcsqclosel</title>
<programlisting>
tcsqclosel selhdl
</programlisting>
<para>Closes a query, and frees the associated resources.</para>
</sect2>
<sect2>
<title>tcsqnext</title>
<programlisting>
tcsqnext selhdl
</programlisting>
<para>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.</para>
</sect2>
<sect2>
<title>tcsqexec</title>
<programlisting>
tcsqexec hdl stmt
</programlisting>
<para>Executes a non-SELECT SQL statement (Ie, INSERT, DELETE,
etc...)</para>
</sect2>
<sect2>
<title>tcsqdiscon</title>
<programlisting>
tcsqdiscon hdl
</programlisting>
<para>Disconnects and frees resources associated with
<emphasis>hdl</emphasis>
.</para>
</sect2>
<sect2>
<title>tcsqtabinfo</title>
<programlisting>
tcsqtabinfo hdl
</programlisting>
<para>Returns a list of the user tables in the database referenced
by hdl.</para>
</sect2>
<sect2>
<title>tcsqcolinfo</title>
<programlisting>
tcsqcolinfo hdl tbl arnm
</programlisting>
<para>Returns information about table
<emphasis>tbl</emphasis>
into the array the name of which is specified by
<emphasis>arnm</emphasis>
.</para>
</sect2>
<sect2>
<title>tcsqinsertid</title>
<para>tcsqinsertid hdl</para>
<para>Returns the auto_increment value for the last inserted
row.</para>
</sect2>
<sect2>
<title>tcsqquotequote</title>
<programlisting>
tcsqquotequote s
</programlisting>
<para>Returns a suitably escaped string, for use in sql
statements.</para>
</sect2>
<sect2>
<title>tcsqquoteblob</title>
<programlisting>
tcsqquoteblob s
</programlisting>
<para>Same for blobs.</para>
</sect2>
</sect1>
<sect1>
<title>Programming example</title>
<para>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.</para>
<programlisting>
#!/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
</programlisting>
</sect1>
</chapter>
<chapter id="SAMPLES">
<title>Sample scripts</title>
<para>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.</para>
<variablelist>
<varlistentry>
<term>tablescreen.tcl</term>
<listitem>
<para>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.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>mysqldb.tcl</term>
<listitem>
<para>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.</para>
</listitem>
</varlistentry>
<varlistentry>
<term>wines.tcl</term>
<listitem>
<para>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).</para>
</listitem>
</varlistentry>
</variablelist>
<para>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.</para>
<para>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.</para>
</chapter>
</book>