Graphics, Visualization, and Usability Center

GSQL-ORACLE Backend

Introduction:

Welp, a lot of people have requested this so here is a modest implementation. Please note that this distribution requires that the use of PRO-C, the C language development environment for ORACLE. If you do not have PRO-C installed as part of the ORACLE distribution do NOT use this distribution. Also, knowledge of C and your operating environment is assumed.

Availability:

This distribution is meant to supplement/replace the gsql distribution written by Jason Ng at NCSA (likkai@ncsa.uiuc.edu). The reason for a separate distribution is keep things simple. Everything you need to know is here. If you want the original gsql files, they are available via anonymous ftp from:

ftp.ncsa.uiuc.edu, in the directory /ftp/Mosaic/gsql. Download the file gsql.tar

The GSQL-ORACLE distribution is available via anonymous ftp from:

ftp.cc.gatech.edu, in the directory /pub/gvu/www/pitkow/gsql-oracle. Download the file: gsql-oracle.tar

Installation:

Please refer to the original documents first. After you have read those documents, proceed. Note, this methodology has only been tested on SunOS 4.1.3. Here is a list of what needs to be done:
  1. Change the name of the sqlprogram variable (line 12) in the file gsql.c to point to the location of the backend executable. This value can be overridden by setting the variable: SQLPROGRAM in the .proc file.
  2. Change the path information in the file imgdb.sh to point to where the gsql-oracle directory is located. Once this is done, place a copy in your cgi-bin. Make sure that the file is executable.
  3. Go into the pro-c directory.
  4. Copy ~oracle/proc/demo/sample9.pc into this directory
  5. Type: patch < gsql-oracle-patchfile
  6. Change the name of the resulting file to whatever you want as long as it has the ".pc" extension. For this example we will call it foo.pc Make sure that the name you change it to is the same as the name you have for sqlprogram (line 12 of gsql.c OR bound to SQLPROGRAM in the .proc file!!!
  7. Edit this file, changing the WWW, ACCOUNT, and PASSWD variables (lines 37 - 42) to suit your setup.
  8. Copy the proc.mk file from the oracle proc distribution to this directory.
  9. Type: make -f proc.mk foo
    Remember that foo is the name from step 3 above without the ".pc" extension.
  10. Finally, inspect the file: setup.sh to make sure that it contains the correct location for your ORACLE environment.

    11. If you have trouble with finding/compiling/etc. consult with your ORACLE administrator.

If all went well, a file called foo will be created. Make sure the permissions are set to execute (e.g. chmod +x foo). It will also have generated a foo.c and a foo.lis file. These were used by the PRO-C compiler to make the executable. It is best to make whatever changes to the foo.pc file and recompile than to make the changes to the foo.c.

How it works:

Hopefully this section will clarify how the pieces fit together. Due to copyright reasons, the source code generated by PRO-C will not be distributed. The program sample9.c is property of ORACLE, even though I modified it slightly. Basically PRO-C allows for high level descriptions of ORACLE functions to be converted into ORACLE specific C code. This code is compiled into a stand alone executable. This executable takes an SQL command as the first command line argument and outputs the result from ORACLE in HTML. In order for the program to run properly, the location of your WWW server, your database account name and password have to have ben changed before compiling. The program will take the value of any attribute that contains the substring "gif" and make it an in-lined image, with an anchor to the actual image. Note that this means the attribute is to be the FULL path from your htdocs directory. Example: 
Attribute in ORACLE database:		

images/234522345.gif

Output from backend:

<A HREF=http://yourmachine/images/234522345.gif><IMG SRC=http://yourmachine/images/234522345.gif></A>
So, when your www server gets a request for the Bourne shell script in the cgi-bin here's what happens. The script will call gsql. If there is one command-line arguments, gsql will parse the .proc file as listed in the shell, and create a form. This form will be sent back to the client. Once the client has filled out the form, the shell will be executed again, which will execute gsql, again. Now, gsql will receive more than one command line argument. Gsql will parse the command line, and create an SQL statement from it. Once this is done, gsql will invoke the stand-alone executable ORACLE backend, with the SQL query as the command line argument. The backend will process the query and return the results embedded in HTML. These results are sent back to the user.

Additional Notes:

This distribution is provided as is mostly as service for those who asked to see how to implement the ORACLE backend. Please feel free to send any comments or suggestions to me at: pitkow@cc.gatech.edu

Thanks.
James Pitkow
Graphics, Visualization and Usability Center
Georgia Institute of Technology
pitkow@cc.gatech.edu