forked from ezSQL/ezsql
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathez_sql_oracleTNS.php
499 lines (438 loc) · 16.6 KB
/
ez_sql_oracleTNS.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
<?php
/**
* ezSQL Database specific class with TNS connection
* Desc..: Oracle TNS component (part of ezSQL databse abstraction library)
*
* @author Stefanie Janine Stoelting (mail@stefanie-stoelting.de)
* @name ezSQL_oracleTNS
* @package ezSQL
* @license FREE / Donation (LGPL - You may do what you like with ezSQL - no exceptions.)
*
*/
class ezSQL_oracleTNS extends ezSQLcore
{
/**
* ezSQL error strings - Oracle 8 and 9
* @var array
*/
private $ezsql_oracle_str = array
(
1 => 'Require $dbuser, $dbpassword and $dbname to connect to a database server',
2 => 'ezSQL auto created the following Oracle sequence'
);
/**
* Database user name
* @var string
*/
private $dbuser;
/**
* Database password for the given user
* @var string
*/
private $dbpassword;
/**
* Database server name or IP address
* @var string
*/
private $host;
/**
* TCP port for the database connection on the specified server
* @var integer
*/
private $port;
/**
* The service name
* @var string
*/
private $serviceName;
/**
* The connection string
* @var string
*/
private $tns;
/**
* The Oracle NLS_LANG character set for the connection
* Default: Empty string
* @var string
*/
private $characterSet;
/**
* Use oci_pconnect instead of oci_connect to have connection pooling
* enabled with PHP
* @var boolean
*/
private $pooling;
/**
* Show errors
* @var boolean Default is true
*/
public $show_errors = true;
/**
* Constructor - allow the user to perform a qucik connect at the same time
* as initialising the ezSQL_oracleTNS class
*
* @param string $host The server name or the IP address of the server
* @param integer $port The TCP port of the server
* @param string $serviceName The service name
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
* @param string $characterSet The Oracle NLS_LANG character string
* Default is empty string
* @param boolean $pooling Use connection pooling with pconnect instead of
* connect
* Default is false
* @throws Exception Requires Orcle OCI Lib and ez_sql_core.php
*/
public function __construct($host, $port, $serviceName, $dbuser='', $dbpassword='', $characterSet='', $pooling=false) {
if ( ! function_exists ('OCILogon') ) {
throw new Exception('<b>Fatal Error:</b> ezSQL_oracleTNS requires Oracle OCI Lib to be compiled and/or linked in to the PHP engine');
}
if ( ! class_exists ('ezSQLcore') ) {
throw new Exception('<b>Fatal Error:</b> ezSQL_oracle8_9 requires ezSQLcore (ez_sql_core.php) to be included/loaded before it can be used');
}
parent::__construct();
// Turn on track errors
ini_set('track_errors',1);
$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->host = $host;
$this->port = $port;
$this->serviceName = $serviceName;
$this->characterSet = $characterSet;
$this->setTNS();
$this->pooling = $pooling;
} // __construct
/**
* Try to connect to Oracle database server
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
* @return boolean
*/
public function connect($dbuser='', $dbpassword='') {
$this->_connected = false;
if (empty($dbuser)) {
$dbuser = $this->dbuser;
}
if (empty($dbpassword)) {
$dbpassword = $this->dbpassword;
}
// Must have a user and a password
if ( ! $dbuser || ! $dbpassword) {
$this->register_error($this->ezsql_oracle_str[1] . ' in ' . __FILE__ . ' on line ' . __LINE__);
$this->show_errors ? trigger_error($this->ezsql_oracle_str[1], E_USER_WARNING) : null;
}
// Try to establish the server database handle
else {
if ($this->pooling) {
$this->_pconnect($dbuser, $dbpassword);
} else {
$this->_connect($dbuser, $dbpassword);
}
}
return $this->_connected;
} // connect
/**
* Try to connect to Oracle database server with connection pooling
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
* @return boolean
*/
public function pconnect($dbuser='', $dbpassword='') {
$this->_connected = false;
if (empty($dbuser)) {
$dbuser = $this->dbuser;
}
if (empty($dbpassword)) {
$dbpassword = $this->dbpassword;
}
// Must have a user and a password
if ( ! $dbuser || ! $dbpassword) {
$this->register_error($this->ezsql_oracle_str[1] . ' in ' . __FILE__ . ' on line ' . __LINE__);
$this->show_errors ? trigger_error($this->ezsql_oracle_str[1], E_USER_WARNING) : null;
}
// Try to establish the server database handle
else {
$this->_pconnect($dbuser, $dbpassword);
}
return $this->_connected;
} // pconnect
/**
* Try to connect to Oracle database server without connection pooling
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
*/
private function _connect($dbuser='', $dbpassword='') {
if ( ! empty($this->characterSet) ) {
if ( ! $this->dbh = @oci_connect($dbuser, $dbpassword, $this->tns, $this->characterSet) ) {
$this->register_error($php_errormsg);
$this->show_errors ? trigger_error($php_errormsg,E_USER_WARNING) : null;
} else {
$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->_connected = true;
}
} else {
if ( ! $this->dbh = @oci_connect($dbuser, $dbpassword, $this->tns) ) {
$this->register_error($php_errormsg);
$this->show_errors ? trigger_error($php_errormsg,E_USER_WARNING) : null;
} else {
$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->_connected = true;
}
}
}
/**
* Try to connect to Oracle database server with connection pooling
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
*/
private function _pconnect($dbuser='', $dbpassword='') {
if ( ! empty($this->characterSet) ) {
if ( ! $this->dbh = @oci_pconnect($dbuser, $dbpassword, $this->tns, $this->characterSet) ) {
$this->register_error($php_errormsg);
$this->show_errors ? trigger_error($php_errormsg,E_USER_WARNING) : null;
} else {
$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->_connected = true;
}
} else {
if ( ! $this->dbh = @oci_pconnect($dbuser, $dbpassword, $this->tns) ) {
$this->register_error($php_errormsg);
$this->show_errors ? trigger_error($php_errormsg,E_USER_WARNING) : null;
} else {
$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->_connected = true;
}
}
} // _connect
/**
* In the case of Oracle quick_connect is not really needed because std.
* connect already does what quick connect does - but for the sake of
* consistency it has been included
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
* @return boolean
*/
public function quick_connect($dbuser='', $dbpassword='') {
return $this->connect($dbuser, $dbpassword);
} // quick_connect
/**
* No real equivalent of mySQL select in Oracle, once again, function
* included for the sake of consistency
*
* @param string $dbuser The database user name
* Default is empty string
* @param string $dbpassword The database users password
* Default is empty string
* @return boolean
*/
public function select($dbuser='', $dbpassword='') {
return $this->connect($dbuser, $dbpassword);
} // select
/**
* Format a Oracle string correctly for safe Oracle insert
*
* @param string $str
* @return string
*/
public function escape($str) {
$return_val = '';
if ( !isset($str) or empty($str) ) {
$return_val = '';
} else if ( is_numeric($str) ) {
$return_val = $str;
} else {
$non_displayables = array(
'/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
'/%1[0-9a-f]/', // url encoded 16-31
'/[\x00-\x08]/', // 00-08
'/\x0b/', // 11
'/\x0c/', // 12
'/[\x0e-\x1f]/' // 14-31
);
foreach ( $non_displayables as $regex ) {
$str = preg_replace( $regex, '', $str );
}
$return_val = str_replace("'", "''", $str );
}
return $return_val;
} // escape
/**
* Return Oracle specific system date syntax
* i.e. Oracle: SYSDATE Mysql: NOW()
*
* @return string
*/
public function sysdate() {
return 'SYSDATE';
} // sysdate
/**********************************************************************
* These special Oracle functions make sure that even if your test
* pattern is '' it will still match records that are null if
* you don't use these funcs then oracle will return no results
* if $user = ''; even if there were records that = ''
*
* SELECT * FROM USERS WHERE USER = ".$db->is_equal_str($user)."
*/
/**
* Returns an escaped equal string
*
* @param string $str
* @return string
*/
public function is_equal_str($str='') {
return ($str=='' ? 'IS NULL' : "= '" . $this->escape($str) . "'");
} // is_equal_str
/**
* Returns an equal string for integer values
*
* @param string $int
* @return string
*/
public function is_equal_int($int) {
return ($int=='' ? 'IS NULL': '= ' . $int);
} // is_equal_int
/**
* Another oracle specific function - if you have set up a sequence this
* function returns the next ID from that sequence
* If the sequence is not defined, the sequence is created by this method.
* Though be shure, that you use the correct sequence name not to end in
* more than one sequence for a primary key of a table.
*
* @param string $seq_name Name of the sequenze
* @return string
*/
public function insert_id($seq_name) {
$return_val = $this->get_var("SELECT $seq_name.nextVal id FROM Dual");
// If no return value then try to create the sequence
if ( ! $return_val ) {
$this->query("CREATE SEQUENCE $seq_name maxValue 9999999999 INCREMENT BY 1 START WITH 1 CACHE 20 CYCLE");
$return_val = $this->get_var("SELECT $seq_name.nextVal id FROM Dual");
$this->register_error($this->ezsql_oracle_str[2] . ": $seq_name");
$this->show_errors ? trigger_error($this->ezsql_oracle_str[2] . ": $seq_name", E_USER_NOTICE) : null;
}
return $return_val;
} // insert_id
/**
* An alias for insert_id using the original Oracle function name.
*
* @param string $seq_name Name of the sequenze
* @return string
*/
public function nextVal($seq_name) {
return $this->insert_id($seq_name);
} // nextVal
/**
* Perform Oracle query and try to determine result value
*
* @param string $query
* @return object
*/
public function query($query) {
$return_value = 0;
// Flush cached values..
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query(\"$query\")";
// Keep track of the last query for debug..
$this->last_query = $query;
$this->num_queries++;
// Use core file cache function
if ( $cache = $this->get_cache($query) ) {
return $cache;
}
// If there is no existing database connection then try to connect
if ( ! isset($this->dbh) || ! $this->dbh ) {
$this->connect($this->dbuser, $this->dbpassword);
}
// Parses the query and returns a statement..
if ( ! $stmt = OCIParse($this->dbh, $query)) {
$error = OCIError($this->dbh);
$this->register_error($error['message']);
$this->show_errors ? trigger_error($error['message'], E_USER_WARNING) : null;
return false;
} elseif ( ! $this->result = OCIExecute($stmt)) {
// Execut the query..
$error = OCIError($stmt);
$this->register_error($error['message']);
$this->show_errors ? trigger_error($error['message'], E_USER_WARNING) : null;
return false;
}
// If query was an insert
$is_insert = false;
if ( preg_match('/^(insert|delete|update|create) /i', $query) ) {
$is_insert = true;
// num afected rows
$this->_affectedRows = @OCIRowCount($stmt);
$return_value = $this->_affectedRows;
} else {
// If query was a select
// Get column information
if ( $num_cols = @OCINumCols($stmt) ) {
// Fetch the column meta data
for ( $i = 1; $i <= $num_cols; $i++ ) {
$this->col_info[($i-1)]->name = @OCIColumnName($stmt, $i);
$this->col_info[($i-1)]->type = @OCIColumnType($stmt, $i);
$this->col_info[($i-1)]->size = @OCIColumnSize($stmt, $i);
}
}
// If there are any results then get them
if ($this->num_rows = @OCIFetchStatement($stmt, $results)) {
// Convert results into object orientated results..
// Due to Oracle strange return structure - loop through columns
foreach ( $results as $col_title => $col_contents ) {
$row_num=0;
// Then - loop through rows
foreach ( $col_contents as $col_content ) {
$this->last_result[$row_num]->{$col_title} = $col_content;
$row_num++;
}
}
}
// Num result rows
$return_value = $this->num_rows;
}
// Disk caching of queries
$this->store_cache($query, $is_insert);
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return $return_value;
} // query
/**
* Close the database connection
*/
public function disconnect() {
if ( $this->dbh ) {
oci_close($this->dbh);
$this->_connected = false;
}
} // disconnect
/**
* Sets the TNS variable with all relevant connection informations
*/
private function setTNS() {
$this->tns = "(DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = $this->host)(PORT = $this->port))
(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = $this->serviceName)))";
} // setTNS
} // ezSQL_oracle8_9