Location: PHPKode > scripts > Simple ADO > simple-ado/class.simpleado.php
<?
/****************************************************
*  simpleADO 1.0
*
*  Author: Zsolt Munoz
*  Email: hide@address.com
*  Licinse: GPL
*
*  Updated using original source code by Carlos Reche
*****************************************************/
// NULL VT Values
define(ADO_EMPTY,0);
define(ADO_NULL,1);
// Integer VT Values
define(ADO_TINYINT,16);
define(ADO_SMALLINT,2);
define(ADO_INT,3);
define(ADO_BIGINT,20);
define(ADO_UTINYINT,17);
define(ADO_USMALLINT,18);
define(ADO_UINT,19);
define(ADO_UBIGINT,21);
define(ADO_SINGLE,4);
define(ADO_DOUBLE,5);
define(ADO_CURRENCY,6);
define(ADO_DECIMAL,14);
define(ADO_NUMERIC,131);
// Boolean VT Values
define(ADO_BOOLEAN,11);
// Error VT Values
define(ADO_ERROR,10);
// User Defines VT Values
define(ADO_USERDEFINED,132);
// Object VT Values
define(ADO_VARIANT,12);
define(ADO_IDISPATCH,9);
define(ADO_IUNKNOWN,13);
define(ADO_GUID,72);
// Date/TIme VT Values
define(ADO_DATE,7);
define(ADO_ADODATE,133);
define(ADO_ADOTIME,134);
define(ADO_ADOTIMESTAMP,135);
// String VT Values
define(ADO_BSTR,8);
define(ADO_CHAR,129);
define(ADO_VARCHAR,200);
define(ADO_LONGVARCHAR,201);
define(ADO_WCHAR,130);
define(ADO_VARWCHAR,202);
define(ADO_LONGVARWCHAR,203);
// Binary VT Values
define(ADO_BINARY,128);
define(ADO_VARBINARY,204);
define(ADO_LONGVARBINARY,205);
// Other VT Values
define(ADO_CHAPTER,136);
define(ADO_FILETIME,64);
define(ADO_ADOFILETIME,137);
define(ADO_PROPVARIANT,138);
define(ADO_VARNUMERIC,139);

class simpleADO {

	var $comConnect;
	var $comRecord;
	var $comCommand;

	var $connectString;

	var $table;
	var $db;
	var $DSN;     // In case of ODBC set as DSN Name

	var $selection = "*";
	var $where;
	var $group;
	var $sort;
	var $order = "ASC";

	var $query;
	var $fieldSet;

	var $user;
	var $pass;

	var $xmlPath = "ado.xml";

	var $readonly = false;

	var $timezone = -7;

	function __construct($user, $pass, $DSN) {
		$this->user = ($user != NULL) ? (string)$user : NULL;
		$this->pass = ($pass != NULL) ? (string)$pass : NULL;
		$this->DSN  = ($DSN != NULL)  ? (string)$DSN  : NULL;
		
		$this->connectionString = $this->createConnectString($this->DSN);
	}

	function __deconstruct() {
		$this->comRecord->Close;
		$this->comConnect->Close;
	}

	// These are all the initial connection strings to the ODBC
	private function connect() {
		try {
			$this->comConnect = new COM("ADODB.Connection");
			$this->comConnect->Open($this->connectionString);
			return $this->comConnect;
		} catch (Exception $e) {
			$this->error($e);
		}
	}

	private function command() {
		try {
			$this->comCommand = new COM("ADODB.Command");
			$this->comCommand->ActiveConnection = $this->connect();
			$this->comCommand->CommandText = $this->query;
			return $this->comCommand->Execute();
		} catch (Exception $e) {
			$this->error($e);
		}
	}

	public function select() {
		$this->query = $this->createSelect();
		$this->comRecord = new COM("ADODB.Recordset");
		try {
			$this->comRecord = $this->command();
			return true;
		} catch (exception $e) {
			$this->error($e);
		}
	}

