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

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