Annoying “Enter Substitution Variable” Dialog Box

Introduction

Have you ever noticed when running PL/SQL scripts in Oracle’s SQL Developer that an annoying “Enter Substitution Variable” dialog pops up, and you have no idea why? I’ve had this happen numerous times and have been meaning to write an article on it to explain how to resolve the issue.

The Problem

I noticed recently it could be easily duplicated in a WHERE clause with a AND operator in which I have comments that have an ampersand in the comments. This is a sample of the code I had that produced the problem:

AND B.SGBSTDN_MAJR_CODE_1 IN (
   'LANL', -- Liberal Arts: Natural & Life Science
   'LANP' -- Liberal Arts: Natural & Physical Science
)

Notice in the above code the “& Life” and also “& Physical”. When I execute this in SQL Developer I get this pop up dialog:

SubstituteVariableLife

So the “Enter Substitution Variable” is prompting for a value for “Life”.

Workaround

A quick workaround is to simply remove the ampersand (&) symbols. For example the following code:

AND B.SGBSTDN_MAJR_CODE_1 IN (
   'LANL', -- Liberal Arts: Natural and Life Science
   'LANP' -- Liberal Arts: Natural and Physical Science
)

Better Fix

An even better fix is to run the following code in SQL Developer:

SET DEFINE OFF;

Once you run that, you won’t ever see the “Enter Substitution Variable” again!

So you can decide. Hopefully this helps someone who has run into the problem before

Create Symfony Cookie and set HttpOnly to false

Introduction

Recently, I tried to get cookies from the DOM using the following code:

var cookies = document.cookie.split('; ');

I noticed every time, the “document.cookie” value was a blank string, and I was scratching my head trying to figure out how such a thing could happen.

Then I happen to stumble upon that there is a “HttpOnly” parameter that can be set and this was the root cause.

HttpOnly Usage

So what’s the purpose of the HttpOnly flag? According to this Coding Horror post, is was introduced in the IE6 SP1 browser as part of a plan to reduce XSS. Actually, it is really a good idea, since with the HttpOnly flag set to “true”, any Javascript code will not be able to access the cookie.

Symfony Defaults

Unfortunately the Symfony cookie defaults to “true”. In my application I was creating a cookie something like this:

$cookie = new Cookie(
   'my_cookie',    // Name.
   $obj->getId(),  // Value.
   time() + ( 2 * 365 * 24 * 60 * 60) // Expires 2 years.
);

If you look at the Symfony Cookie construct documentation, you’ll see the default of $httpOnly is “true”; so given the above code, the HttpOnly flag will be set to true.

Create with HttpOnly set to False

If you need to set the HttpOnly flag to false, you’ll need to code something like this:

$cookie = new Cookie(
   'my_cookie',
   $obj->getId(),
   time() + ( 2 * 365 * 24 * 60 * 60),
   '/', // Path.
   null, // Domain.
   false, // Xmit secure https.
   false // HttpOnly Flag.
);

Some values of the above I just set to common sense values, and in particular the HttpOnly flag is set. Once this is done, you can now do things like delete the cookie or change it as needed.

Hope this helps someone out.

Reducing KnpPaginatorBundle Database Queries

Introduction

If you are not familiar with the KnpLabs KnpPaginatorBundle and how to use it with Symfony applications, you might want to read up on it. The GitHub page does say it is a Symfony 2 bundle, however, it does work perfectly with Symfony 3.

Why would you use it? If you want to have pages where you show all of your database Entity results, this is a perfect solution, since instead of showing all the results, it shows pages of results. You would simply click through the pages of results, also columns are sortable.

I have some applications that use this bundle, and just recently noticed that a large number of database queries were being made on one page, which is not acceptable in a production environment. This article is about how I found that out and what I did to significantly improve performance.

Symfony Debug URL

Most newbies that come from a CakePHP or Drupal environment (and true newbies) are not at all familiar with the Symfony debug URL. To access the debug URL, you simply append “app_dev.php” after the hostname followed by the route. Let’s say you had a route in your controller called “home”, and your hostname was called “example.com”, then you would use the following debug URL:

http://example.com/app_dev.php/home

At the bottom of the page you will now see the debug URL like the screenshot below:

Debug_URL

In the above screenshot, the green 200 shows it got a 200 status code, and the “@ homepage” indicates the route retrieved is “homepage”. The “8 in 15.82 ms” indicates 8 database queries in 15.82 milliseconds. On the far right is the version of Symfony (3.2.8), and you can click on the big “X” to close the debug toolbar.

Too Many Queries

On my submitted route I noticed the color of the database queries section was yellow, which normally indicates a warning. It looked like the following:

submitted_excess

If you hover over the database queries you will also see more details. Below is a screenshot of the full page with data obfuscated so you can see the paginator in action and what is to be expected:

