KjwMsSql.php

<?php // vim:ts=4:sw=4:noet
/*====================================================================== 
Copyright (C) 2006-2009, Walter Doekes
This file is part of KjwLib-php45.
 
KjwLib-php45 is free software: you can redistribute it and/or modify 
it under the terms of the GNU General Public License as published by 
the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version. 

KjwLib-php45 is distributed in the hope that it will be useful, 
but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
GNU General Public License for more details. 

You should have received a copy of the GNU General Public License 
along with KjwLib-php45.  If not, see <http://www.gnu.org/licenses/>. 
======================================================================*/

if (!function_exists('sybase_connect'))
        trigger_error('sybase_* functions not defined. Install php*-sybase.', E_USER_ERROR);
require_once(dirname(__FILE__) . '/KjwSql.php');
require_once(dirname(__FILE__) . '/KjwArrayResultSet.php');
require_once(dirname(__FILE__) . '/KjwMsResultSet.php');

class KjwMsSql extends KjwSql {
        var $_dbconn;           
        var $_quotedDbname;     
        var $_insertId;         
        var $_affectedRows;     
        var $_messages;         
        function KjwMsSql($hostname, $portnum, $username, $password, $database) {
                parent::KjwSql('mssql', $hostname, $portnum ? $portnum : 1433, $username, $password, $database);
                $this->_dbconn = null; // null = unconnected, false = failed connect
                $this->_quotedDbname = $this->nameQuote($database);
                $this->_insertId = $this->_affectedRows = null;
                $this->_messages = array();
                sybase_min_client_severity(0);
                sybase_min_server_severity(0);
        }

        /* [INHERITED DOCS] */
        function destroy() {
                $this->disconnect();
                parent::destroy();
        }
        
        /* [INHERITED DOCS] */
        function connect() {
                if ($this->_dbconn)
                        return true;
                $this->_dbconn = false; // If we fail, we'll be false instead of null.
                // Freetds and/or the MsSql server refuse connections sometimes..
                // We'll attempt to circumvent this by reconnecting after a small wait.
                // Note that we have the connect timeout set to 10 seconds in the freetds.conf.
                // (If it were longer, the N retries could become a very long wait.)
                for ($i = 1; ; ++$i) {
                        if (($res = @sybase_connect($this->_hostname . ':' . $this->_portnum, $this->_username, $this->_password, 'KjwApp')) != false)
                                break;
                        if ($i == 5 && $res == false)  {
                                global $kjw_suppressed_error;
                                $this->setError("Connection to database (temporarily) failed after {$i}th try; host={$this->_hostname}:{$this->_portnum}, "
                                                . "user={$this->_username}, db={$this->_database}. "
                                                . $kjw_suppressed_error);
                                if (KJW_DIE_ON_TRANSIENT_ERROR)
                                        $this->croak('Database Connection Failure', $this->getError(), 2);
                                return false;
                        }
                        usleep(max(500000 << $i, 8000000));
                }
                // Woot, success!
                if (@sybase_set_message_handler(array(&$this, '_messageHandler'), $res) == false) {
                        global $kjw_suppressed_error;
                        $this->setError("sybase_set_message_handler failed: $kjw_suppressed_error ({$this->_hostname}:{$this->_portnum})");
                        if (KJW_DIE_ON_TRANSIENT_ERROR)
                                $this->croak('Database Connection Failure', $this->getError(), 2);
                        return false;
                }
                if (@sybase_select_db($this->nameQuote($this->_database), $res) == false) {
                        $this->setError($this->getLastMessage());
                        if (KJW_DIE_ON_TRANSIENT_ERROR)
                                $this->croak('Database Connection Failure', $this->getError(), 2);
                        @sybase_close($res);
                        return false;
                }
                $this->_dbconn = $res;
                return true;
        }

        /* [INHERITED DOCS] */
        function disconnect() {
                if (!$this->_dbconn)
                        return true;
                if ($ret = @sybase_close($this->_dbconn))
                        $this->_dbconn = null;
                return $ret;
        }
        
        /* [INHERITED DOCS] */
        function beginWork() {
                // BEGIN WORK does not work on MsSql.
                return $this->execute('BEGIN TRANSACTION');
        }

        /* [INHERITED DOCS] */
        function execute($query) {
                // Part 1, initialize
                if (!$this->_execute_1($query))
                        return false;
                // Part 2, preprocess
                list($query, $is_insert_update, $is_select) = $this->_execute_2($query);
                // Part 3, execute
                $sybase_result = $this->_execute_3($query);
                // Part 4, postprocess
                return $this->_execute_4($sybase_result, $is_insert_update, $is_select);
        }

        function executeLowLevel($query) {
                // You can definately not trust these values.
                $this->_insertId = $this->_affectedRows = null;
                // Part 1, initialize
                if (!$this->_execute_1($query))
                        return false;
                // Part 3, execute
                $sybase_result = $this->_execute_3($query);
                if (is_bool($sybase_result))
                        return $sybase_result;
                sybase_free_result($sybase_result);
                return true;
        }