	public function insert() {
		if($this->readonly != true) {
			$this->query = $this->createInsert();
			$this->comRecord = new COM("ADODB.Recordset");
			try {
				$this->comRecord = $this->command();
				return true;
			} catch (exception $e) {
				$this->error($e);
			}
		} else {
			$this->error('Could NOT run "INSERT" function. Table is in <strong>READ ONLY</strong> mode.');
		}
	}

	public function update() {
		if($this->readonly != true) {
			$this->query = $this->createUpdate();
			$this->comRecord = new COM("ADODB.Recordset");
			try {
				$this->comRecord = $this->command();
				return true;
			} catch (exception $e) {
				$this->error($e);
			}
		} else {
			$this->error('Could NOT run "UPDATE" function. Table is in <strong>READ ONLY</strong> mode.');
		}
	}

	public function delete() {
		if($this->readonly != true) {
			$this->query = $this->createDelete();
			$this->comRecord = new COM("ADODB.Recordset");
			try {
				$this->comRecord = $this->command();
				return true;
			} catch (exception $e) {
				$this->error($e);
			}
		} else {
			$this->error('Could NOT run "DELETE" function. Table is in <strong>READ ONLY</strong> mode.');
		}
	}


	//****************************************************************************
	
	// Query Builders
	private function returnWhere() {
		$where = ($this->where != "")  ?  (" WHERE " . $this->where)  :  "";
		return $where;
	}

	private function returnGroup() {
		$group = ($this->group != "")  ?  (" GROUP BY " . $this->group) : "";
		return $group;
	}

	private function returnSort() {
		$sort = ($this->sort != "")  ?  (" ORDER BY " . $this->sort . " " . $this->order) : "";
		return $sort;
	}

	//****************************************************************************
	
	// Query Functions
	public function useDB($db) {
		$this->db = (string)$db;
	}

	public function useTable($table) {
		$this->table = ($this->db != "") ? $this->db . "." . (string)$table : (string)$table;
	}

	public function setSelection($selection) {
		$this->selection = (string)$selection;
	}

	public function setWhere($where) {
		$this->where = (string)$where;
	}

	public function setGroup($group) {
		$this->group = (string)$group;
	}

	public function setSort($sort,$order = NULL) {
		$this->sort = (string)$sort;
		$this->order = ($order == NULL) ? $this->order : $order;
	}

	public function setFieldValue($fieldName, $fieldValue, $dbFunction = false) {
		$this->fieldSet[$fieldName][value]      = (string)$fieldValue;
		$this->fieldSet[$fieldName][dbFunction] = (bool)$dbFunction;
	}

	//****************************************************************************
	
