Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: 8e10840f37
Fetching contributors…

Cannot retrieve contributors at this time

674 lines (540 sloc) 22.95 kb
<!-- $Id$ -->
<html>
<head>
<style type="text/css">
/*<![CDATA[*/
@import url('style.css');
/*]]>*/
</style>
<title>Oracle Driver for AOLserver</title>
</head>
<body>
<h1>Oracle Driver for <a href="http://www.aolserver.com/">AOLserver</a></h1>
<h2>Introduction</h2>
This driver implements the standard AOLserver ns_db command interface for the
Oracle database. In addition it also implements an extended version
of the ns_db interface through the ns_ora command. The extended version is
necessary to support advanced Oracle features such as bind variables, array
dml, lobs, and plsql execution.
<h2>Extended API Reference</h2>
AOLserver does not provide an ns_db abstraction for some things that it
should.
<p>
<div class="api">
<h4><b>ns_ora select</b> <i>dbhandle sql ?-bind set? ?arg1 ... argn?</i></h4>
<h5>Implements bind variable aware version of <b>ns_db select</b> command.</h5>
</div>
<p>
<h4><b>ns_ora 0or1row</b> <i>dbhandle sql ?-bind set? ?arg1 ... argn?</i></h4>
<h5>Implements bind variable aware version of <b>ns_db 0or1row</b> command.</h5>
<p>
<div class="api">
<h4><b>ns_ora 1row</b> <i>dbhandle sql ?-bind set? ?arg1 ... argn?</i></h4>
<h5>Implements bind variable aware version of <b>ns_db 1row</b> command.</h5>
</div>
<p>
<h4><b>ns_ora dml</b> <i>dbhandle sql ?-bind set? ?arg1 ... argn?</i></h4>
<h5>Implements bind variable and transaction aware version of <b>ns_db dml</b> command.</h5>
<p>
<div class="api">
<h4><b>ns_ora resultid</b> <i>dbhandle</i></h4>
<h5>
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.
</h5>
</div>
<p>
<h4><b>ns_ora resultrows</b> <i>dbhandle</i></h4>
<h5>
Returns the number of rows affected by the last DML command. It can be used
to determine how many rows were deleted or updated.
</h5>
<p>
<div class="api">
<h4><b>ns_ora exec_plsql</b> <i>dbhandle sql</i></h4>
<h5>
Executes the given PL/SQL statement, returing the value of the first bind
variable in the statement.
</h5>
</div>
<p>
<h4>
<b>ns_ora clob_dml</b> <i>dbhandle sql clob_value_1 ?clob_value 2 ... clob_value_N?</i><br/>
<b>ns_ora blob_dml</b> <i>dbhandle sql blob_value_1 ?blob_value 2 ... blob_value_N?</i>
</h4>
<h5>
Evaluates the given sql statement, inserting the the given values
into the columns specified by the bind variables referenced.
</h5>
<p>
<div class="api">
<h4>
<b>ns_ora clob_dml_file</b> <i>dbhandle sql path1 ?path2 ... pathN?</i><br/>
<b>ns_ora blob_dml_file</b> <i>dbhandle sql path1 ?path2 ... pathN?</i>
</h4>
<h5>
Evaluates the given sql statement, inserting the contents of the
given files into the columns as specified by the bind variables
referenced.
</h5>
</div>
<p>
<h4>
<b>ns_ora clob_get_file</b> <i>dbhandle sql path</i><br/>
<b>ns_ora blob_get_file</b> <i>dbhandle sql path</i>
</h4>
<h5>
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.
</h5>
<p>
<div class="api">
<h4>
<b>ns_ora write_clob</b> <i>dbhandle sql ?nbytes?</i><br/>
<b>ns_ora write_blob</b> <i>dbhandle sql ?nbytes?</i>
</h4>
<h5>
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.
</h5>
</div>
<p>
<h4><b>ns_ora array_dml</b> <i>dbhandle sql ?arg1 ... argn?</i></h4>
<h5>Implements array dml version of <b>ns_db dml</b>.</h5>
<p>
<div class="api">
<h4><b>ns_ora clob_dml_bind</b> <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i></h4>
<h5></h5>
</div>
<p>
<h4><b>ns_ora blob_dml_bind</b> <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i></h4>
<h5></h5>
<p>
<div class="api">
<h4><b>ns_ora clob_dml_file_bind</b> <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i></h4>
<h5></h5>
</div>
<p>
<h4><b>ns_ora blob_dml_file_bind</b> <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i></h4>
<h5></h5>
<p>
<div class="api">
<h4><b>ns_ora exec_plsql_bind</b> <i>dbhandle sql return_var ?arg1 ... argn?</i></h4>
<h5></h5>
</div>
<h2>Oracle Support</h2>
<h3>Transactions</h3>
In order to support transactions the <b>ns_db dml</b> subcommand implements three
additional SQL-like commands. These commands are as follows:
<p>
<table border="0" cellspacing="0" cellpadding="3" width="100%">
<tr>
<th width="30%">Command</th>
<th>Description</th>
</tr>
<tr bgcolor="#eeeeee">
<td valign="top"><b>ns_db dml</b> "begin transaction"</td>
<td>DML statements after this one are now within a transaction</td>
</tr>
<tr>
<td valign="top"><b>ns_db dml</b> "end transaction"</td>
<td>Commit the changes made during the transaction</td>
</tr>
<tr bgcolor="#eeeeee">
<td valign="top"><b>ns_db dml</b> "abort transaction"</td>
<td>Roll back the transaction. Any DML statements made since the begin transaction will be undone, as if they never happened.</td>
</tr>
</table>
<p>
For example:
<pre class="code">
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"
</pre>
This will affect the contents of three tables once the
<code>end&nbsp;transaction</code> statement is executed.
<p>
You can combine Tcl <code>catch</code> statements and transaction control:
<pre class="code">
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
}
</pre>
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.
<p>
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.
<pre class="code">
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"
</pre>
<p>
Inserting an "abort transaction" call before the if statement fixed
this.
<p>
Note that only DML statements (insert, update, delete) are affected by
transactions. DDL statements (create table, etc.) are <b>not</b> 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.
<h3>CLOBs and BLOBs</h3>
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.
<p>
<pre class="code">
ns_ora clob_dml db query clob_value_1 clob_value_2 ... clob_value_N
</pre>
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,
<pre class="code">
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"
</pre>
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.
<pre class="code">
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
</pre>
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.
<p>
Inserting the contents of files into CLOBs or BLOBs is similar to the
use of the clob_dml command. (Note that you can use <a href=
"http://www.aolserver.com/server/docs/2.3/html/tapi-c88.htm">
ns_queryget</a> to get the file name of data uploaded via multipart
formdata. You will probabily also want to view
<a
href="http://wimpy.arsdigita.com/public/presentation-top.adp?presentation_id=7042&user_id=561">this
lecture by Philip Greenspun on how to do file upload with AOLserver</a>.)
<pre class="code">
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
</pre>
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.
<pre class="code">
ns_ora blob_dml_file $db "update music_archive
set mp3 = empty_blob()
where album_id = 42
returning mp3 into :1" /tmp/barrymanilow.mp3
</pre>
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.
<pre class="code">
ns_ora blob_get_file $db "select mp3 from music_archive
where album_id = 42" /tmp/something.mp3
</pre>
This writes the result of the query, which should return only one column
from one row, into /tmp/something.mp3.
<pre class="code">
ns_ora write_blob $db "select mp3 from music_archive
where album_id = 42"
</pre>
This returns the contents of the <code>mp3</code> column to the
connection. Note that you must first write appropriate headers, e.g.,
<pre class="code">
ReturnHeaders audio/mpeg
ns_ora write_blob $db "select mp3 ..."
</pre>
<code>ReturnHeaders</code> is defined in the standard
ArsDigita utlities.tcl file, available from
<a href="http://arsdigita.com/books/panda/utilities.txt">http://arsdigita.com/books/panda/utilities.txt</a>.
<p>
<h3>Calling PL/SQL Functions</h3>
You can call PL/SQL functions as normal dml statements. For example,
<pre class="code">
ns_db dml $db "declare begin bboard_update_index_column('$new_id'); end;"
</pre>
To execute a PL/SQL function that returns a value, select it from table
dual. For example,
<pre class="code">
ns_db select $db "select get_member_id('Joe', 'Smith') from dual"
</pre>
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.
<pre class="code">
ns_ora exec_plsql $db "begin :1 := get_new_member_id('Cotton', 'Seed'); end;"
</pre>
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.
<h3>Bind variables</h3>
To support using bind variables, we provide some additional ns_ora calls.
<ul>
<li>ns_ora select <i>dbhandle ?-bind set? sql ?arg1 ... argn?</i>
<li>ns_ora 0or1row <i>dbhandle ?-bind set? sql ?arg1 ... argn?</i>
<li>ns_ora 1row <i>dbhandle ?-bind set? sql ?arg1 ... argn?</i>
<li>ns_ora dml <i>dbhandle ?-bind set? sql ?arg1 ... argn?</i>
<li>ns_ora array_dml <i>dbhandle ?-bind set? sql ?arg1 ... argn?</i>
<li>ns_ora clob_dml_bind <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i>
<li>ns_ora blob_dml_bind <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i>
<li>ns_ora clob_dml_file_bind <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i>
<li>ns_ora blob_dml_file_bind <i>dbhandle sql list_of_lob_vars ?clob_value_1 clob_value_2 ... clob_value_N?</i>
<li>ns_ora exec_plsql_bind <i>dbhandle sql return_var ?arg1 ... argn?</i>
</ul>
<p> With the exception of the four <code>lob_dml</code> calls
and <code>exec_plsql_bind</code>, 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: <ul>
<li>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.
<li>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".
<li>As elements of an ns_set, e.g., ":min_value" (only if the
<tt>-bind</tt> argument is specified). The value for the
bind variable is the element named "min_value" in the set
with ID <i>set</i>.
</ul>
Examples:
<pre class="code">
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"]
</pre>
With <code>clob_dml_bind</code>,
<code>blob_dml_bind</code>,
<code>clob_dml_file_bind</code>
and
<code>blob_dml_file_bind</code>, an extra argument is required.
The <code>list_of_lob_vars</code> 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:
<pre class="code">
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]
</pre>
The <code>exec_plsql_bind</code> 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 <code>return_var</code>
argument will be returned as the value of the
<code>exec_plsql_bind</code> call. If it is a named variable and not
a positional variable, then the variable's value will also be set.
Examples:
<pre class="code">
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
</pre>
<h3>Array DML</h3>
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 <i>list</i>
rather than a single value, e.g.:
<pre class="code">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
"</pre>
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.
<h3>Where's the code?</h3>
The code is available for download at
<a href="http://arsdigita.com/download/">http://arsdigita.com/download/</a>.
<h3>Where are more docs?</h3>
<a href="http://www.aolserver.com/doc/3.0/driver/dr.htm">http://www.aolserver.com/doc/3.0/driver/dr.htm</a>
<h3>How do I Report Problems or suggest enhancements?</h3>
Email <a href="mailto:markd@arsdigita.com">markd@arsdigita.com</a>, or visit the <a href="http://photo.net/ticket/project-top.tcl?project_id=361">
Oracle Driver Project</a> on the photo.net ticket tracker.
<h3>Does it work with 7.3?</h3>
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 <a
href="http://demo.webho.com">http://demo.webho.com</a>) instead of
Oracle.
<a name="WeirdStuff">
<h3>Weird stuff to remember</h3>
</a>
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. If you
are experiencing random crashes with no explanation this is most likely your
problem.
<p>
Here's an example of bumping up the stack size:
<pre class="code">
ns_section "ns/threads"
ns_param stacksize 500000
</pre>
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:
<pre class="code">
ns_section "ns/db/pool/ximian"
ns_param Driver ora8
ns_param Connections 3
ns_param User scott
ns_param Password tiger
ns_param LogSQLErrors On
<b>ns_param Datasource dev</b>
ns_param ExtendedTableInfo On
</pre>
<p>
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:
<pre class="code">
[ns/db/driver/ora8]
LOBBufferSize=200000
</pre>
Assuming you loaded the driver using the name "ora8", this makes the
driver will use a buffer size of 200000 bytes.
<p>
If you encounter error ORA-01406, "fetched column value was truncated",
you need to use the CharExpansion parameter:
<pre class="code">[ns/db/driver/ora8]
CharExpansion=2</pre>
This will make the driver allocate twice as much space for char and
varchar columns.
<a name="Oracle8i">
<h3>HPUX11 and Oracle 8.1.5</h3>
</a>
<p>
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().
<p>
The workaround is to generate a version of libclntsh.sl without libcl
and libpthread, and cause the Oracle driver to link against that.
<p>
<a href="hp11-oracle815-driver-2.0.tar.gz">Here</a> 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.
<hr>
<a href="mailto:markd@arsdigita.com"><address>markd@arsdigita.com</address></a>
<a href="mailto:jeremy.collins@tclsource.org"><address>jeremy.collins@tclsource.org</address></a>
</body>
</html>
Jump to Line
Something went wrong with that request. Please try again.