DBAL connection as service throws error

Question

I followed this SO answer to build my DBAL connection as service.

I have the following configuration:

In my service class file "Doctrine.php", I have

<?php
namespace Acme\BookBundle\Services;

use Doctrine\Common\ClassLoader;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;


class Doctrine
{
    // Just rename __construct to create and make it static
    static function create(){
        $doctrineLoader = new ClassLoader('Doctrine');
        $doctrineLoader->register();

        $doctrineConfig = new Configuration();
        $doctrineParams = [
            'driver' => 'pdo_mysql',
            'dbname' => 'book_enterprise_hub',
            'host' => 'localhost',
            'user' => 'root',
            'password' => '',
        ];
        return DriverManager::getConnection($doctrineParams, $doctrineConfig);
    }

}
?>

In my services.yml, I have the following:

parameters:
    dbal_connection: book_enterprise_hub

services:
    doctrine:
        class: Doctrine\DBAL\Connection
        factory_class:  'Acme\BookBundle\Services\Doctrine'
        factory_method: 'create'

In my CompanyController.php, I have the following code,

<?php
use Acme\BookBundle\Services\Doctrine;

class CompanyController extends Controller
{
    public function createAction()
    {
        $con = $this->get('doctrine');
        $sqlQuery = "CREATE TABLE book_info (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, label TEXT, created_on DATETIME) ENGINE = INNODB";
        $stmt = $con->prepare($sqlQuery);
        $stmt->execute();
    }
}   

When calling this line $con->prepare($sqlQuery),

I get the following error,

Attempted to call method "prepare" on class "Doctrine\Bundle\DoctrineBundle\Registry" in C:\wamp\www\symfony\src\Acme\BookBundle\Controller\CompanyController.php

Actually, I am using factory method for getting DBAL connection as service but kept getting this error. Can I write parameters in service.yml as given above or I should write in parameters.yml only? What am I doing wrong?

Answer 1

The OP of question you linked is not using Symfony2 Framework, he is asking how to set up Doctrine service with Symfony Configuration component.

As you are using Symfony2, you don't need to do any of that, you can just do:

<?php
use Acme\BookBundle\Services\Doctrine;

class CompanyController extends Controller
{
    public function createAction()
    {
        $con = $this->get('doctrine.dbal.default_connection');
        $sqlQuery = "CREATE TABLE book_info (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, label TEXT, created_on DATETIME) ENGINE = INNODB";
        $stmt = $con->prepare($sqlQuery);
        $stmt->execute();
    }
}   

As for the error. It is thrown because there is already a service registered into Symfony called doctrine and it resolves to an instance of ManagerRegistry by default.

That said, I don't think it's a good idea to use connection in controller directly.

Answer 2

Finally I read service container very carefully in symfony doc and utilzed an answer given by someone in a different post and got the following config to work for me:

In my services.yml,

parameters:
    dbal_connection: book_enterprise_hub
    bookservice.class:   Acme\BookBundle\Services\BookService

services:
    bookservice:
        class:      %bookservice.class%

** Note, I am not passing arguments here, because when passed, it threw this error "You have requested a non-existent service "doctrine.dbal.book_enterprise_hub", so, I pass this DB information in bookservice __construct method.".

In my service file BookService.php,

<?php
namespace Acme\BookBundle\Services;

//1st method (not working)
//use Doctrine\DBAL\Connection;

//second method
use Doctrine\Common\ClassLoader;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;

class BookService
{
    private $connection;

    //1st method (not working)
    // public function __construct(Connection $connection)  {
    //     $this->connection = $connection;
    // }


    //second method
    public function __construct()  {
        $doctrineLoader = new ClassLoader('BookService');
        $doctrineLoader->register();

        $doctrineConfig = new Configuration();
        $doctrineParams = array(
            'driver' => 'pdo_mysql',
            'dbname' => 'book_enterprise_hub',
            'host' => 'localhost',
            'user' => 'root',
            'password' => '',
        );
        $this->connection = DriverManager::getConnection($doctrineParams, $doctrineConfig);
    }


    public function createTable($tableName){

        $sqlQuery = "CREATE TABLE ".$tableName." (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, label TEXT, created_on DATETIME) ENGINE = INNODB";
        $stmt = $this->connection->prepare($sqlQuery);
        if($stmt->execute())
        {
            return true;
        }
    }
}

In my BookController.php, I could access the DBAL as service like this,

<?php
use Acme\BookBundle\Services\BookService;

class BookController extends Controller
{
    /**
     * Creates a new Book entity.
     *
     */
    public function createAction()
    {
        $tableName = $form->get('entityId')->getData();
        $obj = $this->get('bookservice');
        if($obj->createTable($tableName) == true){
            // some code.
        }
    }
}   

With this config, "You have requested a non-existent service. 500 Internal Server Error - ServiceNotFoundException" is resolved.

Answer 3

You don't have to configure doctrine as a service in Symfony. It is already available as a service and it can be retrieved from the service container within a controller: $this->getDoctrine() or $this->get('some-service-id'). You can see what services are available and their corresponding IDs by running the following console command:

php app/console container:debug

You can also add the --show-private option to that command to see private services as well. Generally speaking, I think the most common doctrine service will be the doctrine Entity Manager (doctrine.orm.entity_manager), which you can use to store/retrieve/remove objects in your database, and entity repositories for querying data, which you can setup as services individually for injection into other services.

If you're simply trying to construct your tables (aka entities) for your application, you don't need to/shouldn't run direct SQL queries in the controller. What you should do is:

1) Create your doctrine Entity classes in the Entity directory of your bundle. For example:

// src/Acme/BookBundle/Entity/BookInfo.php
namespace Acme\BookBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="book_info")
 */
class BookInfo
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    ...
}

2) Run the php app/console doctrine:schema:update --force console command. This will instruct doctrine to create your entity tables in the database that you setup for your project (connection information should be in app/config/parameters.yml).

As an example, to store a BookInfo object $myBookInfo in the database would then be as simple as doing this in your controller:

...
$em = $this->getDoctrine()->getManager();
$em->persist($myBookInfo);
$em->flush;
...

Of course this is a very basic example just to help you understand.

Read Databases and Doctrine in Symfony for more specifics.