[Symfony2] Run raw queries or prepared statement from command in Symfony2 and Doctrine

Arne K. Haaje

2014-11-18 09:46:17 UTC

Hi,

This is how I run raw queries in a Symfony command. I'll show only the
relevant parts for executing the queries.

// Note that you need to execute ContainerAwareCommand, not Command
class UnsubscribeFileCommand extends ContainerAwareCommand {

// You can pass a specific connection name to getConnection(), or get the
default
$dbh = $this->getContainer()->get('doctrine')->getManager()->getConnection();

// Create the prepared statement
$sthInsert = $dbh->prepare("INSERT IGNORE INTO blockedAddress (reason,
blockType, email, addressId) VALUES('Manual unsubscribe from file',
'Unsubscribe', :email, :addressId)");

...

// Execute the statement
$dbh->beginTransaction();
$sthInsert->execute(array(
'email' => $email,
'addressId' => $mailing_id
)
);
$dbh->commit();

}

Hope this helps!

Arne

Post by r***@gmail.com
I made this script that I run from command line all the time on my Linux
#!/bin/sh
# kill all connections to the postgres server
echo "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM
pg_stat_activity where pg_stat_activity.datname='sis_db';" | psql -U
postgres -w
# drop the DB
echo "DROP DATABASE sis_db;" | psql -U postgres -w
# create the DB
echo "CREATE DATABASE sis_db WITH OWNER = postgres ENCODING = 'UTF8'
TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;" | psql -U postgres -w
# create schemas
echo "CREATE SCHEMA nomencladores AUTHORIZATION postgres;" | psql -U
postgres -d sis_db -w
echo "CREATE SCHEMA negocio AUTHORIZATION postgres;" | psql -U postgres
-d sis_db -w
echo "CREATE SCHEMA usuarios_externos AUTHORIZATION postgres;" | psql
-U postgres -d sis_db -w
And since this is a repetitive task I run all the time I'll like to create
a command from run this tasks at Symfony2 console. I read the docs
[here][1] and [here][2] but is not clear to me at all what I need to do
since this is not so simple as example on cookbook or official docs for
Console component so I need some help, push or advice in some tasks inside
inside the command itself. Basically, in the code below, I copied and paste
the needed code from
`DoctrineBundle\Command\CreateDatabaseDoctrineCommand.php` and from
`DoctrineBundle\Command\DropDatabaseDoctrineCommand.php` don't know if is
possible to extend from them in order to avoid DRY. Now the problem here is
how do I execute a raw query on that environment I mean inside the command?
What is missing here is the CREATE SCHEMA statement since I don't know how
to fit on the command, I don't know if I should pass entity manager to the
command or if it's possible to do this, any advice or ideas or help?
<?php
namespace AppBundle\Console\Command;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
class LoadDBCommand extends Command {
const RETURN_CODE_NOT_DROP = 1;
const RETURN_CODE_NO_FORCE = 2;
const RETURN_CODE_NO_CREATED = 3;
/**
*/
protected function configure()
{
$this
->setName('app:loadDB')
->setDescription('Drop and recreate Sencamer DB.')
->addOption('connection', null,
InputOption::VALUE_OPTIONAL, 'The connection to use for this command')
->addOption('force', null, InputOption::VALUE_NONE, 'Set
this parameter to execute this action')
->setHelp(<<<EOT
The <info>app:loadDB</info> command drops and creates the default
connections
<info>php app/console app:loadDB</info>
The --force parameter has to be used to actually drop/create the
database.
You can also optionally specify the name of a connection to drop the
database
<info>php app/console app:loadDB --connection=default</info>
<error>Be careful: All data in a given database will be lost when
executing
this command. Use this command on production environment will DROP all
the DB and its contents!</error>
EOT
);
}
/**
*/
protected function execute(InputInterface $input, OutputInterface
$output)
{
$connection =
$this->getDoctrineConnection($input->getOption('connection'));
$params = $connection->getParams();
if (isset($params['master'])) {
$params = $params['master'];
}
(isset($params['dbname']) ? $params['dbname'] : false);
if (!$name) {
throw new \InvalidArgumentException("Connection does not
contain a 'path' or 'dbname' parameter and cannot be dropped.");
}
unset($params['dbname']);
if ($input->getOption('force')) {
// Only quote if we don't have a path
if (!isset($params['path'])) {
$name =
$connection->getDatabasePlatform()->quoteSingleIdentifier($name);
}
// Drops database and all its content
try {
$connection->getSchemaManager()->dropDatabase($name);
$output->writeln(sprintf('<info>Dropped database for
connection named <comment>%s</comment></info>', $name));
} catch (\Exception $e) {
$output->writeln(sprintf('<error>Could not drop
database for connection named <comment>%s</comment></error>', $name));
$output->writeln(sprintf('<error>%s</error>',
$e->getMessage()));
return self::RETURN_CODE_NOT_DROP;
}
// Creates database
try {
$connection->getSchemaManager()->createDatabase($name);
$output->writeln(sprintf('<info>Created database for
connection named <comment>%s</comment></info>', $name));
} catch (\Exception $e) {
$output->writeln(sprintf('<error>Could not create
database for connection named <comment>%s</comment></error>', $name));
$output->writeln(sprintf('<error>%s</error>',
$e->getMessage()));
return self::RETURN_CODE_NO_CREATED;
}
} else {
$output->writeln('<error>ATTENTION:</error> This operation
should not be executed in a production environment.');
$output->writeln('');
$output->writeln(sprintf('<info>Would drop the database
named <comment>%s</comment>.</info>', $name));
$output->writeln('Please run the operation with --force to
execute');
$output->writeln('<error>All data will be lost!</error>');
return self::RETURN_CODE_NO_FORCE;
}
}
}
[1]: http://symfony.com/doc/current/components/console/introduction.html
[2]: http://symfony.com/doc/current/cookbook/console/console_command.html

--
Arne K. Haaje - Dr Linux
Mobil: +47 92 88 44 66
http://www.drlinux.no/ ::: ***@drlinux.no
LinkedIn: http://no.linkedin.com/pub/arne-haaje/27/189/bb
Skype: drlinux350 ::: Jabber: ***@jabber.org
--
--
If you want to report a vulnerability issue on Symfony, please read the procedure on http://symfony.com/security

You received this message because you are subscribed to the Google
Groups "Symfony2" group.
To post to this group, send email to ***@googlegroups.com
To unsubscribe from this group, send email to
symfony2+***@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony2?hl=en
---
You received this message because you are subscribed to the Google Groups "Symfony2" group.
To unsubscribe from this group and stop receiving emails from it, send an email to symfony2+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.