db.oci8

mkb mkb@ele.uri.edu
Wed, 11 Oct 2000 15:17:09 -0400 (EDT)


I just finished writing this, and everything seems to be working fine. I used
the newer oracle_create.sql that was just posted here. You may want to remove
the comments from that SQL script because they make loud and obnoxious error
messages. In imp/config/defaults.php3, you'll want to switch db_driver to
'oci8'

<?php

/*
IMP Oracle driver originally by: Vadim Kolontsov <sb@123.org>
adapted for OCI by Matt Kane <mkb@ele.uri.edu>

Table Structure for Oracle version

   (see scripts/oracle_create.sql)
   
   Preferences Table:

    field:     type:      	description:

    username   varchar2(255)	joe@imap.doe.com format username
    sig	       varchar2(2000)	signature
    fullname   varchar2(70)	Joe Doe
    replyto    varchar2(70)	joe@doe.com from and replyto address
    lang       varchar2(30)	users's preferred language (en, es, ...)
    (possibly more to be added later)
 
   Address Table:

    field:     type:          description:

    username   varchar2(255)  joe@doe.com identifier of who this entry belongs
to
    address    varchar2(255)  address@mail.server.com - the actual address
    nickname   varchar2(255)  any entered nickname - ie "bonnie".
    fullname   varchar2(255)  full name - "My Bonnie".

*/

