What a nice Oracle feature: RETURNING INTO Clause

Is handy to fetch the number of rows affected by an insert, update or delete statement!

RETURNING INTO Clause

Phrase of the Day: One-Trick Pony.

Wiktionary: “One-Trick Pony”

And a colleague of mine likes to wear this priceless t-shirt: More Dots

Installing PHPUnit to run phpunit.xml for Zend Server 5.3.1

1. Install PEAR by running go-pear.bat

if it starts to complain about: “… does not have a signature …” change the go-pear.bat to the following:

@ECHO OFF
set PHP_BIN=php.exe
%PHP_BIN% -d output_buffering=0 -d phar.require_hash=0 PEAR\go-pear.phar
pause

In windows the last part of the output will be something like:

Would you like to alter php.ini ? [Y/n] :

php.ini include_path updated.

Current include path : .
Configured directory : D:\Zend Server 5.3.1\ZendServer\bin\pear
Currently used php.ini (guess) : D:\Zend Server 5.3.1\ZendServer\etc\php.ini
Press Enter to continue:

** WARNING! Old version found at D:\Zend Server 5.3.1\ZendServer\bin, please remove it or be sure to use the new d:\zend
server 5.3.1\zendserver\bin\pear.bat command

The ‘pear’ command is now at your service at d:\zend server 5.3.1\zendserver\bin\pear.bat

* WINDOWS ENVIRONMENT VARIABLES *
For convenience, a REG file is available under D:\Zend Server 5.3.1\ZendServer\bin\PEAR_ENV.reg .
This file creates ENV variables for the current user.

Double-click this file to add it to the current user registry.

2. Update pear:

pear channel-update pear.php.net

and

pear upgrade pear

3. Install PHPUnit via PEAR by running:

pear channel-discover pear.phpunit.de

and

pear install phpunit/PHPUnit

see also: Chapter 3. Installing PHPUnit

TIP: Get the proper code completion in Zend Framework.

Some of the standard code completions don’t work properly because various Zend Framework classes redirect method calls by using the magic method “__call($method, array $options)”. For example Zend_Translate does not include the addTranslation method but redirects the call to the applied adapter. You can add a document property which enables the correct code completion. Unfortunately it will only complete the adapter’s methods then, but the adapter does include the methods which you probably want to see completed/documented. The question remains if the whole magic method redirection is a nice way to implement an adapter pattern.

/* @var $translate Zend_Translate_Adapter_Array */
$translate = new Zend_Translate('Zend_Translate_Adapter_Array', $us, 'en_US');
$translate->addTranslation($nl, 'nl');

TIP: Missing Zend Framework Errors?!?

If you happen to use Zend tool to create a project and globally turn off the view rendering in your control logic, the error controller will also neglect to show a stack-trace or error details. Add the following code to the error controller just in case.

public function __construct(Zend_Controller_Request_Abstract $request, Zend_Controller_Response_Abstract $response, array $invokeArgs = array()) {
	parent::__construct($request, $response, $invokeArgs);
	$this->getFrontController ()->setParam ( "noViewRenderer", false );
}

It’s also better to disable the view rendering for specific controller actions and not globally, hence the Zend Framework documentation explains it best:

Class FooController extends Zend_Controller_Action {
	public function init() {
		// Local to this controller only; affects all actions,
		// as loaded in init:
		$this->_helper->viewRenderer->setNoRender ( true );

		// Globally:
		$this->_helper->removeHelper ( 'viewRenderer' );

		// Also globally, but would need to be in conjunction with the
		// local version in order to propagate for this controller:
		Zend_Controller_Front::getInstance ()->setParam ( 'noViewRenderer', true );
	}
}

More detailed information at:
Action Controllers

Dutch Public Transport Digital Payment System (OV Chipkaart) – An Epic Fail Story for Me (and in general imho)

My horrible encounters with the system:
- Today was a pretty darn nippy morning (COLD!!! BrrrrRrRrRrrRrrrr), it’s take me a couple of minutes to get to the station. Arriving there the train was already coming, so I had to pick up pace to get in. When I swiped my card the ******* machine rejected it, WTF. It seemed the card itself had expired with 12 euros of credit still on it. Annoyed by that fact I was confronted with another fail, there wasn’t the opportunity to buy a single way ticket or a new card, WTF! So I had to call my wife to bring me to work. I later discovered that to reimburse my 12 euro credit on the expired card, it COSTS me 2,5 euro administration cost. That would probably be on top of the 7,5 euro which costs a new card.

