/ Published in: PHP
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
/* LoneDatabaseClass * PHP SQL class for MySQL, Oracle and MS SQL ref: lonedb ver: 0.5 date:20100305 Copyright (c) 2009 Milan Stankovic semidot@gmail.com, coevolvesoft@gmail.com License: MIT, http://www.opensource.org/licenses/mit-license.php ____________________________________________________________________________ todo: ora: bind and execute always ora: insert -add sequence name ____________________________________________________________________________ changes: 20100305 stable mssql support 20100120 mssql support 20091210 merged my and ora versions ____________________________________________________________________________ public: $con, $rcount, $keys, $mode select($table, $cols = '*', $where = null, $order = null, $opt = 0) insert($table, $values, $cols = null, $filename='', $filefield='') delete($table, $where = null) update($table, $where, $values, $filename='', $filefield='') selectAssociative($table, $cols = '*', $where = null, $order = null, $opt = 0) getResult() setConnection($host, $user, $pass, $dbname) setDatabase($dbname) disconnect() cleanup($data, $write = false) errDetails($rsc=null) ____________________________________________________________________________ private: $db_host, $db_user, $db_pass, $db_name, $result, $oracon, $orast connect() tableExists($table) cleanupSlashes($data, $write = false) ms_ : select(), update(), insert(), delete(), selectAssociative() my_ : select(), update(), insert(), delete(), selectAssociative() ora_ : select(), update(), insert(), delete(), selectAssociative(), updateFile(), insertFile() ____________________________________________________________________________ notes: All methods return true if completed successfully and false if an error occurred, except getResult and cleanup. On setConnection, setDatabase, insert, delete and update, result is empty array, on disconnect result is intact. For update and insert, filename and filefield used in oracle mode for file handling (bind mode). ____________________________________________________________________________ */ class LoneDatabaseClass { var $db_host = ''; // use setConnection() var $db_user = ''; var $db_pass = ''; var $db_name = ''; var $con = false; // connection active flag var $rcount = -1; var $oracon = null; var $orast = null; var $mode = ''; function select($table, $cols = '*', $where = null, $order = null, $opt = 0) { // required: table: table name // optional: cols: columns requested, comma separated // where: expresion as a string // order: 'column DESC' expresion as a string // opt: oracle options: OCI_BOTH | ( OCI_ASSOC | OCI_NUM ) & OCI_RETURN_NULLS & OCI_RETURN_LOBS if (!$this->tableExists($table)) { return false; } $q = 'SELECT '.$cols.' FROM '.$table; if ($where != null) { $q .= ' WHERE '.$where; } if ($order != null) { $q .= ' ORDER BY '.$order; } if ($this->mode=='mysql') { return $this->my_select($q); } else if ($this->mode=='mssql') { return $this->ms_select($q); } else if ($this->mode=='oracle') { return $this->ora_select($q, $opt); } return false; } function insert($table, $values, $cols = null, $filename='', $filefield='') { // required: table: table name // values: values as array // optional: cols: column names, comma separated // filename, filefield: oracle file handling if (!$this->tableExists($table)) { return false; } if ($this->mode=='mysql') { return $this->my_insert($table, $values, $cols); } else if ($this->mode=='mssql') { return $this->ms_insert($table, $values, $cols); } else if ($this->mode=='oracle') { if ($filename=='') { return $this->ora_insert($table, $values, $cols); } else { return $this->ora_insertFile($table, $values, $cols, $filename, $filefield); } } return false; } function update($table, $where, $values, $filename='', $filefield='') { // required: table: tabel name // where: expresion as a string // values: values as array, column names as keys // filename, filefield: oracle file handling if (!$this->tableExists($table)) { return false; } if ($this->mode=='mysql') { return $this->my_update($table, $where, $values); } else if ($this->mode=='mssql') { return $this->ms_update($table, $where, $values); } if ($this->mode=='oracle') { if ($filename=='') { return $this->ora_update($table, $where, $values); } else { return $this->ora_updateFile($table, $where, $values, $filename, $filefield); } } return false; } function delete($table, $where = null) { if (!$this->tableExists($table)) { return false; } if ($where == null) { return false; } if ($this->mode=='mysql') { return $this->my_delete($table, $where); } else if ($this->mode=='mssql') { return $this->ms_delete($table, $where); } else if ($this->mode=='oracle') { return $this->ora_delete($table, $where); } return false; } function selectAssociative($table, $keycolumn, $cols = '*', $where = null, $order = null, $opt = 0) { if (!$this->tableExists($table)) { return false; } $q = 'SELECT '.$cols.' FROM '.$table; if ($where != null) { $q .= ' WHERE '.$where; } if ($order != null) { $q .= ' ORDER BY '.$order; } if ($this->mode=='mysql') { return $this->my_selectAssociative($q, $keycolumn); } else if ($this->mode=='oracle') { return $this->ora_selectAssociative($q, $keycolumn, $opt); } else if ($this->mode=='mssql') { return $this->ms_selectAssociative($q, $keycolumn, $table); } return false; } // ____________________________________________________________________________ function ms_select($q) { // mssql select //echo $q; if (!$query) { return false; } if ($this->rcount < 1) { return true; } for ($i = 0; $i < $this->rcount; $i++) { for ($x = 0; $x < $kcount; $x++) { //$v= iconv('UCS-2LE', 'UTF-8//IGNORE', $r[$this->keys[$x]]); $v = $r[$this->keys[$x]]; if($this->rcount > 1) { $this->result[$i][$this->keys[$x]] = $v; } else { $this->result[$this->keys[$x]] = $r[$this->keys[$x]]; } } } return true; } function ms_update($table, $where, $values) { // mssql update $q = ''; for ($i = 0; $i < $vcount; $i++) { if ($q!=''){ $q .= ','; } $q .= $keys[$i].'='.$values[$keys[$i]]; } $q = 'UPDATE '.$table.' SET '.$q; $q .= ' WHERE '.$where; return $ok; } function ms_insert($table, $values, $cols) { // mssql insert $q = 'INSERT INTO '.$table; if ($cols != null) { $q .= ' ('.$cols.')'; } $q .= ' VALUES ('.$values.')'; return $ok; } function ms_selectAssociative($q, $keycolumn, $table) { // mssql select associative if (!$query) { return false;} if ($this->rcount < 1) { return true; } $doconv = $this->columnsToConvert($table); for ($i = 0; $i < $this->rcount; $i++) { for ($x = 0; $x < $kcount; $x++) { if ($doconv[$this->keys[$x]]) { } else { $v = $r[$this->keys[$x]]; } $this->result[$r[$keycolumn]][$this->keys[$x]] = $v; } } return true; } function ms_delete($table, $where) { // mssql delete $q = 'DELETE FROM '.$table.' WHERE '.$where; return $ok; } // ____________________________________________________________________________ function my_select($q) { // mysql select if (!$query) { return false;} if ($this->rcount < 1) { return true; } for ($i = 0; $i < $this->rcount; $i++) { for ($x = 0; $x < $kcount; $x++) { if($this->rcount > 1) { $this->result[$i][$this->keys[$x]] = $r[$this->keys[$x]]; } else { $this->result[$this->keys[$x]] = $r[$this->keys[$x]]; } } else { } } } return true; } function my_update($table, $where, $values) { // mysql update $q = ''; for ($i = 0; $i < $vcount; $i++) { if ($q==''){ $q = $keys[$i].'='.$values[$keys[$i]]; } else { $q .= ','.$keys[$i].'='.$values[$keys[$i]]; } } $q = 'UPDATE '.$table.' SET '.$q; $q .= ' WHERE '.$where; if ($query) { return true; } else { return false; } } function my_insert($table, $values, $cols) { // mysql insert $q = 'INSERT INTO '.$table; if ($cols != null) { $q .= ' ('.$cols.')'; } $q .= ' VALUES ('.$values.')'; return $ok; } function my_selectAssociative($q, $keycolumn) { // mysql select associative if (!$query) { return false;} if ($this->rcount < 1) { return true; } for ($i = 0; $i < $this->rcount; $i++) { for ($x = 0; $x < $kcount; $x++) { $this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]]; } else { } } } return true; } function my_delete($table, $where) { // mysql delete $q = 'DELETE FROM '.$table.' WHERE '.$where; return $ok; } // ____________________________________________________________________________ function ora_select($q, $opt) { // oracle select $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; } $oramode = OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS; if ($opt!=0) { $oramode = $opt; } $this->rcount = 0; while ($r = oci_fetch_array($this->orast, $oramode)) { } for ($x = 0; $x < $kcount; $x++) { $this->result[$this->rcount][$this->keys[$x]] = $r[$this->keys[$x]]; } $this->rcount++; } if($this->rcount == 1) { // columns as keys on one $t = $this->result[0]; for ($x = 0; $x < $kcount; $x++) { $this->result[$this->keys[$x]]=$t[$this->keys[$x]]; } } return true; } function ora_update($table, $where, $values) { // oracle update $q = ''; for ($i = 0; $i < $vcount; $i++) { if ($q==''){ $q = $keys[$i].'='.$values[$keys[$i]]; } else { $q .= ','.$keys[$i].'='.$values[$keys[$i]]; } } $q = 'UPDATE '.$table.' SET '.$q; $q .= ' WHERE '.$where; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; } return true; } function ora_updateFile($table, $where, $values, $filename, $filefield) { // oracle update file $q = ''; for ($i = 0; $i < $vcount; $i++) { $pair = $keys[$i].'= '.$values[$keys[$i]]; if ($keys[$i]==$filefield) { $pair=$filefield.'= empty_blob()'; } if ($q==''){ $q = $pair; } else { $q .= ','.$pair; } } $q = 'UPDATE '.$table.' SET '.$q; $q .= ' WHERE '.$where; $q .= ' returning '.$filefield.' into :blobdata'; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } $lob = oci_new_descriptor($this->oracon, OCI_D_LOB); oci_bind_by_name($this->orast, ':blobdata', $lob, -1, OCI_B_BLOB); if (!oci_execute($this->orast, OCI_DEFAULT)) { $this->errDetails($this->orast); return false; } $lob->import($filename); oci_commit($this->oracon); $lob->close(); return true; } function ora_insert($table, $values, $cols) { // oracle insert global $oraseq; $q = 'INSERT INTO '.$table; if ($cols != null) { $q .= ' (ID,'.$cols.')'; } $q .= ' VALUES ('.$oraseq.'.NextVal, '.$values.')'; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; } return true; } function ora_insertFile($table, $values, $cols, $filename, $filefield) { // oracle insert file global $oraseq; $q = 'INSERT INTO '.$table; if ($cols != null) { $q .= ' (ID,'.$cols.')'; } $q .= ' VALUES ('.$oraseq.'.NextVal'; for ($i = 0; $i < $vcount; $i++) { if ($keys[$i]==$filefield) { $q .= ',empty_blob()'; } else {$q .= ','.$values[$keys[$i]]; } } $q .= ') returning '.$filefield.' into :blobdata'; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } $lob = oci_new_descriptor($this->oracon, OCI_D_LOB); oci_bind_by_name($this->orast, ':blobdata', $lob, -1, OCI_B_BLOB); if (!oci_execute($this->orast, OCI_DEFAULT)) { $this->errDetails($this->orast); return false; } $lob->import($filename); oci_commit($this->oracon); $lob->close(); return true; } function ora_selectAssociative($q, $keycolumn, $opt) { // oracle select associative $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; } $oramode = OCI_ASSOC+OCI_RETURN_NULLS; if ($opt!=0) { $oramode = $opt; } $this->rcount = 0; while ($r = oci_fetch_array($this->orast, $oramode)) { } for ($x = 0; $x < $kcount; $x++) { $this->result[$r[$keycolumn]][$this->keys[$x]] = $r[$this->keys[$x]]; } $this->rcount++; } return true; } function ora_delete($table, $where) { // oracle delete $q = 'DELETE FROM '.$table.' WHERE '.$where; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); return false; } if (!oci_execute($this->orast)) { $this->errDetails($this->orast); return false; } return true; } // ____________________________________________________________________________ function getResult() { return $this->result; } function setConnection($host, $user, $pass, $dbname) { global $dbmode; $this->mode = $dbmode; if ($this->con) { $ok = $this->disconnect(); if(!$ok) { return false; } } $this->oracon = null; $this->orast = null; $this->con = false; $this->rcount = -1; $this->db_host = $host; $this->db_user = $user; $this->db_pass = $pass; $this->db_name = $dbname; $ok = $this->connect(); // $this->con==true on success return $ok; } function setDatabase($dbname) { // change database global $dbmode; $this->mode = $dbmode; if ($this->con) { $ok = $this->disconnect(); if(!$ok) return false; } $this->con = false; $this->rcount = -1; $this->db_name = $dbname; $ok = $this->connect(); // $this->con==true on success return $ok; } function disconnect() { $ok = false; if ($this->mode=='mysql') { } else if ($this->mode=='mssql') { } else if ($this->mode=='oracle') { if ($this->orast) { $ok = oci_free_statement($this->orast); } if ($ok && $this->oracon) { $ok = oci_close($this->oracon); } } $this->con = !$ok; return $ok; } function connect() { if ($this->db_host=='' || $this->db_user=='' || $this->db_pass=='' || $this->db_name=='') { return false; } $ok = false; if ($this->mode=='mysql') { } else if ($this->mode=='oracle') { $this->oracon = oci_connect($this->db_user,$this->db_pass,$this->db_host.'/'.$this->db_name); if (!$this->oracon) { $this->errDetails(); } else { $ok = true; } } else if ($this->mode=='mssql') { } $this->con = $ok; return $ok; } function tableExists($table) { if (!$this->con || $table=='') { return false; } $this->rcount = -1; $ok = false; if ($this->mode=='mysql') { $q = 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"'; if ($q) { } } else if ($this->mode=='oracle') { $q = "select * from all_objects where object_type in ('TABLE','VIEW') and object_name = '$table'"; $this->orast = oci_parse($this->oracon, $q); if (!$this->orast) { $this->errDetails($this->oracon); } else if (!oci_execute($this->orast)) { $this->errDetails($this->orast); } else { $ok = true; } } else if ($this->mode=='mssql') { $q = "SELECT name FROM sys.sysobjects WHERE name='$table' AND OBJECTPROPERTY(id,'IsUserTable')=1"; if ($q) { } } return $ok; } // ____________________________________________________________________________ function cleanup($data, $write = false) { foreach ($data as $key => $value) { $data[$key] = $this->cleanupSlashes($value, $write); } } else { $data = $this->cleanupSlashes($data, $write); } return $data; } function cleanupSlashes($data, $write = false) { } if ($write && $this->mode=='mysql') { } } return $data; } function errDetails($rsc=null) { global $debug; if ($debug!==true) { return true; } if ($rsc==null) { $err = oci_error(); } else { $err = oci_error($rsc); } echo '<br /><font color="red"><strong>Oracle Error Details for '. $err['code']; echo '<br />message = ' . $err['message']; echo '<br />position = ' . $err['offset']; echo '<br />statement = ' . $err['sqltext']; echo '</strong></font><br /><br />'; return true; } // ____________________________________________________________________________ // MS SQL UCS-2 text read/write, // declare fields in model: convert(varbinary(2*size), fieldname) as fieldname function columnsToConvert($table) { // result as [ sColname=>bDoConv ] global $fields; } else { } foreach ($cols as $k=>$f) { if ($fields[$table][$f]['type']=='ntext') { $ret[$f] = true; } else { $ret[$f] = false; } } return $ret; } /* not used */ function columnsDeclare($table) { // result as [ sColname=>declaration ] global $fields; } else { } foreach ($cols as $k=>$f) { $ret[$f] = $fields[$table][$f]['colname']; } return $ret; } function encUCS2($str) { // converting UCS-2 string into "binary" hexadecimal form return "0x{$arr['hex']}"; // include it in sql statement without quotes } // iconv('UCS-2LE', 'UTF-8', $str) to decode function ucs2html($str) { $html=''; for($i=0;$i<$len;$i+=2) return($html); } function hex2utf($UtfCharInHex) { $OutputChar = ""; return $OutputChar; } }