<!DOCTYPE BOOK PUBLIC "-//FreeBSD//DTD DocBook V4.1-Based Extension//EN" [

<!DOCTYPE BOOK PUBLIC "-//FreeBSD//DTD DocBook V4.1-Based Extension//EN" [

<!ENTITY SQLSC "<application>SQLScreens</application>">

<!ENTITY SQLSC "<application>SQLScreens</application>">

<!ENTITY MYSQL "<application>MySQL</application>">

<!ENTITY MYSQL "<application>MySQL</application>">

<!ENTITY SQLSCDOWN "http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html">

<!ENTITY SQLSCDOWN "http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html">

]>

]>

<book lang="en">

<book lang="en">

  <bookinfo>

  <bookinfo>

    <title>&SQLSC;: a simple SQL screen generator</title>

    <title>&SQLSC;: a simple SQL screen generator</title>

  </bookinfo>

  </bookinfo>

  <chapter>

  <chapter>

    <title>Preface</title>

    <title>Preface</title>

    <para>&SQLSC; is a TCL/TK package allowing the easy creation of

    <para>&SQLSC; is a TCL/TK package allowing the easy creation of

    screen forms, for querying and updating a relational

    database.</para>

    <para>&SQLSC; is no match for commercial application development

    <para>&SQLSC; is no match for commercial application development

    tools. It is a very simple tool to create ad-hoc query screens. We

    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

    found it very handy for creating our data-entry utilities in

    CDKIT/MusicMaker (R.I.P), which is why we decided to publish it,

    CDKIT/MusicMaker (R.I.P), which is why we decided to publish it,

    <para>If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the &MYSQL;

    <para>If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the &MYSQL;

    grant tables, you may find &SQLSC; useful :-)</para>

    grant tables, you may find &SQLSC; useful :-)</para>

    <para>Still interested ? Details 

    <para>Still interested ? Details 

      <title>Quick description</title>

      <title>Quick description</title>

      <para>A typical &SQLSC; application will have a number of

      <para>A typical &SQLSC; application will have a number of

      screens, each with several entry/display fields. Each screen

      screens, each with several entry/display fields. Each screen

      will be linked to one or several database tables, each field to

      will be linked to one or several database tables, each field to

      <para>This said, the software is free and we are open to suggestions

      <para>This said, the software is free and we are open to suggestions

      to improve it.</para>

      to improve it.</para>

      <para>If you want a quick idea of what it does, do the installation,

      <para>If you want a quick idea of what it does, do the installation,

      <programlisting>

      <programlisting>

tablescreen.tcl dbname tablename

