MSSQL error 7405 workaround for ANSI_WARNINGS

For programming and general questions on Zend Framework
Post Reply
andrevdh
Posts: 17
Joined: Wed Jul 08, 2009 4:01 pm

MSSQL error 7405 workaround for ANSI_WARNINGS

Post by andrevdh » Wed Sep 16, 2009 11:29 am

I have had to make a small change to the Zend/Db/Adapter/Pdo/Dblib.php file to work around an issue with the PDO driver having ANSI_WARNINGS set to OFF by default when connection to a MSSQL 2000 Server.

The stored procedure I need to execute has queries in it which include linked tables on a remote server. This has been causing the following error:

2009-09-16T10:30:01+01:00 DEBUG (7): Excuting SQL Stored Proc: exec uspGetTranscriptionSummary 32348
2009-09-16T10:30:01+01:00 CRIT (2): EXCEPTION_OTHER exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 7405 General SQL Server error: Check messages from the SQL Server [7405] (severity 16) [(null)]' in /usr/local/zend/share/ZendFramework/library/Zend/Db/Statement/Pdo.php:238
Stack trace:
#0 /usr/local/zend/share/ZendFramework/library/Zend/Db/Statement.php(284): Zend_Db_Statement_Pdo->_execute(NULL)
#1 /export/www/messagemanager/include/DatabaseObject/WorkFlow.php(158): Zend_Db_Statement->execute()
#2 /export/www/messagemanager/include/Controllers/WorkflowController.php(108): DatabaseObject_WorkFlow->getTranscriptionSummary(Object(Zend_Db_Adapter_Pdo_Dblib), '32348')
#3 /usr/local/zend/share/ZendFramework/library/Zend/Controller/Action.php(513): WorkflowController->summaryAction()
#4 /usr/local/zend/share/ZendFramework/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('summaryAction')
#5 /usr/local/zend/share/ZendFramework/library/Zend/Controller/Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#6 /export/www/messagemanager/htdocs/index.php(116): Zend_Controller_Front->dispatch()
#7 {main} SQLSTATE[HY000]: General error: 7405 General SQL Server error: Check messages from the SQL Server [7405] (severity 16) [(null)]


as a workaround I have edited the Dblip.php file and changed the line

$this->_connection->exec('SET QUOTED_IDENTIFIER ON');

to

$this->_connection->exec('SET QUOTED_IDENTIFIER ON; SET ANSI_WARNINGS ON');

which has resolved the issue ...

I was wondering if there is another place where I can set this default ANSI_WARNINGS to ON without editing the Zend Framework code ?

andrevdh
Posts: 17
Joined: Wed Jul 08, 2009 4:01 pm

Re: MSSQL error 7405 workaround for ANSI_WARNINGS

Post by andrevdh » Wed Sep 16, 2009 11:31 am

FYI:

System Overview
PHP Version 5.3.0
Zend Framework Version 1.9.0

Running on
CentOS 5.3

elnur-home
Posts: 55
Joined: Wed Sep 16, 2009 12:10 am
Location: Moscow, Moscow City, Russian Federation

Re: MSSQL error 7405 workaround for ANSI_WARNINGS

Post by elnur-home » Thu Sep 17, 2009 11:11 pm

Hi, andrevdh.

When I needed to set NAMES setting in my database connection I added this method to my Bootstrap file:

Code: Select all

protected function _initDatabase()
{
    $db = $this->getPluginResource('db')->getDbAdapter();
    $db->exec("SET NAMES 'utf8'");
}
 
I believe you can solve your problem the same way.
Best regards,
Elnur Abdurrakhimov

andrevdh
Posts: 17
Joined: Wed Jul 08, 2009 4:01 pm

Re: MSSQL error 7405 workaround for ANSI_WARNINGS

Post by andrevdh » Tue Oct 13, 2009 2:51 pm

hmmm I dont think that will work for my implementation. I don't have the exec statement in the bootstrap because I'm using Zend_DB::Factory which is using the pdo_dblib driver ... the exec statement I quoted in my previous post happens there ... inside the Zend Framework code ...

Here's what's in my boostrap to set up the connection ($config->database->type = "pdo_dblib";)

Code: Select all

        $params = array('host'     => $config->database->hostname,
                        'username' => $config->database->username,
                        'password' => $config->database->password,
                        'dbname'   => $config->database->database);

        $db = Zend_Db::factory($config->database->type, $params);
        $db->getConnection();

andrevdh
Posts: 17
Joined: Wed Jul 08, 2009 4:01 pm

Re: MSSQL error 7405 workaround for ANSI_WARNINGS

Post by andrevdh » Wed May 15, 2013 1:25 pm

Aaah Elnur ... you where actually right! ... I've come back to this issue after 2 years and finally cracked it ... the solution in my case looks almost idetical to what you proposed.

Here it is (the main db config is set up in the bootstrap and then saved in Zend_Registry)

Code: Select all

<?php
    class CustomControllerAction extends Zend_Controller_Action
    {
        public $db;
        
        public function init()
        {
            $this->db = Zend_Registry::get('db');
            
            if ($this->db instanceof Zend_Db_Adapter_Pdo_Mssql) {
	            $stmt = $this->db->prepare('SET QUOTED_IDENTIFIER ON');
	            $stmt->execute();
	            
	            $stmt = $this->db->prepare('SET ANSI_WARNINGS ON');
	            $stmt->execute();
            }
        }

        // More code ...

?>

Post Reply