<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta name="generator" content=
"HTML Tidy for Linux (vers 25 March 2009), see www.w3.org">
<meta http-equiv="Content-Type" content=
"text/html; charset=us-ascii">
<title>SQLScreens: a simple SQL screen generator</title>
<link rel="stylesheet" type="text/css" href="docbook-xsl.css">
<meta name="generator" content="DocBook XSL Stylesheets V1.76.1">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084"
alink="#0000FF">
<div lang="en" class="book" title=
"SQLScreens: a simple SQL screen generator">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="idp268176" id=
"idp268176"></a><span class=
"application">SQLScreens</span>: a simple SQL screen
generator</h1>
</div>
</div>
<hr>
</div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="chapter">1. <a href=
"#idp2783584">Preface</a></span></dt>
<dd>
<dl>
<dt><span class="sect1">1.1. <a href=
"#PREFACE.QUICKDESCRIPTION">Quick
description</a></span></dt>
</dl>
</dd>
<dt><span class="chapter">2. <a href=
"#INSTALLATION">Installation</a></span></dt>
<dd>
<dl>
<dt><span class="sect1">2.1. <a href=
"#INSTALLATION.EXTERNAL">External software
needed</a></span></dt>
<dt><span class="sect1">2.2. <a href=
"#idp143456">Installation</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">2.2.1. <a href=
"#idp5635776">Generating a wish interpreter with
<span class="application">MySQL</span>
support:</a></span></dt>
</dl>
</dd>
</dl>
</dd>
<dt><span class="chapter">3. <a href="#USING">Using
<span class="application">SQLScreens</span></a></span></dt>
<dd>
<dl>
<dt><span class="sect1">3.1. <a href=
"#USING.ENVIR">Environment variables</a></span></dt>
<dt><span class="sect1">3.2. <a href=
"#idp5682032">Buttons</a></span></dt>
<dt><span class="sect1">3.3. <a href=
"#idp5700560">Keyboard shortcuts</a></span></dt>
<dt><span class="sect1">3.4. <a href=
"#idp5713552">Special characters in
fields</a></span></dt>
</dl>
</dd>
<dt><span class="chapter">4. <a href=
"#PROGRAMMING">Programming Interface</a></span></dt>
<dd>
<dl>
<dt><span class="sect1">4.1. <a href=
"#PROGRAMMING.OVERVIEW">Overview</a></span></dt>
<dt><span class="sect1">4.2. <a href=
"#PROGRAMMING.INIT">Initialization and
termination</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">4.2.1. <a href=
"#idp5738592">sqlscreen</a></span></dt>
<dt><span class="sect2">4.2.2. <a href=
"#idp5745984">sqlscreendelete</a></span></dt>
<dt><span class="sect2">4.2.3. <a href=
"#PROGRAMMING.INIT.FONT">Setting fonts for
<span class="application">SQLScreens</span>
applications</a></span></dt>
</dl>
</dd>
<dt><span class="sect1">4.3. <a href=
"#PROGRAMMING.ARRAYENTRIES">Interface array
entries</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">4.3.1. <a href=
"#PROGRAMMING.ARRAYENTRIES.GENERAL">General
parameters</a></span></dt>
<dt><span class="sect2">4.3.2. <a href=
"#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES">Screen
attributes</a></span></dt>
<dt><span class="sect2">4.3.3. <a href=
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES">Field
attributes</a></span></dt>
<dt><span class="sect2">4.3.4. <a href=
"#idp5846912">Auxiliary list window</a></span></dt>
<dt><span class="sect2">4.3.5. <a href=
"#idp5870688">Miscellaneous array
entries</a></span></dt>
</dl>
</dd>
<dt><span class="sect1">4.4. <a href=
"#idp5880304">Global customization
variables</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">4.4.1. <a href=
"#idp5880944">sqlscshowstmts</a></span></dt>
<dt><span class="sect2">4.4.2. <a href=
"#idp5882288">sqlscnobell</a></span></dt>
</dl>
</dd>
<dt><span class="sect1">4.5. <a href=
"#PROGRAMMING.LINKING">Linking screens</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">4.5.1. <a href=
"#idp5885136">sqlmasterslave</a></span></dt>
<dt><span class="sect2">4.5.2. <a href=
"#idp5889584">sqlslavemaster</a></span></dt>
</dl>
</dd>
<dt><span class="sect1">4.6. <a href=
"#idp5892256">Controlling the number of button
sets</a></span></dt>
<dt><span class="sect1">4.7. <a href=
"#idp5896944">Callback routines:</a></span></dt>
<dt><span class="sect1">4.8. <a href=
"#idp5910496">Visible internal
interfaces</a></span></dt>
<dt><span class="sect1">4.9. <a href=
"#idp5918240">Small utility routines</a></span></dt>
</dl>
</dd>
<dt><span class="chapter">5. <a href="#SQLGENERATION">SQL
generation</a></span></dt>
<dd>
<dl>
<dt><span class="sect1">5.1. <a href=
"#idp5922880">Query</a></span></dt>
<dt><span class="sect1">5.2. <a href=
"#idp5926928">Add</a></span></dt>
<dt><span class="sect1">5.3. <a href=
"#idp5931936">Update</a></span></dt>
<dt><span class="sect1">5.4. <a href=
"#idp5936832">Delete</a></span></dt>
<dt><span class="sect1">5.5. <a href=
"#idp5939296">Update issues</a></span></dt>
</dl>
</dd>
<dt><span class="chapter">6. <a href="#idp5945728">The tcsq
low level database access layer</a></span></dt>
<dd>
<dl>
<dt><span class="sect1">6.1. <a href=
"#idp5950048">Environment variables</a></span></dt>
<dt><span class="sect1">6.2. <a href="#idp5951904">API
calls</a></span></dt>
<dd>
<dl>
<dt><span class="sect2">6.2.1. <a href=
"#idp5952544">tcsqconnect</a></span></dt>
<dt><span class="sect2">6.2.2. <a href=
"#idp5954848">tcsquse</a></span></dt>
<dt><span class="sect2">6.2.3. <a href=
"#idp5957392">tcsqconuse</a></span></dt>
<dt><span class="sect2">6.2.4. <a href=
"#idp5959168">tcsqopensel</a></span></dt>
<dt><span class="sect2">6.2.5. <a href=
"#idp5961824">tcsqrew</a></span></dt>
<dt><span class="sect2">6.2.6. <a href=
"#idp5964096">tcsqclosel</a></span></dt>
<dt><span class="sect2">6.2.7. <a href=
"#idp5965872">tcsqnext</a></span></dt>
<dt><span class="sect2">6.2.8. <a href=
"#idp5967824">tcsqexec</a></span></dt>
<dt><span class="sect2">6.2.9. <a href=
"#idp5969696">tcsqdiscon</a></span></dt>
<dt><span class="sect2">6.2.10. <a href=
"#idp5971936">tcsqtabinfo</a></span></dt>
<dt><span class="sect2">6.2.11. <a href=
"#idp5973808">tcsqcolinfo</a></span></dt>
<dt><span class="sect2">6.2.12. <a href=
"#idp5976432">tcsqinsertid</a></span></dt>
<dt><span class="sect2">6.2.13. <a href=
"#idp5978048">tcsqquotequote</a></span></dt>
<dt><span class="sect2">6.2.14. <a href=
"#idp5979904">tcsqquoteblob</a></span></dt>
</dl>
</dd>
<dt><span class="sect1">6.3. <a href=
"#idp5981808">Programming example</a></span></dt>
</dl>
</dd>
<dt><span class="chapter">7. <a href="#SAMPLES">Sample
scripts</a></span></dt>
</dl>
</div>
<div class="chapter" title="Chapter 1. Preface">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="idp2783584" id=
"idp2783584"></a>Chapter 1. Preface</h2>
</div>
</div>
</div>
<p>This document describes <span class=
"application">SQLScreens</span> release 1.2.1</p>
<p><span class="application">SQLScreens</span> is a TCL/TK
package allowing the easy creation of screen forms, for
querying and updating a relational database.</p>
<p><span class="application">SQLScreens</span> was primarily
designed to work with <span class="application">MySQL</span>
as a backend. It also works with <span class=
"application">SQLite</span>, and <span class=
"application">ODBC</span>. It might still work with
<span class="application">INFORMIX</span> and <span class=
"application">MSQL</span> (untested for a looong time).</p>
<p><span class="application">SQLScreens</span> 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.</p>
<p>If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the
<span class="application">MySQL</span> grant tables, you may
find <span class="application">SQLScreens</span> useful
:-)</p>
<p>Still interested ? Details <a class="link" href=
"#PREFACE.QUICKDESCRIPTION" title=
"1.1. Quick description">follow</a>. If you are reading
this online, you can have a look at the <a class="ulink"
href="http://lesbonscomptes.com/sqlscreens/sqlscdumps.html"
target="_top">screen dumps</a>.</p>
<div class="sect1" title="1.1. Quick description">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"PREFACE.QUICKDESCRIPTION" id=
"PREFACE.QUICKDESCRIPTION"></a>1.1. Quick
description</h2>
</div>
</div>
</div>
<p>A typical <span class="application">SQLScreens</span>
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.</p>
<p>You create each screen by listing the column names that
you want to use.</p>
<p>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 (<code class=
"filename">tablescreen.tcl</code>).</p>
<p>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).</p>
<p><span class="application">SQLScreens</span> 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.</p>
<p>You can also:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>Create multiline text widgets to edit text
blobs.</p>
</li>
<li class="listitem">
<p>Display query results as a list linked to a detail
screen.</p>
</li>
<li class="listitem">
<p>And do many other things described a little
further.</p>
</li>
</ul>
</div>
<p>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.</p>
<p><span class="application">SQLScreens</span> 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 <span class="application">SQLScreens</span> 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.</p>
<p>There are many other bells and whistles, but also a few
drawbacks:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>You have little control over field placement.
Fields are placed in a row-column grid managed by
Tk's grid geometry manager.</p>
</li>
<li class="listitem">
<p>The generated SQL is very basic, and you have
little control over it.</p>
</li>
<li class="listitem">
<p>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.</p>
</li>
<li class="listitem">
<p>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).</p>
</li>
<li class="listitem">
<p>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 .</p>
</li>
<li class="listitem">
<p>The screens are not pretty !</p>
</li>
<li class="listitem">
<p>Etc... Etc...</p>
</li>
</ul>
</div>
<p>This said, the software is free and we are open to
suggestions to improve it.</p>
<p>If you want a quick idea of what it does, do the
installation, have a look at the <code class=
"filename">tablescreen.tcl</code> file in the samples
directory, set the host and user name (depending on the
backend type), and point it to any table, like:</p>
<pre class="programlisting">
tablescreen.tcl dbname tablename
</pre>
<p>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).</p>
</div>
</div>
<div class="chapter" title="Chapter 2. Installation">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="INSTALLATION" id=
"INSTALLATION"></a>Chapter 2. Installation</h2>
</div>
</div>
</div>
<div class="sect1" title=
"2.1. External software needed">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"INSTALLATION.EXTERNAL" id=
"INSTALLATION.EXTERNAL"></a>2.1. External
software needed</h2>
</div>
</div>
</div>
<p>To use <span class="application">SQLScreens</span>, you
will need a number of external software packages:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>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 <a class="ulink" href=
"http://tcl.activestate.com/" target="_top">the main
TCL site</a> . TCL and TK are very easy to build.</p>
</li>
</ul>
</div>
<p>In order to access the databases, the basic TCL
interpreter must be augmented with a database access
module:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>For <span class="application">MySQL</span>, a
modified version of the msqltcl package by Hakan
Soderstrom is included in the distribution
(<code class="filename">mysqltcl.c</code>).</p>
</li>
<li class="listitem">
<p>The original msqltcl can be used for accessing
MSQL databases.</p>
</li>
<li class="listitem">
<p>For UNIX ODBC you will need <a class="ulink" href=
"http://sourceforge.net/projects/tclodbc" target=
"_top">tclodbc</a>, and:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="circle">
<li class="listitem">
<p>An ODBC driver manager: under UNIX, we
tested <a class="ulink" href=
"http://www.iodbc.org/" target=
"_top">iODBC</a>, but <a class="ulink" href=
"http://www.unixodbc.org/" target=
"_top">unixODBC</a> should probably be OK
too.</p>
</li>
<li class="listitem">
<p>The driver for your database. For
<span class="application">MySQL</span>, this
would be <a class="ulink" href=
"http://www.mysql.com/products/connector/odbc/"
target="_top">myodbc</a>.</p>
</li>
</ul>
</div>
</li>
<li class="listitem">
<p>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 <a class=
"ulink" href=
"http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html"
target="_top">download page</a>.</p>
</li>
</ul>
</div>
<p>The <span class="application">SQLScreens</span>
<a class="ulink" href=
"http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html"
target="_top">download page</a> has pointers or copies for
some of these elements.</p>
<p>Only the direct <span class="application">MySQL</span>
and <span class="application">SQLite</span> backends have
been tested lately, and there may be minor problems with
the others.</p>
</div>
<div class="sect1" title="2.2. Installation">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp143456" id=
"idp143456"></a>2.2. Installation</h2>
</div>
</div>
</div>
<p>The package comes as a gzipped tar file named something
like <code class=
"filename">sqlscreens-X.Y.Z.tar.gz</code>.</p>
<p>Unpack the file:</p>
<pre class="programlisting">
gunzip < <em class=
"replaceable"><code>sqlscreens-X.Y.Z.tar.gz</code></em> | tar xvf -
</pre>
<p>This will create a top directory named <code class=
"filename">sqlscreens-X.Y.Z</code>.</p>
<p>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...</p>
<p>First, if needed, compile and install TCL and TK (untar;
cd tcl8.../unix; configure; make; make install, same for
tk).</p>
<p>The next step is to add database-access capability to
the standard TCL/TK wish interpreter. This can be done in
several ways:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">By statically linking the database
access module (e.g. mysqltcl or isqltcl) with the
interpreter.</li>
<li class="listitem">By using the <span class=
"application">TCL</span> load facility and a shared
library. The dynamic version sometimes need some manual
tweaking to work.</li>
<li class="listitem">By loading an external package
that itself does whatever is needed (e.g. <code class=
"literal">package require sqlite3)</code></li>
</ul>
</div>
<p><span class="application">MySQL</span> support is
managed by the <code class="filename">Makefile</code> in
the <span class="application">SQLScreens</span> directory.
You can disable <span class="application">MySQL</span>
support (and the need to install the client library) by
using option <code class="literal">--disable-mysql</code>
to the <span class=
"command"><strong>configure</strong></span> script.</p>
<p>For <span class="application">SQLite</span>, just
install the <span class="application">SQLite</span> TCL
package (which may be named something like <code class=
"literal">libsqlite3-tcl</code>).</p>
<p>For <span class="application">ODBC</span>, you should
first install the driver manager, the driver(s) you need
and the tclodbc TCL extension. Follow the instructions in
each package.</p>
<p>For using <span class="application">isqltcl</span> and
INFORMIX, follow the installation instructions inside the
<span class="application">isqltcl</span> package to
generate the interpreter.</p>
<div class="sect2" title=
"2.2.1. Generating a wish interpreter with MySQL support:">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5635776" id=
"idp5635776"></a>2.2.1. Generating a wish
interpreter with <span class=
"application">MySQL</span> support:</h3>
</div>
</div>
</div>
<p>You do not need this if you are working with
<span class="application">SQLite</span> only. Just give a
<code class="literal">--disable-mysql</code> argument to
<span class=
"command"><strong>configure</strong></span>.</p>
<p>Both the static and dynamic load methods are supported
by the <span class="application">SQLScreens</span> 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.</p>
<p>The configuration script use the <span class=
"command"><strong>mysql_config</strong></span> command to
locate the <span class="application">MySQL</span> client
library and include files. It should be accessible in
your <code class="literal">PATH</code>.</p>
<p>When you are ready:</p>
<div class="orderedlist">
<ol class="orderedlist" type="1">
<li class="listitem">
<p><code class="literal">cd</code> to the
<span class="application">SQLScreens</span>
directory, and type <code class=
"literal">./configure</code>.</p>
</li>
<li class="listitem">
<p>Type <code class="literal">make</code> to
compile and link the <span class=
"command"><strong>mysqlwish</strong></span>
interpreter and the shared library. The shared
library link may produce error messages, see
below.</p>
</li>
<li class="listitem">
<p>Type <code class="literal">make install</code>
to install the package. This will create a
<code class="filename">$TK_PREFIX/lib/sqlsc</code>
directory and copy the shared library and TCL code
there. It will also copy <code class=
"filename">mysqlwish</code> to <code class=
"filename">$TK_PREFIX/bin</code>. TK_PREFIX is
taken from the tkConfig.sh script for your
<span class="command"><strong>wish</strong></span>
interpreter. You can change it by typing</p>
<pre class="programlisting">
make install TK_PREFIX=<em class=
"replaceable"><code>yourdest</code></em>
</pre>
<p>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 <code class=
"literal">make install-static</code> 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.</p>
</li>
</ol>
</div>
<p>If you use TCL with other statically linked
extensions, and want to use the same interpreter with
<span class="application">SQLScreens</span>, you will
have to add the <code class=
"function">Mysqltcl_Init</code> call to your usual
<code class="filename">tkAppInit.c</code> file and modify
your <code class="filename">Makefile</code> to link with
<code class="filename">mysqltcl.o</code>. You have
probably been through this already. Have a look at the
included <code class="filename">tkAppinit.c</code></p>
</div>
</div>
</div>
<div class="chapter" title=
"Chapter 3. Using SQLScreens">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="USING" id=
"USING"></a>Chapter 3. Using <span class=
"application">SQLScreens</span></h2>
</div>
</div>
</div>
<p>The following is organized more like a reference manual,
there is little tutorial material. You may want to look at
the <a class="link" href="#SAMPLES" title=
"Chapter 7. Sample scripts">sample scripts</a> to
get a quick idea.</p>
<div class="sect1" title="3.1. Environment variables">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"USING.ENVIR" id=
"USING.ENVIR"></a>3.1. Environment
variables</h2>
</div>
</div>
</div>
<p>The <code class="literal">SQLDBTYPE</code> environment
variable decides what database code is going to be used. It
has several possible values:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p><code class="literal">MYSQL</code> to access a
<span class="application">MySQL</span> database.</p>
</li>
<li class="listitem">
<p><code class="literal">SQLITE3</code> to access a
<span class="application">SQLite</span> database.</p>
</li>
<li class="listitem">
<p><code class="literal">ODBC</code> to use an ODBC
driver manager.</p>
</li>
<li class="listitem">
<p><code class="literal">INFORMIX</code> to access an
INFORMIX database.</p>
</li>
<li class="listitem">
<p><code class="literal">MSQL</code> to access an
MSQL database.</p>
</li>
</ul>
</div>
<p>The default if the variable is not set is to use
<span class="application">MySQL</span>. Don't depend on
it.</p>
<p>This variable can be set inside the script, before the
first call to <code class="function">sqlscreen</code>. (It
is set to <code class="literal">MYSQL</code> inside the
sample scripts). Of course, the value of this variable must
be consistent with what extensions are available to the TCL
interpreter (See <a class="link" href="#INSTALLATION"
title="Chapter 2. Installation">installation</a>).</p>
<p>The <code class="literal">SQLSCLOG</code> environment
variable can be set to the name of a file where
<span class="application">SQLScreens</span> will log the
SQL statements it executes. The default is to log to
stdout.</p>
<p>The <code class="literal">SQLSCHOST</code>, <code class=
"literal">SQLSCUSER</code>, and <code class=
"literal">SQLSCPASSWORD</code> variables can be used to set
the connection parameters. These are only used by the
sample scripts, not the core package.</p>
<p><code class="literal">MYSQL_TCP_PORT</code> should be
used if you need to change the default <span class=
"application">MySQL</span> connection port.</p>
</div>
<div class="sect1" title="3.2. Buttons">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5682032" id=
"idp5682032"></a>3.2. Buttons</h2>
</div>
</div>
</div>
<p>Each screen has a set of buttons to perform the
following operations:</p>
<div class="variablelist">
<dl>
<dt><span class="term">Query</span></dt>
<dd>
<p>starts a SELECT. The WHERE clause is built with
the values currently shown on the screen and the
nodisplay fields).</p>
</dd>
<dt><span class="term">Next</span></dt>
<dd>
<p>fetches the next record in the current query</p>
</dd>
<dt><span class="term">Rewind</span></dt>
<dd>
<p>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
<span class="application">MySQL</span>, this just
rewinds the local result buffer.</p>
</dd>
<dt><span class="term">Reset</span></dt>
<dd>
<p>clears all visible and hidden fields in the
screen.</p>
</dd>
<dt><span class="term">Add</span></dt>
<dd>
<p>inserts the current values. There is special
handling for auto_increment fields, see <a class=
"link" href="#SQLGENERATION" title=
"Chapter 5. SQL generation">SQL
generation</a></p>
</dd>
<dt><span class="term">Update</span></dt>
<dd>
<p>updates the row according to the current values
(how the WHERE clause is built is described later,
see <a class="link" href=
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
title="4.3.3.6. updateindex">updateindex</a> and
<a class="link" href="#SQLGENERATION" title=
"Chapter 5. SQL generation">Sql
generation</a>).</p>
</dd>
<dt><span class="term">Delete</span></dt>
<dd>
<p>deletes the row(s) selected by the current values.
It will prompt for confirmation if more than one row
would be affected.</p>
</dd>
</dl>
</div>
<p>The Update and Add buttons may not exist on all screens
(some screens may be set up only for querying).</p>
<p>Delete is not created by default (See <a class="link"
href="#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE"
title="4.3.2.2. allowdelete">allowdelete</a> ).</p>
<p>The <a class="link" href="#SQLGENERATION" title=
"Chapter 5. SQL generation">SQL generation</a>
section describes how the SQL statements are generated for
the different actions.</p>
</div>
<div class="sect1" title="3.3. Keyboard shortcuts">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5700560" id="idp5700560"></a>3.3. Keyboard
shortcuts</h2>
</div>
</div>
</div>
<p>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:</p>
<div class="variablelist">
<dl>
<dt><span class="term"><TAB></span></dt>
<dd>
<p>goes to the next field in the screen.</p>
</dd>
<dt><span class="term"><CR></span></dt>
<dd>
<p>in any field will start a SELECT.</p>
</dd>
<dt><span class="term"><ESC>n</span></dt>
<dd>
<p>will fetch the next row.</p>
</dd>
<dt><span class="term"><ESC>r</span></dt>
<dd>
<p>will rewind the query.</p>
</dd>
<dt><span class="term"><ESC>a</span></dt>
<dd>
<p>will start an INSERT.</p>
</dd>
<dt><span class="term"><ESC>u</span></dt>
<dd>
<p>will start an UPDATE.</p>
</dd>
<dt><span class="term"><ESC>w</span></dt>
<dd>
<p>will reset the current screen (clear all
fields).</p>
</dd>
</dl>
</div>
<p>The Update and Add shortcuts will have no effects in
query only screens. There is no Delete shortcut.</p>
</div>
<div class="sect1" title=
"3.4. Special characters in fields">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5713552" id="idp5713552"></a>3.4. Special
characters in fields</h2>
</div>
</div>
</div>
<p>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:</p>
<p>If you enter <code class="literal">>10</code> in a
field named <code class="literal">quantity</code>, a
<code class="literal">quantity > 10</code> condition
will be inserted in the WHERE clause. You could also enter
<code class="literal">>10 AND quantity < 20</code>,
or whatever condition you need. (See also <a class="link"
href="#SQLGENERATION" title=
"Chapter 5. SQL generation">the paragraph about
SQL generation</a> ).</p>
</div>
</div>
<div class="chapter" title=
"Chapter 4. Programming Interface">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="PROGRAMMING" id=
"PROGRAMMING"></a>Chapter 4. Programming
Interface</h2>
</div>
</div>
</div>
<div class="sect1" title="4.1. Overview">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"PROGRAMMING.OVERVIEW" id=
"PROGRAMMING.OVERVIEW"></a>4.1. Overview</h2>
</div>
</div>
</div>
<p>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.</p>
<p>The basic idea is that you set values in the array and
then call <code class="literal">sqlscreen arrayname</code>
to create the screen.</p>
<p>The <span class="emphasis"><em>application</em></span>
can be reduced to a main program to initialize and call
<span class="application">SQLScreens</span>, or it may more
complex and use <span class="application">SQLScreens</span>
as a utility module.</p>
<p>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
<code class="literal">mytable</code> in database
<code class="literal">test</code> on the local host might
look like the following:</p>
<pre class="programlisting">
#!/usr/local/bin/wish8.4
package require sqlsc
set mytable(window) .t
set mytable(database) test
set mytable(table) mytable
sqlscreen mytable
</pre>
<p>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.</p>
<p>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”.</p>
<p><span class="application">SQLScreens</span> stores the
values for the field corresponding to a column as
<code class="literal"><em class=
"replaceable"><code>arrayname</code></em>(sqlsc_<em class=
"replaceable"><code>column</code></em>_value)</code> (Ex:
<code class="literal">customer(sqlsc_custid_value)</code>).
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).</p>
<p>The first release used to store the values as
<code class="literal"><em class=
"replaceable"><code>arrayname</code></em>(<em class=
"replaceable"><code>column</code></em>)</code>. 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).</p>
<p>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.</p>
<p>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.</p>
<p>When you are finished with the screen, you can call</p>
<pre class="programlisting">
sqlscreendelete arrayname
</pre>
<p>to cleanup and release all resources (array, windows,
database connections). Most applications will exit instead.
<code class="function">sqlscreendelete</code> 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).</p>
</div>
<div class="sect1" title=
"4.2. Initialization and termination">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"PROGRAMMING.INIT" id=
"PROGRAMMING.INIT"></a>4.2. Initialization and
termination</h2>
</div>
</div>
</div>
<div class="sect2" title="4.2.1. sqlscreen">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5738592" id=
"idp5738592"></a>4.2.1. sqlscreen</h3>
</div>
</div>
</div>
<p>To create a screen, you set values inside a TCL array
(See the following section: <a class="link" href=
"#PROGRAMMING.ARRAYENTRIES" title=
"4.3. Interface array entries">Interface array
entries</a> ), then perform creation as follows:</p>
<pre class="programlisting">
sqlscreen yourarrayname
</pre>
<p>Note that <code class="function">sqlscreen</code> 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:</p>
<pre class="programlisting">
frame .f
set myarray(window) .f.scr
... set other fields
sqlscreen myarray
# screen still not visible
pack .f #screen appears
</pre>
<p>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.</p>
<p><code class="function">sqlscreen</code> optionally
takes a second parameter. If the value is <code class=
"literal">h</code>, the fields will be arranged
horizontally instead of vertically. There are other ways
to do this(see <a class="link" href=
"#PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS" title=
"4.3.1.6. columns">columns</a>), but it can still be
useful in some cases.</p>
</div>
<div class="sect2" title="4.2.2. sqlscreendelete">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5745984" id=
"idp5745984"></a>4.2.2. sqlscreendelete</h3>
</div>
</div>
</div>
<p>This procedure will destroy all resources associated
with an sqlscreen (windows, database connections and the
array itself). Call it as:</p>
<pre class="programlisting">
sqlscreendelete arrayname
</pre>
</div>
<div class="sect2" title=
"4.2.3. Setting fonts for SQLScreens applications">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="PROGRAMMING.INIT.FONT"
id="PROGRAMMING.INIT.FONT"></a>4.2.3. Setting
fonts for <span class=
"application">SQLScreens</span> applications</h3>
</div>
</div>
</div>
<p>The font used by the screen can be set by <a class=
"link" href="#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT"
title="4.3.2.3. font">setting the <code class=
"literal">font</code> array entry</a>. 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:</p>
<pre class="programlisting">
option add *font {Arial 10}
option add *Button*font {Arial 10 bold}
</pre>
<p>Alternatively, the font could be set in the option
database (ie: <code class="filename">.Xdefault</code>
under Unix). Example:</p>
<pre class="programlisting">
wines*font: Arial 10
wines*Button*font: Arial 10 bold
</pre>
<p>In the latter case, the program name should not
include a <code class="literal">.tcl</code> extension,
else the dot seems to cause problems in the options
database (use <code class="literal">wines</code>, not
<code class="literal">wines.tcl</code>).</p>
</div>
</div>
<div class="sect1" title="4.3. Interface array entries">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"PROGRAMMING.ARRAYENTRIES" id=
"PROGRAMMING.ARRAYENTRIES"></a>4.3. Interface
array entries</h2>
</div>
</div>
</div>
<div class="sect2" title="4.3.1. General parameters">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.GENERAL" id=
"PROGRAMMING.ARRAYENTRIES.GENERAL"></a>4.3.1. General
parameters</h3>
</div>
</div>
</div>
<div class="sect3" title="4.3.1.1. window">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5758160" id=
"idp5758160"></a>4.3.1.1. window</h4>
</div>
</div>
</div>
<p>This defines the TK frame name where the screen will
be created. Example:</p>
<pre class="programlisting">
frame .f1
set customer(window) .f1.cust
</pre>
<p>or just the following to create the window in the
top one:</p>
<pre class="programlisting">
set customer(window) .cust
</pre>
<p>This entry must be a valid TK window name: for
exemple it cannot start with an upper case
character.</p>
<p>The window must not exist before calling sqlscreen,
which will create it. Its parents must exist.</p>
</div>
<div class="sect3" title="4.3.1.2. database">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5762144" id=
"idp5762144"></a>4.3.1.2. database</h4>
</div>
</div>
</div>
<p>This defines the database name.</p>
<pre class="programlisting">
set customer(database) orderdb
</pre>
</div>
<div class="sect3" title=
"4.3.1.3. sqlcpasswd, sqlschost, sqlscuser">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5763920" id=
"idp5763920"></a>4.3.1.3. sqlcpasswd,
sqlschost, sqlscuser</h4>
</div>
</div>
</div>
<p>These define the user name, host and password for
the connection to the database server. These are all
optional.</p>
</div>
<div class="sect3" title="4.3.1.4. table">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5765216" id=
"idp5765216"></a>4.3.1.4. table</h4>
</div>
</div>
</div>
<p>This defines the table name. Example:</p>
<pre class="programlisting">
set customer(table) customers
</pre>
<p>It is also possible to display fields from several
tables in one screen:</p>
<pre class="programlisting">
set custorder(table) {customers orders}
</pre>
<p>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 <a class="link" href=
"#PROGRAMMING.LINKING" title=
"4.5. Linking screens">Linking screens</a> ).</p>
</div>
<div class="sect3" title="4.3.1.5. joinclause">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5769504" id=
"idp5769504"></a>4.3.1.5. joinclause</h4>
</div>
</div>
</div>
<p>In case fields from several tables are displayed in
a screen, <span class="application">SQLScreens</span>
needs to know how to join the tables when performing a
SELECT. This is defined by the joinclause array entry.
Exemple:</p>
<pre class="programlisting">
set custorder(joinclause) {customers.custid = orders.ordercustid}
</pre>
</div>
<div class="sect3" title="4.3.1.6. columns">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS" id=
"PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS"></a>4.3.1.6. columns</h4>
</div>
</div>
</div>
<p>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.</p>
<p>Example for specifying the column names:</p>
<pre class="programlisting">
set customer(columns) {custid custname}
</pre>
<p>If several tables are used, it may be necessary to
qualify the column names if they are not unique:</p>
<pre class="programlisting">
set custorder(columns) {customers.custid customers.custname}
</pre>
<p>By default, all fields will be displayed in one
column. You can get them to be displayed in one line by
calling <code class="function">sqlscreen</code> as</p>
<pre class="programlisting">
sqlscreen <em class="replaceable"><code>arrayname</code></em> h
</pre>
<p>You can also insert <span class="emphasis"><em>line
breaks</em></span> by inserting newline caracters in
the column list, like:</p>
<pre class="programlisting">
set arrayname(columns) {
host "\n"
user db "\n"
select_priv insert_priv update_priv "\n"
delete_priv create_priv drop_priv
}
</pre>
<p>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).</p>
</div>
</div>
<div class="sect2" title="4.3.2. Screen attributes">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES" id=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES"></a>4.3.2. Screen
attributes</h3>
</div>
</div>
</div>
<div class="sect3" title="4.3.2.1. queryonly">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5781232" id=
"idp5781232"></a>4.3.2.1. queryonly</h4>
</div>
</div>
</div>
<p>If this is set, the screen will not have
“add” and “update” buttons, you
will only be able to select data. Example:</p>
<pre class="programlisting">
set customer(queryonly) {}
</pre>
<p>The value has no importance, just setting the array
entry (even to <code class="literal">no</code>) creates
a screen for query only.</p>
</div>
<div class="sect3" title="4.3.2.2. allowdelete">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE"
id=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE">
</a>4.3.2.2. allowdelete</h4>
</div>
</div>
</div>
<p>If this entry is set, and <code class=
"literal">queryonly</code> is not set a <span class=
"guilabel">Delete</span> button will be created.</p>
</div>
<div class="sect3" title="4.3.2.3. font">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT" id=
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT"></a>4.3.2.3. font</h4>
</div>
</div>
</div>
<p>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 <a class="link" href=
"#PROGRAMMING.INIT.FONT" title=
"4.2.3. Setting fonts for SQLScreens applications">
several other ways</a> to set the application font.</p>
</div>
<div class="sect3" title="4.3.2.4. notitle">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5789104" id=
"idp5789104"></a>4.3.2.4. notitle</h4>
</div>
</div>
</div>
<p>Suppresses the screen title. This spares a little
space if your screen is crowded.</p>
</div>
<div class="sect3" title="4.3.2.5. nobuttons">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5790368" id=
"idp5790368"></a>4.3.2.5. nobuttons</h4>
</div>
</div>
</div>
<p>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.</p>
</div>
<div class="sect3" title="4.3.2.6. graphicbuttons">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5791744" id=
"idp5791744"></a>4.3.2.6. graphicbuttons</h4>
</div>
</div>
</div>
<p>If this is set, and the <span class=
"application">Tix</span> package is available, the
buttons will be created with icons instead of textual
labels.</p>
</div>
</div>
<div class="sect2" title="4.3.3. Field attributes">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES" id=
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES"></a>4.3.3. Field
attributes</h3>
</div>
</div>
</div>
<div class="sect3" title=
"4.3.3.1. Column type and length">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5794656" id=
"idp5794656"></a>4.3.3.1. Column type and
length</h4>
</div>
</div>
</div>
<p>The <code class="literal">sqlsc_<em class=
"replaceable"><code>colname</code></em>_len</code> and
<code class="literal">sqlsc_<em class=
"replaceable"><code>colname</code></em>_type</code>
entries are normally created by the package, you do not
need to set them. For character columns, you can set
<code class="literal">sqlsc_<em class=
"replaceable"><code>colname</code></em>_len</code> 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:</p>
<pre class="programlisting">
set customer(sqlsc_custname_len) 20
</pre>
<p>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.</p>
<p>In any case, <span class=
"application">SQLScreens</span> 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).</p>
</div>
<div class="sect3" title="4.3.3.2. autopercent">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5801584" id=
"idp5801584"></a>4.3.3.2. autopercent</h4>
</div>
</div>
</div>
<p>The <code class="literal">autopercentboth</code>,
<code class="literal">autopercentleft</code>,
<code class="literal">autopercentright</code> 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:</p>
<pre class="programlisting">
set customer(autopercentright) {custname}
</pre>
<p>would let you query by entering just the beginning
of the name, without having to reach for the shift key
to type '%'.</p>
</div>
<div class="sect3" title="4.3.3.3. texts">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5805984" id=
"idp5805984"></a>4.3.3.3. texts</h4>
</div>
</div>
</div>
<p>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:</p>
<pre class="programlisting">
set product(texts) {{description 20 70} {notice 10 70 t}}
</pre>
<p>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:</p>
<div class="variablelist">
<dl>
<dt><span class="term">t</span></dt>
<dd>
<p>will display a label (column name) above the
text area</p>
</dd>
<dt><span class="term">l</span></dt>
<dd>
<p>will display a label on the left of the text
area</p>
</dd>
</dl>
</div>
<p>By default, no label will be displayed for text
fields.</p>
<p><span class="application">SQLScreens</span> will
handle quoting and unquoting the blob contents.</p>
<p>Text entries will NOT be validated for maximum
length against the database field width.</p>
<p>There is an exemple of texts use in the wines.tcl
sample application.</p>
</div>
<div class="sect3" title="4.3.3.4. choices">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5814720" id=
"idp5814720"></a>4.3.3.4. choices</h4>
</div>
</div>
</div>
<p>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.</p>
<p>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:</p>
<pre class="programlisting">
set customer(choices) {
custtype custtypelist
custgender custgenderlist
}
</pre>
<p>Would specify that the <code class=
"literal">custtype</code> and <code class=
"literal">custgender</code> 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.</p>
<p>A simple list lists the possible values (would you
believe this ?). Ex:</p>
<pre class="programlisting">
set custtypelist {normal distributor internal}
</pre>
<p><code class="literal">normal</code>, <code class=
"literal">distributor</code> and <code class=
"literal">internal</code> will be both displayed on the
screen and used for querying or updating the
database.</p>
<p>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:</p>
<pre class="programlisting">
set custgenderlist {{unknown 0} {female 1} {male 2}}
</pre>
<p>With this list, the menu would display <code class=
"literal">unknown</code>, <code class=
"literal">male</code>, <code class=
"literal">female</code>, but the values used for the
database would be 0, 1, 2.</p>
<p>Note that when using <span class=
"application">MySQL</span>, 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.</p>
<p>There are exemples of use (both automatic and
explicit) in the wines.tcl sample program.</p>
</div>
<div class="sect3" title="4.3.3.5. ordercols">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5828160" id=
"idp5828160"></a>4.3.3.5. ordercols</h4>
</div>
</div>
</div>
<p>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:</p>
<pre class="programlisting">
set customer(ordercols) "custid desc, custname"
</pre>
</div>
<div class="sect3" title="4.3.3.6. updateindex">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name=
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
id=
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX">
</a>4.3.3.6. updateindex</h4>
</div>
</div>
</div>
<p>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.</p>
<p>If neither <code class="literal">columns</code> nor
<code class="literal">updateindex</code> are set before
calling <code class="function">sqlscreen</code>,
<span class="application">SQLScreens</span> will try to
make up an <code class="literal">updateindex</code> by
using a serial column or primary key if one is
found.</p>
<p>If <code class="literal">columns</code> is set, and
not <code class="literal">updateindex</code>, the
latter is automatically generated only if the primary
key is completely included in the column list.</p>
<p>If <code class="literal">updateindex</code> 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).</p>
<p>If no <code class="literal">updateindex</code> list
finally exists, the screen will have no <span class=
"guilabel">Update</span> button.</p>
<p>See <a class="link" href="#SQLGENERATION" title=
"Chapter 5. SQL generation">Sql
generation</a> for a more complete discussion of update
row selection.</p>
</div>
<div class="sect3" title="4.3.3.7. upshiftcols">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5841168" id=
"idp5841168"></a>4.3.3.7. upshiftcols</h4>
</div>
</div>
</div>
<p>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 <span class=
"application">MySQL</span>. Example:</p>
<pre class="programlisting">
set customer(upshiftcols) {custname custfirstname}
</pre>
</div>
<div class="sect3" title="4.3.3.8. noentry">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5843888" id=
"idp5843888"></a>4.3.3.8. noentry</h4>
</div>
</div>
</div>
<p>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.</p>
</div>
<div class="sect3" title="4.3.3.9. nodisplay">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5845328" id=
"idp5845328"></a>4.3.3.9. nodisplay</h4>
</div>
</div>
</div>
<p>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.</p>
</div>
</div>
<div class="sect2" title=
"4.3.4. Auxiliary list window">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5846912" id=
"idp5846912"></a>4.3.4. Auxiliary list
window</h3>
</div>
</div>
</div>
<div class="sect3" title=
"4.3.4.1. list_columns, list_window">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5847552" id=
"idp5847552"></a>4.3.4.1. list_columns,
list_window</h4>
</div>
</div>
</div>
<p>If <code class="literal">list_columns</code> and
<code class="literal">list_window</code> are set,
<code class="function">sqlscreen</code> 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
<code class="literal">list_columns</code> columns. This
is useful to get a compact display of a query's
results.</p>
<p>Clicking on a line in the list with mouse button 1
will display the corresponding row in the main
screen.</p>
<p>This capability will only be available if an
<code class="literal">updateindex</code> list has been
defined for the screen (either implicitely or
explicitely), see <a class="link" href=
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
title="4.3.3.6. updateindex">the updateindex
section</a>. The <code class=
"literal">updateindex</code> columns must be part of
<code class="literal">list_columns</code>, so that we
can uniquely link back from the list to the detail
screen.</p>
<p>Example:</p>
<pre class="programlisting">
set customers(list_columns) {custname custid}
set customers(updateindex) custid
toplevel .custlist
set customers(list_window) .custlist
</pre>
</div>
<div class="sect3" title="4.3.4.2. list_colwidths:">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5856288" id=
"idp5856288"></a>4.3.4.2. list_colwidths:</h4>
</div>
</div>
</div>
<p><span class="application">SQLScreens</span> 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
<code class="literal">list_colwidths</code> entry, as a
list specifying the width in characters for each
column. Example:</p>
<pre class="programlisting">
set customers(list_colwidths) {40 5}
</pre>
<p>The widths must be specified in the same order as
the columns in <code class=
"literal">list_columns</code>.</p>
</div>
<div class="sect3" title="4.3.4.3. list_lineproc:">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5860608" id=
"idp5860608"></a>4.3.4.3. list_lineproc:</h4>
</div>
</div>
</div>
<p>When displaying the list, <span class=
"application">SQLScreens</span> will alternate the
line's background between white and light grey to
facilitate reading. If defined, the <code class=
"literal">list_lineproc</code> 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:</p>
<pre class="programlisting">
set customers(list_lineproc) custlineproc
</pre>
<p><code class="literal">list_lineproc</code> will then
be called for each line with 3 parameters:</p>
<div class="orderedlist">
<ol class="orderedlist" type="1">
<li class="listitem">
<p>The name of the TK text window where the line
is displayed.</p>
</li>
<li class="listitem">
<p>The TK text tag name for the area associated
with the line.</p>
</li>
<li class="listitem">
<p>The list of column values for this line.</p>
</li>
</ol>
</div>
<p><code class="literal">list_lineproc</code> 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):</p>
<pre class="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}
}
}
</pre>
</div>
</div>
<div class="sect2" title=
"4.3.5. Miscellaneous array entries">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5870688" id=
"idp5870688"></a>4.3.5. Miscellaneous array
entries</h3>
</div>
</div>
</div>
<div class="sect3" title="4.3.5.1. hdl">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5871328" id=
"idp5871328"></a>4.3.5.1. hdl</h4>
</div>
</div>
</div>
<p>The package uses this entry to store the database
handle.</p>
</div>
<div class="sect3" title="4.3.5.2. initfocus">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5872560" id=
"idp5872560"></a>4.3.5.2. initfocus</h4>
</div>
</div>
</div>
<p>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.</p>
</div>
<div class="sect3" title="4.3.5.3. tabcolserial">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5873904" id=
"idp5873904"></a>4.3.5.3. tabcolserial</h4>
</div>
</div>
</div>
<p>If there is a serial or auto_increment column,
sqlscreen sets its name in there.</p>
<p>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.</p>
<p>If the field is modifiable by the user, no special
action will be taken.</p>
<p>If the beforeinsert procedure is defined for the
screen, any modification is performed before calling
it, to allow for a local value allocation scheme.</p>
</div>
<div class="sect3" title="4.3.5.4. querynum">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5876928" id=
"idp5876928"></a>4.3.5.4. querynum</h4>
</div>
</div>
</div>
<p>This is the select result handle.</p>
</div>
<div class="sect3" title=
"4.3.5.5. sqlsc_colname_valsaved">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="idp5878080" id=
"idp5878080"></a>4.3.5.5. sqlsc_colname_valsaved</h4>
</div>
</div>
</div>
<p>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 <a class="link"
href="#SQLGENERATION" title=
"Chapter 5. SQL generation">Sql
generation</a> section).</p>
</div>
</div>
</div>
<div class="sect1" title=
"4.4. Global customization variables">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5880304" id="idp5880304"></a>4.4. Global
customization variables</h2>
</div>
</div>
</div>
<div class="sect2" title="4.4.1. sqlscshowstmts">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5880944" id=
"idp5880944"></a>4.4.1. sqlscshowstmts</h3>
</div>
</div>
</div>
<p>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.</p>
</div>
<div class="sect2" title="4.4.2. sqlscnobell">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5882288" id=
"idp5882288"></a>4.4.2. sqlscnobell</h3>
</div>
</div>
</div>
<p>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...</p>
</div>
</div>
<div class="sect1" title="4.5. Linking screens">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"PROGRAMMING.LINKING" id=
"PROGRAMMING.LINKING"></a>4.5. Linking
screens</h2>
</div>
</div>
</div>
<p>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.</p>
<div class="sect2" title="4.5.1. sqlmasterslave">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5885136" id=
"idp5885136"></a>4.5.1. sqlmasterslave</h3>
</div>
</div>
</div>
<p>This links the first screen to the second one so that
a query in the first will run a query in the second.
Example:</p>
<pre class="programlisting">
sqlmasterslave customer custid order ordercust
</pre>
<p>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.</p>
<p>Things are set up so that it is possible to have
reciprocal links without creating an infinite loop.
Example:</p>
<pre class="programlisting">
sqlmasterslave customer custid order ordercust
sqlmasterslave order ordercust customer custid
</pre>
<p>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.</p>
</div>
<div class="sect2" title="4.5.2. sqlslavemaster">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5889584" id=
"idp5889584"></a>4.5.2. sqlslavemaster</h3>
</div>
</div>
</div>
<p>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:</p>
<pre class="programlisting">
sqlslavemaster customer custid order ordercust
</pre>
<p>could be used to set the ordercust field by querying
customer, typically while entering orders.</p>
</div>
</div>
<div class="sect1" title=
"4.6. Controlling the number of button sets">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5892256" id=
"idp5892256"></a>4.6. Controlling the number of
button sets</h2>
</div>
</div>
</div>
<p>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:</p>
<pre class="programlisting">
sqcommonbuttons .f1.buttons
</pre>
<p>Will create the .f1.buttons frame and buttons inside
there.</p>
<p>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.</p>
<p>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.</p>
<p>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).</p>
</div>
<div class="sect1" title="4.7. Callback routines:">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5896944" id="idp5896944"></a>4.7. Callback
routines:</h2>
</div>
</div>
</div>
<p>The following callback routine names can be defined in
the array:</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>afterinsert</p>
</li>
<li class="listitem">
<p>afterquery</p>
</li>
<li class="listitem">
<p>afterupdate</p>
</li>
<li class="listitem">
<p>afterdelete</p>
</li>
<li class="listitem">
<p>beforeinsert</p>
</li>
<li class="listitem">
<p>beforequery</p>
</li>
<li class="listitem">
<p>beforeupdate</p>
</li>
<li class="listitem">
<p>beforedelete</p>
</li>
</ul>
</div>
<p>Example:</p>
<pre class="programlisting">
set customer(beforeinsert) checkcustfields
</pre>
<p>The different routines will be invoked in the following
manner:</p>
<p>For beforexxx routines:</p>
<pre class="programlisting">
routinename optype arrayname
</pre>
<p>For afterxxx routines:</p>
<pre class="programlisting">
routinename optype txt arrayname
</pre>
<p>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 !</p>
<p>beforeinsert, afterinsert, beforeupdate, afterupdate,
beforedelete and afterdelete will be called before and
after inserting or updating data.</p>
<p>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.</p>
<p>If one of the beforexxx routines returns anything but 0
, the operation will be canceled (not run).</p>
</div>
<div class="sect1" title=
"4.8. Visible internal interfaces">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5910496" id="idp5910496"></a>4.8. Visible
internal interfaces</h2>
</div>
</div>
</div>
<p>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.</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>sqlscinsert: Generate and run an INSERT
statement.</p>
</li>
<li class="listitem">
<p>sqlscupd: Generate and run an UPDATE
statement.</p>
</li>
<li class="listitem">
<p>sqlscquery: Generate and run a SELECT
statement.</p>
</li>
<li class="listitem">
<p>sqlscdelete: Generate and run a DELETE
statement.</p>
</li>
<li class="listitem">
<p>sqlscnext: Fetch the next row in the current
query.</p>
</li>
<li class="listitem">
<p>sqlscreopen: Rewind the current query.</p>
</li>
<li class="listitem">
<p>sqlscreset: Reset all data values for the
screen.</p>
</li>
</ul>
</div>
<p>In all cases, the effect will be exactly the same as the
corresponding button press.</p>
</div>
<div class="sect1" title="4.9. Small utility routines">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5918240" id="idp5918240"></a>4.9. Small
utility routines</h2>
</div>
</div>
</div>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>sqlsc_entrywidget arnm colname Return
the name for the entry widget for arnm and
colname.</p>
</li>
<li class="listitem">
<p>sqlsc_labelwidget arnm colname Return
the name for the label widget for arnm and
colname.</p>
</li>
</ul>
</div>
</div>
</div>
<div class="chapter" title=
"Chapter 5. SQL generation">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="SQLGENERATION" id=
"SQLGENERATION"></a>Chapter 5. SQL
generation</h2>
</div>
</div>
</div>
<p>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.</p>
<div class="sect1" title="5.1. Query">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5922880" id="idp5922880"></a>5.1. Query</h2>
</div>
</div>
</div>
<p>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).</p>
<p>The WHERE clause is built from all the fields that hold
data (including the hidden ones if there are any).</p>
<p>For non character columns, the comparison operator used
is =. For character columns, it is LIKE.</p>
<p>Example: for a screen with custid, custname,
custfirstname, custsomenumber fields, where data was
entered in custname (xxx) and custsomenumber (yyy), the
statement would be:</p>
<p>SELECT custid,custname,custfirstname,custsomenumber from
customers WHERE custname LIKE 'xxx' AND custsomenumber =
yyy</p>
<p>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).</p>
</div>
<div class="sect1" title="5.2. Add">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5926928" id="idp5926928"></a>5.2. Add</h2>
</div>
</div>
</div>
<p>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:</p>
<p>INSERT INTO customers(custname,custsomenumber)
VALUES('xxx',yyy)</p>
<p>There is no explicitely provided way to insert a NULL
value (and certainly none for a char field).</p>
<p>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.</p>
<p>There seems to be no way to retrieve the auto_increment
attribute from a mysql client program, so that, when using
<span class="application">MySQL</span>, 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.</p>
</div>
<div class="sect1" title="5.3. Update">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5931936" id=
"idp5931936"></a>5.3. Update</h2>
</div>
</div>
</div>
<p>The Update button generates an UPDATE statement. There
are two issues: the WHERE clause and the values.</p>
<p>The WHERE clause is built from the columns that were
designated in the updateindex list (if no such list was
explicitely indicated, <span class=
"application">SQLScreens</span> 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).</p>
<p>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.</p>
<p>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).</p>
<p>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.</p>
</div>
<div class="sect1" title="5.4. Delete">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5936832" id=
"idp5936832"></a>5.4. Delete</h2>
</div>
</div>
</div>
<p>The Delete button generates a DELETE statement. The
WHERE clause is built like the SELECTs, except that no LIKE
operators are used.</p>
<p>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.</p>
<p>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.</p>
</div>
<div class="sect1" title="5.5. Update issues">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5939296" id="idp5939296"></a>5.5. Update
issues</h2>
</div>
</div>
</div>
<p>Any application that displays database values and allows
the user to update them has two problems:</p>
<div class="orderedlist">
<ol class="orderedlist" type="1">
<li class="listitem">
<p>It must ensure that the generated UPDATE statement
will really update the row that was displayed and not
many other rows in the database.</p>
</li>
<li class="listitem">
<p>It must ensure that the affected row has not
changed since it was displayed.</p>
</li>
</ol>
</div>
<p>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
<span class="application">SQLScreens</span> 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.</p>
<p>For the second issue, <span class=
"application">SQLScreens</span> 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).</p>
</div>
</div>
<div class="chapter" title=
"Chapter 6. The tcsq low level database access layer">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="idp5945728" id=
"idp5945728"></a>Chapter 6. The tcsq low
level database access layer</h2>
</div>
</div>
</div>
<p><span class="application">SQLScreens</span> uses an
intermediate code layer to access the different databases in
a consistent fashion. This intermediate layer is called
tcsq.</p>
<p>The tcsq calls which are documented here may be useful as
a database access layer for non-<span class=
"application">SQLScreens</span> applications (for any TCL
script accessing the supported databases), or in auxiliary
routines inside an <span class=
"application">SQLScreens</span> application (for example, for
building lists of values by querying a table).</p>
<div class="sect1" title="6.1. Environment variables">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5950048" id=
"idp5950048"></a>6.1. Environment variables</h2>
</div>
</div>
</div>
<p>tcsq uses the same SQLDBTYPE environment variable as the
<span class="application">SQLScreens</span> layer to define
the database type.</p>
</div>
<div class="sect1" title="6.2. API calls">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5951904" id="idp5951904"></a>6.2. API
calls</h2>
</div>
</div>
</div>
<div class="sect2" title="6.2.1. tcsqconnect">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5952544" id=
"idp5952544"></a>6.2.1. tcsqconnect</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqconnect [host [user [passwd]]
</pre>
<p>Returns a server connection handle ( <span class=
"emphasis"><em>hdl</em></span> in the following).
Depending on the database type, it may actually connect
to a server, or do nothing (Ex: informix).</p>
</div>
<div class="sect2" title="6.2.2. tcsquse">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5954848" id=
"idp5954848"></a>6.2.2. tcsquse</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsquse hdl dbname
</pre>
<p>Associates the connection handle <span class=
"emphasis"><em>hdl</em></span> with database <span class=
"emphasis"><em>dbname</em></span> .</p>
</div>
<div class="sect2" title="6.2.3. tcsqconuse">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5957392" id=
"idp5957392"></a>6.2.3. tcsqconuse</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqconuse database [host]
</pre>
<p>Utility function: connect and use.</p>
</div>
<div class="sect2" title="6.2.4. tcsqopensel">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5959168" id=
"idp5959168"></a>6.2.4. tcsqopensel</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqopensel hdl stmt
</pre>
<p>Opens a query operation. <span class=
"emphasis"><em>stmt</em></span> is a string holding an
SQL SELECT statement. Returns a select handle (
<span class="emphasis"><em>selhdl</em></span> in the
following).</p>
</div>
<div class="sect2" title="6.2.5. tcsqrew">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5961824" id=
"idp5961824"></a>6.2.5. tcsqrew</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqrew selhdl
</pre>
<p>Rewinds the query associated with <span class=
"emphasis"><em>selhdl</em></span> . This may actually
rerun the query (INFORMIX) or be purely local
(MYSQL).</p>
</div>
<div class="sect2" title="6.2.6. tcsqclosel">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5964096" id=
"idp5964096"></a>6.2.6. tcsqclosel</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqclosel selhdl
</pre>
<p>Closes a query, and frees the associated
resources.</p>
</div>
<div class="sect2" title="6.2.7. tcsqnext">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5965872" id=
"idp5965872"></a>6.2.7. tcsqnext</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqnext selhdl
</pre>
<p>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.</p>
</div>
<div class="sect2" title="6.2.8. tcsqexec">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5967824" id=
"idp5967824"></a>6.2.8. tcsqexec</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqexec hdl stmt
</pre>
<p>Executes a non-SELECT SQL statement (Ie, INSERT,
DELETE, etc...)</p>
</div>
<div class="sect2" title="6.2.9. tcsqdiscon">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5969696" id=
"idp5969696"></a>6.2.9. tcsqdiscon</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqdiscon hdl
</pre>
<p>Disconnects and frees resources associated with
<span class="emphasis"><em>hdl</em></span> .</p>
</div>
<div class="sect2" title="6.2.10. tcsqtabinfo">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5971936" id=
"idp5971936"></a>6.2.10. tcsqtabinfo</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqtabinfo hdl
</pre>
<p>Returns a list of the user tables in the database
referenced by hdl.</p>
</div>
<div class="sect2" title="6.2.11. tcsqcolinfo">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5973808" id=
"idp5973808"></a>6.2.11. tcsqcolinfo</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqcolinfo hdl tbl arnm
</pre>
<p>Returns information about table <span class=
"emphasis"><em>tbl</em></span> into the array the name of
which is specified by <span class=
"emphasis"><em>arnm</em></span> .</p>
</div>
<div class="sect2" title="6.2.12. tcsqinsertid">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5976432" id=
"idp5976432"></a>6.2.12. tcsqinsertid</h3>
</div>
</div>
</div>
<p>tcsqinsertid hdl</p>
<p>Returns the auto_increment value for the last inserted
row.</p>
</div>
<div class="sect2" title="6.2.13. tcsqquotequote">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5978048" id=
"idp5978048"></a>6.2.13. tcsqquotequote</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqquotequote s
</pre>
<p>Returns a suitably escaped string, for use in sql
statements.</p>
</div>
<div class="sect2" title="6.2.14. tcsqquoteblob">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="idp5979904" id=
"idp5979904"></a>6.2.14. tcsqquoteblob</h3>
</div>
</div>
</div>
<pre class="programlisting">
tcsqquoteblob s
</pre>
<p>Same for blobs.</p>
</div>
</div>
<div class="sect1" title="6.3. Programming example">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name=
"idp5981808" id=
"idp5981808"></a>6.3. Programming example</h2>
</div>
</div>
</div>
<p>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.</p>
<pre class="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
</pre>
</div>
</div>
<div class="chapter" title=
"Chapter 7. Sample scripts">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="SAMPLES" id=
"SAMPLES"></a>Chapter 7. Sample scripts</h2>
</div>
</div>
</div>
<p>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.</p>
<div class="variablelist">
<dl>
<dt><span class="term">tablescreen.tcl</span></dt>
<dd>
<p>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.</p>
</dd>
<dt><span class="term">mysqldb.tcl</span></dt>
<dd>
<p>creates a screen to access the <span class=
"application">MySQL</span> “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.</p>
</dd>
<dt><span class="term">wines.tcl</span></dt>
<dd>
<p>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).</p>
</dd>
</dl>
</div>
<p>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.</p>
<p>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.</p>
</div>
</div>
</body>
</html>