TYPO3: How to use Extbase with external database

Question

An extbase extension shall select data from an external non-typo3 database and display them in TYPO3 frontend. Updating data or insertion of new data is not planned. It is not allowed to store the external data in the local TYPO3 system. The data are relational, and the external database is physically on an external server.

Upto now a pibase extension was used for this. The funcionality shall now be replaced by an extbase extension.

How can the external database be accessed with extbase?

Answer 1

I solved it with a helper utility which can send SQL SELECT requests to the external server and handles the connection. It works on PHP level with mysqli commands.

In the repository classes I build the SQL string and call the helper utility to handle the connection. As results I return arrays.

Like this I am able to handle anything in my controller and with the FLUID mechanisms.

Works fine.

Answer 2

Have a look at dbal. Map you external database as dbal handler and map the needed table. And then use extbase and map the table to your objects.

Answer 3

While dbal is definitely the cleanest way, I also used a helper, cause some third party extensions in my system made the usage of dbal impossible.

You can instantiate a completely new db connection as a simple utility.

<?php
namespace FOO\YourExt\Utility;

use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * Database utility
 */
class DatabaseUtility
{

    private static $config = 'External';

    /**
     * Instantiate custom queryBuilder for different database config
     * 
     * @return type
     */
    public static function queryBuilder()
    {
        $cObj = GeneralUtility::makeInstance('TYPO3\\CMS\\Frontend\\ContentObject\\ContentObjectRenderer');
        $objectManager = GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Object\\ObjectManager');
        $connectionPool = $objectManager->get('TYPO3\\CMS\\Core\\Database\\ConnectionPool');
        $externalConnection = $connectionPool->getConnectionByName(self::$config);
        $queryBuilder = $externalConnection->createQueryBuilder();

        return $queryBuilder;
    }

    /**
     * Instantiate custom connectionPool for different database config
     * 
     * @return type
     */
    public static function connectionPool()
    {
        $cObj = GeneralUtility::makeInstance('TYPO3\\CMS\\Frontend\\ContentObject\\ContentObjectRenderer');
        $objectManager = GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Object\\ObjectManager');
        $connectionPool = $objectManager->get('TYPO3\\CMS\\Core\\Database\\ConnectionPool');
        $externalConnection = $connectionPool->getConnectionByName(self::$config);

        return $externalConnection;
    }
}

You can use any database connection defined in your LocalConfiguration.php.

[...]
    'DB' => [
        'Connections' => [
            'Default' => [],
            'External' => [
                'charset' => 'utf8',
                'dbname' => 'external_database_name',
                'driver' => 'mysqli',
                'host' => '',
                'password' => '',
                'port' => 3306,
                'user' => '',
            ],
        ],
    ],

[...]