	// Recordset Functions
	public function recordSet() {
		try {
			$this->comRecord->MoveFirst;
			$recordNum = 0;
			while(!$this->comRecord->EOF) {
				foreach($this->comRecord->fields as $rsVal) {
					// This has not been finished but if you have any other variants that require more let me know.
					switch((int)$rsVal->type) {
						case 133:
							$dateValue = (trim($rsVal->value) == "") ? NULL : @variant_date_to_timestamp(trim($rsVal->value));
							$myRecordSet[$recordNum][$rsVal->name] = date('Y-m-d', $dateValue);
							break;
						case 134:
							$timeValue = (trim($rsVal->value) == "") ? NULL : @variant_date_to_timestamp(trim($rsVal->value));
							$myRecordSet[$recordNum][$rsVal->name] = date('H:i:s', $timeValue);
							break;
						case   7:
							$timeValue = (trim($rsVal->value) == "") ? NULL : @variant_date_to_timestamp(trim($rsVal->value));
							$myRecordSet[$recordNum][$rsVal->name] = date('Y-m-d H:i:s', $timeValue);
							//$myRecordSet[$recordNum][$rsVal->name] = $rsVal->value->HighPart;
							break;
						case   0:
						case   1:
							$myRecordSet[$recordNum][$rsVal->name]  = NULL;
							break;
						case  16:
						case   2:
						case   3:
						case  20:
						case 131:
							$myRecordSet[$recordNum][$rsVal->name] = (int)(trim($rsVal->value));
							break;
						case  17:
						case  18:
						case  19:
						case  21:
						case   4:
						case   5:
						case   6:
						case  14:
							$myRecordSet[$recordNum][$rsVal->name] = (float)(trim($rsVal->value));
							break;
						case  11:
							$myRecordSet[$recordNum][$rsVal->name] = (bool)(trim($rsVal->value));
							break;
						case  10:
							$myRecordSet[$recordNum][$rsVal->name] = (string)(trim($rsVal->value));
							break;
						case  12:
						case 204:
							if(isset($rsVal->value->LowPart) || ($rsVal->value == NULL)) {
								$myRecordSet[$recordNum][$rsVal->name]['LowPart'] = $rsVal->value->LowPart;
								$myRecordSet[$recordNum][$rsVal->name]['HighPart'] = $rsVal->value->HighPart;
								$myRecordSet[$recordNum][$rsVal->name]['TimeStamp'] = $this->int64($rsVal->value->HighPart, $rsVal->value->LowPart);
							} else {
								foreach($rsVal->value as $goVar=>$goVal) {
									$myRecordSet[$recordNum][$rsVal->name][$goVar] = ($goVal);
								}
							}
							break;
						default:
							$myRecordSet[$recordNum][$rsVal->name] = ($rsVal->type);
							break;
					}
				}
				$recordNum++;
				$this->comRecord->MoveNext;
			}
		} catch (exception $e) {
			$this->error($e);
		}
		return $myRecordSet;
	}

	//****************************************************************************
	
	// Select/Insert/Update/Delete Functions
	// Select function must run select() to run
	private function createSelect() {
		$select = "SELECT " . $this->selection . " FROM " . $this->table . $this->returnWhere() . $this->returnGroup() . $this->returnSort();
		return $select;
	}

	// Insert function must run insert() to run
	private function createInsert() {
		if ($this->table == ""  ||  count($this->fieldSet) == 0) {
			if ($this->table == "") {
				$this->error('Could NOT create "INSERT" query. Parameter <strong>"table"</strong> was empty.');
			} if (count($this->values) == 0) {
				$this->error('Could NOT create "INSERT" query. Parameter <strong>"values"</strong> was empty.');
			}
			return false;
		}
		$values = $fields = array();
		foreach ($this->fieldSet as $fieldName => $fieldSettings) {
			$fields[] = $fieldName;
			if ($fieldSettings['value'] === NULL) {
				$values[] = "NULL";
			} else if (($fieldSettings['dbFunction']) || (is_numeric($fieldSettings['value']))){
				$values[] = $fieldSettings['value'];
			} else {
				$values[] = "'" . $fieldSettings['value'] . "'";
			}
		}
		$values = " (" .   implode(', ', $fields)   . ") VALUES (" .   implode(', ', $values)   . ")";
		$insert = "INSERT INTO " . $this->table . $values;
		return $insert;
	}

	// Update function must run update() to run
	private function createUpdate() {
		if ($this->table == ""  ||  count($this->fieldSet) == 0  ||  $this->where == "") {
			if ($this->table == "") {
				$this->error('Could NOT create "UPDATE" query. Parameter <strong>"table"</strong> was empty.');
			} if (count($this->fieldSet) == 0) {
				$this->error('Could NOT create "UPDATE" query. Parameter <strong>"values"</strong> was empty.');
			} if ($this->where == "") {
				$this->error('Safety procedure: "UPDATE" query was not created because <strong>"where"</strong> clause was empty.');
			}
			return false;
		}
		$values = $fields = array();
		foreach ($this->fieldSet as $fieldName => $fieldSettings) {
			if ($fieldSettings['value'] === NULL) {
				$values[] = $fieldName . " = NULL";
			} else if (($fieldSettings['dbFunction']) || (is_numeric($fieldSettings['value']))){
				$values[] = $fieldName . " = " . $fieldSettings['value'];
			} else {
				$values[] = $fieldName . " = '" . $fieldSettings['value'] . "'";
			}
		}
		$values = " SET " .   implode(', ', $values);
		$update = "UPDATE " . $this->table . $values . $this->returnWhere();// . $this->returnLimit();
		return $update;
	}

