This page was last updated on 2000-10-12.
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.
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.
This will affect the contents of three tables once thens_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"
end transaction
statement is executed.
You can combine Tcl catch
statements and transaction control:
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.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 }
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.
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,ns_ora clob_dml db query clob_value_1 clob_value_2 ... clob_value_N
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 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 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.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
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.)
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.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
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_dml_file $db "update music_archive set mp3 = empty_blob() where album_id = 42 returning mp3 into :1" /tmp/barrymanilow.mp3
This writes the result of the query, which should return only one column from one row, into /tmp/something.mp3.ns_ora blob_get_file $db "select mp3 from music_archive where album_id = 42" /tmp/something.mp3
This returns the contents of thens_ora write_blob $db "select mp3 from music_archive where album_id = 42"
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.
To execute a PL/SQL function that returns a value, select it from table dual. For example,ns_db dml $db "declare begin bboard_update_index_column('$new_id'); end;"
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_db select $db "select get_member_id('Joe', 'Smith') from dual"
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.ns_ora exec_plsql $db "begin :1 := get_new_member_id('Cotton', 'Seed'); end;"
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:
Withns_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"]
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:
Thens_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]
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
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.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 "
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/parameters] User=nsadmin Home=/home/nsadmin StackSize=500000
[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:
Assuming you loaded the driver using the name "ora8", this makes the driver will use a buffer size of 200000 bytes.[ns/db/driver/ora8] LOBBufferSize=200000
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