]> projects.mako.cc - scuttle/blob - includes/db/oracle.php
Added original date and updated date to bookmark output if different.
[scuttle] / 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 ?>

Benjamin Mako Hill || Want to submit a patch?