Excess_DB_queries

If you actually click on the database queries section of the debug toolbar, it will bring up the Doctrine debug menu showing all the queries that were run and details. This is how I figured out where the excessive queries were coming from (more below).

Original Query

My original query simply selected all columns in my Application and Student Doctrine Entities, which at first I thought was ok, but later realized was the cause of the excess queries. Here is a screenshot of the original Doctrine query in my Eclipse IDE:

Unmodified_Query

I query the Application Entity as “appl” alias, and then join Student on “app_student” which is a OneToOne JoinColumn in Doctrine. The problem with querying all columns of an Entity, is that it will try to get all properties of the Entity, including joined Entities.

Required Columns

At first I wasn’t sure how to optimize my query, but then I realized I only need to get what was used in my Twig file to render the page. I needed the Student id, first & last names, and ban_id, plus the Application signature. So I first made my select in my Doctrine Query Builder like so:

$dql->select('s.stu_id,s.l_name,s.f_name,s.ban_id,appl.stu_sig')

However, if you do something like that, you mos likely will get an error:

Cannot select entity through identification variables without choosing at least one root entity alias.

This is because “app.app_id” the Application identifier needs to be selected from the alias. I then came up with this final query:

Optimal_Query

Then when I render the page, you can see the significant improvement:

Optimized_DB_queries

So the number of queries went from “64” down to “4”, which is incredibly significant.

Lesson learned: Make sure you optimize your Doctrine queries, as they may not be performing exactly as you expect.

Enjoy!

Using Cron Job to Adjust Time

Introduction

Recently in an application I developed, I noticed I received a “unauthorized” response for an API GET to a RESTful Service which I developed in Symfony. I was baffled as to why I would all of a sudden get an “unauthorized” response, since the app was previously working. In this case my app sends X-WSSE headers to authenticate using the WSSE protocol, which I described previously in my REST Easy With Symfony article.

What I found is that there was drift occurring in the virtual machine guest, and it was losing up to 6 minutes in a month, which was a large amount of time drift.

Adjusting the Time

I found the simplest solution to adjust the time in the guest was to run the following command:

ntpdate time.nist.gov

The above command just runs the “ntpdate” command with the parameter “time.nist.gov”, which essentially polls an ntp server to synchronize time on the guest. One way to use this command to compensate for the time drift, is to occasionally run the command; however, this is not feasible, since I may not check the machine(s) very often.

Cron to the Rescue

Cron is a system scheduler that allows you to run a command or script at very specific time(s). Most Linux operating systems have the command “crontab” which makes it easy to edit and list the cron scheduler. To edit your own (that belongs to the user role you are logged in as) cron scheduler, run the following command:

crontab -e

The “-e” parameter specifies “edit” and opens the default crontab editor (which is normally vi). You can then use the standard vi editor command to edit the cron file as to your required needs. If you need to see how the cron scheduler is configured, then run the following command

crontab -l

The “-l” parameter specifies “list”, so it lists the cron scheduler settings.

Format of the Cron File

The cron file that gets created normally resides in “/var/spool/cron”, so you could run:

sudo /var/spool/cron

Which would list all the cron files for all the users on the system. The above presumes you have been added to the “sudo” users list, otherwise you would not be able to run the command.

The following diagram shows the format of the cron file:

Cron_Format

Each item is separated by a space, so the above file runs the “ntpdate” command at “0” minutes” and “0” hours (midnight), everyday of the month, every month, and every day of the week.

Running as the Root User

In my case, I normally log onto this system as a “user” and not the “root” user. The “ntpdate” also needs to be run as the root user. So I have to run it from the command line like so:

sudo ntpdate time.nist.gov

So the question then is, how do you do this in cron? If you put the above in your own cron scheduler using “crontab -e”, it will fail when it tries to run, since you are not the root user.

The solution is to run “crontab” with sudo. So you would use this command:

sudo crontab -e

This creates a cron file for the “root” user.

Running More Frequently

I noticed when I ran the above cron scheduler (root cron job) only at midnight, the clock didn’t synchronize fully. So I wanted to increase the number of times it runs. To do that run “sudo crontab -e” and let’s make it run 3 times a day:

0 0,08,16 * * * sudo ntpdate time.nist.gov

The above changes will now run everyday at midnight, 8:00 AM, and 4:00 PM.

Verifying Cron Job Ran

To check that the above cron job actually ran successfully, you can check the cron log file which is normally located in the folder “/var/log” and will have the file name “cron”. If your system uses log rotate, then older logs will have a timestamp appended; for example “cron-20170522”.

To verify, just open the cron log file and search for “ntpdate”, and you should see that the command is run by the root user.

Hopefully this helps someone that needs to run a scheduled command or script.

Another Twig Ternary Example

Introduction