function imp_add_address ($address, $nickname, $fullname, $user, $server) {
	global $default;
	
	/* post: adds $address, $nickname, $fullname to the addressbook for
$user@$server
	   returns true on success and false on failure
        */
	$nickname = stripslashes("$nickname");
	$fullname = stripslashes("$fullname");
	$address = stripslashes("$address");
	$nickname = str_replace("'","''",$nickname); 
	$nickname = str_replace("'","''",$nickname); 
	$address = str_replace("'","''",$address); 

	if (!enterOracle(&$conn, &$cursor, "insert into $default->db_address_table
values ('$user@$server', '$address', '$nickname', '$fullname')"))
		return false;
	
	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
   
	return leaveOracle($conn, $cursor, true);
}

function imp_check_prefs($user,$server) {
    return false;
}

function imp_delete_address ($address, $user, $server) {
	global $default;
	
	/* post: deletes $address from the addressbook of $user@$server
	   returns true on success and false on failure
    */
	$address = stripslashes("$address");
	$address = str_replace("'","''",$address); 
   
	if (!enterOracle(&$conn, &$cursor, "delete from $default->db_address_table
where username='$user@$server' and address='$address'"))
		return false;
   
	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
   
	return leaveOracle($conn, $cursor, true);
}

function imp_get_addresses ($user, $server) {
   global $default;
   
   $result = '';
   
   /* post: returns a 2d array of addresses where each
    * element is an array in which element 0 is the address,
    * element 1 is the nickname, and element 2 is the fullname.
    * it returns false on failure.
    */
   
   if (!enterOracle(&$conn, &$cursor, "SELECT address, nickname, fullname from
$default->db_address_table where username='$user@$server' order by nickname"))
     return false;
   
   for($i = 0; OCIFetch($cursor); $i++) {
     $result[$i] = array(0 => OCIResult($cursor, 1),
			 1 => OCIResult($cursor, 2),
			 2 => OCIResult($cursor, 3));
   }
   return leaveOracle($conn, $cursor, $result);
}

function imp_get_from ($user, $server) {
   global $default;
   $result = '';

   /* post: returns the replyto for the database key $user@$server
    (a string), or false on failure.
    */
   
   if (!enterOracle(&$conn, &$cursor, "SELECT replyto from
$default->db_pref_table where username='$user@$server'"))
     return false;
   
   if (OCIFetch($cursor))
     $result = OCIResult($cursor, 1);
   
   return leaveOracle($conn, $cursor, $result);
}

function imp_get_fullname ($user, $server) {
   global $default;
   
   /* post: returns the full name for the database key $user@$server
    (a string), or false on failure.
    */
   $result = '';
   
   if (!enterOracle(&$conn, &$cursor, "SELECT fullname from
$default->db_pref_table where username='$user@$server'"))
     return false;
   
   if (OCIFetch($cursor))
     $result = OCIResult($cursor, 1);
   
   return leaveOracle($conn, $cursor, $result);
}

function imp_get_lang ($user, $server) {
   global $default;
   $result = '';
   
   /* post: returns the language
    */
   
   if (!enterOracle(&$conn, &$cursor, "SELECT lang from $default->db_pref_table
where username='$user@$server'"))
     return false;
   
   if (OCIFetch($cursor))
     $result = OCIResult($cursor, 1);
   
   return leaveOracle($conn, $cursor, $result);
}

function imp_get_signature ($user, $server) {
   global $default;
   $result = '';
   
   /* post: returns the signature for the database key $user@$server
    (a string), or false on failure.
    */
   
   if (!enterOracle(&$conn, &$cursor, "SELECT sig from $default->db_pref_table
where username='$user@$server'"))
     return false;
   
   if (OCIFetch($cursor))
     $result = OCIResult($cursor, 1);
   
   return leaveOracle($conn, $cursor, $result);
}

function imp_set_from ($from, $user, $server) {
	global $default;
   
	/* post: sets the replyto to $from for the database key $user@$server
	   returns true on success and false on failure
    */
	$from = stripslashes("$from");
	$from = str_replace("'","''",$from); 
   
	/* escape any malicious characters */
	$from = ereg_replace("(.)'","''",$from);

	if (!enterOracle(&$conn, &$cursor,
					 $sql="select username from $default->db_pref_table where
username='$user@$server'"))
		return false;
	
	if (OCIFetch($cursor)) /* no rows */
		$sql = "update $default->db_pref_table set replyto='$from' where
username='$user@$server'";
	else
		$sql = "insert into $default->db_pref_table (username,replyto) values
('$user@$server','$from')";
   

	if (!($cursor = OCIParse($conn, $sql)))
		return leaveOracle($conn, $cursor, false);
        else
                if (!OCIExecute($cursor,OCI_DEFAULT))
			return leaveOracle($conn,$cursor,false);
	
	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
   
	return leaveOracle($conn, $cursor, true);
}

function imp_set_fullname ($fullname, $user, $server) {
	global $default;
	
	/* post: sets the replyto to $from for the database key $user@$server
	   returns true on success and false on failure
    */
	$fullname = stripslashes("$fullname");
	$fullname = str_replace("'","''",$fullname); 
	
	/* escape any malicious characters */
	$fullname = ereg_replace("(.)'","''",$fullname);
	
	if (!enterOracle(&$conn, &$cursor, 
					 "select username from $default->db_pref_table where
username='$user@$server'"))
		return false;
	
	if (OCIFetch($cursor))
		$sql = "update $default->db_pref_table set fullname='$fullname' where
username='$user@$server'";
	else
		$sql = "insert into $default->db_pref_table (username,fullname) values
('$user@$server', '$fullname')";
	
	if (!($cursor = OCIParse($conn, $sql)))
		return leaveOracle($conn, $cursor, false);
        else
                if (!OCIExecute($cursor,OCI_DEFAULT))
			return leaveOracle($conn, $cursor, false);

	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
   
	return leaveOracle($conn, $cursor, true);
}

function imp_set_lang ($lang, $user, $server) {
   global $default;
   
   /* post: sets the language to $lang 
    */
   
   if (!enterOracle(&$conn, &$cursor, 
		    "select username from $default->db_pref_table where
username='$user@$server'"))
     return false;
   
   if (OCIFetch($cursor))
     $sql = "update $default->db_pref_table set lang='$lang' where
username='$user@$server'";
   else
     $sql = "insert into $default->db_pref_table (username,lang) values
('$user@$server', '$lang')";
   
   OCIParse($conn, $sql);
   
   if (!OCIExecute($cursor))
     return leaveOracle($conn, $cursor, false);
   
   if (!OCICommit($conn))
     return leaveOracle($conn, $cursor, false);
   
   return leaveOracle($conn, $cursor, true);
}

function imp_set_signature ($signature, $user, $server) {
	global $default;
   
	/* post: sets the signature to $signature for the database key $user@$server
	   returns true on success and false on failure
    */
	$signature = stripslashes("$signature");
	$signature = str_replace("'","''",$signature); 
	
	/* escape any malicious characters */
	$signature = ereg_replace("(.)'","''",$signature);
	
	if (!enterOracle(&$conn, &$cursor,
					 "select username from $default->db_pref_table where
username='$user@$server'"))
		return false;
	
	if (OCIFetch($cursor)) /* no rows */
		$sql = "update $default->db_pref_table set sig='$signature' where
username='$user@$server'";
	else
		$sql = "insert into $default->db_pref_table (username,sig) values
('$user@$server', '$signature')";
	
	OCIParse($conn, $sql);
	
	if (!OCIExecute($cursor))
		return leaveOracle($conn, $cursor, false);
	
	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
	
	return leaveOracle($conn, $cursor, true);
}

function imp_update_address ($old_address, $address, $nickname, $fullname,
$user, $server) {
	global $default;
	
	/* post: changes the entry for $old_address to $address, $nickname, $fullname.
	   returns true on success and false on failure
    */
	
	$nickname = stripslashes("$nickname");
	$fullname = stripslashes("$fullname");
	$nickname = str_replace("'","''",$nickname);
	$fullname = str_replace("'","''",$fullname);
   
	if (!enterOracle(&$conn, &$cursor, "update $default->db_address_table set
address='$address', nickname='$nickname', fullname='$fullname' where
username='$user@$server' and address='$old_address'"
					 ))
		return false;
   
	if (!OCICommit($conn))
		return leaveOracle($conn, $cursor, false);
   
	return leaveOracle($conn, $cursor, true);
}

/* Connect to oracle and execute the statement */
function enterOracle($conn, $cursor, $sql)
{                                     
   global $default;
   
   $conn = OCILogon($default->db_user_name, $default->db_password,
$default->db_name);
   
   if ($conn < 0)                                  
     return leaveOracle($conn, 0, false);
   
   if (!($cursor = OCIParse($conn,$sql)))
     return leaveOracle($conn, $cursor, false);
   else
     OCIExecute($cursor,OCI_DEFAULT);
   
   return true;
}

/* Disconnect from Oracle and return results.. */
function leaveOracle($conn, $cursor, $result)
{
   OCILogoff($conn);
   
   return $result;
}

?>