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

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