/sql/oracle
[return to app]1
<?php
2 /**
3 * Converts the procedural Oracle PHP class to object-oriented
4 * @author Eric David Benari, Chris Jones
5 */
6 class oracle {
7 /**
8 * Database connection resource ID and statement resource handle
9 * @var object
10 */
11 public $connectionResource, $statementResource;
12
13 /**
14 * The database method used to sanitize user input strings
15 */
16 const ESCAPE_STRING = 'escapeString';
17
18 /**
19 * Creates a string sanitation method as it is missing in the Oracle PHP extension
20 *
21 * @param string $var
22 * @return string
23 */
24 public function escapeString($var) {
25 return str_replace("'", "''", $var);
26 }
27
28 /**
29 * Parse statement to create a statement resource
30 * @param string $query
31 */
32 public function parse($query) {
33 $this->statementResource = oci_parse($this->connectionResource, $query);
34 }
35
36 /**
37 * Alias for oci_parse() + oci_execute() to update OCI8 to use the modern naming convention
38 * and not require passing the connection to every statement via argument
39 *
40 * @param string $query
41 */
42 public function query($query) {
43 $this->parse($query);
44 $this->execute();
45 }
46
47 /**
48 * Oracle prepared query using "IN" binds (i.e that pass values from PHP to Oracle).
49 * Pass an array where the keys are the bind variable names and the values are the bind data values. Eg:
50 * $sql = 'select name, description from widgets where itemid=:it';
51 * $this->db->opquery($sql, array(":it" => $itemid));
52 *
53 * @param string $query
54 * @param array $bindArgs
55 */
56
57 public function opquery($query, $bindArgs) {
58 $this->parse($query);
59 foreach ($bindArgs as $key => $val) { // location needs to be passed by reference to oci_bind_by_name
60 oci_bind_by_name($this->statementResource, $key, $bindArgs[$key]);
61 }
62 $this->execute();
63 }
64
65 /**
66 * Connect to Oracle via OCI8
67 *
68 * @param string $username
69 * @param string $passwd
70 * @param string $connectionString Optional
71 * @param boolean $persistent Optional default is true
72 * @param string $charset Optional
73 * @param int $sessionMode Optional
74 */
75 public function __construct($username, $password, $connectionString = null, $persistent = true,
76 $charset = null, $sessionMode = null) {
77 try {
78 $connect = 'oci_' . ($persistent ? 'pconnect' : 'connect');
79 $this->connectionResource = $connect($username, $password, $connectionString, $charset,
$sessionMode);
80 if (!$this->connectionResource) {
81 throw new Exception('Cannot connect to Oracle');
82 }
83 } catch (Exception $e) {
84 $this->error = $e->getMessage();
85 }
86 }
87
88 /**
89 * Functions that require the OCI connection resource as the first argument
90 * @var array
91 */
92 protected $_connectionFunctions = array(
93 'close', 'commit', 'new_collection', 'new_cursor', 'new_descriptor', 'rollback', 'server_version',
94 'set_action', 'set_client_identifier', 'set_client_info', 'set_module_name'
95 );
96
97 /**
98 * Functions that require the statement resource as the first argument
99 * @var array
100 */
101 protected $_statementFunctions = array(
102 'bind_array_by_name', 'bind_by_name', 'cancel', 'define_by_name', 'error', 'execute',
103 'fetch_all', 'fetch_array', 'fetch_assoc', 'fetch_object', 'fetch_row', 'fetch', 'field_is_null',
'field_name',
104 'field_precision', 'field_scale', 'field_size', 'field_type_raw', 'field_type', 'free_statement',
105 'num_fields', 'num_rows', 'result', 'set_prefetch', 'statement_type'
106 );
107
108 /**
109 * Catch-all method allowing you to call any OCI8 function via camel-cap object-oriented syntax
110 * and skipping/omitting the initial argument for a connection resource or statement resource.
111 *
112 * From your models you could access:
113 *
114 * $this->db->fetchAll()
115 * $this->db->fetchAssoc()
116 * $this->db->commit()
117 * $this->db->parse()
118 * $this->db->result()
119 * $this->db->rollback()
120 * $this->db->close()
121 * etc.
122 *
123 * @param string $name
124 * @param mixed $val
125 */
126 public function __call($name, $val) {
127 $name = strtolower(preg_replace('/[A-Z]/', '_$0', $name));
128 if (function_exists('oci_' . $name)) {
129 if (in_array($name, $this->_statementFunctions)) {
130 array_unshift($val, $this->statementResource);
131 } else if (in_array($name, $this->_connectionFunctions)) {
132 array_unshift($val, $this->connectionResource);
133 }
134 return call_user_func_array('oci_' . $name, $val);
135 }
136 }
137 }