tablescreen.tcl dbname tablename

      </programlisting>

      </programlisting>

      table (you may need to adjust the host and user names in the script

      table (you may need to adjust the host and user names in the script

      or the environment to get the right permissions).</para>

      or the environment to get the right permissions).</para>

    </sect1>

    </sect1>

  </chapter>

  </chapter>

    <title>Installation</title>

    <title>Installation</title>

      <title>External software needed</title>

      <title>External software needed</title>

      <para>To use &SQLSC;, you will need a number of external software

      <para>To use &SQLSC;, you will need a number of external software

      packages:</para>

      packages:</para>

          FreeBSD or Linux, you just need to install the packages. Else,

          FreeBSD or Linux, you just need to install the packages. Else,

          you can get the source distributions from 

          you can get the source distributions from 

          <ulink url="http://tcl.activestate.com/">the main TCL

          <ulink url="http://tcl.activestate.com/">the main TCL

          site</ulink>

          site</ulink>

        </listitem>

        </listitem>

      </itemizedlist>

      </itemizedlist>

      <para>In order to access the databases, the basic TCL interpreter

      <para>In order to access the databases, the basic TCL interpreter

      must be augmented with a database access module:</para>

      must be augmented with a database access module:</para>

        </listitem>

        </listitem>

        <listitem>

        <listitem>

          <para>For INFORMIX you will need the isqltcl package by Srinivas

          <para>For INFORMIX you will need the isqltcl package by Srinivas

          Kumar. It has become a little difficult to find lately and

          Kumar. It has become a little difficult to find lately and

          there is a copy on the <ulink url="&SQLSCDOWN">download

          page</ulink>.</para>

          page</ulink>.</para>

        </listitem>

        </listitem>

      </itemizedlist>

      </itemizedlist>

      <para>The &SQLSC; <ulink url="&SQLSCDOWN;">download page</ulink>

      <para>The &SQLSC; <ulink url="&SQLSCDOWN;">download page</ulink>

      has pointers or copies for some of these elements.</para>

      has pointers or copies for some of these elements.</para>

    </sect1>

    </sect1>

    <sect1>

    <sect1>

      <title>Installation</title>

      <title>Installation</title>

      <para>First, if needed, compile and install TCL and TK (untar; cd

      <para>First, if needed, compile and install TCL and TK (untar; cd

      tcl8.../unix; configure; make; make install, same for tk).</para>

      tcl8.../unix; configure; make; make install, same for tk).</para>

      <para>The next step is to add database-access capability to the

      <para>The next step is to add database-access capability to the

      <application>isqltcl</application> package to generate the

      <sect2>

      <sect2>

        <title>Generating a wish interpreter with &MYSQL; support:</title>

        <title>Generating a wish interpreter with &MYSQL; support:</title>

        <para>Both the static and dynamic load methods are supported by

        <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>When you are ready:</para>

        <para>When you are ready:</para>

        <orderedlist>

        <orderedlist>

          <listitem>

          <listitem>

        your <filename>Makefile</filename> to link with

        your <filename>Makefile</filename> to link with

        <filename>mysqltcl.o</filename>. You have probably been

        <filename>mysqltcl.o</filename>. You have probably been

        through this already. Have a look at the included

        through this already. Have a look at the included

        <filename>tkAppinit.c</filename></para>

        <filename>tkAppinit.c</filename></para>

      </sect2>

      </sect2>

    </sect1>

    </sect1>

  </chapter>

  </chapter>

    <title>Using &SQLSC;</title>

    <title>Using &SQLSC;</title>

    <para>The following is organized more like a reference manual, there

    <para>The following is organized more like a reference manual, there

    is little tutorial material. You may want to look at the 

    is little tutorial material. You may want to look at the 

    to get a quick idea.</para>

    to get a quick idea.</para>

      <title>Environment variables</title>

      <title>Environment variables</title>

      <para>The <literal>SQLDBTYPE</literal> environment variable

      <para>The <literal>SQLDBTYPE</literal> environment variable

      decides what database code is going to be used. It has several

      decides what database code is going to be used. It has several

      possible values:</para> 

      possible values:</para> 

      <itemizedlist>

      <itemizedlist>

        <listitem>

        <listitem>

          <para><literal>MYSQL</literal> to access a &MYSQL; database.</para>

          <para><literal>MYSQL</literal> to access a &MYSQL; database.</para>

        </listitem>

        </listitem>

        <listitem>

        <listitem>

          <para><literal>INFORMIX</literal> to access an INFORMIX

          <para><literal>INFORMIX</literal> to access an INFORMIX

          database.</para> 

          database.</para> 

        </listitem>

        </listitem>

        <listitem>

        <listitem>

          <para><literal>MSQL</literal> to access an MSQL database.</para>

          <para><literal>MSQL</literal> to access an MSQL database.</para>

        </listitem>

        </listitem>

      </itemizedlist>

      </itemizedlist>

      <para>The default if the variable is not set is to use &MYSQL;. Don't

      <para>The default if the variable is not set is to use &MYSQL;. Don't

      depend on it.</para>

      depend on it.</para>

      first call to <function>sqlscreen</function>. (It is set to

      first call to <function>sqlscreen</function>. (It is set to

      <literal>MYSQL</literal> 

      <literal>MYSQL</literal> 

      inside the sample scripts).  Of course, the value of this

      inside the sample scripts).  Of course, the value of this

      variable must be consistent with what extensions are available

      variable must be consistent with what extensions are available

      to the TCL interpreter (See <link

      to the TCL interpreter (See <link

      <para>The <literal>SQLSCLOG</literal> environment variable can

      <para>The <literal>SQLSCLOG</literal> environment variable can

      be set to the name of a file where &SQLSC; will log the SQL

      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>

      statements it executes. The default is to log to stdout.</para>

          <term>Add</term>

          <term>Add</term>

          <listitem>

          <listitem>

            <para>inserts the current values. There is special handling

            <para>inserts the current values. There is special handling

            for auto_increment fields, see 

            for auto_increment fields, see 

            </para>

            </para>

          </listitem>

          </listitem>

        </varlistentry>

        </varlistentry>

        <varlistentry>

        <varlistentry>

          <term>Update</term>

          <term>Update</term>

          <listitem>

          <listitem>

            <para>updates the row according to the current values (how the

            <para>updates the row according to the current values (how the

            WHERE clause is built is described later, see 

            WHERE clause is built is described later, see 

            and 

            and 

          </listitem>

          </listitem>

        </varlistentry>

        </varlistentry>

        <varlistentry>

        <varlistentry>

          <term>Delete</term>

          <term>Delete</term>

      <para>The Update and Add buttons may not exist on all screens (some

      <para>The Update and Add buttons may not exist on all screens (some

      screens may be set up only for querying).</para>

      screens may be set up only for querying).</para>

      <para>Delete is not created by default (See 

      <para>Delete is not created by default (See 

      ).</para>

      ).</para>

      <para>The 

      <para>The 

      section describes how the SQL statements are generated for the

      section describes how the SQL statements are generated for the

      different actions.</para>

      different actions.</para>

    </sect1>

    </sect1>

      <literal>quantity</literal>, a <literal>quantity &gt;

      <literal>quantity</literal>, a <literal>quantity &gt;

      10</literal> condition 

      10</literal> condition 

      will be inserted in the WHERE clause. You could also enter

      will be inserted in the WHERE clause. You could also enter

      <literal>&gt;10 AND quantity &lt; 20</literal>, or whatever

      <literal>&gt;10 AND quantity &lt; 20</literal>, or whatever

      condition you need. (See also

      condition you need. (See also

      generation</link>

      generation</link>

      ).</para>

      ).</para>

    </sect1>

    </sect1>

  </chapter>

  </chapter>

    <title>Programming Interface</title>

    <title>Programming Interface</title>

      <title>Overview</title>

      <title>Overview</title>

      <para>All exchanges between the package and the user application are

      <para>All exchanges between the package and the user application are

      made through TCL arrays that hold all data and parameters. There is

      made through TCL arrays that hold all data and parameters. There is

      one such array for every screen. The array name is not significant

      one such array for every screen. The array name is not significant

      instead. <function>sqlscreendelete</function> is mostly useful in case you want to

      instead. <function>sqlscreendelete</function> is mostly useful in case you want to

      recreate the screen with different options (most options can't be

      recreate the screen with different options (most options can't be

      changed once a screen is created).</para>

      changed once a screen is created).</para>

    </sect1>

    </sect1>

      <title>Initialization and termination</title>

      <title>Initialization and termination</title>

      <sect2>

      <sect2>

        <title>sqlscreen</title>

        <title>sqlscreen</title>

        <para>To create a screen, you set values inside a TCL array

        <para>To create a screen, you set values inside a TCL array

        (See the following section: 

        (See the following section: 

        ), then perform creation as follows:</para>

        ), then perform creation as follows:</para>

        <programlisting>

        <programlisting>

sqlscreen yourarrayname

sqlscreen yourarrayname

        <para><function>sqlscreen</function> optionally takes a

        <para><function>sqlscreen</function> optionally takes a

        second parameter. If the value is <literal>h</literal>, the

        second parameter. If the value is <literal>h</literal>, the

        fields will be arranged horizontally instead of 

        fields will be arranged horizontally instead of 

        vertically. There are other ways to do this(see 

        vertically. There are other ways to do this(see 

        but it can still be useful in some cases.</para>  

        but it can still be useful in some cases.</para>  

      </sect2>

      </sect2>

      <sect2>

      <sect2>

        <title>sqlscreendelete</title>

        <title>sqlscreendelete</title>

        <programlisting>

        <programlisting>

sqlscreendelete arrayname

sqlscreendelete arrayname

        </programlisting>

        </programlisting>

      </sect2>

      </sect2>

      <title>Setting fonts for &SQLSC; applications</title>

      <title>Setting fonts for &SQLSC; applications</title>

      <para>The font used by the screen can be set by 

      <para>The font used by the screen can be set by 

        setting the <literal>font</literal> array entry</link>. This

        setting the <literal>font</literal> array entry</link>. This

        will only adjust the font for the specific screen. It may

        will only adjust the font for the specific screen. It may

        be more convenient to set the font at the start of the

        be more convenient to set the font at the start of the

        application script, with a variation on the following example:</para>

        application script, with a variation on the following example:</para>

    <programlisting>

    <programlisting>

    </sect1>

    </sect1>

      <title>Interface array entries</title>

      <title>Interface array entries</title>

        <title>General parameters</title>

        <title>General parameters</title>

        <sect3>

        <sect3>

          <title>window</title>

          <title>window</title>

          clause (see the following paragraph), and you will not be able

          clause (see the following paragraph), and you will not be able

          to modify data through the screen. (You CAN update several

          to modify data through the screen. (You CAN update several

          tables in one application, but each table will need a separate

          tables in one application, but each table will need a separate

          screen, and the screen links will be through cascaded queries,

          screen, and the screen links will be through cascaded queries,

          not join clauses - See 

          not join clauses - See 

          ).</para>

          ).</para>

        </sect3>

        </sect3>

        <sect3>

        <sect3>

          <programlisting>

          <programlisting>

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

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

          </programlisting>

          </programlisting>

        </sect3>

        </sect3>

          <title>columns</title>

          <title>columns</title>

          <para>This is a list to define the columns that you want

          <para>This is a list to define the columns that you want

          included. If it is not set, sqlscreen will query the database

          included. If it is not set, sqlscreen will query the database

          for all the column names in the table, and build the screen with

          for all the column names in the table, and build the screen with

          last) spans the remaining columns. You will probably need several

          last) spans the remaining columns. You will probably need several

          tries to get it right (at least I usually do).</para>

          tries to get it right (at least I usually do).</para>

        </sect3>

        </sect3>

      </sect2>

      </sect2>

        <title>Screen attributes</title>

        <title>Screen attributes</title>

        <sect3>

        <sect3>

          <title>queryonly</title>

          <title>queryonly</title>

          <para>The value has no importance, just setting the array entry

          <para>The value has no importance, just setting the array entry

          (even to <literal>no</literal>) creates a screen for query

          (even to <literal>no</literal>) creates a screen for query

          only.</para>

          only.</para>

        </sect3>

        </sect3>

          <title>allowdelete</title>

          <title>allowdelete</title>

          <para>If this entry is set, and <literal>queryonly</literal>

          <para>If this entry is set, and <literal>queryonly</literal>

          is not set a <guilabel>Delete</guilabel> button will be

          is not set a <guilabel>Delete</guilabel> button will be

          created.</para> 

          created.</para> 

        </sect3>

        </sect3>

          <title>font</title>

          <title>font</title>

          <para>If this entry is set, the value will be used as a font

          <para>If this entry is set, the value will be used as a font

          definition for the screen elements. Any TK font definition

          definition for the screen elements. Any TK font definition

          can be used. There are <link

          can be used. There are <link

          ways</link> to set the application font.</para>

          ways</link> to set the application font.</para>

        </sect3>

        </sect3>

        <sect3>

        <sect3>

          package is available, the buttons will be created with icons

          package is available, the buttons will be created with icons

          instead of textual labels.</para> 

          instead of textual labels.</para> 

        </sect3>

        </sect3>

      </sect2>

      </sect2>

        <title>Field attributes</title>

        <title>Field attributes</title>

        <sect3>

        <sect3>

          <title>Column type and length</title>

          <title>Column type and length</title>

set customer(ordercols) "custid desc, custname"

set customer(ordercols) "custid desc, custname"

          </programlisting>

          </programlisting>

        </sect3>

        </sect3>

          <title>updateindex</title>

          <title>updateindex</title>

          <para>This defines a column name (or a list of column names)

          <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

          that will be used in the WHERE clause of an UPDATE statement. It

          should provide a way to uniquely identify a row.</para>

          should provide a way to uniquely identify a row.</para>

          <para>If no <literal>updateindex</literal> list finally

          <para>If no <literal>updateindex</literal> list finally

          exists, the screen will have no <guilabel>Update</guilabel>

          exists, the screen will have no <guilabel>Update</guilabel>

          button.</para>

          button.</para>

          <para>See 

          <para>See 

          for a more complete discussion of update row selection.</para>

          for a more complete discussion of update row selection.</para>

        </sect3>

        </sect3>

        <sect3>

        <sect3>

          display the corresponding row in the main screen.</para>

          display the corresponding row in the main screen.</para>

          <para>This capability will only be available if an

          <para>This capability will only be available if an

          <literal>updateindex</literal> list has been defined for the

          <literal>updateindex</literal> list has been defined for the

          screen (either implicitely or explicitely), see <link

          screen (either implicitely or explicitely), see <link

          updateindex section</link>. The

          updateindex section</link>. The

          <literal>updateindex</literal> columns must be part of

          <literal>updateindex</literal> columns must be part of

          <literal>list_columns</literal>, so that we can uniquely

          <literal>list_columns</literal>, so that we can uniquely

          link back from the list to the detail screen.</para>

          link back from the list to the detail screen.</para>

          <title>sqlsc_colname_valsaved</title>

          <title>sqlsc_colname_valsaved</title>

          <para>The package uses these entries to save the database values

          <para>The package uses these entries to save the database values

          when a query is performed. This is used to compute the UPDATE

          when a query is performed. This is used to compute the UPDATE

          statements (See the 

          statements (See the 

          section).</para>

          section).</para>

        </sect3>

        </sect3>

      </sect2>

      </sect2>

    </sect1>

    </sect1>

        when the end of a query is reached. The sound can become quite

        when the end of a query is reached. The sound can become quite

        ennoying...</para>

        ennoying...</para>

      </sect2>

      </sect2>

    </sect1>

    </sect1>

      <title>Linking screens</title>

      <title>Linking screens</title>

      <para>It is possible to link two screens so that a change in one

      <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

      screen will update the other one. This can be done in two

      ways.</para>

      ways.</para>

        </listitem>

        </listitem>

      </itemizedlist>

      </itemizedlist>

    </sect1>

    </sect1>

  </chapter>

  </chapter>

    <title>SQL generation</title>

    <title>SQL generation</title>

    <para>Every time the user presses a button like query or update, the

    <para>Every time the user presses a button like query or update, the

    program will generate a SQL statement to perform the appropriate

    program will generate a SQL statement to perform the appropriate

    operation on the database. The following paragraphs describe how the

    operation on the database. The following paragraphs describe how the

exit 0

exit 0

      </programlisting>

      </programlisting>

    </sect1>

    </sect1>

  </chapter>

  </chapter>

    <title>Sample scripts</title>

    <title>Sample scripts</title>

    <para>Three sample scripts are provided with the package (in the

    <para>Three sample scripts are provided with the package (in the

    samples directory). These are not real applications. For example you

    samples directory). These are not real applications. For example you

    have to set the password in the environment or edit the scripts to

    have to set the password in the environment or edit the scripts to