CodeSamplez.com

Programming, Web development, Cloud Technologies

  • Facebook
  • Google+
  • RSS
  • Twitter
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact
Home Database How To Execute Native SQL Query With Doctrine

How To Execute Native SQL Query With Doctrine

Rana Ahsan April 21, 2013 1 Comment


 How To Execute Native SQL Query With Doctrine    

So, seems like you are in big trouble? Otherwise why you will want to execute doctrine native SQL query? In this tutorial I will describe and show how you can perform such operations. But that doesn’t necessarily mean, I am encouraging you to use it all the time. I will still strongly recommend to find your way within doctrine’s ORM, DBAL And DQL implementation, as much possible. However, you may face some UN-avoidable situation where you can’t help without native SQL support as I faced recently.

My Scenario For Choosing Doctrine Native SQL Query Support:

My application was in a stable database schema/entity implementation and live. For refactoring purpose, I had to change entities and in db schema for adding/removing columns/properties. Now, as the application was live, I do need to transfer all my old data to new schema thus need a bulk migration script. Now here is what I could do – retrieve data from old database, process them accordingly and insert them to new database table(s). But did you notice, we did changed our entities as per new schema and thus can’t retrieve data from old database in ORM style.

Hmm, so, now what? Well, here what came into my mind to use the doctrine native SQL query support. Using this, after retrieving data from old database, we can either insert/process them as new entity or in native support, what suits best for the requirement.

This is not the only case where we may face similar issues, you may find others as well. For sake of such scenario, let’s go and learn the basics.

Getting Database Connection From Entity Manager:

First step to use doctrine is to get the connection object from entity manager. Lets see the basic initialization of doctrine entity manager and retrieving connection instance in PHP:

As you are possibly using doctrine already, you can skip initialization. Best way to use them as a separate wrapper class for your application as the one I showed before while integrating doctrine with codeigniter.

//**********priliminary doctrine set up. You can do it separate generalized class/function as well********

$config = new \Doctrine\ORM\Configuration;
// Cache Setup
$apcCache = new \Doctrine\Common\Cache\ApcCache;
$config->setMetadataCacheImpl($apcCache);
$config->setQueryCacheImpl($apcCache);
$config->setResultCacheImpl($apcCache);
		
// Proxy Setup
$config->setProxyDir('proxy');
$config->setProxyNamespace('Proxies');
$config->setAutoGenerateProxyClasses(true);
		
// Mapping Setup
$driverImpl = $config->newDefaultAnnotationDriver(__DIR__);
$config->setMetadataDriverImpl($driverImpl);
		
// Connection Setup
$connectionOptions = array(
	'dbname' => "database_name",
	'user' => "db_user_name",
	'password' => "db_password",
	'host' => "db_host",
	'driver' => 'pdo_mysql');

// Initialize Entity Manager
$entityManager = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

//**** Basic Initialization Ends Here*********

//retrieve the connection object
$connection = $entityManager->getConnection();

Prepare And Execute SQL Query:

there is a helpful method named ‘prepare’ which will help us to create SQL statement suitable for execution under doctrine. Then, generalized ‘execute’ method will run the SQL query whether its insert/delete/update or select statement. Following is the code snippet example:

$statement = $oldConnection->prepare("SELECT * FROM mytable");
$statement->execute();

Now, if the query is of insert/update or delete, we should be done and fine. But in case of select query, we need to fetch the data, which should be something like as below:

//fetch all results in associative array format
$results = $statement->fetchAll();

//fetch single row
$result = $statement->fetch();

//total row count
$result = $statement->rowCount();

Friendly Support For CRUD Operations:

As we have seen, we can now run raw SQL query, we can use some more friendly methods for database update operations, which are directly applied on the connection object. Lets see few examples:

//insert data from an associative array
$connection->insert('table_name', $associative_array);

//update row from an associative array
$connection->update('table_name', $associative_array,$identifier);

//delete a row
$connection->delete('table_name', $identifier);

Running Transaction In Doctrine Native SQL Query Mode:

You may sometimes need to run multiple query in a single transaction to optimize the database operations. As you can see above in my case, optimization is crucial and thus I needed a way to do such operations to reduce total number of database connectivity and make the process faster. You might need it for other purposes like rollback if some operations failed etc cases as well. Lets see some code examples how they can be achieved:

$connection = $em->getConnection();
$connection->transactional (function ($connection) {
    $connection->insert('table1', $associative_array1);
    $connection->insert('table2', $associative_array2);
    ...
    $connection->insert('tableN', $associative_arrayN);
});

The above example is for insert opeartion though, you can use it for update/delete or ‘execute’ method etc as well too.

Final Words:

Hopefully, this small tutorial on doctrine native SQL query support will be helpful for you in some situations. Read more on doctrine documentation on this topic as further reference. Let me know if you are facing any issue while trying with the php code examples above. Happy coding 🙂

Related

Filed Under: Database Tagged With: doctrine, mysql, php

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Comments

  1. Mr JiJo says

    April 24, 2019 at 6:38 am

    Thanksssssssssssssssssssssssssssssssssa lot brother you don’t know how it could help me ! BDW thank you sooo much !! i just got logic and edited my query ! after 4hrs i found solution from ur blog ! (Y) appreciated !

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Email Subscription

Never miss any programming tutorial again.

Popular Tutorials

  • PHP HTML5 Video Streaming Tutorial
  • How To Work With JSON In Node.js / JavaScript
  • Generate HTTP Requests using c#
  • How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
  • How To Work With CodeIgniter Pagination
  • Get Facebook C# Api Access Token
  • LinQ Query With Like Operator
  • How To Work With Multithreaded Programming In C#.NET Application
  • Getting Started With HTML5 Web Speech API

Recent Tutorials

  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read
  • Getting Started With Golang Unit Testing
  • Getting Started With Big Data Analytics Pipeline
  • NodeJS Tips And Tricks For Beginners
  • Apple Push Notification Backend In NodeJS
  • Web Based Universal Language Translator, Voice/Text Messaging App
  • How To Dockerize A Multi-Container App From Scratch

Recent Comments

  • intolap on PHP HTML5 Video Streaming Tutorial
  • manishpanchal on PHP HTML5 Video Streaming Tutorial
  • Rana Ghosh on PHP HTML5 Video Streaming Tutorial
  • ld13 on Pipe Email To PHP And Parse Content
  • Daniel on PHP HTML5 Video Streaming Tutorial

Archives

Resources

  • CodeSamplez.com Demo

Tags

.net apache api audio aws c# cache cloud server codeigniter deployment doctrine facebook git github golang htaccess html5 http image java javascript linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty ssh tfs thread tips ubuntu unit-test utility web application wordpress wpf

Copyright © 2010 - 2021 · CodeSamplez.com ·

Copyright © 2021 · Streamline Pro Theme on Genesis Framework · WordPress · Log in