[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: MSSQLCallableStatement.php,v 1.20 2005/09/16 13:09:50 hlellelid Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  require_once 'creole/drivers/mssql/MSSQLPreparedStatement.php';
  23  require_once 'creole/CallableStatement.php';
  24  include_once 'creole/CreoleTypes.php';
  25  
  26  /**
  27   * MS SQL Server class to handle stored procedure execution.
  28   * 
  29   * Developer note: 
  30   *
  31   *    There is no CallableStatement superclass.  Unlike JDBC, Creole
  32   *    uses abstract parent classes rather than interfaces -- in order
  33   *    to minimize code duplication.  Since PHP doesn't support multiple
  34   *    inheritance, the DRIVERCallableStatement class cannot extend both
  35   *    the DRIVERPreparedStatement class and the would-be abstract
  36   *    CallableStatement class.
  37   *
  38   * @author Hans Lellelid <hans@xmpl.org>
  39   * @version $Revision: 1.20 $
  40   * @package creole.drivers.mssql
  41   */
  42  class MSSQLCallableStatement extends MSSQLPreparedStatement implements CallableStatement {
  43      
  44      /** Output variables */
  45      private $boundOutVars = array();
  46      
  47      /**
  48       * Match Creole types to SQL Server types
  49       * @var array
  50       */
  51      private static $typeMap = array(
  52          CreoleTypes::BOOLEAN => SQLBIT,
  53          CreoleTypes::BIGINT => SQLINT4,
  54          CreoleTypes::SMALLINT => SQLINT2,
  55          CreoleTypes::TINYINT => SQLINT2,
  56          CreoleTypes::INTEGER => SQLINT4,
  57          CreoleTypes::CHAR => SQLCHAR,
  58          CreoleTypes::VARCHAR => SQLVARCHAR,
  59          CreoleTypes::TEXT => SQLTEXT,
  60          CreoleTypes::FLOAT => SQLFLT8,
  61          CreoleTypes::DOUBLE => SQLFLT8,
  62          CreoleTypes::DATE => SQLVARCHAR,
  63          CreoleTypes::TIME => SQLVARCHAR,
  64          CreoleTypes::TIMESTAMP => SQLVARCHAR,
  65          CreoleTypes::VARBINARY => SQLVARCHAR,
  66          CreoleTypes::NUMERIC => SQLINT4,
  67          CreoleTypes::DECIMAL => SQLFLT8                                        
  68      );
  69      
  70      /**
  71       * Statement created by mssql_init()
  72       * @var resource
  73       */
  74      private $stmt;
  75  
  76  
  77      /**
  78       * The result resource.
  79       * @var resource
  80       */
  81      private $result;
  82      
  83      /**
  84       * Construct new MSSQLCallableStatement.
  85       * 
  86       * @param Connection $conn
  87       * @param resource $stmt
  88       */
  89      public function __construct(Connection $conn, $stmt)
  90      {
  91          print " - > IN CONSTRUCTOR \n";
  92          $this->conn = $conn;
  93          $this->stmt = $stmt;
  94      }   
  95      
  96      /**
  97       * @see CallableStatement::getResource()
  98       */
  99      public function getResource()
 100      {
 101          return $this->stmt;
 102      }
 103          
 104      /**
 105       * @see CallableStatement::close()
 106       */
 107      function close()
 108      {
 109          @mssql_free_statement($this->stmt);
 110          $this->rsFetchCount = 0;
 111      }
 112      
 113      /**
 114       * @see CallableStatement::executeQuery()
 115       */
 116      function executeQuery($p1 = null, $fetchmode = null)
 117      {
 118          $params = null;
 119          if ($fetchmode !== null) {
 120              $params = $p1;
 121          } elseif ($p1 !== null) {
 122              if (is_array($p1)) $params = $p1;
 123              else $fetchmode = $p1;
 124          }
 125          
 126          if ($params) {
 127              for($i=0,$cnt=count($params); $i < $cnt; $i++) {
 128                  $this->set($i+1, $params[$i]);
 129              }
 130          }                
 131          
 132          $this->result = mssql_execute($this->stmt);
 133          if (!$this->result) {
 134              throw new SQLException('unable to execute callable statement', mssql_get_last_message());
 135          }
 136          
 137          return new MSSQLResultSet($this->conn, $this->result, $fetchmode, $this->offset, $this->limit);
 138      }
 139      
 140      /**
 141       * @see CallableStatement::getMoreResults()
 142       */
 143      function getMoreResults()
 144      {
 145          $this->rsFetchCount++; // we track this because 
 146          $hasMore = mssql_next_result($this->result);
 147          if ($this->resultSet) $this->resultSet->close();                    
 148          if ($hasMore) {
 149              $clazz = $this->resultClass;
 150              $this->resultSet = new $clazz($this, $this->result);
 151          } else {
 152              $this->resultSet = null;
 153          }
 154          return $hasMore;
 155      }
 156  
 157      /**
 158       * @see CallableStatement::registerOutParameter()
 159       */
 160      function registerOutParameter($paramIndex, $sqlType, $maxLength = null)
 161      {
 162          mssql_bind($this->stmt, $paramIndex, $this->boundOutVars[$paramIndex], self::$typeMap[$sqlType], true, false, $maxLength);
 163      }
 164      
 165      /**
 166       * @see CallableStatement::setArray()
 167       */
 168      function setArray($paramIndex, $value, $out = false) 
 169      {
 170          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 171          if ($value === null) {
 172              $this->setNull($paramIndex);
 173          } else {
 174              $value = serialize($value);
 175              mssql_bind($this->stmt, $paramIndex, $value, SQLTEXT, $out);
 176          }
 177      }
 178  
 179      /**
 180       * @see CallableStatement::setBoolean()
 181       */
 182      function setBoolean($paramIndex, $value, $out = false) 
 183      {
 184          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected        
 185          if ($value === null) {
 186              $this->setNull($paramIndex);
 187          } else {
 188              $value = ($value) ? 1 : 0;
 189              mssql_bind($this->stmt, $paramIndex, $value, SQLBIT, $out);
 190          }
 191      }
 192      
 193  
 194      /**
 195       * @see CallableStatement::setBlob()
 196       */
 197      function setBlob($paramIndex, $blob, $out = false) 
 198      {
 199          if ($blob === null) {
 200              $this->setNull($paramIndex);
 201          } else {
 202              if (is_object($blob)) {
 203                  $blob = $blob->__toString();
 204              }
 205              if ($out) $this->boundOutVars[$paramIndex] = &$blob; // reference means that changes to value, will be reflected        
 206              $data = unpack("H*hex", $blob);
 207              mssql_bind($this->stmt, $paramIndex, $data, SQLTEXT, $out);
 208          }
 209      } 
 210      
 211      /**
 212       * @see CallableStatement::setClob()
 213       */
 214      function setClob($paramIndex, $clob, $out = false) 
 215      {
 216          if ($clob === null) {
 217              $this->setNull($paramIndex);
 218          } else {
 219              if (is_object($clob)) {
 220                  $clob = $clob->__toString();
 221              }
 222              if ($out) $this->boundOutVars[$paramIndex] = &$clob; // reference means that changes to value, will be reflected
 223              mssql_bind($this->stmt, $paramIndex, $clob, SQLTEXT, $out);
 224          }
 225      }
 226  
 227      /**
 228       * @see CallableStatement::setDate()
 229       */
 230      function setDate($paramIndex, $value, $out = false) 
 231      {
 232          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 233          if ($value === null) {
 234              $this->setNull($paramIndex);
 235          } else {
 236              if (is_numeric($value)) $value = date("Y-m-d", $value);
 237              mssql_bind($this->stmt, $paramIndex, $value, SQLVARCHAR, $out);
 238          }
 239      } 
 240          
 241      /**
 242       * @see CallableStatement::setFloat()
 243       */
 244      function setFloat($paramIndex, $value, $out = false) 
 245      {
 246          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 247          if ($value === null) {
 248              $this->setNull($paramIndex);
 249          } else {
 250              $value = (float) $value;
 251              mssql_bind($this->stmt, $paramIndex, $value, SQLFLT8, $out);
 252          }
 253      }
 254      
 255      /**
 256       * @see CallableStatement::setInt()
 257       */
 258      function setInt($paramIndex, $value, $out = false) 
 259      {
 260          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 261          if ($value === null) {
 262              $this->setNull($paramIndex);
 263          } else {
 264              $value = (int) $value;
 265              mssql_bind($this->stmt, $paramIndex, $value, SQLINT4, $out);
 266          }
 267      }    
 268      
 269      /**
 270       * @see CallableStatement::setNull()
 271       */
 272      function setNull($paramIndex) 
 273      {
 274          // hopefully type isn't essential here :)
 275          $value = null; // wants a var to pass by reference
 276          mssql_bind($this->stmt, $paramIndex, $value, $type=null, $out=false, $is_null=true);
 277      }
 278  
 279      /**
 280       * @see CallableStatement::setString()
 281       */
 282      function setString($paramIndex, $value, $out = false) 
 283      {    
 284          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 285          if ($value === null) {
 286              $this->setNull($paramIndex);
 287          } else {
 288              $value = (string) $value;            
 289              mssql_bind($this->stmt, $paramIndex, $value, SQLVARCHAR, $out);
 290          }
 291      } 
 292      
 293      /**
 294       * @see CallableStatement::setTime()
 295       */
 296      function setTime($paramIndex, $value, $out = false) 
 297      {    
 298          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 299          if ($value === null) {
 300              $this->setNull($paramIndex);
 301          } else {
 302              if (is_numeric($value)) $value = date("H:i:s", $value);
 303              mssql_bind($this->stmt, $paramIndex, $value, SQLVARCHAR, $out);
 304          }
 305      }
 306      
 307      /**
 308       * @see CallableStatement::setTimestamp()
 309       */
 310      function setTimestamp($paramIndex, $value, $out = false) 
 311      {
 312          if ($out) $this->boundOutVars[$paramIndex] = &$value; // reference means that changes to value, will be reflected
 313          if ($value === null) {
 314              $this->setNull($paramIndex);
 315          } else {
 316              if (is_numeric($value)) $value = date('Y-m-d H:i:s', $value);
 317              mssql_bind($this->stmt, $paramIndex, $value, SQLVARCHAR, $out);
 318          }
 319      }            
 320          
 321      /**
 322       * @see CallableStatement::getArray()
 323       */
 324      function getArray($paramIndex) 
 325      {
 326          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 327              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 328          }
 329          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 330          return (array) unserialize($this->boundOutVars[$paramIndex]);
 331      } 
 332  
 333      /**
 334       * @see CallableStatement::getBoolean()
 335       */
 336      function getBoolean($paramIndex) 
 337      {
 338          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 339              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 340          }
 341          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 342          return (boolean) $this->boundOutVars[$paramIndex];
 343      }
 344              
 345      /**
 346       * @see CallableStatement::getBlob()
 347       */
 348      function getBlob($paramIndex) 
 349      {
 350          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 351              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 352          }
 353          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 354          require_once 'creole/util/Blob.php';
 355          $b = new Blob();
 356          $b->setContents($this->boundOutVars[$paramIndex]);
 357          return $b;
 358      }     
 359  
 360      /**
 361       * @see CallableStatement::getClob()
 362       */
 363      function getClob($paramIndex) 
 364      {
 365          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 366              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 367          }
 368          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 369          require_once 'creole/util/Clob.php';
 370          $c = new Clob();
 371          $c->setContents($this->boundOutVars[$paramIndex]);
 372          return $c;
 373      } 
 374      
 375      /**
 376       * @see CallableStatement::getDate()
 377       */
 378      function getDate($paramIndex, $fmt = '%Y-%m-%d') 
 379      {
 380          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 381              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 382          }
 383          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 384          
 385          $ts = strtotime($this->boundOutVars[$paramIndex]);        
 386          if ($ts === -1 || $ts === false) { // in PHP 5.1 return value changes to FALSE
 387              throw new SQLException("Unable to convert value at column " . $paramIndex . " to timestamp: " . $this->boundOutVars[$paramIndex]);
 388          }        
 389          if (strpos($format, '%') !== false) {
 390              return strftime($format, $ts);
 391          } else {
 392              return date($format, $ts);
 393          }
 394          
 395          return $this->boundOutVars[$paramIndex];
 396      } 
 397  
 398      /**
 399       * @param mixed $paramIndex Column name (string) or index (int).
 400       * @return float
 401       */
 402      function getFloat($paramIndex) 
 403      {
 404          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 405              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 406          }
 407          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 408          return (float) $this->boundOutVars[$paramIndex];
 409      }
 410  
 411      /**
 412       * @see CallableStatement::getInt()
 413       */
 414      function getInt($paramIndex) 
 415      {
 416          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 417              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 418          }
 419          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 420          return (int) $this->boundOutVars[$paramIndex];
 421      }            
 422  
 423      /**
 424       * @see CallableStatement::getString()
 425       */
 426      function getString($paramIndex) 
 427      {
 428          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 429              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 430          }
 431          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 432          return (string) $this->boundOutVars[$paramIndex];
 433      } 
 434  
 435      /**
 436       * @see CallableStatement::getTime()
 437       */
 438      function getTime($paramIndex, $format='%X') 
 439      {
 440          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 441              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 442          }
 443          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 444          
 445          $ts = strtotime($this->boundOutVars[$paramIndex]);        
 446          if ($ts === -1  || $ts === false) { // in PHP 5.1 return value changes to FALSE
 447              throw new SQLException("Unable to convert value at column " . $paramIndex . " to timestamp: " . $this->boundOutVars[$paramIndex]);
 448          }        
 449          if (strpos($format, '%') !== false) {
 450              return strftime($format, $ts);
 451          } else {
 452              return date($format, $ts);
 453          }
 454              
 455      }
 456  
 457      /**
 458       * @see CallableStatement::getTimestamp()
 459       */
 460      function getTimestamp($paramIndex, $format = 'Y-m-d H:i:s') 
 461      {
 462          if (!array_key_exists($paramIndex, $this->boundOutVars)) {
 463              throw new SQLException('Requesting variable not bound to output var: '.$paramIndex);
 464          }
 465          if ($this->boundOutVars[$paramIndex] === null) { return null; }
 466                  
 467          $ts = strtotime($this->boundOutVars[$paramIndex]);        
 468          if ($ts === -1 || $ts === false) { // in PHP 5.1 return value changes to FALSE
 469              throw new SQLException("Unable to convert value at column " . $paramIndex . " to timestamp: " . $this->boundOutVars[$paramIndex]);
 470          }        
 471          if (strpos($format, '%') !== false) {
 472              return strftime($format, $ts);
 473          } else {
 474              return date($format, $ts);
 475          }
 476      }    
 477  
 478  }