[Summary view]

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

   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->_hastrans) $this->BeginTrans();
  99  	 	 $tables = str_replace(',',' HOLDLOCK,',$tables);
 100  	 	 return $this->GetOne("select $col from $tables HOLDLOCK where $where");
 101  
 102  	 }
 103  
 104  	function SelectDB($dbName)
 105  	 {
 106  	 	 $this->database = $dbName;
 107  	 	 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
 108  	 	 if ($this->_connectionID) {
 109  	 	 	 return @sybase_select_db($dbName);
 110  	 	 }
 111  	 	 else return false;
 112  	 }
 113  
 114  	 /*	 Returns: the last error message from previous database operation
 115  	 	 Note: This function is NOT available for Microsoft SQL Server.	 */
 116  
 117  
 118  	function ErrorMsg()
 119  	 {
 120  	 	 if ($this->_logsql) return $this->_errorMsg;
 121  	 	 if (function_exists('sybase_get_last_message'))
 122  	 	 	 $this->_errorMsg = sybase_get_last_message();
 123  	 	 else {
 124  	 	 	 $this->_errorMsg = 'SYBASE error messages not supported on this platform';
 125  	 	 }
 126  
 127  	 	 return $this->_errorMsg;
 128  	 }
 129  
 130  	 // returns true or false
 131  	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 132  	 {
 133  	 	 if (!function_exists('sybase_connect')) return null;
 134  
 135  	 	 // Sybase connection on custom port
 136  	 	 if ($this->port) {
 137  	 	 	 $argHostname .= ':' . $this->port;
 138  	 	 }
 139  
 140  	 	 if ($this->charSet) {
 141  	 	 	 $this->_connectionID = @sybase_connect($argHostname,$argUsername,$argPassword, $this->charSet);
 142  	 	 } else {
 143  	 	 	 $this->_connectionID = @sybase_connect($argHostname,$argUsername,$argPassword);
 144  	 	 }
 145  
 146  	 	 if ($this->_connectionID === false) return false;
 147  	 	 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 148  	 	 return true;
 149  	 }
 150  
 151  	 // returns true or false
 152  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 153  	 {
 154  	 	 if (!function_exists('sybase_connect')) return null;
 155  
 156  	 	 // Sybase connection on custom port
 157  	 	 if ($this->port) {
 158  	 	 	 $argHostname .= ':' . $this->port;
 159  	 	 }
 160  
 161  	 	 if ($this->charSet) {
 162  	 	 	 $this->_connectionID = @sybase_pconnect($argHostname,$argUsername,$argPassword, $this->charSet);
 163  	 	 } else {
 164  	 	 	 $this->_connectionID = @sybase_pconnect($argHostname,$argUsername,$argPassword);
 165  	 	 }
 166  
 167  	 	 if ($this->_connectionID === false) return false;
 168  	 	 if ($argDatabasename) return $this->SelectDB($argDatabasename);
 169  	 	 return true;
 170  	 }
 171  
 172  	 // returns query ID if successful, otherwise false
 173  	function _query($sql,$inputarr=false)
 174  	 {
 175  	 global $ADODB_COUNTRECS;
 176  
 177  	 	 if ($ADODB_COUNTRECS == false)
 178  	 	 	 return sybase_unbuffered_query($sql,$this->_connectionID);
 179  	 	 else
 180  	 	 	 return sybase_query($sql,$this->_connectionID);
 181  	 }
 182  
 183  	 // See http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12
 184  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 185  	 {
 186  	 	 if ($secs2cache > 0) {// we do not cache rowcount, so we have to load entire recordset
 187  	 	 	 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
 188  	 	 	 return $rs;
 189  	 	 }
 190  
 191  	 	 $nrows = (integer) $nrows;
 192  	 	 $offset = (integer) $offset;
 193  
 194  	 	 $cnt = ($nrows >= 0) ? $nrows : 999999999;
 195  	 	 if ($offset > 0 && $cnt) $cnt += $offset;
 196  
 197  	 	 $this->Execute("set rowcount $cnt");
 198  	 	 $rs = ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,0);
 199  	 	 $this->Execute("set rowcount 0");
 200  
 201  	 	 return $rs;
 202  	 }
 203  
 204  	 // returns true or false
 205  	function _close()
 206  	 {
 207  	 	 return @sybase_close($this->_connectionID);
 208  	 }
 209  
 210  	static function UnixDate($v)
 211  	 {
 212  	 	 return ADORecordSet_array_sybase::UnixDate($v);
 213  	 }
 214  
 215  	static function UnixTimeStamp($v)
 216  	 {
 217  	 	 return ADORecordSet_array_sybase::UnixTimeStamp($v);
 218  	 }
 219  
 220  
 221  
 222  	 # Added 2003-10-05 by Chris Phillipson
 223  	 # 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
 224  	 # to convert similar Microsoft SQL*Server (mssql) API into Sybase compatible version
 225  	 // Format date column in sql string given an input format that understands Y M D
 226  	function SQLDate($fmt, $col=false)
 227  	 {
 228  	 	 if (!$col) $col = $this->sysTimeStamp;
 229  	 	 $s = '';
 230  
 231  	 	 $len = strlen($fmt);
 232  	 	 for ($i=0; $i < $len; $i++) {
 233  	 	 	 if ($s) $s .= '+';
 234  	 	 	 $ch = $fmt[$i];
 235  	 	 	 switch($ch) {
 236  	 	 	 case 'Y':
 237  	 	 	 case 'y':
 238  	 	 	 	 $s .= "datename(yy,$col)";
 239  	 	 	 	 break;
 240  	 	 	 case 'M':
 241  	 	 	 	 $s .= "convert(char(3),$col,0)";
 242  	 	 	 	 break;
 243  	 	 	 case 'm':
 244  	 	 	 	 $s .= "str_replace(str(month($col),2),' ','0')";
 245  	 	 	 	 break;
 246  	 	 	 case 'Q':
 247  	 	 	 case 'q':
 248  	 	 	 	 $s .= "datename(qq,$col)";
 249  	 	 	 	 break;
 250  	 	 	 case 'D':
 251  	 	 	 case 'd':
 252  	 	 	 	 $s .= "str_replace(str(datepart(dd,$col),2),' ','0')";
 253  	 	 	 	 break;
 254  	 	 	 case 'h':
 255  	 	 	 	 $s .= "substring(convert(char(14),$col,0),13,2)";
 256  	 	 	 	 break;
 257  
 258  	 	 	 case 'H':
 259  	 	 	 	 $s .= "str_replace(str(datepart(hh,$col),2),' ','0')";
 260  	 	 	 	 break;
 261  
 262  	 	 	 case 'i':
 263  	 	 	 	 $s .= "str_replace(str(datepart(mi,$col),2),' ','0')";
 264  	 	 	 	 break;
 265  	 	 	 case 's':
 266  	 	 	 	 $s .= "str_replace(str(datepart(ss,$col),2),' ','0')";
 267  	 	 	 	 break;
 268  	 	 	 case 'a':
 269  	 	 	 case 'A':
 270  	 	 	 	 $s .= "substring(convert(char(19),$col,0),18,2)";
 271  	 	 	 	 break;
 272  
 273  	 	 	 default:
 274  	 	 	 	 if ($ch == '\\') {
 275  	 	 	 	 	 $i++;
 276  	 	 	 	 	 $ch = substr($fmt,$i,1);
 277  	 	 	 	 }
 278  	 	 	 	 $s .= $this->qstr($ch);
 279  	 	 	 	 break;
 280  	 	 	 }
 281  	 	 }
 282  	 	 return $s;
 283  	 }
 284  
 285  	 # Added 2003-10-07 by Chris Phillipson
 286  	 # 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
 287  	 # to convert similar Microsoft SQL*Server (mssql) API into Sybase compatible version
 288  	function MetaPrimaryKeys($table, $owner = false)
 289  	 {
 290  	 	 $sql = "SELECT c.column_name " .
 291  	 	 	    "FROM syscolumn c, systable t " .
 292  	 	 	    "WHERE t.table_name='$table' AND c.table_id=t.table_id " .
 293  	 	 	    "AND t.table_type='BASE' " .
 294  	 	 	    "AND c.pkey = 'Y' " .
 295  	 	 	    "ORDER BY c.column_id";
 296  
 297  	 	 $a = $this->GetCol($sql);
 298  	 	 if ($a && sizeof($a)>0) return $a;
 299  	 	 return false;
 300  	 }
 301  }
 302  
 303  /*--------------------------------------------------------------------------------------
 304  	  Class Name: Recordset
 305  --------------------------------------------------------------------------------------*/
 306  global $ADODB_sybase_mths;
 307  $ADODB_sybase_mths = array(
 308  	 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
 309  	 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
 310  
 311  class ADORecordset_sybase extends ADORecordSet {
 312  
 313  	 var $databaseType = "sybase";
 314  	 var $canSeek = true;
 315  	 // _mths works only in non-localised system
 316  	 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);
 317  
 318  	function __construct($id,$mode=false)
 319  	 {
 320  	 	 if ($mode === false) {
 321  	 	 	 global $ADODB_FETCH_MODE;
 322  	 	 	 $mode = $ADODB_FETCH_MODE;
 323  	 	 }
 324  	 	 if (!$mode) $this->fetchMode = ADODB_FETCH_ASSOC;
 325  	 	 else $this->fetchMode = $mode;
 326  	 	 parent::__construct($id);
 327  	 }
 328  
 329  	 /*	 Returns: an object containing field information.
 330  	 	 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
 331  	 	 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
 332  	 	 fetchField() is retrieved.	 */
 333  	function FetchField($fieldOffset = -1)
 334  	 {
 335  	 	 if ($fieldOffset != -1) {
 336  	 	 	 $o = @sybase_fetch_field($this->_queryID, $fieldOffset);
 337  	 	 }
 338  	 	 else if ($fieldOffset == -1) {	 /*	 The $fieldOffset argument is not provided thus its -1 	 */
 339  	 	 	 $o = @sybase_fetch_field($this->_queryID);
 340  	 	 }
 341  	 	 // older versions of PHP did not support type, only numeric
 342  	 	 if ($o && !isset($o->type)) $o->type = ($o->numeric) ? 'float' : 'varchar';
 343  	 	 return $o;
 344  	 }
 345  
 346  	function _initrs()
 347  	 {
 348  	 global $ADODB_COUNTRECS;
 349  	 	 $this->_numOfRows = ($ADODB_COUNTRECS)? @sybase_num_rows($this->_queryID):-1;
 350  	 	 $this->_numOfFields = @sybase_num_fields($this->_queryID);
 351  	 }
 352  
 353  	function _seek($row)
 354  	 {
 355  	 	 return @sybase_data_seek($this->_queryID, $row);
 356  	 }
 357  
 358  	function _fetch($ignore_fields=false)
 359  	 {
 360  	 	 if ($this->fetchMode == ADODB_FETCH_NUM) {
 361  	 	 	 $this->fields = @sybase_fetch_row($this->_queryID);
 362  	 	 } else if ($this->fetchMode == ADODB_FETCH_ASSOC) {
 363  	 	 	 $this->fields = @sybase_fetch_assoc($this->_queryID);
 364  
 365  	 	 	 if (is_array($this->fields)) {
 366  	 	 	 	 $this->fields = $this->GetRowAssoc();
 367  	 	 	 	 return true;
 368  	 	 	 }
 369  	 	 	 return false;
 370  	 	 }  else {
 371  	 	 	 $this->fields = @sybase_fetch_array($this->_queryID);
 372  	 	 }
 373  	 	 if ( is_array($this->fields)) {
 374  	 	 	 return true;
 375  	 	 }
 376  
 377  	 	 return false;
 378  	 }
 379  
 380  	 /*	 close() only needs to be called if you are worried about using too much memory while your script
 381  	 	 is running. All associated result memory for the specified result identifier will automatically be freed.	 */
 382  	function _close() {
 383  	 	 return @sybase_free_result($this->_queryID);
 384  	 }
 385  
 386  	 // sybase/mssql uses a default date like Dec 30 2000 12:00AM
 387  	static function UnixDate($v)
 388  	 {
 389  	 	 return ADORecordSet_array_sybase::UnixDate($v);
 390  	 }
 391  
 392  	static function UnixTimeStamp($v)
 393  	 {
 394  	 	 return ADORecordSet_array_sybase::UnixTimeStamp($v);
 395  	 }
 396  }
 397  
 398  class ADORecordSet_array_sybase extends ADORecordSet_array {
 399  
 400  	 // sybase/mssql uses a default date like Dec 30 2000 12:00AM
 401  	static function UnixDate($v)
 402  	 {
 403  	 global $ADODB_sybase_mths;
 404  
 405  	 	 //Dec 30 2000 12:00AM
 406  	 	 if (!preg_match( "/([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})/"
 407  	 	 	 ,$v, $rr)) return parent::UnixDate($v);
 408  
 409  	 	 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 410  
 411  	 	 $themth = substr(strtoupper($rr[1]),0,3);
 412  	 	 $themth = $ADODB_sybase_mths[$themth];
 413  	 	 if ($themth <= 0) return false;
 414  	 	 // h-m-s-MM-DD-YY
 415  	 	 return  adodb_mktime(0,0,0,$themth,$rr[2],$rr[3]);
 416  	 }
 417  
 418  	static function UnixTimeStamp($v)
 419  	 {
 420  	 global $ADODB_sybase_mths;
 421  	 	 //11.02.2001 Toni Tunkkari toni.tunkkari@finebyte.com
 422  	 	 //Changed [0-9] to [0-9 ] in day conversion
 423  	 	 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})/"
 424  	 	 	 ,$v, $rr)) return parent::UnixTimeStamp($v);
 425  	 	 if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
 426  
 427  	 	 $themth = substr(strtoupper($rr[1]),0,3);
 428  	 	 $themth = $ADODB_sybase_mths[$themth];
 429  	 	 if ($themth <= 0) return false;
 430  
 431  	 	 switch (strtoupper($rr[6])) {
 432  	 	 case 'P':
 433  	 	 	 if ($rr[4]<12) $rr[4] += 12;
 434  	 	 	 break;
 435  	 	 case 'A':
 436  	 	 	 if ($rr[4]==12) $rr[4] = 0;
 437  	 	 	 break;
 438  	 	 default:
 439  	 	 	 break;
 440  	 	 }
 441  	 	 // h-m-s-MM-DD-YY
 442  	 	 return  adodb_mktime($rr[4],$rr[5],0,$themth,$rr[2],$rr[3]);
 443  	 }
 444  }