library/adodb/drivers/adodb-sybase.inc.php

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