Previously in my Simplified Web Development with JSON and the Twig Ternary Operator, I had used JSON that had boolean values. Recently I’ve created another form that has a survey with checkboxes and I stored the JSON with string values instead. This article gives an alternate example of using the Twig ternary operator to handle strings.

The JSON Code

My survey JSON sample looks like the following example:

JSOM_Sample

In the above JSON, the keys represent the survey questions, and the string values represent the checkbox that was chosen. In the above case “strong_A” means “Strong Agree”, and “some_D” means “Somewhat Disagree”. It’s just a simpler way to write/code the JSON and still keep it understandable.

I struggled for a long time on how to handle processing of the JSON data in my Twig template when presenting a view of the form that was submitted. I show the results of the submitted for in a html table with a table row for each question and the selected checkboxes. So one row consists of the question + checkbox1 + checkbox2 + checkbox3 + checkbox4, and a total of 4 questions. So to do this in Twig, I struggle with how to create the for loop.

I thought maybe I could do a Twig for loop using {% for i in 0..3 %} to process each of the 4 checkboxes, and thus I was thinking of using the following JSON code:

JSOM_Sample_Alt

So in the above I would store each checkbox as a boolean in the JSON array, however, this would actually require a lot more Twig code, and a lot of if/else statements. I wanted to simplify my code and reduce the number of lines required.

Problems with Twig If Statements

One way of processing whether any of the checkboxes is selected is by using if/else statements, however, the code gets messy and looks something like the following code sample:

Twig_If_Probs

Notice each html td element has an if/else statement, and a total of 32 lines are used for one table row. This is a lot of code.

Simplifying the Code with the Twig Ternary Operator

I simplified the code by using the Twig ternary operator (as described in the documentation) to replace all the if/else statements. For example, to check my JSON in Twig to see if the “Strongly Agree” checkbox is selected, I use the following code in my html td element:

code

Where in the above “(survey[‘Recommend’] == ‘strong_A’)” does a comparison to see if it matches the string, and if so then show a checked checkbox. The code CheckCode is an html escape code to render a checked checkbox. The code UncheckCode is an empty checkbox.

Example in Twigfiddle

I created a Twigfiddle here to show you the twig working in case you need to see it in action. Also, you will need to see the result in html, so there’s a twig fiddle showing the resultant rendered html in this SyncFiddle.

Enjoy!

Displaying a Longer Description When Hovering Over a Drop-Down List Item in Symfony

Introduction

In a form I’ve recently created, it has a “Program Type” drop-down list, and in that list I have only abbreviated keys and values. I wanted to make it so that when you hover over the list item, that you see a longer description of what the value is. This article describes how I did that.

Drop-Down List Code

When using the Symfony PHP framework, you use a ChoiceType Field type in your FormBuilderInterface for a drop-down list. The “expanded” and “multiple” determine what type of choice widget it is. Here is a screenshot of my Eclipse IDE of what the code looks like:

Prog_Type_code

Notice I use the “choice_attr” option, which can be callable (a function). In my case, the code checks the what the key is set to and sets the return value to the appropriate string. I used the html title attribute to achieve the hover functionality.

Resultant Rendering

In my Twig code, I simply render the field like so:

{{ form_label(form.program) }} {{ form_widget(form.program) }}

Which just renders the label and the widget. When hovering over one of the items in the drop-down list it will look like the following screenshot:

Hover_Title_Choice_Attr

Notice in the above case, the key is equal to “Cert Achieve”, the “choice_attr” function checks that it is equal to that and sets the html title attribute to “Certificate of Achievement”. That is the text that is shown when hovering.

Hopefully this helps someone out with figuring out the “callable” options in Symfony field types.

Proper Doctrine Annotation Spacing

Introduction

I was recently working on a console application in Symfony and I had an Entity file called “Record.php”. Whenever I tried to run:

php bin/console doctrine:generate:entities AppBundle/Entity/Record

I would always get a Doctrine\ORM\MappingException Class “AppBundle\Entity\Record” is not a valid entity or mapped super class. error as shown in the following screenshot:

Doctrine_MappingException

Annotation Spacing Problem

I know I had the correct Doctrine annotations specified, since I referred to working Entities in other projects, so I was baffled as to what could be the problem. I decided to change the indentation of the @ORM\Entity annotation. This fixed the problem.

Apparently, if you have a tab instead of a single space, the annotation does not get recognized. I had a similar problem that I noticed with the Symfony Route annotation.

The way to see the problem is to turn on whitespace visibility in the IDE you use. In Eclipse, you would go to “Window > Preferences” and then view under “General > Editors > Text Editors” and select the checkbox “Show whitespace characters”. The tab will look like this:

Eclipse_whitespace

Change the tab to space and then rerun the “doctrine:generate:entities” and it should work for you.

Hopefully this will help someone out! I struggled for a while on this.