phpBB2/db/oracle.php

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