PHP is an extremely popular open source
scripting language. When the language itself is an open source, many
open source products use it. Unfortunately, PHP5 is a new version
of PHP and most of the open source products are written with PHP4.
In addition, most of the open source software written in PHP is
written for open source databases, such as MySQL, SQL Lite and
PostgreSQL.
However, open source products that work with PHP5
and Oracle do exist and this chapter is devoted to one such product,
Tim Strehle’s wonderful, Oracle Editor.
Oracle Editor is a general purpose web form used
for querying, inserting, deleting or updating any Oracle table. The
form is created dynamically for each table, and the interface is
extremely natural and easy to use. This tool is an open source tool
and can be downloaded free of charge from
http://tim.digicol.de/oraedit.
Oracle Editor is written as one large PHP script
named OracleEditor.php. All a person needs to do is download the
script and place it into a directory visible by the Apache web server.
On one PC, it is available in the /PHP/util
directory and is invoked by pointing the browser to the following URL:
http://localhost/PHP/util/OracleEditor.php.
The directory /PHP is not a real directory on this
system, it is a virtual directory, defined for the Apache web server
by using the following directives in the configuration file:
So, what happens when OracleEditor.php is
invoked? An ordinary login screen appears, like many that were
shown earlier in this book:
The author of the product advises the user to
protect this page with an additional protection mechanism such as .htaccess
or SSL, but this may be unnecessary because there does exist, after
all, Oracle’s own authorization. The user must already have username
and password to login to the database, so requiring a login twice is
pointless. So, now the user can login using the highly secure username
and password combination, namely SCOTT/TIGER:
All that remains to do is to select the table to
work on in the “FROM” field and the editing can begin. Here it what
the editing looks like, shown on the table DEPT:
The first column allows the user to select an
update or delete operation on the existing row. Immediately after the
last row of the table, an “insert new row” link appears which brings
up an additional row of the table and inserts the entered values into
the database. Here is what the screen looks like:
A value or a function can be entered; the script
works with both. Clicking on the insert button inserts the row into
the database. Clicking on the “Delete” link in the first column brings
up a similar screen:
Clicking on the delete button purges the newly
created shipping department in Miami, leaving the table in its
original state. Oracle Editor is written using the OCI8 module in the
auto-commit mode. This means that there is no rollback. Once the
delete button is pressed, the record is deleted and the change is
automatically committed.
The script can also export the table in one of the
three most popular formats: XML, CSV or HTML. This looks like the
following:
The created file is
called dbexport.csv (extensions are xml or html for the other two
formats) and is saved to the user’s directory of choice. All column
values are enclosed with double quotes and separated by a comma, which
is ideal for SQL*Loader, MS Excel or some other spreadsheet processor,
like the one within the SUN Microsystems OpenOffice software suite.
There is however a quirk within this tool; the
return from the export page back to editor page is not smooth or easy.
The user has to follow the link for the manual SQL entry and from
there back to the automatically populated SQL. The table has to be
chosen again and the SQL re-executed. If the intention is to return
back to the exact rows that were in the editor window before saving
the table, this will not happen.
The philosophy of the tool is one of screen
editor; edit a table, save the modifications into a file and get out.
This philosophy is consistent with the name, an admirable quality in
the age of very resounding and meaningless marketing names leaving
possible users wondering about the purpose of the software.
The manual entry mode allows the entry of any SQL
statement, with no rollback, but does not allow the editing of the
results. Results can only be edited when the SQL is generated
automatically. Here is what the window for the manual SQL entry looks
like:
Oracle Editor is a very convenient tool for
editing small tables on the fly without the need for writing
cumbersome DML statements manually and has many convenient options. It
is not a general data entry tool and cannot be used instead of Oracle
Forms or similar products.
The source code of the script has 1700+ rows and
reading it is a highly advanced exercise and would not be very
meaningful for a book that is intended to be an introduction into
using PHP5 with Oracle. OracleEditor.php is written using PHP4 and
works with any PHP newer than 4.1.0 with session support and OCI8
support. In particular, it works well with PHP5.
