Oracle Driver

for AOLserver, part of the ArsDigita services section

This page was last updated on 2000-10-12.


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:

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://arsdigita.com/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.

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:

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...

Where's the code?

The code is available for download at http://arsdigita.com/download/.

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.

We have encountered error ORA-24812 when reading CLOBs containing UTF-8 data from the database. Increasing the chunk size that the driver uses to read the LOBs helps avoid the problem. The default size is 16K. You can set the size in bytes of the LOB buffer by adding something like this to your config file:

[ns/db/driver/ora8]
LOBBufferSize=200000
Assuming you loaded the driver using the name "ora8", this makes the driver will use a buffer size of 200000 bytes.

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


markd@arsdigita.com