blob: bc0a100c2d83d1f5757665e4916923a7908119da [file] [log] [blame]
admin99e1b292006-09-24 18:12:43 +00001<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
2/**
3 * Code Igniter
4 *
5 * An open source application development framework for PHP 4.3.2 or newer
6 *
7 * @package CodeIgniter
8 * @author Rick Ellis
9 * @copyright Copyright (c) 2006, pMachine, Inc.
10 * @license http://www.codeignitor.com/user_guide/license.html
11 * @link http://www.codeigniter.com
12 * @since Version 1.0
13 * @filesource
14 */
15
16// ------------------------------------------------------------------------
17
18/**
19 * oci8 Database Adapter Class
20 *
21 * Note: _DB is an extender class that the app controller
22 * creates dynamically based on whether the active record
23 * class is being used or not.
24 *
25 * @package CodeIgniter
26 * @subpackage Drivers
27 * @category Database
28 * @author Rick Ellis
29 * @link http://www.codeigniter.com/user_guide/database/
30 */
31
32/**
33 * oci8 Database Adapter Class
34 *
35 * This is a modification of the DB_driver class to
36 * permit access to oracle databases
37 *
38 * NOTE: this uses the PHP 4 oci methods
39 *
40 * @author Kelly McArdle
41 *
42 */
43
44class CI_DB_oci8_driver extends CI_DB {
45
46 // Set "auto commit" by default
47 var $_commit = OCI_COMMIT_ON_SUCCESS;
48
49 // need to track statement id and cursor id
50 var $stmt_id;
51 var $curs_id;
52
53 // if we use a limit, we will add a field that will
54 // throw off num_fields later
55 var $limit_used;
56
57 /**
58 * Non-persistent database connection
59 *
60 * @access private called by the base class
61 * @return resource
62 */
63 function db_connect()
64 {
65 return ocilogon($this->username, $this->password, $this->hostname);
66 }
67
68 // --------------------------------------------------------------------
69
70 /**
71 * Persistent database connection
72 *
73 * @access private called by the base class
74 * @return resource
75 */
76 function db_pconnect()
77 {
78 return ociplogon($this->username, $this->password, $this->hostname);
79 }
80
81 // --------------------------------------------------------------------
82
83 /**
84 * Select the database
85 *
86 * @access private called by the base class
87 * @return resource
88 */
89 function db_select()
90 {
91 return TRUE;
92 }
93
admin9cd4e8e2006-09-25 23:26:25 +000094 // --------------------------------------------------------------------
95
96 /**
97 * Version number query string
98 *
99 * @access public
100 * @return string
101 */
102 function _version()
103 {
104 return ociserverversion($this->conn_id);
105 }
106
admin99e1b292006-09-24 18:12:43 +0000107 // --------------------------------------------------------------------
108
109 /**
110 * Execute the query
111 *
112 * @access private called by the base class
113 * @param string an SQL query
114 * @return resource
115 */
116 function _execute($sql)
117 {
admin9cd4e8e2006-09-25 23:26:25 +0000118 // oracle must parse the query before it is run. All of the actions with
119 // the query are based on the statement id returned by ociparse
admin99e1b292006-09-24 18:12:43 +0000120 $this->_set_stmt_id($sql);
121 ocisetprefetch($this->stmt_id, 1000);
122 return @ociexecute($this->stmt_id, $this->_commit);
123 }
124
125 /**
126 * Generate a statement ID
127 *
128 * @access private
129 * @param string an SQL query
130 * @return none
131 */
132 function _set_stmt_id($sql)
133 {
134 if ( ! is_resource($this->stmt_id))
135 {
136 $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
137 }
138 }
139
140 // --------------------------------------------------------------------
141
142 /**
143 * Prep the query
144 *
145 * If needed, each database adapter can prep the query string
146 *
147 * @access private called by execute()
148 * @param string an SQL query
149 * @return string
150 */
151 function _prep_query($sql)
152 {
153 return $sql;
154 }
155
156 // --------------------------------------------------------------------
157
158 /**
159 * getCursor. Returns a cursor from the datbase
160 *
161 * @access public
162 * @return cursor id
163 */
164 function get_cursor()
165 {
166 return $this->curs_id = ocinewcursor($this->conn_id);
167 }
168
169 // --------------------------------------------------------------------
170
171 /**
172 * Stored Procedure. Executes a stored procedure
173 *
174 * @access public
175 * @param package package stored procedure is in
176 * @param procedure stored procedure to execute
177 * @param params array of parameters
178 * @return array
179 *
180 * params array keys
181 *
182 * KEY OPTIONAL NOTES
183 * name no the name of the parameter should be in :<param_name> format
184 * value no the value of the parameter. If this is an OUT or IN OUT parameter,
185 * this should be a reference to a variable
186 * type yes the type of the parameter
187 * length yes the max size of the parameter
188 */
189 function stored_procedure($package, $procedure, $params)
190 {
191 if ($package == '' OR $procedure == '' OR ! is_array($params))
192 {
193 if ($this->db_debug)
194 {
195 log_message('error', 'Invalid query: '.$package.'.'.$procedure);
196 return $this->display_error('db_invalid_query');
197 }
198 return FALSE;
199 }
200
201 // build the query string
202 $sql = "begin $package.$procedure(";
203
204 $have_cursor = FALSE;
205 foreach($params as $param)
206 {
207 $sql .= $param['name'] . ",";
208
209 if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
210 {
211 $have_cursor = TRUE;
212 }
213 }
214 $sql = trim($sql, ",") . "); end;";
215
216 $this->stmt_id = FALSE;
217 $this->_set_stmt_id($sql);
218 $this->_bind_params($params);
219 $this->query($sql, FALSE, $have_cursor);
220 }
221
222 // --------------------------------------------------------------------
223
224 /**
225 * Bind parameters
226 *
227 * @access private
228 * @return none
229 */
230 function _bind_params($params)
231 {
232 if ( ! is_array($params) OR ! is_resource($this->stmt_id))
233 {
234 return;
235 }
236
237 foreach ($params as $param)
238 {
239 foreach (array('name', 'value', 'type', 'length') as $val)
240 {
241 if ( ! isset($param[$val]))
242 {
243 $param[$val] = '';
244 }
245 }
246
247 ocibindbyname($this->stmt_id, $param['name'], $param['value'], $param['length'], $param['type']);
248 }
249 }
250
251 // --------------------------------------------------------------------
252
253 /**
254 * Begin Transaction
255 *
256 * @access public
257 * @return bool
258 */
259 function trans_begin($test_mode = FALSE)
260 {
261 if ( ! $this->trans_enabled)
262 {
263 return TRUE;
264 }
265
266 // When transactions are nested we only begin/commit/rollback the outermost ones
267 if ($this->_trans_depth > 0)
268 {
269 return TRUE;
270 }
271
272 // Reset the transaction failure flag.
273 // If the $test_mode flag is set to TRUE transactions will be rolled back
274 // even if the queries produce a successful result.
275 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
276
277 $this->_commit = OCI_DEFAULT;
278 return TRUE;
279 }
280
281 // --------------------------------------------------------------------
282
283 /**
284 * Commit Transaction
285 *
286 * @access public
287 * @return bool
288 */
289 function trans_commit()
290 {
291 if ( ! $this->trans_enabled)
292 {
293 return TRUE;
294 }
295
296 // When transactions are nested we only begin/commit/rollback the outermost ones
297 if ($this->_trans_depth > 0)
298 {
299 return TRUE;
300 }
301
302 $ret = OCIcommit($this->conn_id);
303 $this->_commit = OCI_COMMIT_ON_SUCCESS;
304 return $ret;
305 }
306
307 // --------------------------------------------------------------------
308
309 /**
310 * Rollback Transaction
311 *
312 * @access public
313 * @return bool
314 */
315 function trans_rollback()
316 {
317 if ( ! $this->trans_enabled)
318 {
319 return TRUE;
320 }
321
322 // When transactions are nested we only begin/commit/rollback the outermost ones
323 if ($this->_trans_depth > 0)
324 {
325 return TRUE;
326 }
327
328 $ret = OCIrollback($this->conn_id);
329 $this->_commit = OCI_COMMIT_ON_SUCCESS;
330 return $ret;
331 }
332
333 // --------------------------------------------------------------------
334
335 /**
336 * Escape String
337 *
338 * @access public
339 * @param string
340 * @return string
341 */
342 function escape_str($str)
343 {
344 return $str;
345 }
346
347 // --------------------------------------------------------------------
348
349 /**
350 * Affected Rows
351 *
352 * @access public
353 * @return integer
354 */
355 function affected_rows()
356 {
357 return @ocirowcount($this->stmt_id);
358 }
359
360 // --------------------------------------------------------------------
361
362 /**
363 * Insert ID
364 *
365 * @access public
366 * @return integer
367 */
368 function insert_id()
369 {
370 // not supported in oracle
371 return 0;
372 }
373
374 // --------------------------------------------------------------------
375
376 /**
377 * "Count All" query
378 *
379 * Generates a platform-specific query string that counts all records in
380 * the specified database
381 *
382 * @access public
383 * @param string
384 * @return string
385 */
386 function count_all($table = '')
387 {
388 if ($table == '')
389 return '0';
390
391 $query = $this->query("SELECT COUNT(1) AS numrows FROM ".$table);
392
393 if ($query == FALSE)
394 {
395 return 0;
396 }
397
398 $row = $query->row();
399 return $row->NUMROWS;
400 }
401
402 // --------------------------------------------------------------------
403
404 /**
admin9cd4e8e2006-09-25 23:26:25 +0000405 * Show columnn query
406 *
407 * Generates a platform-specific query string so that the column names can be fetched
408 *
409 * @access public
410 * @param string the table name
411 * @return string
412 */
413 function _list_columns($table = '')
414 {
415 return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'";
416 }
417
418 // --------------------------------------------------------------------
419
420 /**
421 * Field data query
422 *
423 * Generates a platform-specific query so that the column data can be retrieved
424 *
425 * @access public
426 * @param string the table name
427 * @return object
428 */
429 function _field_data($table)
430 {
431 return "SELECT * FROM ".$this->_escape_table($table)." where rownum = 1";
432 }
433
434 // --------------------------------------------------------------------
435
436 /**
admin99e1b292006-09-24 18:12:43 +0000437 * The error message string
438 *
adminbb1d4392006-09-24 20:14:38 +0000439 * @access private
admin99e1b292006-09-24 18:12:43 +0000440 * @return string
441 */
adminbb1d4392006-09-24 20:14:38 +0000442 function _error_message()
admin99e1b292006-09-24 18:12:43 +0000443 {
444 $error = ocierror($this->conn_id);
445 return $error['message'];
446 }
447
448 // --------------------------------------------------------------------
449
450 /**
451 * The error message number
452 *
adminbb1d4392006-09-24 20:14:38 +0000453 * @access private
admin99e1b292006-09-24 18:12:43 +0000454 * @return integer
455 */
adminbb1d4392006-09-24 20:14:38 +0000456 function _error_number()
admin99e1b292006-09-24 18:12:43 +0000457 {
458 $error = ocierror($this->conn_id);
459 return $error['code'];
460 }
461
462 // --------------------------------------------------------------------
463
464 /**
465 * Escape Table Name
466 *
467 * This function adds backticks if the table name has a period
468 * in it. Some DBs will get cranky unless periods are escaped
469 *
adminbb1d4392006-09-24 20:14:38 +0000470 * @access private
admin99e1b292006-09-24 18:12:43 +0000471 * @param string the table name
472 * @return string
473 */
adminbb1d4392006-09-24 20:14:38 +0000474 function _escape_table($table)
admin99e1b292006-09-24 18:12:43 +0000475 {
476 if (stristr($table, '.'))
477 {
478 $table = preg_replace("/\./", "`.`", $table);
479 }
480
481 return $table;
482 }
483
484 // --------------------------------------------------------------------
485
486 /**
admin99e1b292006-09-24 18:12:43 +0000487 * Insert statement
488 *
489 * Generates a platform-specific insert string from the supplied data
490 *
491 * @access public
492 * @param string the table name
493 * @param array the insert keys
494 * @param array the insert values
495 * @return string
496 */
497 function _insert($table, $keys, $values)
498 {
adminbb1d4392006-09-24 20:14:38 +0000499 return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
admin99e1b292006-09-24 18:12:43 +0000500 }
501
502 // --------------------------------------------------------------------
503
504 /**
505 * Update statement
506 *
507 * Generates a platform-specific update string from the supplied data
508 *
509 * @access public
510 * @param string the table name
511 * @param array the update data
512 * @param array the where clause
513 * @return string
514 */
515 function _update($table, $values, $where)
516 {
517 foreach($values as $key => $val)
518 {
519 $valstr[] = $key." = ".$val;
520 }
521
adminbb1d4392006-09-24 20:14:38 +0000522 return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where);
admin99e1b292006-09-24 18:12:43 +0000523 }
524
525 // --------------------------------------------------------------------
526
527 /**
528 * Delete statement
529 *
530 * Generates a platform-specific delete string from the supplied data
531 *
532 * @access public
533 * @param string the table name
534 * @param array the where clause
535 * @return string
536 */
537 function _delete($table, $where)
538 {
adminbb1d4392006-09-24 20:14:38 +0000539 return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where);
540 }
541
542 // --------------------------------------------------------------------
543
544 /**
545 * Limit string
546 *
547 * Generates a platform-specific LIMIT clause
548 *
549 * @access public
550 * @param string the sql query string
551 * @param integer the number of rows to limit the query to
552 * @param integer the offset value
553 * @return string
554 */
555 function _limit($sql, $limit, $offset)
556 {
557 $limit = $offset + $limit;
558 $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";
559
560 if ($offset != 0)
561 {
562 $newsql .= " WHERE rnum >= $offset";
563 }
564
565 // remember that we used limits
566 $this->limit_used = TRUE;
567
568 return $newsql;
569 }
570
571 // --------------------------------------------------------------------
572
573 /**
574 * Close DB Connection
575 *
576 * @access public
577 * @param resource
578 * @return void
579 */
580 function _close($conn_id)
581 {
582 ocilogoff($conn_id);
admin99e1b292006-09-24 18:12:43 +0000583 }
584
adminbb1d4392006-09-24 20:14:38 +0000585
admin99e1b292006-09-24 18:12:43 +0000586}
587
588
589?>