	// Delete function must run delete() to run
	private function createDelete() {
		if ($this->table == ""  ||  $this->where == "") {
			if ($this->table == "") {
				$this->error('Could NOT create "DELETE" query. Parameter <strong>"table"</strong> was empty.');
			} if ($this->where == "") {
				$this->error('Safety procedure: "DELETE" query was not created because <strong>"where"</strong> clause was empty.');
			}
			return false;
		}
		$delete = "DELETE FROM " . $this->table . $this->returnWhere();// . $this->returnLimit();
		return $delete;
	}

	//****************************************************************************
	
	// This is the error function for all ODBC Errors
	private function error($objError) {
		if(is_object($objError)) {
			$error = $objError->getMessage();
			$errno = $objError->getCode();
			die('<span style="{background-color: #ee0000; color: #FFFFFF}"><strong>Error Code:</strong>'.$errno.'<br />'.nl2br($error).'</span>');
		} else {
			die('<span style="{background-color: #ee0000; color: #FFFFFF}">'.$objError.'</span>');
		}
	}

	private function createConnectString($type) {
		$xml = simplexml_load_file($this->xmlPath);
		foreach($xml as $xmlVar => $xmlVal) {
			if($xmlVal->attributes()->name == $type) {
				if($xmlVal->attributes()->readonly == "true") {
					$this->readonly = true;
				}
				if(isset($xmlVal->username)) {
					$connectionString[(string)$xmlVal->username->attributes()->variable] = $this->user;
				}
				if(isset($xmlVal->password)) {
					$connectionString[(string)$xmlVal->password->attributes()->variable] = $this->pass;
				}
				foreach($xmlVal->option as $strVar=>$strVal) {
					if((string)$strVal->attributes()->variable == "Driver") {
						$connectionString[(string)$strVal->attributes()->variable] = "{".(string)$strVal[0]."}";
					} else {
						$connectionString[(string)$strVal->attributes()->variable] = (string)addslashes($strVal[0]);
					}
				}
			}
		}
		foreach($connectionString as $conVar=>$conVal) {
			$finalConnection .= $conVar."=".$conVal.";";
		}
		return rtrim($finalConnection, ";");
	}

	private function int64($highPart, $lowPart, $tmZone = NULL) {
		$highPart = ($lowPart <= 0) ? $highPart + 1 : $highPart;
		$int64 = (($highPart * pow(2,32)) + $lowPart);
		$getTmZone = ($tmZone == NULL) ? -(60*60*($this->timezone)) : -(60*60*($tmZone));
		$yearsFrom1601to1970 = 1970 - 1601;
		$daysFrom1601to1970 = $yearsFrom1601to1970 * 365;
		$daysFrom1601to1970 += $yearsFrom1601to1970 / 4; // leap years
		$daysFrom1601to1970 -= 3; // non-leap centuries (1700,1800,1900).  2000 is a leap century
		$secondsFrom1601to1970 = $daysFrom1601to1970 * 24 * 60 * 60;
		$totalSecondsSince1601 = ($int64 / 10000000);
		$totalSecondsSince1970 = ($totalSecondsSince1601 - $secondsFrom1601to1970)+$getTmZone;
		return date("Y-m-d H:i:s" ,$totalSecondsSince1970);
	}
};
?>
Return current item: Simple ADO