Skip to content


Lesson of the day: PRINT in T-SQL has side effects

Not strictly PHP this one, but worth mentioning to the people that use SQL Server from PHP. This is an example based on a stored procedure that I spent too much time debugging today:

   IF @@ROWCOUNT = 0

This always fails--why? The PRINT statement resets the @@ROWCOUNT variable back to 0. Nasty eh?

On a related note, PRINT generates 'SUCCESS_WITH_INFO' diagnostic records (one for each print call), so you can capture the output over ODBC. A gotcha here is that, with SQL Server at least, once you start pulling diagnostic records, you must pull them all, otherwise you block your database connection, leading to invalid cursor state errors when you try to fetch.

Double bad day for PRINT in SQL Server for me.

redefining malloc() is evil

Especially when it's in an external library, like say, perl. It's a recipe for heap corruption when pointers allocated by the real malloc are passed to the redefined free and vice versa. Nasty. Whats worse is when that library redefines malloc(), free(), calloc() and realloc(), but leaves strdup() alone.

Guess how long it took to track that one down on windows, where valgrind is sorely missed? Too long, that's how long.

How did we live without valgrind!?

PDO goes stable

With the release of PHP 5.1 stable, it's time to mark PDO as stable too. I've pushed the first batch of stable PDO extensions out to PECL.

Now that PDO 1.0 is out the door, we can start looking at new features. I've put together a preliminary list of things, which can be found on the tip of the 5.1 branch.

Reasonable suggestions for additions to this list are welcomed. I'm not sure that everything on that list will make it into PDO 1.1, because I'd like to keep the development cycle shorter, rather than longer. There's always PDO 1.2 to think of, after all.

Happy PDO'ing!

Calling SQLBindParameter() to bind SQL_TIMESTAMP_STRUCT as SQL_C_TYPE_TIMESTAMP, avoiding a datetime overflow.

I've spent a lot of time with ODBC recently, working on interfacing our PostalEngine product with SQL Server. One of the things that caused me a LOT of trouble is binding date/time values into our stored procedure calls. One of the reasons that it was hard work was lack of documentation-by-example.

This particular part of the product is a SOAP server implemented using gSOAP, and since we need to be able handle people born before the 70's, we're using ISO8601 strings for datetime values rather than the unix time_t type. We parse the string (watching out for timezones) and store it into a SQL_TIMESTAMP_STRUCT and bind a pointer to that struct as an input parameter.

