ArsDigita Archives
 
 
   
 
spacer

Oracle Driver

for AOLserver, part of ArsDigita Free Tools

This page was last updated on 2000-09-11.

Note: If you are running version 1.0.2 of the Oracle driver, it is strongly recommended that you upgrade to at least version 2.1 to prevent unexpected error conditions. (See Version History or the README for more details.)

This product lets you use our favorite Web server, AOLserver, with the Oracle RDBMS. If you want to see whether it works, use the database-backed services at photo.net. This is backed by Oracle 8 and handles several queries/second at all times.

Our driver is free but making it work requires some C and Unix expertise. Basically you have to have all the right Oracle libraries on the computer where you are running AOLserver. In practice, we install the complete Oracle RDBMS on the same machine as AOLserver and then all the right stuff is there. Anyway, then you have to compile the driver and make sure that AOLserver runs with the right environment variables set. If this all sounds too hard, then you can hire us to install it on your machine and support you for one year.

Support for special Oracle features

Transactions

Like all decent database products, Oracle supports transactions (a number of inserts, deletes, and updates that happen as a unit, or not at all). Check out the Transactions chapter of SQL for Web Nerds for details on the concept.

To support transactions in the Oracle driver, we hijack ns_db dml and add three new commands:

  • begin transaction - DML statements after this one are now within a transaction
  • end transaction - commit the changes made during the transaction
  • abort transaction - roll back the transaction. Any DML statements made since the begin transaction will be undone, as if they never happened.
For example: 
ns_db dml $db "begin transaction"
ns_db dml $db "insert into users (user_id, email) values (1234, 'markd@arsdigita.com')"
ns_db dml $db "update user_contact_info set aim_name='alex samoyed' where user_id=2342"
ns_db dml $db "delete from user_group_map where group_id=55"
ns_db dml $db "end transaction"
This will affect the contents of three tables once the end transaction statement is executed.

You can combine Tcl catch statements and transaction control: 

ns_db dml $db "begin transaction"

if [catch { ns_db dml $db "insert into ..." 
            ns_db dml $db "update ..." 
	    ns_db dml $db "end transaction" } errmsg] {
    ns_db dml $db "abort transaction"
    # perhaps write an error back to the user
    return
} else {
    # tell the user the transaction was successful
}
This will roll back the transaction if an error happened during the processing of the insert and update. If they finished succesfully, commit the transaction.

In the event of problems you should explicitly abort the transaction rather than letting the page exit do the rollback. If you check for anything that was done in the first (successful) part of the transaction, it will give the NEW value. In the following example, a situation where the first insert worked but the second failed. The 'double-click' check of getting the group count, causing the script to try the redirect - only to find there actually is no group number because moving off the current page rolled back the creation of the group. 

