GSQL PROC file commands

See also the other document howto.html on installing and writing the database backend.

Getting Started

Using the gateway is really easier than what this document makes it to be. The best way to go about using GSQL is to start with the sample.proc file and modify it and see what you get. Heck, you don't even need to write your database backend until later. In the meantime, just experiment with the syntax and commands of the PROC file. And it's like a higher-level HTML - with its pros and cons..

Commands can appear in any order without affecting the SQL query. Display commands (eg HEADING, TEXT, SHOW) will be executed sequentially as they appear in the proc file. All commands must be terminated by a semi-colon.

Introduction

The PROC file contains commands that GSQL uses to map components of the SQL query string to widgets (field, buttons, pulldown menus, etc) for user input and selection. The mapping is done by declaring a variable for each phrase of the SQL query string that requires user input. This is usually a database field. From the inputs and the mappings, GSQL will build the complete SQL query to be sent to the database backend program.

A Simple Example

This example is found in sample.proc. It is described in detail below.

We start off with some SQL directives. The line

SQLPROG /home/likkai/sqlstuff/sqlmain;

specifies where your database backend is. The line

FROMLIST  helpdeskdb..ticket , helpdeskdb..user, helpdeskdb..recipient, clientdb..client;

specifies which databases and tables are being used, and will be substituted verbatim into the SQL query. The line

WHERELIST helpdeskdb..ticket.uid = helpdeskdb..user.uid and
     helpdeskdb..ticket.opened_by = clientdb..client.client_id and
     helpdeskdb..ticket.recipient_id = helpdeskdb..recipient.recipient_id;

specifies JOINS between tables and databases, and are needed in many if not all dbmses.

The DEFINES commands are used to supply additional information to your database backend program. In this Sybase example, the application-specific keywords "LOGIN", "PASSWORD" and "SYBASE_ENV" are defined, presumably for initializing the database.

DEFINE  LOGIN whoever;
DEFINE  PASSWORD whatever;
DEFINE  SYBASE_ENV SYBASE=/usr/local/sybase;

SUB/SHOW command pairs are used to define variables and create widget mappings. The SUB command defines a variable for a SQL phrase. There are 2 types of variables in a SUB command: SELECTLIST and WHERELIST. As the names suggest, they map to phrases in the SELECT and WHERE clauses of a SQL query rspectively.

The $ (dollar) sign is a placeholder where the user-inputs will be substituted directly into. It shows up in the phrase for a WHERELIST variable because it's often used to specify a condition for the query. Thus for

SUB user WHERELIST AS helpdeskdb..user.logon like '$';
SHOW user TITLE "Ticket User <p>" FIELD;

with user input "htunca", the phrase helpdeskdb..user.logon like 'htunca' will appear in the WHERE clause of the final SQL query.

The line

SUB subject WHERELIST AS upper(helpdeskdb..ticket.subject) like upper('%$%');

makes use of Sybase's built-in functions. Both user input and actual database field are converted to uppercase before comparing. The % signs are wildcards in Sybase. Since GSQL does not check the contents of the SQL phrases, you are free to use whatever your database librray supports.

You should begin to get a feel for how the variable substitution works. Basically any any legal SQL phrase or whatever extensins or built-in functions is supported by your database library may be associated with a vraiable and hence be mapped to a widget.

In this SUB command, a SELECTLIST variable is mapped to TWO database fields.

SUB ropen_name  SELECTLIST AS ( clientdb..client.last_name+','+clientdb..client.first_name);

Thus if ropen_name is selected, the resulting SQL query will return the last and first names, nicely formatted using Sybase built-in functions. SHOW commands will instantiate a variable to simple widgets like Field or Button, or to menu widgets. The SHOW command supports 4 menu types: SCROLL, PULLDOWN, BUTTONSET and RADIO. These commands takes a list of comma-separated menu items. eg

SHOW  in_mthopen TITLE "for which month? <p>" PULLDOWN ,01,02,03,04,05,06,07,08,09,10,11,12;

Note that HTML commands like <p> can be part of the title. Alternatively an EXEC can be used to generate the menu-items on the fly. eg

SHOW folks TITLE "folks at work now? <p>" SCROLL EXEC (who);

Substitution works the same for variables that map to both simple and menu widgets. ie, the $ sign in the corresponding SQL phrase is replaced by the user input, and that SQL phrase is assembled into the final SQL query.

A variable prefixed with an underscore (_) takes on a special meaning. It becomes an indirect variable. ie, inputs or selections for that variable are themselves variables subject to another level of substitution. In the following, _displaylist is an indirect variable.