        function _execute_1($query) {
                $this->_setLastQuery($query);
                if ($this->_dbconn === null)
                        $this->connect(); // Auto-connect once.
                if ($this->_dbconn === false)
                        return false;
                return true;
        }

        function _execute_2($query) {
                // If query is an insert/update, we add code to select @@IDENTITY/@@ROWCOUNT.
                if (preg_match('!^\s*(insert|update)\s+!i', $query)) {
                        $this->_insertId = $this->_affectedRows = null;
                        $is_insert_update = true;
                        $is_select = false;
                        // Some databases require a BEGIN TRANSACTION around the @@ROWCOUNT as well... haven't found out why.
                        $query = "BEGIN TRANSACTION ; $query ; SELECT @@ROWCOUNT AS rc, @@IDENTITY AS id ; COMMIT";
                } elseif (preg_match('!^\s*select\s+!i', $query)) {
                        $this->_insertId = $this->_affectedRows = null;
                        $is_insert_update = false;
                        $is_select = true;
                } else {
                        $is_insert_update = $is_select = false;
                }

                // As seen in bugs: http://bugs.php.net/bug.php?id=36639 and http://bugs.php.net/bug.php?id=42544
                // All MSSQL links started with the same arguments, share the same link.
                // Therefore we have to reselect the database and we have to reinitialize the message handler.
                return array("USE {$this->_quotedDbname} ; $query", $is_insert_update, $is_select);
        }

        function _execute_3($query) {
                // Run it.
                $this->_clearLastMessage();
                if (sybase_set_message_handler(array(&$this, '_messageHandler'), $this->_dbconn) == false
                                || ($sybase_result = @sybase_query($query, $this->_dbconn)) === false) {
                        $errmsg = $this->getLastMessage();
                        /*if (($sybase_result = sybase_query('SELECT @@ERROR AS error', $this->_dbconn)) !== false) {
                                $row = sybase_fetch_assoc($sybase_result);
                                $errmsg .= ' [' . array_shift($row) . ']';
                        }*/
                        $errmsg .= "\n   Query was: " . $this->_getLastQuery();
                        $this->croak('Database Data Transfer Failure', "sybase_query failed: $errmsg", 3);
                        return false;
                }
                return $sybase_result;
        }

        function _execute_4($sybase_result, $is_insert_update, $is_select) {
                if (is_bool($sybase_result)) {
                        // Ms Sql returns true instead of an empty result on empty select.
                        if ($is_select)
                                return new KjwArrayResultSet(array());
                        return true;
                } elseif ($is_insert_update && sybase_num_rows($sybase_result) == 1) {
                        $row = sybase_fetch_assoc($sybase_result);
                        sybase_free_result($sybase_result);
                        $this->_affectedRows = array_shift($row);
                        if (is_integer($this->_affectedRows))
                                $this->_affectedRows = (int)$this->_affectedRows;
                        $this->_insertId = array_shift($row);
                        if (is_integer($this->_insertId))
                                $this->_insertId = (int)$this->_insertId;
                        return true;
                }
                return new KjwMsResultSet($sybase_result);
        }

        /* [INHERITED DOCS] */
        function safeQuote($mixed) {
                if (is_string($mixed))
                        // Notes:
                        // - Can't find docs on exact escaping. We'll do s/'/''/g and s/\0//g.
                        // - We usually use UTF8.. this is converted to UCS2 if we use NVARCHARs.
                        //   Return N'string' instead of 'string'.
                        return "N'" . str_replace("'", "''", str_replace("\x00", "", $mixed)) . "'";
                return parent::safeQuote($mixed);
        }

        /* [INHERITED DOCS] */
        function nameQuote($string) {
                return '[' . str_replace(']', ']]', preg_replace("/[\x01-\x1f]/", '',
                                str_replace("\x00", '', $string))) . ']';
        }

        /* [INHERITED DOCS] */
        function affectedRows() {
                // sybase_affected_rows($this->_dbconn) does *not* work with MsSql or something..
                // SELECT @@ROWCOUNT *only* works within the same execute statement
                //if ($this->_affectedRows === null)
                //      $this->_affectedRows = $this->selectAtom('@@ROWCOUNT');
                return $this->_affectedRows;
        }
        
        /* [INHERITED DOCS] */
        function insertId() {
                //return $this->selectAtom('@@IDENTITY');
                return $this->_insertId;
        }

        function getLastMessage() {
                return implode("\n     ", $this->_messages);
        }

        function _clearLastMessage() {
                $this->_messages = array();
        }

        function _messageHandler($message, $severity, $state, $lineNumber, $description) {
                array_push($this->_messages, "$description [err=$message, line=$lineNumber, sev=$severity, stat=$state]");
        }
}

?>

---