if [catch { ns_db dml $db "begin transaction"
            ns_db dml $db $insert_for_user_groups
            if [info exists insert_for_helper_table] {
                ns_db dml $db $insert_for_helper_table
            } else {
                set $insert_for_helper_table ""
            }
            ns_db dml $db "end transaction" } errmsg] {
                # something went wrong
		# !!! note that this count(*) is picking up the group
		# !!! inserted inside of the transaction
                if { [database_to_tcl_string $db "select count(*) from user_gr\
oups where group_id = $group_id"] > 0 } {
                    # group was already in database
                    ns_returnredirect "group.tcl?group_id=$group_id"

Inserting an "abort transaction" call before the if statement fixed this.

Note that only DML statements (insert, update, delete) are affected by transactions. DDL statements (create table, etc.) are not affected by transactions. You can't roll back a table creation; to leave the database in a consistant state after a failed transaction, you must wrap the transaction in a "catch" that explicitly drops the table upon error.

CLOBs and BLOBs

Oracle lets you have character columns with up to 2 GB of data. But their SQL parser can only handle string literals up to 4000 characters long. So we have some special calls for stuffing long strings into columns of type CLOB, and for stuffing the contents of files into columns of type CLOB or BLOB. 

ns_ora clob_dml db query clob_value_1 clob_value_2 ... clob_value_N
The query must insert or update empty CLOBs into CLOB columns and return those columns into bind variables using RETURNING (but see below for an alternate way to use bind variables with the clob_* and blob_* commands). The Oracle function "empty_clob()" returns an empty CLOB. The clob_dml command inserts the Nth clob_value into the Nth bind variable. The bind variable names are not significant. Note that you don't have to escape apostrophes in the clob values. For example, 
create table messages (
    message_id	integer primary key,
    subject     varchar(200),
    body        clob
);

ns_ora clob_dml $db "insert into messages (message_id, subject, body)
values (34, 'This is a test', empty_clob())
returning body into :1" "Here's a long body ... to test clob inserts"
This would insert a new row into the table messages with message_id 34, subject "This is a test" and body "Here's a long ... clob inserts". The clob_dml command also works with updates. 
create table personal_ads (
    person_id		integer primary key,
    self_desc	        clob,
    bondage_story	clob,
    perfect_mate_desc	clob
);

ns_ora clob_dml $db "update personal_ads 
set self_desc = empty_clob(), 
    bondage_story = empty_clob(),
    perfect_mate_desc = empty_clob()
where person_id = 96
returning self_desc, bondage_story, perfect_mate_desc
into :one, :two, :three" $new_self_desc $new_bondage_story $new_perfect_mate_desc
This updates the row where person_id = 96, setting the three CLOB columns to the values of the tcl variables $new_self_desc, $new_bondage_story, $new_perfect_mate_desc. If there were multiple rows with person_id = 96, each row is updated with the new CLOB values.

Inserting the contents of files into CLOBs or BLOBs is similar to the use of the clob_dml command. (Note that you can use ns_queryget to get the file name of data uploaded via multipart formdata. You will probabily also want to view this lecture by Philip Greenspun on how to do file upload with AOLserver.) 

create table music_archive (
    album_id        integer primary key,
    title           varchar(200),
    mp3             blob
);

ns_ora blob_dml_file $db "insert into music_archive (album_id, title, mp3)
values (42, 'Old Skull''s Greatest Hits', empty_blob())
returning mp3 into :1" /net/downloads/getoutofschool.mp3
But here, instead of supplying a string to insert into the column, you're specifying the path to a file. The Oracle driver will read the contents of the file and insert it into the database. Why have this seperate mechanism? AOLserver uses Tcl 7.4 which can't handle embedded null-characters in its variables, so there's no way through the Tcl API to read a binary file without the data getting munged. 
ns_ora blob_dml_file $db "update music_archive
set mp3 = empty_blob()
where album_id = 42
returning mp3 into :1" /tmp/barrymanilow.mp3
This updates the row where album_id = 42, setting the BLOB column to the contents of /tmp/barrymanilow.mp3. If there were multiple rows with album_id = 42, each row is updated with the new BLOB value. 
ns_ora blob_get_file $db "select mp3 from music_archive
where album_id = 42" /tmp/something.mp3
This writes the result of the query, which should return only one column from one row, into /tmp/something.mp3. 
ns_ora write_blob $db "select mp3 from music_archive
where album_id = 42"
This returns the contents of the mp3 column to the connection. Note that you must first write appropriate headers, e.g., 
ReturnHeaders audio/mpeg
ns_ora write_blob $db "select mp3 ..."
ReturnHeaders is defined in the standard ArsDigita utlities.tcl file, available from http://eveander.com/arsdigita/books/panda/utilities.txt.

Calling PL/SQL Functions

You can call PL/SQL functions as normal dml statements. For example, 
ns_db dml $db "declare begin bboard_update_index_column('$new_id'); end;"
To execute a PL/SQL function that returns a value, select it from table dual. For example, 
ns_db select $db "select get_member_id('Joe', 'Smith') from dual"
But this will not work if the PL/SQL function has side effects (inserts or updates). When the PL/SQL function has side effects, you must use the ns_ora exec_plsql command. 
ns_ora exec_plsql $db "begin :1 := get_new_member_id('Cotton', 'Seed'); end;"
The ns_ora exec_plsql command returns the value of the first bind variable in the PL/SQL statement. The name of the bind variable is not significant.

Bind variables

To support using bind variables, we provide some additional ns_ora calls.
  • ns_ora select dbhandle ?-bind set? sql ?arg1 ... argn?
  • ns_ora 0or1row dbhandle ?-bind set? sql ?arg1 ... argn?
  • ns_ora 1row dbhandle ?-bind set? sql ?arg1 ... argn?
  • ns_ora dml dbhandle ?-bind set? sql ?arg1 ... argn?
  • ns_ora array_dml dbhandle ?-bind set? sql ?arg1 ... argn?
  • ns_ora clob_dml_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora blob_dml_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora clob_dml_file_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora blob_dml_file_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora exec_plsql_bind dbhandle sql return_var ?arg1 ... argn?

With the exception of the four lob_dml calls and exec_plsql_bind, these work the same as the corresponding ns_db calls, except that you can use bind variables in your SQL. There are three ways to specify the bind variables:

  • As positional parameters, e.g. ":1". The value for the bind variable is taken from the arguments after the SQL argument in order. ":1" is the first argument after the SQL, ":2" is the second, and so on.
  • As Tcl variable values, e.g. ":min_value". The value for the bind variable is taken from the local Tcl environment, so in this example it would be the value of the variable "min_value".
  • As elements of an ns_set, e.g., ":min_value" (only if the -bind argument is specified). The value for the bind variable is the element named "min_value" in the set with ID set.
Examples: 
ns_ora dml $db "update users set last_name = :1
    where user_id = :2" "Gates" $user_id

set values [ns_set create]
ns_set put $values last_name "Gates"
ns_set put $values user_id   $user_id
ns_ora dml $db -bind $values "update users set last_name = :last_name
    where user_id = :user_id"    

set user_email "billg@microsoft.com"
set selection [ns_ora 0or1row $db "select first_names,
    last_name from users where email = :user_email"]
With clob_dml_bind, blob_dml_bind, clob_dml_file_bind and blob_dml_file_bind, an extra argument is required. The list_of_lob_vars argument is a list of bind variables that represent LOB arguments. This is required because there is no way to tell in advance from examining the SQL or the Tcl variables which arguments are a LOB type, yet Oracle requires the driver to specify the LOB type of the arguments before executing the SQL. The solution is for the programmer to pass that information in along with the variables. Examples: 
ns_ora clob_dml_bind $db "update manuscripts set text = :1
    where manuscript_id = :2" [list 1] $gettysburg_address $man_id

set gettysburg_address "Four score and seven years ago"
set man_id 1861
ns_ora clob_dml_bind $db "update manuscripts set text = :gettysburg_address
    where manuscript_id = :man_id" [list gettysburg_address]
The exec_plsql_bind call has a similar problem: it needs to be told which bind variable is the return value of the function. The value of the bind variable named in the return_var argument will be returned as the value of the exec_plsql_bind call. If it is a named variable and not a positional variable, then the variable's value will also be set. Examples: 
ns_ora exec_plsql_bind $db "begin :1 := translate_to_german('Four score'); end;" 1

set english "Four score and seven years ago"
ns_ora exec_plsql_bind $db "begin :deutsch := translate_to_german(:english); end;" deutsch

Array DML

Array DML works exactly like single-row DML, except that the statement is executed multiple times with distinct data sets. This is useful when inserting many similarly-structured items into the database. To use array DML, set the value of each bind variable to be a list rather than a single value, e.g.: 
set last_names [list Gates Ellison Jobs]
set first_names [list Bill Larry Steve]
set user_ids [list 666 816 1984]

ns_ora array_dml $db "
    update users
    set last_name = :last_names, first_name = :first_names
    where user_id = :user_ids
"

# Or, equivalently:

ns_ora array_dml $db "
    update users
    set last_name = :1, first_name = :2
    where user_id = :3
" $last_names $first_names $user_ids

# Or, equivalently:

ns_set values [ns_set create]
ns_set put $values last_names $last_names
ns_set put $values first_names $first_names
ns_set put $values user_ids $user_ids
ns_ora array_dml $db -bind $values "
    update users
    set last_name = :last_names, first_name = :first_names
    where user_id = :user_ids
"
Note that the statement is prepared (i.e., parsed) by Oracle only once, so there is much less overhead and far fewer round trips to the server.

Extra AOLserver Tcl API calls

AOLserver does not provide an ns_db abstraction for some things that it should. Consequently, when talking to Illustra, they have ns_ill calls. We were forced to provide ns_ora calls. Here they are...
  • ns_ora resultid dbhandle -- returns the rowid (Oracle hidden column) of the last object affected by a DML command. This rowid can be used to determine the rowid for a newly-inserted row. Note that this is currently not implemented.

  • ns_ora resultrows dbhandle -- returns the number of rows affected by the last DML command. It can be used to determine how many rows were deleted or updated.

  • ns_ora exec_plsql dbhandle sql -- executes the given PL/SQL statement, returing the value of the first bind variable in the statement.

  • ns_ora clob_dml dbhandle sql clob_value_1 ?clob_value 2 ... clob_value_N?
  • ns_ora blob_dml dbhandle sql blob_value_1 ?blob_value 2 ... blob_value_N?
    Evaluates the given sql statement, inserting the the given values into the columns specified by the bind variables referenced.

  • ns_ora clob_dml_file dbhandle sql path1 ?path2 ... pathN?
  • ns_ora blob_dml_file dbhandle sql path1 ?path2 ... pathN?
    Evaluates the given sql statement, inserting the contents of the given files into the columns as specified by the bind variables referenced.

  • ns_ora clob_get_file dbhandle sql path
  • ns_ora blob_get_file dbhandle sql path
    Evaluates the given sql statement (which should return just one column from one row) and writes the value to the specified file. (any previous file contents will be replaced by the new value) The caller is responsible for deleting the file.

  • ns_ora write_clob dbhandle sql ?nbytes?
  • ns_ora write_blob dbhandle sql ?nbytes?
    Evaluates the given sql statement (which should return just one column from one row) and returns the value to the connection. You can specify the number of bytes to be returned in the nbytes argument. By default the entire BLOB/CLOB is returned.

  • ns_ora select dbhandle sql ?arg1 ... argn?
  • ns_ora 0or1row dbhandle sql ?arg1 ... argn?
  • ns_ora 1row dbhandle sql ?arg1 ... argn?
  • ns_ora dml dbhandle sql ?arg1 ... argn?
  • ns_ora array_dml dbhandle sql ?arg1 ... argn?
  • ns_ora clob_dml_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora blob_dml_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora clob_dml_file_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora blob_dml_file_bind dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?
  • ns_ora exec_plsql_bind dbhandle sql return_var ?arg1 ... argn?
    These are described above under the Bind Variables section.

Where's the code?

The code is available for download in the ACS Repository.

Where are more docs?

http://www.aolserver.com/doc/3.0/driver/dr.htm

How do I Report Problems or suggest enhancements?

Email markd@arsdigita.com, or visit the Oracle Driver Project on the photo.net ticket tracker.

Does it work with 7.3?

Depends what you mean by "work". We don't have any production AOLserver/Oracle 7.3 systems live on the Web. By our definition, "work" means "publicly accessible on the Internet and handling at least 10 hits/second". So we can't guarantee that you will download a tar file from us and be happy without editing the C code. In fact, Oracle completely changed their C interface between Release 7 and 8. So you can't just take our latest Oracle 8 driver and win. Anyway, if you're a C wizard you can probably make our driver work in a day. If you're not, hire a C wizard! If you can't hack C and you're too poor to pay a C wizard, then you probably should be running Solid (see http://demo.webho.com) instead of Oracle.

Weird stuff to remember

 Make sure to set the AOLserver StackSize parameter to something reasonable, e.g., 500000, if you're using our driver and any recursion in Tcl. The installation instruction page for the ArsDigita Community System covers this. Basically the problem is that AOLserver only allocates a stack by default of 50,000 bytes. Cotton Seed wrote the driver initially to stack-allocate 40,000 bytes to handle error messages. I guess this is how C programmers deal with the fact that they don't have a real computer language like Lisp that can allocate appropriate-sized data structures on the fly. Anyway, that only leaves 10,000 bytes for Tcl recursion, which might not be enough. Here's an example of bumping up the stack size: 
[ns/parameters]
User=nsadmin
Home=/home/nsadmin
StackSize=500000
There are also a number of moving pieces in the AOLserver database configuration. The one that seems to give folks the most trouble is the DataSource parameter in the pool configuration. It should look something like this:
[ns/db/pool/poolname]
Driver=ora8
Connections=4
DataSource=
User=alex
Password=woofwoof
Verbose=On
ExtendedTableInfo=On
DataSource must be included, and it should be blank. You may want to take a peek at a sample database configuration section.

Be sure to set your Oracle environment variables. Take a look at our sample AOLserver/Oracle startup script

If you're wanting to run AOLserver on one machine and Oracle on another, then check out this thread on photo.net. We don't use this configuration ourselves so we haven't tested it.

HPUX11 and Oracle 8.1.5

There is a problem with this driver under HPUX11 with Oracle 8.1.5 (8i). The Oracle driver must link against libclntsh.sl, the Oracle shared client library. This library links against libcl and libpthread, which contain "thread local storage". As documented in the man page for shl_load(), libraries containing thread local storage cannot be loaded using shl_load().

The workaround is to generate a version of libclntsh.sl without libcl and libpthread, and cause the Oracle driver to link against that.

Here is a binary version of the 2.0 Oracle driver and libclntsh.sl compiled on HPUX 11 with Oracle 8.1.5 and AOLserver 2.3.3 for HPUX11. Place the files in the bin directory into your AOLserver's bin directory and read the README.txt file for instructions on how to use, and how to generate your own version of these files if necessary.

Version History

  • first version released June 1997

  • 0.6: Released January 13, 1999. Adds lots of comments stuck in by philg over Cotton's vociferous objections. Fixes the bug where driver wasn't allocating enough bytes to hold a very precise average or date computation result, e.g., when getting avg(some_integer_column)

  • 1.0: Released March 12, 1999.
    • Fixed corrupted return data for LONG columns < 1024 bytes. LONG columns over 1024 bytes are not supported.
    • Fixed complaint when inserting an empty string via clob_dml. It now inserts a NULL value in this case
    • Added clob_dml_file and clob_get_file commands
    • Added write_clob command
    • Added blob versions of all of the clob commands
    • Added safety features for Cassandracle
    • Added the debug configuration parameter, so now getting trace output doesn't require recompiling the driver
    • clob_dml SQL and the CLOB contents are now logged to the server.log when verbose=on is set in the pool's configuration
    • Rewrote makefiles so the same makefile works on Solaris, HP-UX 11.00, and Linux

  • 1.0.1: Released April 5, 1999. Fixed clob_get_file so it won't leak open files. (fix donated by Igor Baikalov - thanks!)

  • 1.0.2: Released April 14, 1999. Added display of Oracle's warning messages when a result code of OCI_SUCCESS_WITH_INFO is returned (which can happen in cases such as having NULL values in aggregate functions)

  • 1.0.3: Released June 11, 1999. Turned OCI_SUCCESS_WITH_INFO into a non-error condition. Oracle changed behavior between 8.0.X and 8.1.X, causing the use of aggregate functions with NULLs to generate a warning. Since NULLs in aggregates usually aren't a problem, we recommend upgrading to this version from 1.0.2

  • 2.0: Released January 6, 2000
    • Fixed error where *lob_get_file would leave temporary files in /var/tmp.
    • Optimized *lob_get_file to return directly to the connection rather than spooling from the file system.
    • It's now easier to see what version of the driver you are running. Perform a "strings ora8.so | grep ArsDigita" to get the version number.
    • Fixed ora_open_db to return a better error than "NULL Connection" if it fails to connect to Oracle.
    • Fixed a number of errors found by Jin Choi (jsc@arsdigita.com)
    • Added cleanup if the oracle process dies unexpectedly (which would ultimately lead to the "hostdef extension doesn't exist" error.
    • Added cleanup of the oracle process if a "too many oracle processes" (ORA-00020) or "oracle not available" (ORA-01034) error occurs.
    • Added in reimplementations of some functions that were removed from AOLserver 3.0 but are needed by ACS.
    • The oracle 8.1.X makefile is now the default makefile. If you're still using Oracle 8.0.X, you'll need to use the makefile-80: gmake -f makefile-80

  • 2.0.1: Release January 14, 2000
    • Fixed linking problem on Linux.
      (if you get an "undefined symbol fstat" error when loading the driver, you need this version)

  • 2.1: Release May 1, 2000
    • Plugged some potential buffer overflow situations when reporting errors.
    • Added bind variable support.
    • The error message for certain SQL errors now indicates the location in the SQL of the error. (contributed by H.B. Weinberg)
    • You can now use ns_dberrorcode to get the numeric Oracle error code after an error.
    • We no longer put huge arrays on the stack, so you shouldn't need to turn up your AOLserver stack size so much any more.
    • Fixed some memory-allocation problems in bind variable support (we were not calling Ns_StrDup in all the right places).
    • Expanded bind-variable support.
    • Added array DML.
    • Fixed bug when reading partial LOBs.
    • Added support for building on Win32 platforms.

  • 2.2: Release July 28, 2000
    • Fixed a problem with EPIPE handling in stream_write_lob. (Contributed by Paul Laferriere.)
    • Fixed a problem with handling UTF-8 data in LOBs. (Contributed by Jin Choi.)
    • Added clob_dml_bind, clob_dml_file_bind, blob_dml_bind, blob_dml_file_bind, exec_plsql_bind functions.

  • 2.3: Release 2000-09-11
    • New Makefile based on AOLserver 3 Makefiles.
    • Beginnings of a code reorganization.
    • Eliminated a tiny memory leak in the [bc]lob_dml* commands.
markd@arsdigita.com
spacer