SUB rticket     SELECTLIST AS helpdeskdb..ticket.ticket;
SUB rrecipient  SELECTLIST AS helpdeskdb..recipient.recipient_name;
SUB rsubject    SELECTLIST AS helpdeskdb..ticket.subject;

SUB  _displaylist    SELECTLIST variable $;
SHOW _displaylist    TITLE  "Select field(s) to see:" SCROLL
			rsubject,  rrecipient, rticket ;

If its "rsubject" menu-item selected, the value "rsubject" is assumed to be a variable, and its value (ie, "helpdeskdb..ticket.subject")will be used as the value of _displaylist. Hence all menu-items for an indirect variable should also be defined using SUB commands. (Compare with direct variables, where the input becomes directly part of the final SQL string.) This is useful for allowing selection of database fields to be displayed, and being able to display meaningful names of menu-items as opposed to often cryptic database field names.

DISPLAY COMMANDS

TEXT

Format: TEXT any-html-text;

Displays any html text in the fillout form (only). TEXT and SHOW commands are executed sequentially as they appear in the PROC file.

HEADING

Format: HEADING any-html-text;

Displays a heading for the fillout form. This heading will also be displayed in the query results page. Any valid html text can be used; the entire text is sent to Mosaic without intepretation.

SHOW

Format: SHOW var TITLE "title-string" [WIDGETTYPE] [MENUOPTION]

Creates a widget for user entry or selection. WIDGETTYPE is one of

BUTTON    - creates a button widget.
FIELD     - creates a text-entry widget. 
PULLDOWN  - creates a pulldown menu.        SINGLE selection.
SCROLL    - creates a scroll menu.          MULTIPLE selection.
RADIO     - creates a set of radio buttons. SINGLE selection.
CHECKBOX  - creates a set of buttons.       MULTIPLE selection.

BUTTON and FIELD are single input widgets. FIELD is used for specifying a condition in a WHERELIST clause. BUTTON is used for specifying a database filed in a SELECTLIST clause. PULLDOWN, SCROLL, CHECKBOX and RADIO are multi-itemed selection widgets. In the PULLDOWN or RADIO widgets, the user may select only one item. In the CHECKBOX and SCROLL widgets, several items may be selected. (Just click on the buttons for the CHECKBOX widget, or hold down the control key for the SCROLL widget).

MENUOPTION is available only for menu widgets (PULLDOWN, SCROLL, CHECKBOX, RADIO).

It may be

a comma-separated list of menu-items.

an EXEC command generating output that will become menu-items.

Egs:

SHOW menu1 TITLE "some states" SCROLL idaho, new york, hawaii, n dakota;
SHOW menu2 TITLE "my gif files " PULLDOWN EXEC (ls -1 *.gif);

Examples of actual forms created using the SHOW commands can be found in the section "Examples of the SHOW command" at the end of this document.

GENERAL COMMANDS

SUB

Format: SUB var [LISTTYPE] AS sql-definition;

Defines a local variable to represent a SQL query field or phrase. Recall that the PROC file is used to specify pieces of the SQL query, and that these pieces, together with user inputs, will be assembled into the final SQL query. Variables are used to refer to these pieces of the SQL query, and also to map these pieces to widgets for user inputs or selections. There are 2 types of variables:

WHERELIST
   - for variable associated with a phrase in the SQL query's 
	  WHERE clause.
SELECTLIST
   - for variable associated with a phrase in the SQL query's 
	  SELECT clause.

eg:

    SUB rname SELECTLIST AS helpdeskdb..recipient.recipient_name;

creates a variable rname that represents the database field after the AS. ie helpdeskdb..recipient.recipient_name.

eg:

    SUB name WHERELIST AS helpdeskdb..recipient.recipient_name like '$';

creates a variable name that represents the phrase after the AS. The $ sign will be replaced by the user input or selection.

EXEC

Format: EXEC unix-commands ;

Execute other programs/queries and display the results on the form. The unix-commands are invoked using the system() call. eg:

	EXEC (finger | sort );

displays the sorted results from finger. This can also used as to create menu items for a menu: eg

	SHOW  Bogus2 TITLE "My files:" PULLDOWN EXEC (ls -1);

creates a pulldown menu containing file names. Each output line (terminated by a line-feed) from EXEC is made into a menu item.

DEFINE

Format: DEFINE defvar definition-string

The DEFINE command is used to pass whatever application-specific information to the database program. The gateway will pass a definition to the database program as a command-line argument as a variable-value pair ie, "defvar definition-string". For example, to pass a login-name to the database program, use the command

	  DEFINE username jason

