DB2 is so lame

So I’m trying to change the size of a column in DB2. Nothing fancy, just expand a varchar from 100 to 200 and in DB2 Control Center I get the following message:

The current column attribute change may take some time to complete, as it requires re-creating the table, loading the data, and restoring the dependencies.

This table is approximately 0.00390625 Mb in size. The new table will be created before the existing table is removed.

Click ‘Related Objects…’ for more detail.

I find it crazy that MySQL can do this in a heartbeat and a commercial database like DB2 needs to jump through the hoops of dropping, backing up data, recreating data, deleting the backup and God knows what else.

Lame.

Advertisements

Using SQL queries to read Excel files in macros

I’m doing my Masters in Geographic Information Systems at U of T and found the need to do this in my research.

You can treat Excel sheets as tables and perform SELECTs and JOINS like you would against a regular database. The language is Visual Basic (VBA to be exact). You can put the following code inside a Macro Sub. NAICS_CODE (Row 1) is a heading of one of the columns in the Results worksheet.

I did need to add the Microsoft ActiveX Data Objects 6.0 Library by going to Tools -> References in the VBA Project (the program that opens up when you edit a macro).

    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As New ADODB.Recordset

    With cn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=C:\Users\zarar\Documents\researchdata.xls;" & _
            "Extended Properties=Excel 8.0;"
        .Open
    End With
    
    'Store all naicsCodes in an array
    Dim naicsCodes() As String
    'Get all distinct NAICS codes
    Set rs = cn.Execute("SELECT DISTINCT NAICS_CODE FROM [Results$]")
    Dim i
    i = 0
    Do Until rs.EOF
        ReDim Preserve naicsCodes(i + 1)
        naicsCodes(i) = rs.Fields("NAICS_CODE")
        i = i + 1
        rs.MoveNext
    Loop
    rs.Close
    cn.Close

What files changed in WordPress 2.8.4? The proper way to upgrade patches

I run raptorsrepublic.com which uses WordPress with some pretty heavy customizations here and there so every time WordPress releases an upgrade I’m very wary of doing it because I just don’t know how it’ll effect the platform. Partial file copying with FTP can really throw you a curveball and if one file gets corrupted it’ll take you hours to find out which one.

I think the best way of upgrading WordPress installations is by only copying files that changed over patch releases (e.g.: 2.8.2, 2.8.3 and 2.8.4). If there’s a database change things get a little more complicated but in most patch releases that’s not the case.

The way you find out what files were changed is by accessing the WordPress SVN repository and running the svn diff command. Here is the commands one need to run and the output that follows it. I’m running this on a Linux box with SVN installed. You could also do it in windows if you download the binaries.

svn diff --summarize http://core.svn.wordpress.org/tags/2.8.3/ http://core.svn.wordpress.org/tags/2.8.4/
M      http://core.svn.wordpress.org/tags/2.8.4/wp-login.php
M      http://core.svn.wordpress.org/tags/2.8.4/wp-includes/version.php
M      http://core.svn.wordpress.org/tags/2.8.4/readme.html

This means three files changed between the two versions as M stands for modified. That value could also be A for added and D for deleted. If you’d like to see what the actual changes were just get rid of the --summarize part. I copied these three files over to my WordPress installation and I’m done!

Integrating vBulletin with a WordPress theme

OK, so you have a WordPress blog and are thinking about adding a vBulletin message board to it and want it to appear seamlessly within your WP theme. This post will show you how to do it.

The way you go about accomplishing this is by creating two Plugins under vBulletin. Before we do anything we have to make sure that the Plugin/Hook system is enabled. For this select vBulletin Options under the vBulletin Options from the control panel. Select the Plugin/Hook System option in the select list and make sure its enabled. Once you’ve done that, you’ll need to create a couple plugins. Click on the Add New Plugin option under the Plugins & Products on the left hand side of the control panel. Create a plugin with the Hook Location specified as global_start and the default execution order. Give it the title of WP Header and in the Plugin PHP Code field, write the following:

ob_start();
   include('../path/to/wp-load.php');
   include('../blog/wp-content/themes/sandbox/header.php');
   $wp_header = ob_get_contents();
ob_end_clean();

Now let’s examine what I did here. Bascially, I’m invoking two WordPress PHP files, wp-load.php from the main installation and header.php from my theme’s directory. I capture the content of it in the $wp_header variable which we’ll use later.

Make sure you change the plugin to be active by clicking on the Yes radio button and save the plugin.

Now we’ll create another plugin for the footer. Once again, specify the Hook Location as global_start, give it the title of WP Footer and select the default execution order. In the Plugin PHP Code field write the following:

ob_start();
   include('../blog/wp-content/themes/sandbox/footer.php');
   $wp_footer = ob_get_contents();
ob_end_clean();

In this plugin we’re capturing the theme’s footer.php in the $wp_footer variable.

Now we need to insert the two pieces of data we captured into the vBulletin theme. For that let’s go to Style Manager under the Styles & Templates group in the left control panel. Once there, select Edit Templates from the drop down list which should give you a listing of all the editable files in your theme. Double-click on header from the list and simply add the following line of code as the first line in the file:

$wp_header

Save it and go back to the list of editable files and edit footer. Make sure the following line of code is the last line in the file:

$wp_footer

Save it and you’re done.

Well, almost. Depending on the version of vBulletin you have you might get a PHP error which says something like “sanitize_url function has already been defined”. Check the vBulletin file its happening in, download it and rename both occurrences of the function to something like vb_sanitize_url and now you’re done.

Conditionally printing HTML tags in JSF Facelets

This took me a bit to figure out so I thought I’d post it.

Say you’re using JSF and want to conditionally print some HTML tags in your Facelet that could, technically speaking, result in non well-formed markup. You can’t really do it. Printing something like is illegal as it’ll complain about the < and > characters being in there. Using the tag doesn’t work either, even if you wrap the content in a CDATA element. Problem is that it’s preventing you from writing non well-formed markup in any which way.

One of the solutions is to force the rendering kit to not analyze the output for “well-formedness” by passing the HTML markup in a variable.

In your facelet.xhtml

<h:outputText escape="false" 
              rendered="#{someCondition}" 
              value="#{messages&#91;'lessThan'&#93;.concat('ul').concat(messages&#91;'greaterThan'&#93;)}"/>

In messages.properties

lessThan=<
greaterThan=>