Magento – Add connection to MS SQL database

databasemagento-1.9mssql

I want to add a connection to our ERP database that uses MS-Server, but i can't figure out what I'm doing wrong.

I know how to connect to another MySQL DB, but for MSSQL?

config.xml

<resources>
    <erp_db>
        <connection>
            <host><![CDATA[1.2.3.4\somename]]></host>
            <username><![CDATA[user]></username>
            <password><![CDATA[password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <!-- ??? -->
            <model>mssql</model>
            <type>pdo_mssql</type>
            <!-- ??? -->
            <active>1</active>
        </connection>
    </erp_db>
    <erp_write>
        <connection>
            <use>erp_db</use>
        </connection>
    </erp_write>
    <erp_read>
        <connection>
            <use>erp_db</use>
        </connection>
    </erp_read>
    <erp_setup>
        <connection>
            <use>core_setup</use>
        </connection>
    </erp_setup>
</resources>

If I try this to list all tables …

$resource   = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('erp_db');
$results    = $connection->fetchAll('SELECT * FROM sysobjects WHERE xtype="U"');
var_dump($results);

I get …

Fatal error: Class name must be a valid object or a string in /app/code/core/Mage/Core/Model/Resource.php on line 225

I also read Using MS SQL with Magento 1.9, some say it's not supported, but from comments it seems to be possible …

The PDO adapter exists in Zend_Db_Adapter_Pdo_Mssql, but how to correctly use it?

Best Answer

Try adding this to the local.xml file inside the config/global tag

<resource><!-- singular form. No 's' at the end -->
    <connection>
        <types>
            <pdo_mssql>
                <adapter>Zend_Db_Adapter_Pdo_Mssql</adapter>
                <class>Mage_Core_Model_Resource_Type_Db_Pdo_Mssql</class>
                <compatibleMode>1</compatibleMode><!-- Not sure of what this does or if it's even needed -->
            </pdo_mysql>
        </types>
    </connection>
</resource>

Still it won't work probably because the class listed above in the class tag does not exist...
you can create one based on Mage_Core_Model_Resource_Type_Db_Pdo_Mysql and replace the value above with the class name you create.
What you need to change is the result of the method _getDbAdapterClassName.

[EDIT]
You are getting that error because the method getResourceTypeConfig in the same class returns null. This method should return this->_xml->global->resource->connection->types->{$type}. That's why I added the node pdo_mssql inside connection types.
[/EDIT]

Note: This is not tested, I started from the error message you got and checked what could be missing.

Related Topic