How to call an Oracle Function in Symfony 2

Hi All,

How are you doing? Seems like my fingers do not want me to stay away from Symfony2 despite the Spring Bank Holiday. 🙂

Today, I am going to talk about how to call an Oracle Function in Symfony2. A function (aka stored function) is a collection of SQL statements which is used to perform various different activities within the Database. The good thing about the function is you can write your chunks of SQL inside the function and call it using a name to initiate. I will be focusing on how to call an Oracle Function in Symfony2 and if you would like to know more about Oracle functions please click here.

First, let’s take a look at a sample function which I have created and is saved in Oracle 12c Database.

create or replace 
FUNCTION testFunc123(old_bname IN VARCHAR2) 
   RETURN VARCHAR2 IS 
   BEGIN 
      IF old_bname = 'TEST' THEN
          RETURN 'Y';
      ELSE
        RETURN 'N';
      END IF;  
    END;

The role of the above created Oracle function is to check whether the value of ‘old_name’ is equal to ‘TEST’ or not. If it is ‘TEST’, then the function will return ‘Y’ otherwise ‘N’. So, now what we going to do is call this function inside a Symfony2 controller. This is really easy, thankfully to wonderful Doctrine2.

Continue reading

Share

Accessing SQL parameters and its values in DQL 2 in Symfony 2

Hi All,

When you are executing a DQL there must be situation where you might need to see the parameters and its values for the underlying SQL. Usually Doctrine queries are prepared statements and you will not be able to see the values which pass through the query. Prepared statement works in the following way,

  1. Sending the statement,
  2. Sending the parameters,
  3. And executing the prepared statement.
    (More information on prepared statements :- http://www.w3schools.com/pHp/php_mysql_prepared_statements.asp)

So, what you see when you get the SQL query is something similar to this,

 SELECT * FROM some_table WHERE column1 = ? AND column2 = ?

So today, I am going to show you a workaround to get the parameters (such as column1, column2 and so on…) and most importantly get the respective values for those parameters. Continue reading

Share

Symfony 2 fetching data using getRepository() and DQL

Hi folks,

Happy New Year !!!

Sorry I have been really busy past few months. I always wanted to continue my blog but the amount of workload I had to go through was immense. Fortunately, I found sometime to come up with a new post.

Well, this brief post is about the impact on fetching data mainly using getRepository and DQL (Doctrine Query Language), which you might have already known before. But I took some time out to highlight this fact, mainly for developers who are new to Symfony 2.x & Doctrine 2.x., which can heavily impact on performance on your Symfony application in long run.

Imagine you have 3 tables which are ‘users’, ‘user_roles’ and ‘user_categories’. There are unidirectional many-to-one relationships between ‘users’ to ‘user_roles’ and ‘user_roles’ to ‘user_categories’. Refer to Users.orm, UserRoles.orm and UserCategories.orm files below.

Continue reading

Share

SQL into DQL, examples | Symfony 2 | Doctrine 2

Hi everyone,

Today I am going to talk about some examples on converting SQL queries into DQL . Doctrine query language (DQL) is the query language used by Doctrine ORM. Doctrine ORM 2.x is shipped with Symfony 2.x by default. Let’s take some examples and discuss. For the following examples we will be creating an instance of Doctrine query builder to structure DQL’s. 

Creating an instance of doctrine query builder

$em = $this->getDoctrine()->getManager();

$result = $em->createQueryBuilder();

SQL

SELECT * FROM contact

DQL

$dql = $result->select(‘c’)
->from(‘ACMETestBundle:Contact’, ‘c’)
->getQuery();

Continue reading

Share

Many-to-one relationship mapping using Doctrine 2 in Symfony 2 in YAML

Hello doctrine lovers,

Well come back. In my previous post, we discussed how to map one-to-one relationships. If you have not read it but want to make yourself aware of one-to-one relationship mapping in Doctrine using YAML, make sure you read it before reading this post. So, today I am going to show you how to map many-to-one relationships in both unidirectional and bidirectional ways using Doctrine 2. As you are aware by now I will be using YAML instead of PHP annotations or XML. It is simply because YAML is very easy to understand (though you need to stick with proper indentation, which is not a big deal), will not run into unexpected problems, such as pre-compilers/caching engines tend to ignore comments, which will be used in annotations and few other things. By the way, in Symfony 2, you can use either YAML, Annotations or XML as part of the Doctrine configuration, but use one, not all. Symfony 2 evaluates all these methods equally. At the end of this post, you will learn a technique to evaluate your mapping whether it’s correct or not. So make sure you don’t miss anything in this post.

So back to the topic. Let’s have a quick look at what’s many-to-one relationship is all about. In simple terms, many-to-one is many rows of a table can be relate with only one row of another table. Take a look at the image below,

many-to-one-relationship

So basically, what this tells is, one supplier can have more than one product. For an in instance supplier ‘acer’ can have more than one products such as aspire e1 series laptops, e2 series laptops and so on.

Unidirectional – Many to One

In Unidirectional many-to-one mapping, you will only point to the target table from the parent table (same as in the one-to-one example in my previous post). I call ‘product‘ table as the owning side (parent table) and ‘supplier‘ table as the target table. The reason why I declare parent table to be the owning side is below, Continue reading

Share