This passes a command-line argument "username jason" to your database program. As many DEFINES as needed can be made, and each will be available to your database program as a separate command-line argument. Your database program should then parse these "variable value" pairs.

SQLPROG

Specifies which database program to run for this PROC file. This is a REQUIRED command. Format: SQLPROG database-program

The database-program is the full path name of your database program. It will be invoked by the gateway when the user has filled out the form.

SQL DIRECTIVES

FROMLIST

Format: FROMLIST from-clause;

Specifies which databases and tables to use for the query. This is a REQUIRED sql directive. The from-clause will be used to build the FROM clause in the SQL query. The from-clause is a comma-separated list of database and table names. In most databases, this is required, but if your database is smart enough to figure out the information, fill it in anyway, and let me know! Eg, for Sybase; Eg

    FROM helpdeskdb..user, helpdeskdb..recipient;

specifies 2 tables (from 1 database) to be used. Eg

    FROMLIST usradmdb..prop_tbl p,  clientdb..client c;

specifies 2 tables (from 2 databases) to be used, and at the same time creates aliases (p and c) that are used to references the fields elsewhere in the query.

WHERELIST

Format: WHERELIST where-clause;

Hardcodes certain conditions for the query, and more importantly, for you to specify the necessary joins when more than one table or database is used. This is an OPTIONAL sql directive (though probably required if more than one table or databases is used). The where_clause will be included as part of the WHERE clause in the SQL query. Eg:

   WHERELIST helpdeskdb..ticket.uid = helpdeskdb..user.uid;

specifies that the uid fields from the 2 tables helpdeskdb..ticket and helpdeskdb..user be used for the join. Thus outer-joins and inner-joins are possible: Eg:

   WHERELIST p.prop_num = pr.prop_num
             and p.client_id = c.client_id and p.prop_num in
             (select prop_num from usradmdb..prop_mach_app_tbl );

(assuming p, pr and c are properly aliased using the FROMLIST directive).

SELECTLIST

Format: SELECTLIST select-clause

Hardcodes certain database fields to be always selected in addition to what the user may select. This is an OPTIONAL sql directive. The select_clause will be included as part of the SELECT clause in the SQL query. Eg

    SELECTLIST  clientdb..client_id;

causes client_id to be always selected (and displayed) in the results. Thus even if the user selects nothing, the client_id will be displayed. The SELECTLIST can also be used to format outputs,by making use of your DBMS's formatting functions. Eg

SELECTLIST  c.first_name + upper(c.last_name);

results in a nicer output of the client's name, like "Gregory COOMBER". So naturally, you can build hypertext links from the results, Eg

SELECTLIST '<A HREF=doit/?' + p.prop_num + '>' + p.prop_num + '</A>';

creates a hypertext link using the resulting proposal number to another program "doit" which presumably knows what to do with the proposal number as input.

SORTLIST

Format: SORTLIST sql-sort-clause;

Specifies a sort order of the query results. This is an OPTIONAL sql directive. If it exist, it will be appended to the query. The sql-sort-clause is any valid SQL sort statement. Eg, For Sybase,

    SORT order by prop_title  asc;

results in an ascending ordering by prop_title.

    SORT order by prop_title  asc, prop_num desc;

results in an ascending ordering by prop_title, then a descending order by prop_num.

Examples of the SHOW command

The 2 commands SUB and SHOW associate a variable to a phrase, and maps the variable to a widget.

SUB  aname SELECTLIST AS helpdeskdb..recipient.recipient_name; 
SHOW aname TITLE "please enter a name" FIELD;

produces

please enter a name

SUB  ptitle  SELECTLIST AS  propdb..prop_title;
SHOW ptitle TITLE "click to show proposal title too" BUTTON ;

produces

click to show proposal title too

SUB  year WHERELIST AS  propdb..review_year = $;
SHOW year TITLE "choose a year" PULLDOWN 1989, 1990, 1991, 1992, 1993, 1994;

produces

choose a year

SHOW places TITLE "choose several " SCROLL kansas, idaho, alaska , nevada, 
		texas, ohio, maine, florida;

produces

choose several

SHOW now TITLE "AGE:" BUTTONSET under 19, 20-29, 30-49, 50-79, over 80 ;

produces

AGE: under 19 20-29 30-49 50-79 over 80


You can also execute Unix commands and have the results appear in a menu:
SHOW now TITLE "choose several " PULLDOWN EXEC (who | head -7);

produces

choose several

Dec 1993

Send comments to Jason Ng at likkai@ncsa.uiuc.edu.