<?
/****************************************************
* 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);
}
};
?>