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

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