[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  /**
   3   * Sybase driver
   4   *
   5   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   6   *
   7   * @package ADOdb
   8   * @link https://adodb.org Project's web site and documentation
   9   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  10   *
  11   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  12   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  13   * any later version. This means you can use it in proprietary products.
  14   * See the LICENSE.md file distributed with this source code for details.
  15   * @license BSD-3-Clause
  16   * @license LGPL-2.1-or-later
  17   *
  18   * @copyright 2000-2013 John Lim
  19   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  20   * @author Toni Tunkkari <toni.tunkkari@finebyte.com>
  21   */
  22  
  23   // security - hide paths
  24  if (!defined('ADODB_DIR')) die();
  25  
  26  class ADODB_sybase extends ADOConnection {
  27  	 var $databaseType = "sybase";
  28  	 var $dataProvider = 'sybase';
  29  	 var $replaceQuote = "''"; // string to use to replace quotes
  30  	 var $fmtDate = "'Y-m-d'";
  31  	 var $fmtTimeStamp = "'Y-m-d H:i:s'";
  32  	 var $hasInsertID = true;
  33  	 var $hasAffectedRows = true;
  34    	 var $metaTablesSQL="select name from sysobjects where type='U' or type='V'";
  35  	 // see http://sybooks.sybase.com/onlinebooks/group-aw/awg0800e/dbrfen8/@ebt-link;pt=5981;uf=0?target=0;window=new;showtoc=true;book=dbrfen8
  36  	 var $metaColumnsSQL = "SELECT c.column_name, c.column_type, c.width FROM syscolumn c, systable t WHERE t.table_name='%s' AND c.table_id=t.table_id AND t.table_type='BASE'";
  37  	 /*
  38  	 "select c.name,t.name,c.length from
  39  	 syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id
  40  	 where o.name='%s'";
  41  	 */
  42  	 var $concat_operator = '+';
  43  	 var $arrayClass = 'ADORecordSet_array_sybase';
  44  	 var $sysDate = 'GetDate()';
  45  	 var $leftOuter = '*=';
  46  	 var $rightOuter = '=*';
  47  
  48  	 var $port;
  49  
  50  	 /**
  51  	  * might require begintrans -- committrans
  52  	  * @inheritDoc
  53  	  */
  54  	protected function _insertID($table = '', $column = '')
  55  	 {
  56  	 	 return $this->GetOne('select @@identity');
  57  	 }
  58  
  59  	   // might require begintrans -- committrans
  60  	function _affectedrows()
  61  	 {
  62  	 	 return $this->GetOne('select @@rowcount');
  63  	 }
  64  
  65  
  66  	function BeginTrans()
  67  	 {
  68  
  69  	 	 if ($this->transOff) return true;
  70  	 	 $this->transCnt += 1;
  71  
  72  	 	 $this->Execute('BEGIN TRAN');
  73  	 	 return true;
  74  	 }
  75  
  76  	function CommitTrans($ok=true)
  77  	 {
  78  	 	 if ($this->transOff) return true;
  79  
  80  	 	 if (!$ok) return $this->RollbackTrans();
  81  
  82  	 	 $this->transCnt -= 1;
  83  	 	 $this->Execute('COMMIT TRAN');
  84  	 	 return true;
  85  	 }
  86  
  87  	function RollbackTrans()
  88  	 {
  89  	 	 if ($this->transOff) return true;
  90  	 	 $this->transCnt -= 1;
  91  	 	 $this->Execute('ROLLBACK TRAN');
  92  	 	 return true;
  93  	 }
  94  
  95  	 // http://www.isug.com/Sybase_FAQ/ASE/section6.1.html#6.1.4
  96  	function RowLock($tables,$where,$col='top 1 null as ignore')
  97  	 {
  98  	 	 if (!$this->hasTransactions) {
  99  	 	 	 $this->BeginTrans();
 100  	 	 }
 101  	 	 $tables = str_replace(',',' HOLDLOCK,',$tables);
 102  	 	 return $this->GetOne("select $col from $tables HOLDLOCK where $where");
 103  
 104  	 }
 105  
 106  	function SelectDB($dbName)
 107  	 {
 108  	 	 $this->database = $dbName;
 109  	 	 if ($this->_connectionID) {
 110  	 	 	 return @sybase_select_db($dbName);
 111  	 	 }
 112  	 	 else return false;
 113  	 }
 114  
 115  	 /*	 Returns: the last error message from previous database operation
 116  	 	 Note: This function is NOT available for Microsoft SQL Server.	 */
 117  
 118  
 119  	function ErrorMsg()
 120  	 {
 121  	 	 if ($this->_logsql) return $this->_errorMsg;
 122  	 	 if (function_exists('sybase_get_last_message'))
 123  	 	 	 $this->_errorMsg = sybase_get_last_message();
 124  	 	 else {
 125  	 	 	 $this->_errorMsg = 'SYBASE error messages not supported on this platform';
 126  	 	 }
 127  
 128  	 	 return $this->_errorMsg;
 129  	 }
 130  
 131  	 // returns true or false
 132  	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 133  	 {
 134  	 	 if (!function_exists('sybase_connect')) return null;
 135  
 136  	 	 // Sybase connection on custom port
 137  	 	 if ($this->port) {
 138  	 	 	 $argHostname .= ':' . $this->port;
 139  	 	 }
 140  
 141  	 	 if ($this->charSet) {
 142  	 	 	 $this->_connectionID = @sybase_connect($argHostname,$argUsername,$argPassword, $this->charSet);
 143  	 	 } else {
 144  	 	 	 $this->_connectionID = @sybase_connect($argHostname,$argUsername,$argPassword);
 145  	 	 }
 146  
 147  	 	 if ($this->_connectionID === false) return false;
 148  	 	 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 149  	 	 return true;
 150  	 }
 151  
 152  	 // returns true or false
 153  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 154  	 {
 155  	 	 if (!function_exists('sybase_connect')) return null;
 156  
 157  	 	 // Sybase connection on custom port
 158  	 	 if ($this->port) {
 159  	 	 	 $argHostname .= ':' . $this->port;
 160  	 	 }
 161  
 162  	 	 if ($this->charSet) {
 163  	 	 	 $this->_connectionID = @sybase_pconnect($argHostname,$argUsername,$argPassword, $this->charSet);
 164  	 	 } else {
 165  	 	 	 $this->_connectionID = @sybase_pconnect($argHostname,$argUsername,$argPassword);
 166  	 	 }
 167  
 168  	 	 if ($this->_connectionID === false) return false;
 169  	 	 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 170  	 	 return true;
 171  	 }
 172  
 173  	 // returns query ID if successful, otherwise false
 174  	function _query($sql,$inputarr=false)
 175  	 {
 176  	 global $ADODB_COUNTRECS;
 177  
 178  	 	 if ($ADODB_COUNTRECS == false)
 179  	 	 	 return sybase_unbuffered_query($sql,$this->_connectionID);
 180  	 	 else
 181  	 	 	 return sybase_query($sql,$this->_connectionID);
 182  	 }
 183  
 184  	 // See http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12
 185  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 186  	 {
 187  	 	 if ($secs2cache > 0) {// we do not cache rowcount, so we have to load entire recordset
 188  	 	 	 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 189  	 	 	 return $rs;
 190  	 	 }
 191  
 192  	 	 $nrows = (integer) $nrows;
 193  	 	 $offset = (integer) $offset;
 194  
 195  	 	 $cnt = ($nrows >= 0) ? $nrows : 999999999;
 196  	 	 if ($offset > 0 && $cnt) $cnt += $offset;
 197  
 198  	 	 $this->Execute("set rowcount $cnt");
 199  	 	 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,0);
 200  	 	 $this->Execute("set rowcount 0");
 201  
 202  	 	 return $rs;
 203  	 }
 204  
 205  	 // returns true or false
 206  	function _close()
 207  	 {
 208  	 	 return @sybase_close($this->_connectionID);
 209  	 }
 210  
 211  	static function UnixDate($v)
 212  	 {
 213  	 	 return ADORecordSet_array_sybase::UnixDate($v);
 214  	 }
 215  
 216  	static function UnixTimeStamp($v)
 217  	 {
 218  	 	 return ADORecordSet_array_sybase::UnixTimeStamp($v);
 219  	 }
 220  
 221  
 222  
 223  	 # Added 2003-10-05 by Chris Phillipson
 224  	 # Used ASA SQL Reference Manual -- http://sybooks.sybase.com/onlinebooks/group-aw/awg0800e/dbrfen8/@ebt-link;pt=16756?target=%25N%15_12018_START_RESTART_N%25
 225  	 # to convert similar Microsoft SQL*Server (mssql) API into Sybase compatible version
 226  	 // Format date column in sql string given an input format that understands Y M D
 227  	function SQLDate($fmt, $col=false)
 228  	 {
 229  	 	 if (!$col) $col = $this->sysTimeStamp;
 230  	 	 $s = '';
 231  
 232  	 	 $len = strlen($fmt);
 233  	 	 for ($i=0; $i < $len; $i++) {
 234  	 	 	 if ($s) $s .= '+';
 235  	 	 	 $ch = $fmt[$i];
 236  	 	 	 switch($ch) {
 237  	 	 	 case 'Y':
 238  	 	 	 case 'y':
 239  	 	 	 	 $s .= "datename(yy,$col)";
 240  	 	 	 	 break;
 241  	 	 	 case 'M':
 242  	 	 	 	 $s .= "convert(char(3),$col,0)";
 243  	 	 	 	 break;
 244  	 	 	 case 'm':
 245  	 	 	 	 $s .= "str_replace(str(month($col),2),' ','0')";
 246  	 	 	 	 break;
 247  	 	 	 case 'Q':
 248  	 	 	 case 'q':
 249  	 	 	 	 $s .= "datename(qq,$col)";
 250  	 	 	 	 break;
 251  	 	 	 case 'D':
 252  	 	 	 case 'd':
 253  	 	 	 	 $s .= "str_replace(str(datepart(dd,$col),2),' ','0')";
 254  	 	 	 	 break;
 255  	 	 	 case 'h':
 256  	 	 	 	 $s .= "substring(convert(char(14),$col,0),13,2)";
 257  	 	 	 	 break;
 258  
 259  	 	 	 case 'H':
 260  	 	 	 	 $s .= "str_replace(str(datepart(hh,$col),2),' ','0')";
 261  	 	 	 	 break;
 262  
 263  	 	 	 case 'i':
 264  	 	 	 	 $s .= "str_replace(str(datepart(mi,$col),2),' ','0')";
 265  	 	 	 	 break;
 266  	 	 	 case 's':
 267  	 	 	 	 $s .= "str_replace(str(datepart(ss,$col),2),' ','0')";
 268  	 	 	 	 break;
 269  	 	 	 case 'a':
 270  	 	 	 case 'A':
 271  	 	 	 	 $s .= "substring(convert(char(19),$col,0),18,2)";
 272  	 	 	 	 break;
 273  
 274  	 	 	 default:
 275  	 	 	 	 if ($ch == '\\') {
 276  	 	 	 	 	 $i++;
 277  	 	 	 	 	 $ch = substr($fmt,$i,1);
 278  	 	 	 	 }
 279  	 	 	 	 $s .= $this->qstr($ch);
 280  	 	 	 	 break;
 281  	 	 	 }
 282  	 	 }
 283  	 	 return $s;
 284  	 }
 285  
 286  	 # Added 2003-10-07 by Chris Phillipson
 287  	 # Used ASA SQL Reference Manual -- http://sybooks.sybase.com/onlinebooks/group-aw/awg0800e/dbrfen8/@ebt-link;pt=5981;uf=0?target=0;window=new;showtoc=true;book=dbrfen8
 288  	 # to convert similar Microsoft SQL*Server (mssql) API into Sybase compatible version
 289  	function MetaPrimaryKeys($table, $owner = false)
 290  	 {
 291  	 	 $sql = "SELECT c.column_name " .
 292  	 	 	    "FROM syscolumn c, systable t " .
 293  	 	 	    "WHERE t.table_name='$table' AND c.table_id=t.table_id " .
 294  	 	 	    "AND t.table_type='BASE' " .
 295  	 	 	    "AND c.pkey = 'Y' " .
 296  	 	 	    "ORDER BY c.column_id";
 297  
 298  	 	 $a = $this->GetCol($sql);
 299  	 	 if ($a && sizeof($a)>0) return $a;
 300  	 	 return false;
 301  	 }
 302  }
 303  
 304  /*--------------------------------------------------------------------------------------
 305  	  Class Name: Recordset
 306  --------------------------------------------------------------------------------------*/
 307  global $ADODB_sybase_mths;
 308  $ADODB_sybase_mths = array(
 309  	 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
 310  	 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
 311  
 312  class ADORecordset_sybase extends ADORecordSet {
 313  
 314  	 var $databaseType = "sybase";
 315  	 var $canSeek = true;
 316  	 // _mths works only in non-localised system
 317  	 var  $_mths = array('JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
 318  
 319  	function __construct($id,$mode=false)
 320  	 {
 321  	 	 if ($mode === false) {
 322  	 	 	 global $ADODB_FETCH_MODE;
 323  	 	 	 $mode = $ADODB_FETCH_MODE;
 324  	 	 }
 325  	 	 if (!$mode) $this->fetchMode = ADODB_FETCH_ASSOC;
 326  	 	 else $this->fetchMode = $mode;
 327  	 	 parent::__construct($id);
 328  	 }
 329  
 330  	 /*	 Returns: an object containing field information.
 331  	 	 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
 332  	 	 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
 333  	 	 fetchField() is retrieved.	 */
 334  	function FetchField($fieldOffset = -1)
 335  	 {
 336  	 	 if ($fieldOffset != -1) {
 337  	 	 	 $o = @sybase_fetch_field($this->_queryID, $fieldOffset);
 338  	 	 }
 339  	 	 else if ($fieldOffset == -1) {	 /*	 The $fieldOffset argument is not provided thus its -1 	 */
 340  	 	 	 $o = @sybase_fetch_field($this->_queryID);
 341  	 	 }
 342  	 	 // older versions of PHP did not support type, only numeric
 343  	 	 if ($o && !isset($o->type)) $o->type = ($o->numeric) ? 'float' : 'varchar';
 344  	 	 return $o;
 345  	 }
 346  
 347  	function _initrs()
 348  	 {
 349  	 global $ADODB_COUNTRECS;
 350  	 	 $this->_numOfRows = ($ADODB_COUNTRECS)? @sybase_num_rows($this->_queryID):-1;
 351  	 	 $this->_numOfFields = @sybase_num_fields($this->_queryID);
 352  	 }
 353  
 354  	function _seek($row)
 355  	 {
 356  	 	 return @sybase_data_seek($this->_queryID, $row);
 357  	 }
 358  
 359  	function _fetch($ignore_fields=false)
 360  	 {
 361  	 	 if ($this->fetchMode == ADODB_FETCH_NUM) {
 362  	 	 	 $this->fields = @sybase_fetch_row($this->_queryID);
 363  	 	 } else if ($this->fetchMode == ADODB_FETCH_ASSOC) {
 364  	 	 	 $this->fields = @sybase_fetch_assoc($this->_queryID);
 365  
 366  	 	 	 if (is_array($this->fields)) {
 367  	 	 	 	 $this->fields = $this->GetRowAssoc();
 368  	 	 	 	 return true;
 369  	 	 	 }
 370  	 	 	 return false;
 371  	 	 }  else {
 372  	 	 	 $this->fields = @sybase_fetch_array($this->_queryID);
 373  	 	 }
 374  	 	 if ( is_array($this->fields)) {
 375  	 	 	 return true;
 376  	 	 }
 377  
 378  	 	 return false;
 379  	 }
 380  
 381  	 /*	 close() only needs to be called if you are worried about using too much memory while your script
 382  	 	 is running. All associated result memory for the specified result identifier will automatically be freed.	 */
 383  	function _close() {
 384  	 	 return @sybase_free_result($this->_queryID);
 385  	 }
 386  
 387  	 // sybase/mssql uses a default date like Dec 30 2000 12:00AM
 388  	static function UnixDate($v)
 389  	 {
 390  	 	 return ADORecordSet_array_sybase::UnixDate($v);
 391  	 }
 392  
 393  	static function UnixTimeStamp($v)
 394  	 {
 395  	 	 return ADORecordSet_array_sybase::UnixTimeStamp($v);
 396  	 }
 397  }
 398  
 399  class ADORecordSet_array_sybase extends ADORecordSet_array {
 400  
 401  	 // sybase/mssql uses a default date like Dec 30 2000 12:00AM
 402  	static function UnixDate($v)
 403  	 {
 404  	 global $ADODB_sybase_mths;
 405  
 406  	 	 //Dec 30 2000 12:00AM
 407  	 	 if (!preg_match( "/([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})/"
 408  	 	 	 ,$v, $rr)) return parent::UnixDate($v);
 409  
 410  	 	 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 411  
 412  	 	 $themth = substr(strtoupper($rr[1]),0,3);
 413  	 	 $themth = $ADODB_sybase_mths[$themth];
 414  	 	 if ($themth <= 0) return false;
 415  	 	 // h-m-s-MM-DD-YY
 416  	 	 return  adodb_mktime(0,0,0,$themth,$rr[2],$rr[3]);
 417  	 }
 418  
 419  	static function UnixTimeStamp($v)
 420  	 {
 421  	 global $ADODB_sybase_mths;
 422  	 	 //11.02.2001 Toni Tunkkari toni.tunkkari@finebyte.com
 423  	 	 //Changed [0-9] to [0-9 ] in day conversion
 424  	 	 if (!preg_match( "/([A-Za-z]{3})[-/\. ]([0-9 ]{1,2})[-/\. ]([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})/"
 425  	 	 	 ,$v, $rr)) return parent::UnixTimeStamp($v);
 426  	 	 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 427  
 428  	 	 $themth = substr(strtoupper($rr[1]),0,3);
 429  	 	 $themth = $ADODB_sybase_mths[$themth];
 430  	 	 if ($themth <= 0) return false;
 431  
 432  	 	 switch (strtoupper($rr[6])) {
 433  	 	 case 'P':
 434  	 	 	 if ($rr[4]<12) $rr[4] += 12;
 435  	 	 	 break;
 436  	 	 case 'A':
 437  	 	 	 if ($rr[4]==12) $rr[4] = 0;
 438  	 	 	 break;
 439  	 	 default:
 440  	 	 	 break;
 441  	 	 }
 442  	 	 // h-m-s-MM-DD-YY
 443  	 	 return  adodb_mktime($rr[4],$rr[5],0,$themth,$rr[2],$rr[3]);
 444  	 }
 445  }