OraForm: Generically FORM'ing a multi-table query for the Oracle RDBMS

Introduction

How OraForm Works

OraForm consists of 6 programs (oraform.html, oraform1.pc, oraform2.pc, cleaner.sh, sql-build.c and sql-result.pc). Each of these programs are called sequentially, unless one program terminates due to an error (in which case it reports the appropriate Oracle error message to the web client). oraform1.pc, oraform2.pc and sql-result.pc are written in C with embedded SQL, with the executable programs being compiled using Oracle's Pro*C precompiler and a standard C compiler. sql-builder.c is written in standard C, and oraform.html and cleaner.sh are a HTML file and a C shell script respectively.

oraform.html

oraform.html forms the entry point to the OraForm system. It prompts the user to enter their Oracle username and password, and then calls the first program, oraform1.

oraform1.pc

oraform1 retrieves all the tables and views that the user has access to in the Oracle database , and presents these as a multicolumn select form to the web client. Once the user has selected one or more tables, oraform2 is called.

oraform2

oraform2 selects all the unique columns from the tables selected in oraform1 and presents them to the web client as a fill-in form. Uniqueness of a column is determined by its name, its datatype and precision. For example, consider the two tables below:

	    _________________________________________

	    | EMP               | DEPT              |

	    |-------------------|-------------------|

	    | empno     N(4)    | deptno  N(2)      |

	    | ename     C(10)   | dname   C(14)     |

	    | job       C(9)    | loc     C(13)     |

	    | mgr       N(4)    |                   |

	    | hiredate  date    |                   |

	    | sal       N(7,2)  |                   |

	    | comm      N(7,2)  |                   |

	    | deptno    N(2)    |                   |

	    |-------------------|-------------------|

			    {table 1}

cleaner.sh

Once the filled-in form is submitted, the shell script cleaner.sh is called. cleaner.sh translates escaped characters such as % and < to ASCII, and then passes the edited query string to sql-build.c, the SQL statement generator.

sql-build.c

The purpose of sql-build.c is to break the query string generated by the web client into a meaningful SQL statement. At present, sql-build is coded only to handle select type statements, but future developments will incorporate the ability to handle insert, delete and update operations as well.

A SQL select statement is made up of three blocks, which I have called the "select", "where" and "join" blocks. The "select" block selects the required display records from the relevant tables, the "where" block applies the search conditions to the statement, and the "join" block attempts to avoid Cartesian joins between tables by matching the primary and foreign keys between tables. The "select", "where" and "join" blocks for the query specified in figure 3 are shown in table 2 below:

	_______________________________________________________

	| SQL Statement                            | Block    |

	|------------------------------------------|----------|

	| SELECT dept.dname, emp.empno, emp.ename  | Select   |

	| from dept, emp                           |          |

	|------------------------------------------|----------|

	| WHERE (dept.deptno = '20') AND           |          |     

	|       (emp.empno > = '6000' OR           |          |

	|        emp.empno < = '8000') AND         | Where    |

	|       (emp.job = 'CLERK' OR              |          |

	|        emp.job = 'ANALYST')              |          |

	|------------------------------------------|----------|

	| AND (dept.deptno = emp.deptno)           | Join     |

	|------------------------------------------|----------|

			    {table 2}

sql-result.pc

sql-result.pc is Oracle's sample9.pc program, with the SQL statement module modified to accept the username, password and SQL statement from sql-build.pc rather than getting this information from the keyboard. Providing that the search conditions were properly entered at oraform2.pc, and the search conditions find matching records in the data base, a screen like figure 4 will be produced. Should the search conditions not find any matching data, the same screen will be displayed, with no records retrieved and the message "0 records processed".

Further Work

At present, only the join between tables is user defined (the OR part of the generated sql statement). Whilst this is adequate for single data values, it reduces the flexibility of the system when the user is performing a task on multiple data values. For example, say we wish to find name of all employees who have a manager number greater than 7600 excluding all those employees with a manager number of 7782. The form line for this would look like figure 6. The current version of OraForm would equate this to "where emp.mgr > = '7600' OR emp.mgr != '7782'", which would return 13 rows. What is required is a line that includes the join condition at the end, as shown in figure 7, or by allowing the user to explicitly state the join condition on the line itself, as shown in figure 8. By allowing user control over the join between data values, OraForm would equate this to "where emp.mgr > '7600' AND emp.mgr != '7782'", which would return 10 rows. This requires a small amount of reworking of both oraform2 and sql-build, and is currently being worked on.

{figure 6}

{figure 7}

{figure 8}