Do you need training or consultancy? Get in touch!

Inserting binary data into SQL Server with ZF1 & PHP 7

If you want to insert binary data into SQL Server in Zend Framework 1 then you probably used the trick of setting an array as the parameter's value with the info required by the sqlsrv driver as noted in Some notes on SQL Server blobs with sqlsrv.

Essentially you do this;

Where $db is an instance of Zend_Db_Adapter_Sqlsrv.

If you use SQL Server with ZF1 and happen to have updated to PHP 7, then you may have found that you get this error:

(At least, that's what happened to me!)

Working through the problem, I discovered that this is due to Zend_Db_Statement_Sqlsrv converting the $params array to references with this code:

The Sqlsrv driver (v4) for PHP 7 does not like this!

As Zend Framework 1 is EOL, we can't get a fix into upstream and update the new release, so we have to write our solution.

We want to override Zend_Db_Statement_Sqlsrv::_execute() with our own code. To do this we firstly need to override Zend_Db_Adapter_Sqlsrv. (Also, let's assume we already have a App directory registered with the autoloader)

Firstly our adapter:


This class simply changes the default statement class to our new one. Now, we can write our Statement class:


This class, takes the _execute() method from Zend_Db_Statement_Sqlsrv and makes the necessary changes the section that creates parameter references. Specifically, we only create a reference if the parameter has a direction of SQLSRV_PARAM_OUT or SQLSRV_PARAM_INOUT:

Finally, we need to register our new adapter with Zend_Application's Database resource. This is done in the config file:


That's it.

We can now insert binary data into our SQL Server database from PHP 7 using the latest sqlsrv drivers.

Autocomplete Composer script names on the command line

As I add more and more of my own script targets to my composer.json files, I find that it would be helpful to have tab autocomplete in bash. I asked on Twitter and didn't get an immediate solution and as I had already done something similar for Phing, I rolled up my sleeves and wrote my own.

Start by creating a new bash completion file called composer in the bash_completion.d directory. This file needs executable permission. This directory can usually be found at /etc/bash_completion.d/, but on OS X using Homebrew, it's at /usr/local/etc/bash_completion.d/.

This is the file:

(Note that __ltrim_colon_completions is only in recent versions of bash-completion, so you may need to remove this line.)

Reading from the bottom, to get the list of commands to composer, we create a list of words for the -W option to compgen by running composer --no-ansi and then manipulating the output to remove everything that isn't a command using awk. We also create a separate list of flag arguments when the user types a hyphen and then presses tab.

Finally, we also autocomplete flags for any subcommand by running composer {cmd} -h --no-ansi and using tr and grep to limit the list to just words starting with a hyphen.

That's it. Now composer {tab} will autocomplete both built-in composer commands and also custom scripts!

Composer autocomplete

As you can see, in this example, in addition to the built-in commands like dump-autoload and show, you can also see my custom scripts, including apiary-fetch and .

This is very helpful for when my memory fails me!

Switching OpenWhisk environments

When developing with OpenWhisk, it's useful to use separate environments for working locally or on the cloud for development, staging and production of your application. In OpenWhisk terms, this means setting the host and the API key for your wsk command line application.

(Of course, for live and staging, ideally, you will be using a build server!)

For a Vagrant install of OpenWhisk, the host is and the key can be found inside the ansible provisioning files. On Bluemix, the host is always and separate environments is most easily done using separate "spaces" as each space has its own key.

To avoid having to keep looking up the correct keys, I wrote a simple Bash function in my .bash_profile file:

(Actual keys redacted!)

This code uses a case statement to set up the right host and key to use. I'm lazy, so just hardcode my Bluemix keys. There's probably a better way to that though. For the local Vagrant instance, I get the key directly from the file used by Ansible for provisioning. Again, due to laziness, I've hardcoded the Vagrant VM's IP address.

Lastly I set display the current host and namespace – tput is my new favourite bash command!

Switching environments

I can then use the function to switch to my Vagrant installation like this:

Or, I can switch to my Cloud development environment using:

Updating the CLI tool

I also have a script to update the CLI tool:

This is a quick way to collect the latest version of the wsk app.

POSTing data using KituraNet

I had a need to send a POST request with a JSON body from Swift and as I had KituraNet and SwiftyJSON already around, it proved to reasonably easy.

To send a POST request using KituraNet, I wrote this code:

As you can see, I've liberally commented it, so it should be easy to follow. Let's look at some interesting bits.

SwiftJSON is convenient

SwiftyJSON does all the heavy lifting of converting dictionaries. As KituraNet requires a Data object for the body, we can do this in one line with SwiftyJSON:

(admittedly, this assumes a valid dictionary! In a real app, consider better error checking…)

Similarly, if we get a JSON string back from the server, converting it to a dictionary is as easy as:

(This time, with some checking!)


Making a JSON-based POST request is easy enough with KituraNet and SwiftyJSON. Of course, the reason I chose this approach is that they are baked into OpenWhisk which is where this code is running.

I also refactored this code into the methods postTo() and postJsonTo() as you can see in this gist.

Using CircleCI for a PHP project

For a new client project, I've decided to use CircleCI to run my tests every time I push to GitHub.

This turned out to be quite easy; this is how I did it.

I started by creating a config file, .circleci/config.yml containing the following:

The documentation is really good, but the file's organisation is pretty self-expanatory.

The config file has a list of jobs. The build job is run on a push to GitHub, so that's the one I've created. Inside the docker section contains a list of docker images that are required – in my case, I just need a single container running PHP 7.1. The other section in the job is the list of steps to run. Each step has a name and a command which is a bash command.

For my job, I need to install git and the PDO PHP extension. I then run the "magic" step called checkout which as per its name, checks out my source code. I then install composer, including verifying that it's valid and display the PHP version number and composer version number in case I ever need them to work out why a build failed.

I then turn my attention to the project itself and run composer install and then the tests themselves: phpcs and phpunit.

Running the build

To actually get builds to run, you log into CircleCI – usefully you authenticate via GitHub – and select the project to build. From now on, pushing to GitHub or a branch will result in the build running. On success, you get something like this:

Circle ci

Hooking up to Slack

Hooking up to Slack is done by going to Slack's Apps & Integrations section and searching for CircleCI. Add the configuration and follow the wizard. Once done, you get a URL that you add to the project's Chat Notifications settings in CircleCI. This is found by clicking on the "cog" next to the project's name in the builds list. There's also a setting callled "Fixed/Failed only" which I check.

Once that's done, you get Slack notifications on failures and then on the first success after a failure and you are now secure in the knowledge that your tests are being run reliably.

Automatically converting PDF to Keynote

I use rst2pdf to create presentations which provides me with a PDF file. When it comes to presenting on stage, on Linux there are tools such as pdfpc and on Mac there's Keynote.

Keynote doesn't read PDF files by default, so we have to convert them and the tool I use for this is Melissa O'Neill's PDF to Keynote. This is a GUI tool, so I manually create the Keynote file when I need it which is tedious. Recently, with Melissa's prompting, I realised that I could automate the creation of the keynote file which makes life easier!

I use a Makefile for this and this is the target & relevant variables:

The nice thing about PDF to Keynote is that it has preferences to automatically create the Keynote file after a PDF file opened and to automatically close the PDF file once saved. We can also programmatically set the aspect ratio. To do this, we use the defaults command line tool to set up PDF to Keynote the way that we want.

We then call open -a to open PDF to Keynote with the PDF file as the argument which then automatically creates the Keynote file and stores it into the same directory. The PDF file is automatically closed for us too.

Finally, we can use AppleScript via the osacript command to quite PDF to Keynote. I'm not sure if we need to wait for the conversion to happen before we quit, in which case, we can add sleep 3 if we need to.

That's it. Automatically creating the Keynote file vastly improves my workflow and I no longer have to think so much about it.

Update: Note that I changed the default write commands for the booleans as they need to be 1 and 0, not "YES" and "NO"…

Detecting OpenWhisk web actions

I've already written about OpenWhisk web actions and how they allow you to send a status code and HTTP headers to the client by returning a dictionary with the keys status, headers and body from your main() method:

If this test action is in the default namespace, then we create it with wsk action update test test.swift -a web-export true to enable web action support and access it via curl:

However, when you invoke this via the authenticated POST API (eg. Via curl or wsk action invoke) you get this:

This could have been predicted as the authenticated POST API call just executes the action and sends back what it returned.

Additional arguments in a web action

When your action is called as a web action, then there are additional arguments, that don't appear otherwise. We can simply look for one of these. Specifically, I chose to look for __ow_meta_verb.

The simple way of doing this:

Note that we return a dictionary as an authenticated POST API call expects this. Calling internally via curl:

(We can only get JSON back this way)

and of course calling the web action hasn't changed and we still get our XML.

We can call our function with whatever mechanism is appropriate and generate the right response.

Calling an OpenWhisk action in Swift

As OpenWhisk is a Functions as a Service system, it makes sense to create actions that do one thing and call other actions when they want other work done. As an example, in DrinksChooser, the choose action calls the incrementDrinkCount action which increments the count of the recommended drink in Redis. This way, choose doesn't have to know anything about Redis as that's not its job.

In OpenWhisk's Swift environment, there's the Whisk.invoke() method to do this. This is how we do it.

The action's name

To invoke an action we need it's fully qualified name. This is your OpenWhisk namespace concatenated with the action's name, including it's package name if it has one.

Let's start with the namespace:

Conveniently, the namespace is held in an environment variable called __OW_NAMESPACE. In Swift, we can retrieve environment variables from the ProcessInfo.processInfo.environment which will return an Optional String. As we're lazy, we convert the Optional to a concrete String using ?? "". In a proper application, we'd implement Swift's error handling and do it properly.

Note that the namespace doesn't start with a leading /, but our fully qualified action name does, so we create our action name like this:

The action's name is incrementDrinkCount and it's in the DC package, so we add those in to create our action name.

Invoke the action

Invoking the action is easy enough:

We call Whisk.invoke() with our action name and a dictionary of parameters if we have any. In our case, we pass in the name of the drink who's count we want to increment.

The action is executed and the result is returned as a dictionary of type [String:Any].

Data returned from Whisk.invoke()

You get a dictionary back from Whisk.invoke() with lots of interesting information:

The interesting information is in the response dictionary and the first thing to check is the success key which is a boolean and and so is either true or false:

As you can see, we have to downcast a lot

As the type of the dictionary is [String:Any], we have to downcast all the time!

To make this easier, we can use the SwiftyJSON library which handles the casting for us:

This becomes even more useful as we delve deeper into a nested dictionary!

Passing secrets to your OpenWhisk action

There is only one way to pass data into your OpenWhisk action: parameters. Your function receives a dictionary of parameters and returns one. Let's explore how you can set these parameters.

Parameters passed to the action

You can see this by creating this action:


Add the action to OpenWhisk with: wsk action update params params.swift -a web-export true and test:

If you use a Web Action (unauthenticated HTTP requests), then OpenWhisk will add some additional parameters related to the request:

From now on, I'll skip the __ow_ parameters from the output I display as they are just noise in the context of this article.

Default parameters

You can also set default parameters that are always passed to an action. This is done when you update or create an action by using the -p flag:

We have now added the default parameter foo to our list of args as you can see:

We can therefore use default parameters to pass secrets such as Redis credentials to our action which is a very useful thing to be able to do! You can also add default parameters to packages using the same -p flag which is really useful when we have a group of actions that need the same credentials.

Overriding default parmeters

One problem however is that the defaults can be overridden:

Sometimes, this could be really useful behaviour, for example the canonical hello world action could have a default paramter of name set to world which is then overridden when calling the action. However, if the default parameters contain credentials or other operational data, then we don't want them overridden as it lead to some unexpected behaviour.

The "final" annotation

To prevent overriding of default parameters, we use the final annotation.

OpenWhisk actions can be annotated using the -a flag when creating and updating. We've already used the web-export annotation to inform OpenWhisk that this action is accessible to the WebActions system and so can be called without authorisation.

The final annotation makes all the parameters that are already defined on the action immutable. This means that they cannot be overridden.

To do this, we pass in -a final true when we update/create our action:

When we now call our action, we get an error:

Problem solved!

There's only one caveat. As of the time of writing, if you invoke the action using the authenticated API (i.e. via the wsk tool or a POST request with the Authorisation header), then the final annotation is ignored. I understand that this will change in the future.


The argument dictionary that you receive into your action is the only way to pass credential data into the action and so the best way to do this is with default parameters on the action or package and then setting the final annotation to true.

You may also want to consider using a JSON dictionary for all your config data, so that there's only one parameter in the argument's list that will clash with anything passed in by the action's callers.

Quick tip: OpenWhisk autocompletion

I've just discovered how to enable Bash autocompletion for the wsk command line tool!

$ cd /usr/local/bin
$ wsk sdk install bashauto

This will create a file called in your /usr/local/bin directory.

Now, source this file within your .bash_profile or equivalent:

$ echo -e "\n# OpenWhisk autocompletion\nsource ~/bin/" >> ~/.bash_profile

Start a new terminal window, (or source ~/.bash_profile in your current one) and you can now press the tab key after typing wsk to see the available options.