GSQL in detail

How it works

See also the other document proc-fmt.html on details of the PROC file, and for a simple example.

GSQL is a simple Mosaic gateway to SQL databases. It parses a SQL-specification file (called a PROC file) to create a form, and then with the user-inputs, call a database backend program to process the SQL query. The PROC file maps components of the SQL string to widgets (fields, buttons, pulldown menus, etc) for user input or selection. Details on writing a PROC file can be found in proc-fmt.html.

GSQL is a C program that is invoked through a shell script by the HTTP server. (I find it's too clumsy to invoke GSQL directly as a URL. If anyone knows a nice method to do this, let me know.) Currently, the URLs I'm using refer to shell scripts. eg:

http://base.ncsa.uiuc.edu:1234/htbin/wmenu1

This shell script (wmenu1) will then invoke GSQL. The script looks like this:

# /bin/sh
/home/jason/htbin/gsql /home/jason/sqlstuff/wfile.proc $*

or if you're using http 1.1,

# /bin/sh
/home/jason/htbin/gsql /home/jason/sqlstuff/wfile.proc $QUERY_STRING

The httpd server will pass the rest of the arguments via "$*" or the "$QUERY_STRING" environment variable. If there's none, only the PROC file (wfile.proc) is passed to GSQL, and it will parse that file and send HTML commands to Mosaic to create a form. Thus for each PROC file, you should have a shell script somewhere which the httpd server can execute.

Downloading and Installing GSQL

You need to get:

a http server, eg NCSA httpd server (v 1.0 or later) available on NCSA's anonymous ftp (ftp.ncsa.uiuc.edu in the directory /ftp/Mosaic/ncsa_httpd/httpd_1.0).

the GSQL source, available on NCSA's anonymous ftp (ftp.ncsa.uiuc.edu in the directory /ftp/Mosaic/ncsa_httpd/gsql). The GSQL source include:

gsql.c       -  the GSQL source (yep, just 1 file :-) ).
make.gsql    -  how to compile gsql. 

sqlmain.c    -  sample backend main.
sqlutil.c    -  utility functions for backend program. 
sybproc.c    -  sample database backend code (sybase example here).
make.backend -  how to compile a backend (sybase example here). 

wfile1.proc  -  sample PROC file.
wmenu1       -  sample shell script.

howto.html    - this document.
proc-fmt.html - how to write a proc file.

GSQL may be compiled using gcc or cc. Relatively simple. Important Note: If your database uses the SQL keyword RETRIEVE instead of SELECT, you need to edit gsql.c and replace

 #define SELECTKEYWORD "SELECT"

with

 #define SELECTKEYWORD "RETRIEVE"

The backend program can be written in any language like C, Perl, tcl, etc. The sample backend program is a C program that uses sqlmain.c, sqlutil.c.

The shell script should be placed someplace accessible by the httpd server (eg the htbin directory of the httpd root directory). Everything else (eg, gsql, sqlmain, PROC files) can be placed someplace secure.

Since GSQL is invoked through a shell script, the PROC file is totally hidden from the client. Caution: This is important since it could contain whatever information is needed to gain access to your database (like logins and passwords). The PROC files should also be placed in some directory that is protected from user-access via ftp or httpd. You might want to use NCSA's httpd server, which can be configured to deny access to specific directories.

Testing

Test gsql on just a proc file:

gsql wfile1.proc

This will just output form-creation (html) commands to standard output based on the proc file. Save the output to a file, and look at the form through Mosaic. Try out different forms by editing the proc file.

Test sqlmain on a query: This tests your specific database application (sqlmain) to see if it correctly processes a SQL query ie, run sqlmain with the sql query and the user-defines (all quoted separately) as command-line arguments, eg:

sqlmain "SELECT clientdb..client.last_name, clientdb..client.first_name, FROM clientdb..client WHERE clientdb..client.last_name LIKE 'J%' ORDER BY clientdb..client.last_name " "LOGIN foo" "PASSWORD baz" "SYBASE_ENV SYBASE=/usr/local/sybase"

The above example (for sybase) has 4 arguments to sqlmain.

Writing the Database Backend

The Backend is your database program that processes th SQL query. It is invoked by GSQL - through a system() call - after the user has filled in the form. It can be written in C, Perl, tcl, etc. A sample backend, written in C for sybase is found in sqlmain.c. Recall that GSQL will build the SQL string, parse the DEFINES from the PROC file, and then call the Backend specified in the PROC file using the SQLPROG command.

Inputs to the Backend:
arg[0]           - name of executable (the backend).
arg[1]           - the SQL string.
arg[2], arg[3],. - user-defines of the form "name value" eg "USER jason ng".

The sample backend program (written in C) is described: #include <stdio.h> #include <string.h> main (ac,av) int ac; char**av; { char * sqlstring = NULL; if (ac<3) exit(0); parseargs(ac,av); sqlstring = strdup(av[1]); execute_sql(sqlstring); } You get to write the routine execute_sql(). The SQL string is passed into this routine, and what you do with it is totally up to you. Minimally you should process that SQL string and just print out the results using printf(). The output will be automatically piped to the Mosaic client. Presumably you will want to do your own HTML formatting, compleat with images, links, etc, etc..

The user-defines are made available to your application through 2 utility functions, app_parseargs() and app_getdefine().

void app_parseargs (int argc, char**argv);

parses the command-line arguments for user-defines. Must be called before any app_getdefine() can be called.

char* app_getdefine (char * name);

returns a pointer to the value of the define specified by name;

app_parseargs() should be called before anything else. It takes the command-line arguments, and separate the names from the values in the user-defines. After that, app_getdefine() should be called in elsewhere in your program to retrieve the value for the define that you need.

Here is a really bare bones sybase example. The basic steps are setting up and initializing the database, executing the query, and printing out the results. Note how app_getdefine() is used to retrieve the user-defined values needed for the setup.

int execute_sql (char *sqlstring) /* for sybase */

  int i;
  DBPROCESS  *dbproc, *init_sybase;
  char * app_getdefine();

  /* -- setup environment, and init the database */
   putenv( app_getdefine("SYBASE_ENV"));
   dbproc = init_sybase ( app_getdefine("LOGIN"), 
                        app_getdefine("PASSWORD"));

  /* -- execute the sql string */
   dbcmd (dbproc, sqlstring); /* send query to sybase */
   dbsqlexec (dbproc);

  /* -- bind the results */
   nfields =  dbnumcols (dbproc);
   for(i=0;i < nfields;i++) dbbind(dbproc, i+1, STRINGBIND, 0, results[i]);
           
  /* -- retrieve each row, and print the results */
   while (dbnextrow(dbproc) != NO_MORE_ROWS) {
      for (i=0;i < nfields;i++) printf("%s ", results[i]);
      }
  /* -- all done -- */
}

Dec 1993 Send comments and questions to Jason Ng at likkai@ncsa.uiuc.edu.