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

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