Our ODBC code tries to be smart by not being too smart; when we call out to the stored procedures in the database, we bind parameters using the best fitting native C types for the data we're sending in. For each parameter in the procedure, we execute the following code:

    SWORD sqltype, ctype, scale, nullable;
    UDWORD precis;
    struct odbc_param *param = &params[i];
    SQLDescribeParam(stmt->stmt, i+1, &sqltype, &precis, &scale, &nullable);
    SQLBindParameter(stmt->stmt, i+1, param->inout, ctype, sqltype,
         precis, scale, (SQLPOINTER)param->buf, param->size,

The timestamp parameters are already set up with param->buf pointing to the SQL_TIMESTAMP_STRUCT, ctype is set to SQL_C_TYPE_TIMESTAMP, and param->size is set to sizeof(SQL_TIMESTAMP_STRUCT).

When this is run, the execute fails with SQLSTATE 22008, Date time overflow. Everything looks correct in the code, and most of the values we're passing are based on the description of the parameter provided by ODBC, so what's going on? It took me several hours to figure out the answer; it's the precision field screwing things up. The driver reports a precision of 16, which is the size of the timestamp structure in bytes. However, when binding the parameter, ODBC wants the precision to reflect the number of human-readable characters; 16 is too small, so the datetime would overflow it. I found that 22 is the magic number; I now have code like this:

    SWORD sqltype, ctype, scale, nullable;
    UDWORD precis;
    struct odbc_param *param = &params[i];
    SQLDescribeParam(stmt->stmt, i+1, &sqltype, &precis, &scale, &nullable);
    if (ctype == SQL_C_TYPE_TIMESTAMP) {
        precis = 22;
    SQLBindParameter(stmt->stmt, i+1, param->inout, ctype, sqltype,
         precis, scale, (SQLPOINTER)param->buf, param->size,

and my datetime values can be bound and passed correctly.

Interestingly, you don't have this same problem when using SQLBindCol() to bind rowset cols to a SQL_TIMESTAMP_STRUCT.

So, there we have it; I posted this here in the hope that it saves someone else a few hours of scratching around in the ass-end of google's search results.

Why no ACAP?

So, I'm sitting here swearing at my "big three" applications; Gaim, Firefox and Thunderbird, because they work great if you only ever use a single machine, but are a real pain in the ass if you move between machines.

The biggest pain is Gaim; it stores its buddy list configuration in a local file, and when it starts up, it merges the buddy lists with the server side data. If you have two or more machines, only one of them will have the most up-to-date settings. If you've done a lot of reorganization to your buddy list, and switch to one of the other machines, you'll have all your buddies re-added to the server-side buddy list in their old locations, and also have the new locations from the server-side added to the local copy on disk. You need to re-organize the buddy list again. Repeat for each additional machine that you use.

Thunderbird and firefox are in a similar boat; even using IMAP, it's "impossible" (as in, too damned painful) to keep your thunderbird message filtering rules and junk mail training data synchronized. Firefox; it would be nice to sync passwords and Sage feed lists between machines.

So, why isn't there some magical way to look after this? Surprisingly, there is actually a standard protocol for exactly this purpose; it's called ACAP:

ACAP is the Application Configuration Access Protocol, an internet protocol for accessing client program options, configurations, and preference information remotely. ACAP is a solution for the problem of client mobility on the Internet. Almost all Internet applications currently store user preferences, options, server locations, and other personal data in local disk files. These leads to the unpleasant problems of users having to recreate configuration set-ups, subscription lists, addressbooks, bookmark files, folder storage locations, and so forth every time they change physical locations.

This sounds perfect, but why don't "the big three" apps support it? It might have something to do with the lack of ACAP implementations. Where are they? Is anybody working on them? Is anybody selling them? What sounds like a great idea from 1997 still isn't here with any real presence in 2005. What went wrong?

I've had a quick look at the ACAP RFC and it seems overly complicated (it's based on IMAP--urgh!) so perhaps this has something to do with it. Maybe there just wasn't enough demand for ACAP, so it got shelved.

Whatever the reason, I wonder why we don't have even a simplified ACAP-style preferences server or service today. Am I just looking in the wrong places?

Oracle's PHP Developer of the year 2005

You may remember that Oracle Magazine were asking for nominations to honour people that have worked in some Oracle-related facility over the past year. Someone nominated me for the "PHP Developer of the year" category, and I "won" the award.

I'm normally a bit sceptical of awards because they're somewhat arbitrary and tend to be rather intangible, however, Oracle Magazine have gone the whole nine yards to counter that intangibility aspect; I received this weighty chunk of glass as the physical manifestation of the award:

I suspect that I won the award as a result of my efforts in developing PDO, the PDO OCI driver and my role in the redesign of the oci8 extension. Antony Dovgal has put in a similar amount of effort (perhaps even more) in his work on Oracle support in PHP. As far as I'm concerned, Tony, both our names are on the award :)

If you're really interested, you can read the blurb from the magazine here; paraphrasing-for-print has slightly twisted my words, but it's more or less what I said.

Some observations about that chunk of glass:

  • it's difficult to photograph
  • it's invisible on my white mantelpiece
  • it's waaay too easy to get finger and palm prints all over it

Don't blog bugs, file bug reports (and read the manual before you do that)

Well, I'm disappointed again by John Lim's continual lack of a decent bug report; claiming that you lack the time while having had time to post two blog entries about it is pretty poor.

Let's see the code you're using to call into PDO, John; there's not much time before we release, and without your cooperation, the problems you're seeing won't get addressed.

I'll speculate that John's ADOdb snippet:

   $rs = $db->Execute("select * from table where a=? and b=?",array('a'=>1,'b'=>2));

is trying to bind 'a' and 'b' by parameter names, but the parameters in his query are identified only by their positions (using question marks). I wonder how that is supposed to work? Maybe he should try this:

   $rs = $db->Execute("select * from table where a=:a and b=:b",array('a'=>1,'b'=>2));

It's also worth noting (again) that PDOStatement::getColumnMeta is intentionally unimplemented on this first PDO release, hence its experimental status; in other words, don't use it. Most people don't need this kind of feature anyway.

Sun Studio 11 Compiler now available, and it's free

Sun's commercial grade compiler is now available for free on Linux (RHEL and SuSE) and Solaris platforms.

It will be interesting to see how the use of this takes off with the various OpenSource projects; the Sun compiler is pretty good, and should have some nice optimizations on AMD architectures (Sun's new favourite).

In other Solaris news, Intel Pro Wireless 2100 and 2200 chipsets are now supported on OpenSolaris, in addition to Atheros, which has been around for a little bit longer. I have the latter in my laptop and it works flawlessley.

In other-other Solaris news, Oracle announces that Solaris 10 is its preferred platform again.

Recent books

I've had a couple of longish flights recently, and opportunity for reading.

Life Expectancy is a Dean Koontz novel. I find that his books tend to be a bit cliche and predictable (but still enjoyable), but this one is different. It's not his typical horror novel; you're led through the story from the perspective of Jimmy Tock, born on the night his grandfather died. Before his death, he made 10 predictions about Jimmy, 5 of which are proven in short order and the remaining 5 are billed as terrible dates between his 20th and 30th birthdays. This is a humourous and thrilling story of a baker trying to figure out how best to tackle his fate in the face of some pretty heavy goings-on. While some parts are predictable (but again still enjoyable) there is some chain-yanking, in good humour.

Deception Point by Dan Brown. The first Dan Brown novel I've read; it's a fast paced story that ties some political interests to an important scientific discovery. Exciting, fast-paced and has a healthy dose of science in there too. Apparently, all of the cutting edge special forces gear mentioned in the novel actually exists. I enjoyed this book; not as much as I enjoyed Neal Stephensons Zodiac, but I think it's hard to come close to that one :) I can't really write too much without revealing parts of the plot that take a while to emerge; it was a good read.

Airframe by Michael Crichton. I bought this one to read on my flight back from the Zend Conference. While reading the first chapter or so, I began to wonder if this was a mistake, but quickly found that the point of the novel is not to dwell on an incident but to get to the bottom of it. The story follows Casey Singleton as she works towards this goal while trying to avoid harm from disgruntled employees at the aircraft manufacturing plant and unwanted attention from the press. Crichton does an excellent job of making the characters come to life, dosing you up with inside information about aircraft and aircraft safety along the way. It's easy to convince yourself that you've figured out the ending, but you're almost certainly wrong. It's not a plot twist so much as masterful misdirection; a damned good read that'll generally make you feel safe about flying.

Techie Books

In addition to some leisure reading, I've also read a couple of tech books in recent months. I was given a copy of Ilia Alshanetsky's book php|architect's Guide to PHP Security (signed by Ilia himself :) and also a copy of Chris Shiflett's book Essential PHP Security.

I like both books, and I'm not just saying that because the authors are friends of mine. Both books are short, with Ilia's book weighing in the heavier of the two. They're written in different styles; Chris very clearly presents the most common styles of web application attacks and explains how they work and what you should look out for, and how to defend against them. Ilia's book is more of a whirlwind exploration of a lot of different attacks coupled with (fairly specific) examples of how to counter them, while at the same time considering application usability based on his experiences with FUDForum. He also talks about the use of honeypots to draw attackers away from important or sensitive areas of your site.

Chris' book is easier to read, and although it is the shorter of the books, it will help instill the right kind of security thinking that will serve the reader very well. Ilia's book is much more comprehensive, although the very dense writing style can make it a bit harder to read and use as a reference.

If I had to recommend just one book, I'd find it hard. If really pushed, I'd probably suggest that Chris' book be recommended for more junior developers and Ilia's for more senior developers. I'd actually recommend both books to anyone who's really serious; use Chris' book as an introduction to firmly set you on the right track and then follow up with Ilia's book for some more advanced techniques.

I read SQL Server 2005 Service Broker Beta Preview on the flight back from Paris. I picked this up from the Microsoft booth at the Zend Conference. In truth, I only read it because I'd run out of reading material and it was still stashed in the side pocket of my suitcase. This book talks about the Service Broker feature in the new version of SQL Server. This is a reliable, optionally distributed, message queuing facility that ensures message ordering. You're either thinking "Big Deal", "Eh?" or "Neat". This facility is ideal for implementing workflows in a very robust manner. If you're on Windows and have a project coming up in this space, it's worth a read. It sounds like pretty neat stuff; enough so that I might even find some time to play around with it and write some examples in PHP + PDO.