I quote (sorry Dutch only, no translation available, seems they are lacking in the customer service department):

Defect?
Is uw persoonlijke of anonieme OV-chipkaart defect? In dat geval kunt u vragen om restitutie (teruggave) van uw saldo.
Bij een defecte persoonlijke OV-chipkaart, gebruikt u het formulier Beëindiging en restitutie van een persoonlijke OV-chipkaart.
U ontvangt het bedrag op uw bank- of girorekening (na aftrek van € 2,50 administratiekosten).
Restitutie is alleen mogelijk als er een saldo van minimaal € 5,00 op uw OV-chipkaart staat.

OV-chipkaart gestolen / verloren / defect / verlopen?

- Once I couldn’t check-out (a “wonderful” new feature, didn’t have to do that with the old paper card system) because the check-in/out device failed with a nice error message. Now my ride would cost 4 euros instead of 1,5. I had to wait in line for 20 minutes to get my money back.

- Some of the non-city stations have devices which allow both check-in and check-out. In the city area’s however, there are these small swinging doors you need to get through. One side is check-in, one is check-out. If you, by mistake, end up on the wrong side, you’re screwed! The first time that such a event happened to me my thought was: “no problem I’ll just swipe the card”, which would be OK for the non-city situation. The problem was however, I swiped against the check-out section, which costs me 4 euros, I thought I checked-in (yeah I didn’t look). When I exited from my ride I swiped to get out, for real this time, and again 4 euros disappeared from my card. Considering I don’t regularly use public transportation, I noticed it a couple of days later.

- I also accidentally carried two cards once and couldn’t remember which one I used to check-in. And there’s no way to find that out because the charging machines which show information about the card are behind the check-out. And yeah, I choose the wrong card … :(

- Another issue they experience is that people with a subscription of some sort (e.g. monthly) don’t check-in / check-out at all in trams. As long as they don’t enforce that they will miss out on a lot of valuable data which this system should have supplied.

- There are also issues because some of the transportation companies have different implementations, which for example means that using your subscription card at a specific buss  company would result in loss of credit (hence the card holds both subscription and credit information!) instead of just registering the trip. Plus every company would charge it’s own “administration” fee, thus using different types of public transportation  each being owned by a different company would create an additional cost.

- With this new system people are required to check-out. Checking-in rarely causes a queue, people simply don’t arrive all at the same time, getting out however causes everyone to queue up to get checked-out. Some stations have a limited supply of check-out machines or poorly placed ones. There’s also at least one person in the queue which is unable to check-out and tries repeatedly while holding up the queue, which might be incompetence or plain system failure.

I’m getting really annoyed by this system and even when it isn’t my fault it costs me time and money. Did we (poorly) re-invent the wheel here, I’ve seen much better systems abroad.

Bug: Doctrine Oracle ID-Triggers.

Doctrine’s YAML created the following trigger to allow the creation of IDs via a sequencer.
Notice that the trigger name is not entirely upper-cased: USER_seq which in our case caused an error of the type: OCI_NO_DATA

CREATE OR REPLACE TRIGGER USER_AI_PK
   BEFORE INSERT
   ON USER
   FOR EACH ROW
DECLARE
   last_Sequence NUMBER;
   last_InsertID NUMBER;
BEGIN
   IF (:NEW.id IS NULL OR :NEW.id = 0) THEN
      SELECT USER_seq.NEXTVAL INTO :NEW.id FROM DUAL;
   ELSE
      SELECT NVL(Last_Number, 0) INTO last_Sequence
        FROM User_Sequences
       WHERE Sequence_Name = 'USER_seq';
      SELECT :NEW.id INTO last_InsertID FROM DUAL;
      WHILE (last_InsertID > last_Sequence) LOOP
         SELECT USER_seq.NEXTVAL INTO last_Sequence FROM DUAL;
      END LOOP;
   END IF;
END;

Oracle Tip: Apply RowNum after Order By

Ordering a resultset which contains a rownum statement will order the resultset which is return by the rownum.

e.g. the query:

select * from mytable where rownum <= 10 order by id desc

does not have to return the last 10 records.

See also:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.