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

Symfony 2, five quick tips

Hi all, today I am going to share with you five quick, but important tips in Symfony 2 programming.

  1. Use bundles where appropriate, such as to wrap set of reusable functionalities (business logic), reusable services and reusable views. But, don’t over use bundles as this will include unnecessary headache on performance and maintenance in long run.
  2. If you use a functionality in more than five different places, make it as a services function (Service container) . Also you might need to inject varies different services to the services class (Injecting services). In that case make sure you inject starting from the most specific to most generic service. If possible try not to inject big entities such as Container, unless you left out with no other option.
  3. Make the view less brainy. What I mean by that is, let the view (such as twig) to do less work than the Controller. In some rare cases this can be hardly achievable but in most cases you could easily make the view less brainy by doing most of the logical operations inside the Controller, Repository, Services etc. Let the view to render without it doing complex logical operations.
  4. Use Doctrine Queries Language (DQL) to query data¬†instead of Repository class (such as $em->getRepository()) to get the maximum efficiency of your project. If you are querying from one table, using either option won’t make a big difference. But if you are querying varies tables through joins, definitely use DQL.
  5. In terms of security, try not to send parameters in the URL as it is, if possible (including non sensitive parameters). What I mean by that is, if you use¬†URL patterns like this ‘route_to_page/{id1}/{id2}’, encrypt¬†those ‘id1’ and ‘id2’ through a custom encrypt¬†function created by you. Then decrypt (using a custom decrypt function created by you) those ‘id1’ and ‘id2’ in the Controller to continue with your logic.
    (Ideally through an injected services class which contains your security functionalities – How to create and inject services ? )
    See below,

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

Thoughts on data persist in Symfony 2

Hi All,

Today I will be discussing about persisting form submitted data in Symfony 2. Data persisting is all about inserting/updating records to/in a table in database. In this example I assume that you have a form ready which will contain few fields in order to insert values to the database. This is exactly what I am going to do in the following scenario. I will have a table (Entity) called ‘user_details (UserDetails)’. I will be passing some values from a form (which is not here) to the ‘insertUserDetails‘ action in ‘UserDetails‘ controller. Then using that action, I will be inserting new user details to the ‘user_details‘ table.

Well, in this post I would like to have an open ended discussion regarding data persisting. The approach you are using can be different from mine but still do the same intended job. More or less performance can vary, lines of codes can vary, efficiency can vary and so on.  You can see the controller and function below. First take a look at the code and I will explain the code below. Continue reading

Share