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

   1 <?php
   2 /*
   3 @version   v5.20.1  06-Dec-2015
   4 @copyright (c) 2000-2013 John Lim. All rights reserved.
   5 @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   6   Released under both BSD license and Lesser GPL library license.
   7   Whenever there is any discrepancy between the two licenses,
   8   the BSD license will take precedence.
   9   Set tabs to 4 for best viewing.
  10
  11   Latest version is available at http://adodb.sourceforge.net
  12
  13   Informix port by Mitchell T. Young (mitch@youngfamily.org)
  14
  15   Further mods by "Samuel CARRIERE" <samuel_carriere@hotmail.com>
  16
  17 */
  18
  19 // security - hide paths
  20 if (!defined('ADODB_DIR')) die();
  21
  22 if (!defined('IFX_SCROLL')) define('IFX_SCROLL',1);
  23
  24 class ADODB_informix72 extends ADOConnection {
  25         var $databaseType = "informix72";
  26         var $dataProvider = "informix";
  27         var $replaceQuote = "''"; // string to use to replace quotes
  28         var $fmtDate = "'Y-m-d'";
  29         var $fmtTimeStamp = "'Y-m-d H:i:s'";
  30         var $hasInsertID = true;
  31         var $hasAffectedRows = true;
  32     var $substr = 'substr';
  33         var $metaTablesSQL="select tabname,tabtype from systables where tabtype in ('T','V') and owner!='informix'"; //Don't get informix tables and pseudo-tables
  34
  35
  36         var $metaColumnsSQL =
  37                 "select c.colname, c.coltype, c.collength, d.default,c.colno
  38                 from syscolumns c, systables t,outer sysdefaults d
  39                 where c.tabid=t.tabid and d.tabid=t.tabid and d.colno=c.colno
  40                 and tabname='%s' order by c.colno";
  41
  42         var $metaPrimaryKeySQL =
  43                 "select part1,part2,part3,part4,part5,part6,part7,part8 from
  44                 systables t,sysconstraints s,sysindexes i where t.tabname='%s'
  45                 and s.tabid=t.tabid and s.constrtype='P'
  46                 and i.idxname=s.idxname";
  47
  48         var $concat_operator = '||';
  49
  50         var $lastQuery = false;
  51         var $has_insertid = true;
  52
  53         var $_autocommit = true;
  54         var $_bindInputArray = true;  // set to true if ADOConnection.Execute() permits binding of array parameters.
  55         var $sysDate = 'TODAY';
  56         var $sysTimeStamp = 'CURRENT';
  57         var $cursorType = IFX_SCROLL; // IFX_SCROLL or IFX_HOLD or 0
  58
  59         function __construct()
  60         {
  61                 // alternatively, use older method:
  62                 //putenv("DBDATE=Y4MD-");
  63
  64                 // force ISO date format
  65                 putenv('GL_DATE=%Y-%m-%d');
  66
  67                 if (function_exists('ifx_byteasvarchar')) {
  68                         ifx_byteasvarchar(1); // Mode "0" will return a blob id, and mode "1" will return a varchar with text content.
  69                 ifx_textasvarchar(1); // Mode "0" will return a blob id, and mode "1" will return a varchar with text content.
  70                 ifx_blobinfile_mode(0); // Mode "0" means save Byte-Blobs in memory, and mode "1" means save Byte-Blobs in a file.
  71                 }
  72         }
  73
  74         function ServerInfo()
  75         {
  76             if (isset($this->version)) return $this->version;
  77
  78             $arr['description'] = $this->GetOne("select DBINFO('version','full') from systables where tabid = 1");
  79             $arr['version'] = $this->GetOne("select DBINFO('version','major') || DBINFO('version','minor') from systables where tabid = 1");
  80             $this->version = $arr;
  81             return $arr;
  82         }
  83
  84
  85
  86         function _insertid()
  87         {
  88                 $sqlca =ifx_getsqlca($this->lastQuery);
  89                 return @$sqlca["sqlerrd1"];
  90         }
  91
  92         function _affectedrows()
  93         {
  94                 if ($this->lastQuery) {
  95                    return @ifx_affected_rows ($this->lastQuery);
  96                 }
  97                 return 0;
  98         }
  99
 100         function BeginTrans()
 101         {
 102                 if ($this->transOff) return true;
 103                 $this->transCnt += 1;
 104                 $this->Execute('BEGIN');
 105                 $this->_autocommit = false;
 106                 return true;
 107         }
 108
 109         function CommitTrans($ok=true)
 110         {
 111                 if (!$ok) return $this->RollbackTrans();
 112                 if ($this->transOff) return true;
 113                 if ($this->transCnt) $this->transCnt -= 1;
 114                 $this->Execute('COMMIT');
 115                 $this->_autocommit = true;
 116                 return true;
 117         }
 118
 119         function RollbackTrans()
 120         {
 121                 if ($this->transOff) return true;
 122                 if ($this->transCnt) $this->transCnt -= 1;
 123                 $this->Execute('ROLLBACK');
 124                 $this->_autocommit = true;
 125                 return true;
 126         }
 127
 128         function RowLock($tables,$where,$col='1 as adodbignore')
 129         {
 130                 if ($this->_autocommit) $this->BeginTrans();
 131                 return $this->GetOne("select $col from $tables where $where for update");
 132         }
 133
 134         /*      Returns: the last error message from previous database operation
 135                 Note: This function is NOT available for Microsoft SQL Server.  */
 136
 137         function ErrorMsg()
 138         {
 139                 if (!empty($this->_logsql)) return $this->_errorMsg;
 140                 $this->_errorMsg = ifx_errormsg();
 141                 return $this->_errorMsg;
 142         }
 143
 144         function ErrorNo()
 145         {
 146                 preg_match("/.*SQLCODE=([^\]]*)/",ifx_error(),$parse);
 147                 if (is_array($parse) && isset($parse[1])) return (int)$parse[1];
 148                 return 0;
 149         }
 150
 151
 152         function MetaProcedures($NamePattern = false, $catalog  = null, $schemaPattern  = null)
 153     {
 154         // save old fetch mode
 155         global $ADODB_FETCH_MODE;
 156
 157         $false = false;
 158         $save = $ADODB_FETCH_MODE;
 159         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 160         if ($this->fetchMode !== FALSE) {
 161                $savem = $this->SetFetchMode(FALSE);
 162
 163         }
 164         $procedures = array ();
 165
 166         // get index details
 167
 168         $likepattern = '';
 169         if ($NamePattern) {
 170            $likepattern = " WHERE procname LIKE '".$NamePattern."'";
 171         }
 172
 173         $rs = $this->Execute('SELECT procname, isproc FROM sysprocedures'.$likepattern);
 174
 175         if (is_object($rs)) {
 176             // parse index data into array
 177
 178             while ($row = $rs->FetchRow()) {
 179                 $procedures[$row[0]] = array(
 180                         'type' => ($row[1] == 'f' ? 'FUNCTION' : 'PROCEDURE'),
 181                         'catalog' => '',
 182                         'schema' => '',
 183                         'remarks' => ''
 184                     );
 185             }
 186             }
 187
 188         // restore fetchmode
 189         if (isset($savem)) {
 190                 $this->SetFetchMode($savem);
 191         }
 192         $ADODB_FETCH_MODE = $save;
 193
 194         return $procedures;
 195     }
 196
 197     function MetaColumns($table, $normalize=true)
 198         {
 199         global $ADODB_FETCH_MODE;
 200
 201                 $false = false;
 202                 if (!empty($this->metaColumnsSQL)) {
 203                         $save = $ADODB_FETCH_MODE;
 204                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 205                         if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 206                         $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
 207                         if (isset($savem)) $this->SetFetchMode($savem);
 208                         $ADODB_FETCH_MODE = $save;
 209                         if ($rs === false) return $false;
 210                         $rspkey = $this->Execute(sprintf($this->metaPrimaryKeySQL,$table)); //Added to get primary key colno items
 211
 212                         $retarr = array();
 213                         while (!$rs->EOF) { //print_r($rs->fields);
 214                                 $fld = new ADOFieldObject();
 215                                 $fld->name = $rs->fields[0];
 216 /*  //!eos.
 217                                                 $rs->fields[1] is not the correct adodb type
 218                                                 $rs->fields[2] is not correct max_length, because can include not-null bit
 219
 220                                 $fld->type = $rs->fields[1];
 221                                 $fld->primary_key=$rspkey->fields && array_search($rs->fields[4],$rspkey->fields); //Added to set primary key flag
 222                                 $fld->max_length = $rs->fields[2];*/
 223                                 $pr=ifx_props($rs->fields[1],$rs->fields[2]); //!eos
 224                                 $fld->type = $pr[0] ;//!eos
 225                                 $fld->primary_key=$rspkey->fields && array_search($rs->fields[4],$rspkey->fields);
 226                                 $fld->max_length = $pr[1]; //!eos
 227                                 $fld->precision = $pr[2] ;//!eos
 228                                 $fld->not_null = $pr[3]=="N"; //!eos
 229
 230                                 if (trim($rs->fields[3]) != "AAAAAA 0") {
 231                                         $fld->has_default = 1;
 232                                         $fld->default_value = $rs->fields[3];
 233                                 } else {
 234                                         $fld->has_default = 0;
 235                                 }
 236
 237                 $retarr[strtolower($fld->name)] = $fld;
 238                                 $rs->MoveNext();
 239                         }
 240
 241                         $rs->Close();
 242                         $rspkey->Close(); //!eos
 243                         return $retarr;
 244                 }
 245
 246                 return $false;
 247         }
 248
 249    function xMetaColumns($table)
 250    {
 251                 return ADOConnection::MetaColumns($table,false);
 252    }
 253
 254          function MetaForeignKeys($table, $owner=false, $upper=false) //!Eos
 255         {
 256                 $sql = "
 257                         select tr.tabname,updrule,delrule,
 258                         i.part1 o1,i2.part1 d1,i.part2 o2,i2.part2 d2,i.part3 o3,i2.part3 d3,i.part4 o4,i2.part4 d4,
 259                         i.part5 o5,i2.part5 d5,i.part6 o6,i2.part6 d6,i.part7 o7,i2.part7 d7,i.part8 o8,i2.part8 d8
 260                         from systables t,sysconstraints s,sysindexes i,
 261                         sysreferences r,systables tr,sysconstraints s2,sysindexes i2
 262                         where t.tabname='$table'
 263                         and s.tabid=t.tabid and s.constrtype='R' and r.constrid=s.constrid
 264                         and i.idxname=s.idxname and tr.tabid=r.ptabid
 265                         and s2.constrid=r.primary and i2.idxname=s2.idxname";
 266
 267                 $rs = $this->Execute($sql);
 268                 if (!$rs || $rs->EOF)  return false;
 269                 $arr = $rs->GetArray();
 270                 $a = array();
 271                 foreach($arr as $v) {
 272                         $coldest=$this->metaColumnNames($v["tabname"]);
 273                         $colorig=$this->metaColumnNames($table);
 274                         $colnames=array();
 275                         for($i=1;$i<=8 && $v["o$i"] ;$i++) {
 276                                 $colnames[]=$coldest[$v["d$i"]-1]."=".$colorig[$v["o$i"]-1];
 277                         }
 278                         if($upper)
 279                                 $a[strtoupper($v["tabname"])] =  $colnames;
 280                         else
 281                                 $a[$v["tabname"]] =  $colnames;
 282                 }
 283                 return $a;
 284          }
 285
 286    function UpdateBlob($table, $column, $val, $where, $blobtype = 'BLOB')
 287    {
 288                 $type = ($blobtype == 'TEXT') ? 1 : 0;
 289                 $blobid = ifx_create_blob($type,0,$val);
 290                 return $this->Execute("UPDATE $table SET $column=(?) WHERE $where",array($blobid));
 291    }
 292
 293    function BlobDecode($blobid)
 294    {
 295                 return function_exists('ifx_byteasvarchar') ? $blobid : @ifx_get_blob($blobid);
 296    }
 297
 298         // returns true or false
 299    function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 300         {
 301                 if (!function_exists('ifx_connect')) return null;
 302
 303                 $dbs = $argDatabasename . "@" . $argHostname;
 304                 if ($argHostname) putenv("INFORMIXSERVER=$argHostname");
 305                 putenv("INFORMIXSERVER=".trim($argHostname));
 306                 $this->_connectionID = ifx_connect($dbs,$argUsername,$argPassword);
 307                 if ($this->_connectionID === false) return false;
 308                 #if ($argDatabasename) return $this->SelectDB($argDatabasename);
 309                 return true;
 310         }
 311
 312         // returns true or false
 313    function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 314         {
 315                 if (!function_exists('ifx_connect')) return null;
 316
 317                 $dbs = $argDatabasename . "@" . $argHostname;
 318                 putenv("INFORMIXSERVER=".trim($argHostname));
 319                 $this->_connectionID = ifx_pconnect($dbs,$argUsername,$argPassword);
 320                 if ($this->_connectionID === false) return false;
 321                 #if ($argDatabasename) return $this->SelectDB($argDatabasename);
 322                 return true;
 323         }
 324 /*
 325         // ifx_do does not accept bind parameters - weird ???
 326         function Prepare($sql)
 327         {
 328                 $stmt = ifx_prepare($sql);
 329                 if (!$stmt) return $sql;
 330                 else return array($sql,$stmt);
 331         }
 332 */
 333         // returns query ID if successful, otherwise false
 334         function _query($sql,$inputarr=false)
 335         {
 336         global $ADODB_COUNTRECS;
 337
 338           // String parameters have to be converted using ifx_create_char
 339           if ($inputarr) {
 340                  foreach($inputarr as $v) {
 341                         if (gettype($v) == 'string') {
 342                            $tab[] = ifx_create_char($v);
 343                         }
 344                         else {
 345                            $tab[] = $v;
 346                         }
 347                  }
 348           }
 349
 350           // In case of select statement, we use a scroll cursor in order
 351           // to be able to call "move", or "movefirst" statements
 352           if (!$ADODB_COUNTRECS && preg_match("/^\s*select/is", $sql)) {
 353                  if ($inputarr) {
 354                         $this->lastQuery = ifx_query($sql,$this->_connectionID, $this->cursorType, $tab);
 355                  }
 356                  else {
 357                         $this->lastQuery = ifx_query($sql,$this->_connectionID, $this->cursorType);
 358                  }
 359           }
 360           else {
 361                  if ($inputarr) {
 362                         $this->lastQuery = ifx_query($sql,$this->_connectionID, $tab);
 363                  }
 364                  else {
 365                         $this->lastQuery = ifx_query($sql,$this->_connectionID);
 366                  }
 367           }
 368
 369           // Following line have been commented because autocommit mode is
 370           // not supported by informix SE 7.2
 371
 372           //if ($this->_autocommit) ifx_query('COMMIT',$this->_connectionID);
 373
 374                 return $this->lastQuery;
 375         }
 376
 377         // returns true or false
 378         function _close()
 379         {
 380                 $this->lastQuery = false;
 381                 return ifx_close($this->_connectionID);
 382         }
 383 }
 384
 385
 386 /*--------------------------------------------------------------------------------------
 387          Class Name: Recordset
 388 --------------------------------------------------------------------------------------*/
 389
 390 class ADORecordset_informix72 extends ADORecordSet {
 391
 392         var $databaseType = "informix72";
 393         var $canSeek = true;
 394         var $_fieldprops = false;
 395
 396         function __construct($id,$mode=false)
 397         {
 398                 if ($mode === false) {
 399                         global $ADODB_FETCH_MODE;
 400                         $mode = $ADODB_FETCH_MODE;
 401                 }
 402                 $this->fetchMode = $mode;
 403                 return parent::__construct($id);
 404         }
 405
 406
 407
 408         /*      Returns: an object containing field information.
 409                 Get column information in the Recordset object. fetchField() can be used in order to obtain information about
 410                 fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
 411                 fetchField() is retrieved.      */
 412         function FetchField($fieldOffset = -1)
 413         {
 414                 if (empty($this->_fieldprops)) {
 415                         $fp = ifx_fieldproperties($this->_queryID);
 416                         foreach($fp as $k => $v) {
 417                                 $o = new ADOFieldObject;
 418                                 $o->name = $k;
 419                                 $arr = explode(';',$v); //"SQLTYPE;length;precision;scale;ISNULLABLE"
 420                                 $o->type = $arr[0];
 421                                 $o->max_length = $arr[1];
 422                                 $this->_fieldprops[] = $o;
 423                                 $o->not_null = $arr[4]=="N";
 424                         }
 425                 }
 426                 $ret = $this->_fieldprops[$fieldOffset];
 427                 return $ret;
 428         }
 429
 430         function _initrs()
 431         {
 432                 $this->_numOfRows = -1; // ifx_affected_rows not reliable, only returns estimate -- ($ADODB_COUNTRECS)? ifx_affected_rows($this->_queryID):-1;
 433                 $this->_numOfFields = ifx_num_fields($this->_queryID);
 434         }
 435
 436         function _seek($row)
 437         {
 438                 return @ifx_fetch_row($this->_queryID, (int) $row);
 439         }
 440
 441    function MoveLast()
 442    {
 443           $this->fields = @ifx_fetch_row($this->_queryID, "LAST");
 444           if ($this->fields) $this->EOF = false;
 445           $this->_currentRow = -1;
 446
 447           if ($this->fetchMode == ADODB_FETCH_NUM) {
 448                  foreach($this->fields as $v) {
 449                         $arr[] = $v;
 450                  }
 451                  $this->fields = $arr;
 452           }
 453
 454           return true;
 455    }
 456
 457    function MoveFirst()
 458         {
 459           $this->fields = @ifx_fetch_row($this->_queryID, "FIRST");
 460           if ($this->fields) $this->EOF = false;
 461           $this->_currentRow = 0;
 462
 463           if ($this->fetchMode == ADODB_FETCH_NUM) {
 464                  foreach($this->fields as $v) {
 465                         $arr[] = $v;
 466                  }
 467                  $this->fields = $arr;
 468           }
 469
 470           return true;
 471    }
 472
 473    function _fetch($ignore_fields=false)
 474    {
 475
 476                 $this->fields = @ifx_fetch_row($this->_queryID);
 477
 478                 if (!is_array($this->fields)) return false;
 479
 480                 if ($this->fetchMode == ADODB_FETCH_NUM) {
 481                         foreach($this->fields as $v) {
 482                                 $arr[] = $v;
 483                         }
 484                         $this->fields = $arr;
 485                 }
 486                 return true;
 487         }
 488
 489         /*      close() only needs to be called if you are worried about using too much memory while your script
 490                 is running. All associated result memory for the specified result identifier will automatically be freed.       */
 491         function _close()
 492         {
 493                 return ifx_free_result($this->_queryID);
 494         }
 495
 496 }
 497 /** !Eos
 498 * Auxiliar function to Parse coltype,collength. Used by Metacolumns
 499 * return: array ($mtype,$length,$precision,$nullable) (similar to ifx_fieldpropierties)
 500 */
 501 function ifx_props($coltype,$collength){
 502         $itype=fmod($coltype+1,256);
 503         $nullable=floor(($coltype+1) /256) ?"N":"Y";
 504         $mtype=substr(" CIIFFNNDN TBXCC     ",$itype,1);
 505         switch ($itype){
 506                 case 2:
 507                         $length=4;
 508                 case 6:
 509                 case 9:
 510                 case 14:
 511                         $length=floor($collength/256);
 512                         $precision=fmod($collength,256);
 513                         break;
 514                 default:
 515                         $precision=0;
 516                         $length=$collength;
 517         }
 518         return array($mtype,$length,$precision,$nullable);
 519 }