projects.mako.cc - scuttle/blob - includes/db/oracle.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal_oracle
   5 * @version $Id: oracle.php,v 1.2 2005/06/10 08:52:03 devalley Exp $
   6 * @copyright (c) 2005 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 /**
  12 * @ignore
  13 */
  14 if(!defined("SQL_LAYER"))
  15 {
  16
  17 define("SQL_LAYER","oracle");
  18
  19 /**
  20 * @package dbal_oracle
  21 * Oracle Database Abstraction Layer
  22 */
  23 class sql_db
  24 {
  25
  26         var $db_connect_id;
  27         var $query_result;
  28         var $in_transaction = 0;
  29         var $row = array();
  30         var $rowset = array();
  31         var $num_queries = 0;
  32         var $last_query_text = "";
  33
  34         //
  35         // Constructor
  36         //
  37         function sql_db($sqlserver, $sqluser, $sqlpassword, $database="", $persistency = true)
  38         {
  39                 $this->persistency = $persistency;
  40                 $this->user = $sqluser;
  41                 $this->password = $sqlpassword;
  42                 $this->server = $sqlserver;
  43                 $this->dbname = $database;
  44
  45                 if($this->persistency)
  46                 {
  47                         $this->db_connect_id = @OCIPLogon($this->user, $this->password, $this->server);
  48                 }
  49                 else
  50                 {
  51                         $this->db_connect_id = @OCINLogon($this->user, $this->password, $this->server);
  52                 }
  53                 if($this->db_connect_id)
  54                 {
  55                         return $this->db_connect_id;
  56                 }
  57                 else
  58                 {
  59                         return false;
  60                 }
  61         }
  62
  63         //
  64         // Other base methods
  65         //
  66         function sql_close()
  67         {
  68                 if($this->db_connect_id)
  69                 {
  70                         // Commit outstanding transactions
  71                         if($this->in_transaction)
  72                         {
  73                                 OCICommit($this->db_connect_id);
  74                         }
  75
  76                         if($this->query_result)
  77                         {
  78                                 @OCIFreeStatement($this->query_result);
  79                         }
  80                         $result = @OCILogoff($this->db_connect_id);
  81                         return $result;
  82                 }
  83                 else
  84                 {
  85                         return false;
  86                 }
  87         }
  88
  89         //
  90         // Base query method
  91         //
  92         function sql_query($query = "", $transaction = FALSE)
  93         {
  94                 // Remove any pre-existing queries
  95                 unset($this->query_result);
  96
  97                 // Put us in transaction mode because with Oracle as soon as you make a query you're in a transaction
  98                 $this->in_transaction = TRUE;
  99
 100                 if($query != "")
 101                 {
 102                         $this->last_query = $query;
 103                         $this->num_queries++;
 104
 105                         if(eregi("LIMIT", $query))
 106                         {
 107                                 preg_match("/^(.*)LIMIT ([0-9]+)[, ]*([0-9]+)*/s", $query, $limits);
 108
 109                                 $query = $limits[1];
 110                                 if($limits[3])
 111                                 {
 112                                         $row_offset = $limits[2];
 113                                         $num_rows = $limits[3];
 114                                 }
 115                                 else
 116                                 {
 117                                         $row_offset = 0;
 118                                         $num_rows = $limits[2];
 119                                 }
 120                         }
 121
 122                         if(eregi("^(INSERT|UPDATE) ", $query))
 123                         {
 124                                 $query = preg_replace("/\\\'/s", "''", $query);
 125                         }
 126
 127                         $this->query_result = @OCIParse($this->db_connect_id, $query);
 128                         $success = @OCIExecute($this->query_result, OCI_DEFAULT);
 129                 }
 130                 if($success)
 131                 {
 132                         if($transaction == END_TRANSACTION)
 133                         {
 134                                 OCICommit($this->db_connect_id);
 135                                 $this->in_transaction = FALSE;
 136                         }
 137
 138                         unset($this->row[$this->query_result]);
 139                         unset($this->rowset[$this->query_result]);
 140                         $this->last_query_text[$this->query_result] = $query;
 141
 142                         return $this->query_result;
 143                 }
 144                 else
 145                 {
 146                         if($this->in_transaction)
 147                         {
 148                                 OCIRollback($this->db_connect_id);
 149                         }
 150                         return false;
 151                 }
 152         }
 153
 154         //
 155         // Other query methods
 156         //
 157         function sql_numrows($query_id = 0)
 158         {
 159                 if(!$query_id)
 160                 {
 161                         $query_id = $this->query_result;
 162                 }
 163                 if($query_id)
 164                 {
 165                         $result = @OCIFetchStatement($query_id, $this->rowset);
 166                         // OCIFetchStatment kills our query result so we have to execute the statment again
 167                         // if we ever want to use the query_id again.
 168                         @OCIExecute($query_id, OCI_DEFAULT);
 169                         return $result;
 170                 }
 171                 else
 172                 {
 173                         return false;
 174                 }
 175         }
 176         function sql_affectedrows($query_id = 0)
 177         {
 178                 if(!$query_id)
 179                 {
 180                         $query_id = $this->query_result;
 181                 }
 182                 if($query_id)
 183                 {
 184                         $result = @OCIRowCount($query_id);
 185                         return $result;
 186                 }
 187                 else
 188                 {
 189                         return false;
 190                 }
 191         }
 192         function sql_numfields($query_id = 0)
 193         {
 194                 if(!$query_id)
 195                 {
 196                         $query_id = $this->query_result;
 197                 }
 198                 if($query_id)
 199                 {
 200                         $result = @OCINumCols($query_id);
 201                         return $result;
 202                 }
 203                 else
 204                 {
 205                         return false;
 206                 }
 207         }
 208         function sql_fieldname($offset, $query_id = 0)
 209         {
 210                 // OCIColumnName uses a 1 based array so we have to up the offset by 1 in here to maintain
 211                 // full abstraction compatibitly
 212                 $offset += 1;
 213                 if(!$query_id)
 214                 {
 215                         $query_id = $this->query_result;
 216                 }
 217                 if($query_id)
 218                 {
 219                         $result = strtolower(@OCIColumnName($query_id, $offset));
 220                         return $result;
 221                 }
 222                 else
 223                 {
 224                         return false;
 225                 }
 226         }
 227         function sql_fieldtype($offset, $query_id = 0)
 228         {
 229                 // This situation is the same as fieldname
 230                 $offset += 1;
 231                 if(!$query_id)
 232                 {
 233                         $query_id = $this->query_result;
 234                 }
 235                 if($query_id)
 236                 {
 237                         $result = @OCIColumnType($query_id, $offset);
 238                         return $result;
 239                 }
 240                 else
 241                 {
 242                         return false;
 243                 }
 244         }
 245         function sql_fetchrow($query_id = 0, $debug = FALSE)
 246         {
 247                 if(!$query_id)
 248                 {
 249                         $query_id = $this->query_result;
 250                 }
 251                 if($query_id)
 252                 {
 253                         $result_row = "";
 254                         $result = @OCIFetchInto($query_id, $result_row, OCI_ASSOC+OCI_RETURN_NULLS);
 255                         if($debug)
 256                         {
 257                                 echo "Query was: ".$this->last_query . "<br>";
 258                                 echo "Result: $result<br>";
 259                                 echo "Query ID: $query_id<br>";
 260                                 echo "<pre>";
 261                                 var_dump($result_row);
 262                                 echo "</pre>";
 263                         }
 264                         if($result_row == "")
 265                         {
 266                                 return false;
 267                         }
 268
 269                         for($i = 0; $i < count($result_row); $i++)
 270                         {
 271                                 list($key, $val) = each($result_row);
 272                                 $return_arr[strtolower($key)] = $val;
 273                         }
 274                         $this->row[$query_id] = $return_arr;
 275
 276                         return $this->row[$query_id];
 277                 }
 278                 else
 279                 {
 280                         return false;
 281                 }
 282         }
 283         // This function probably isn't as efficant is it could be but any other way I do it
 284         // I end up losing 1 row...
 285         function sql_fetchrowset($query_id = 0)
 286         {
 287                 if(!$query_id)
 288                 {
 289                         $query_id = $this->query_result;
 290                 }
 291                 if($query_id)
 292                 {
 293                         $rows = @OCIFetchStatement($query_id, $results);
 294                         @OCIExecute($query_id, OCI_DEFAULT);
 295                         for($i = 0; $i <= $rows; $i++)
 296                         {
 297                                 @OCIFetchInto($query_id, $tmp_result, OCI_ASSOC+OCI_RETURN_NULLS);
 298
 299                                 for($j = 0; $j < count($tmp_result); $j++)
 300                                 {
 301                                         list($key, $val) = each($tmp_result);
 302                                         $return_arr[strtolower($key)] = $val;
 303                                 }
 304                                 $result[] = $return_arr;
 305                         }
 306                         return $result;
 307                 }
 308                 else
 309                 {
 310                         return false;
 311                 }
 312         }
 313         function sql_fetchfield($field, $rownum = -1, $query_id = 0)
 314         {
 315                 if(!$query_id)
 316                 {
 317                         $query_id = $this->query_result;
 318                 }
 319                 if($query_id)
 320                 {
 321                         if($rownum > -1)
 322                         {
 323                                 // Reset the internal rownum pointer.
 324                                 @OCIExecute($query_id, OCI_DEFAULT);
 325                                 for($i = 0; $i < $rownum; $i++)
 326                                   {
 327                                                 // Move the interal pointer to the row we want
 328                                                 @OCIFetch($query_id);
 329                                   }
 330                                 // Get the field data.
 331                                 $result = @OCIResult($query_id, strtoupper($field));
 332                         }
 333                         else
 334                         {
 335                                 // The internal pointer should be where we want it
 336                                 // so we just grab the field out of the current row.
 337                                 $result = @OCIResult($query_id, strtoupper($field));
 338                         }
 339                         return $result;
 340                 }
 341                 else
 342                 {
 343                         return false;
 344                 }
 345         }
 346         function sql_rowseek($rownum, $query_id = 0)
 347         {
 348                 if(!$query_id)
 349                 {
 350                                 $query_id = $this->query_result;
 351                 }
 352                 if($query_id)
 353                 {
 354                                 @OCIExecute($query_id, OCI_DEFAULT);
 355                         for($i = 0; $i < $rownum; $i++)
 356                                 {
 357                                         @OCIFetch($query_id);
 358                                 }
 359                         $result = @OCIFetch($query_id);
 360                         return $result;
 361                 }
 362                 else
 363                 {
 364                                 return false;
 365                 }
 366         }
 367         function sql_nextid($query_id = 0)
 368         {
 369                 if(!$query_id)
 370                 {
 371                         $query_id = $this->query_result;
 372                 }
 373                 if($query_id && $this->last_query_text[$query_id] != "")
 374                 {
 375                         if( eregi("^(INSERT{1}|^INSERT INTO{1})[[:space:]][\"]?([a-zA-Z0-9\_\-]+)[\"]?", $this->last_query_text[$query_id], $tablename))
 376                         {
 377                                 $query = "SELECT ".$tablename[2]."_id_seq.currval FROM DUAL";
 378                                 $stmt = @OCIParse($this->db_connect_id, $query);
 379                                 @OCIExecute($stmt,OCI_DEFAULT );
 380                                 $temp_result = @OCIFetchInto($stmt, $temp_result, OCI_ASSOC+OCI_RETURN_NULLS);
 381                                 if($temp_result)
 382                                 {
 383                                         return $temp_result['CURRVAL'];
 384                                 }
 385                                 else
 386                                 {
 387                                         return false;
 388                                 }
 389                         }
 390                         else
 391                         {
 392                                 return false;
 393                         }
 394                 }
 395                 else
 396                 {
 397                         return false;
 398                 }
 399         }
 400
 401         function sql_nextid($query_id = 0)
 402         {
 403                 if(!$query_id)
 404                 {
 405                         $query_id = $this->query_result;
 406                 }
 407                 if($query_id && $this->last_query_text[$query_id] != "")
 408                 {
 409                         if( eregi("^(INSERT{1}|^INSERT INTO{1})[[:space:]][\"]?([a-zA-Z0-9\_\-]+)[\"]?", $this->last_query_text[$query_id], $tablename))
 410                         {
 411                                 $query = "SELECT ".$tablename[2]."_id_seq.CURRVAL FROM DUAL";
 412                                 $temp_q_id =  @OCIParse($this->db_connect_id, $query);
 413                                 @OCIExecute($temp_q_id, OCI_DEFAULT);
 414                                 @OCIFetchInto($temp_q_id, $temp_result, OCI_ASSOC+OCI_RETURN_NULLS);
 415
 416                                 if($temp_result)
 417                                 {
 418                                         return $temp_result['CURRVAL'];
 419                                 }
 420                                 else
 421                                 {
 422                                         return false;
 423                                 }
 424                         }
 425                         else
 426                         {
 427                                 return false;
 428                         }
 429                 }
 430                 else
 431                 {
 432                         return false;
 433                 }
 434         }
 435
 436
 437
 438         function sql_freeresult($query_id = 0)
 439         {
 440                 if(!$query_id)
 441                 {
 442                                 $query_id = $this->query_result;
 443                 }
 444                 if($query_id)
 445                 {
 446                                 $result = @OCIFreeStatement($query_id);
 447                                 return $result;
 448                 }
 449                 else
 450                 {
 451                                 return false;
 452                 }
 453         }
 454         function sql_error($query_id  = 0)
 455         {
 456                 if(!$query_id)
 457                 {
 458                         $query_id = $this->query_result;
 459                 }
 460                 $result  = @OCIError($query_id);
 461                 return $result;
 462         }
 463
 464 } // class sql_db
 465
 466 } // if ... define
 467
 468 ?>