Category Archives: Coding

T-SQL Pattern Matching

We were recently migrating some user records over to a new system and found that the older system had allowed a number of rogue characters into the username field (auto-generated based on firstname/surnname). So I needed to query the old SQL Server 2000 database to find any usernames that wouldn’t be accepted by the new system. At first it seemed that a huge list of LIKE tests would be required, but ideally what I needed was a way to use a simple regular expression to match any username that didn’t contain acceptable characters. The T-SQL PATINDEX allows you to do simple expression matching so I was able to pull out the records quite easily.

SELECT * FROM [usertable]
WHERE PATINDEX('%[^- a-zA-Z0-9]%', username) > 0

Bascially the pattern starts and ends with a wildcard % and then contains a range of acceptable characters, which is negated with the ^ operator, thus ensuring it only returns usernames that do not contain the acceptable characters. Since PATINDEX returns the starting position of the first match, the statement is looking for results where PATINDEX returns > 0.

This statement could also be written using basic LIKE syntax but the negation makes it confusing to read.

SELECT * FROM [usertable]
WHERE username LIKE ('%[^- a-zA-Z0-9]%')

See the PATINDEX documentation over on MSDN


PHP-SOAP -> NuSOAP decoding problems

I’ve been helping our Operations team resolve some issues integrating a Joomla front-end with the SugarCRM SOAP API. The main problem: the sugarcases Joomla component refused to return any data from Sugar. After spending some time debugging the interaction between the Joomla component’s PHP-SOAP client and Sugar’s NuSOAP server, I managed to trace the following two errors:

looks like we got no XML document

SoapFault Object *RECURSION*

by enabling the SoapClient trace ('trace' => 1) feature, I was also able to call __getLastResponse to print the HTTP response content that was being sent from Sugar. What I was seeing was a completely garbled text. I concluded this must have been some sort of encoding mismatch between the client and server, so my first step was to disable SOAP compression by removing the constructor option:

$this->sugarClient = new SoapClient(null, array(
		'location' => $this->server
		,'uri' => ''
		,'soap_version'   => SOAP_1_1
		,'trace' => 1
		,'exceptions' => 0

Immediately after doing that I was seeing valid content coming through in the response, and the Joomla component was correctly displaying data. Obviously we want to use gzip compression if Sugar and Joomla are going to be sharing large amounts of data, so I needed to get to the bottom of this. Fortunately a little more research, and I found a hint, way down in the comments (comment 45) on this nonplussed blog post.
Basically both Apache and NuSOAP were gzipping the response. NuSOAP was responding correctly to the accept-encoding header and encoding the response, but then apache was also gzipping the already gzipped content. So by the time PHP-SOAP got its hands on the response and decoded it, it still had a second layer of encoding to deal with, which it couldn’t.
To workaround this I modified nusoap.php and commented out the compression logic (30 lines in total) in the send_response() method, and I could then re-enable the compression in my SoapClient instance and everything worked as intended.


JAXB 2.1 Generating Primitive Type Getters

We’ve been using JAXB generated classes at work for quite some time without problem. Recently it became necessary to make them serializable so we could stow certain objects safely in the HttpSession for later retrieval. So a quick look around on the web revealed the following example in the Sun docs showing how to use xjc’s extension mechanism to force generated classes to implement Serializable. All good. Except as it turns out the Sun docs included the generateIsSetMethod attribute on the <jaxb:globalBindings> element: <jaxb:globalBindings generateIsSetMethod="true">. Originally I included this attribute in my external bindings file that I passed to XJC, thinking I should follow the Sun example, but it soon became clear that this wasn’t the way to go: when I swapped the newly generated classes with our previous version a colleague spotted some of our code complaining about primitive types being returned from the Getters in the generated classes.
Continue reading JAXB 2.1 Generating Primitive Type Getters