Technology Tales

Adventures & experiences in contemporary technology

Running SQL scripts with MySQL

23rd September 2007

Here’s another of those little things that you forget if you aren’t using them every day: running MySQL scripts using the Windows command line. Yes, you can also run SQL commands interactively but there’s a certain convenience about scripts. I am putting an example here so that it can be found again easily:

mysql -u username -p databasename < script.sql

I wouldn’t be at all surprised if the same line worked under Linux and UNIX but I haven’t needed to give it a try.

WordPress database error: [Can’t create table … (errno: 121)]

22nd September 2007

I was trying to upgrade one of my test blogs to WordPress 2.3 RC1 when I got error messages like the above littering the screen during the installation. The PHP functions mysql_noerr (or mysqli_noerr) and mysql_error (or mysqli_error) seem to have been busily at work. These messages told me that the upgrade hadn’t worked so I went off googling as usual and perusing the MySQL tomes in my possession. Not for the first time, the web yielded nothing but dross and, in the end, I tried deleting the relevant database and starting from scratch again. That resolved the problem.

The reason for database deletion sorting things out is that MySQL got confused when there was a mismatch between what was in the file system and what its InnoDB table was saying. I think that the cause of this was that I naively copied in tables using Windows Explorer. Deleting the database cleared the air and all was well once I allowed WordPress to do the needful in its time honoured way. Another lesson learned for the future and I wish that frustration wasn’t part of the learning experience too…

Twelve months of WordPress

18th September 2007

It was on this date several months ago that I moved my hillwalking blog into the world of WordPress. It’s a self-hosted WordPress instance and has been for all that time. Because of the move, I was taken into the world of MySQL, a world that intrudes still to this day. Most of the time for the migration was spent setting up a theme to fit in with the rest of the website of which it forms a very important part. The matter of importing all the old posts took up time too, especially when it came to fixing glitches with the XML import. Still, it was all done within a weekend and my website hasn’t looked back since. More people now have a reason to visit, and the blog may even have surpassed the photo gallery as the site’s main attraction. I kept up the old blog for a while but have dispensed with that by now; I was keeping both blogs synchronised and that became a tiring manoeuvre. Another upshot of the whole experience is that I have become more aware of the UK outdoor scene and learnt a thing or two too. It might even have encouraged me to go from day tripping to multi-day backpacking, a real-world change that is well removed from the world of technology.

TechnologyTales.com has moved server…

18th June 2007

The last week on WordPress.com has featured decisions that some may question, to say the least. For instance, the death of Feed Stats was something that I saw as a retrograde step. Next, I saw a slight change to the appearance of my blog that led me to take full control of the situation; a previous discussion with WordPress.com staff about changing a theme to the way that I wanted it to be got me nowhere so I wasn’t about to try again… I might miss having advance notice of where WordPress goes next but I am not sure that I want to be a guinea pig either.

So, you now find this hosted by Streamline.net and, apart from a lengthy hiatus (at least, it felt like forever thanks to recurring thoughts of PageRank loss; yes, I know that sounds silly…) spent awaiting FTP access to be sorted out, the set up ran smoothly enough; I think that my request for transfer from a Windows server to a Linux one might have been the cause of the delay. Setting up a MySQL database was a breeze and it’s part of the package too. In fact, the Pro package that I am using is £31.99 ex VAT per annum, not bad at all, and, teething problems out of the way, it’ll be interesting to see how things will fare from now on.

Having a self-hosted WordPress installation is nothing new to me since I do it for my hillwalking blog and everything came together very quickly this time around. knowing what plug-ins add real value was a definite help: Bad Behaviour and Ultimate GA headed the list but a Spam Karma 2 is another option. Feedburner integration is another potential item on the configuration list. I have stuck with the Andreas09 theme but am unsure as to how far I will take customising it; it’s not a high priority right now. In fact, I may find another three-column layout that takes my eye now that I am not limited to the offerings available from WordPress.com.

The posts from the old blog have arrived over here and that seems to have worked fine first time around thanks the WordPress’ import/export functionality. I still have to get the images over but there’ll be time for that yet. Another thing on the to do list is to transfer over the links and set up any text and RSS widgets that have been a feature of the previous rendition of the blog. That means linking to wp-links-opml.php on the old blog using the import links functionality. There is no other link export function and you can only import into a single category; a link import/export plug-in that retains the link category information would be a bonus.

While I wonder if I have made a rod for my own back with my having two self-hosted WordPress blogs, it does feel good to have more control and it’ll be interesting where where this journey goes next.

Exploring AJAX

7th June 2007

When I first started it, my online photo gallery started out simply as a set of interlinked HTML pages. Over time, I discovered frames (yes, them!) and started to make use of JavaScript to make the slideshows slicker. In those days, I was working off free webspace provided by my ISP and client-side scripting was the only tool that I had for enhancing functionality. Having tired of the vagaries of client-side scripting -- the browser wars were in full swing and incompatibilities reigned supreme, I went with paid hosting in order to get access to tools like Perl and PHP for server-side processing; their flexibility compared to JavaScript was a breath of fresh air to me and I am still a fan of the server-side approach.

The journey that I have just described is one that I now know was followed by a lot of website builders around the same time. Nevertheless, I have still held onto JavaScript for some things, particularly for updating the DOM as part of making the pages more responsive to user interaction. In the last few years, a hybrid approach has been gaining currency: AJAX. This offers the ability to modify parts of a page without needing to reload the whole thing and that has generated a considerable amount of interest among web application developers.

The world of AJAX is evidently a complex one though the underlying principle can be explained in simple terms. The essential idea is that you use JavaScript to call a server-side script, PHP is as good an example as any, that returns either text or XML that can be used to update part of a web page in situ without the need to reload it as per the traditional way of working. It has opened up so many possibilities from the interface design point of view that AJAX became a hot topic that still receives much attention today. One bugbear is efficiency because I have seem an AJAX application lock up a PC with a little help from IE6. There will always remain times where server-side processing is the best route and that needs to balanced against the client-side and vice versa.

Like its forbear DHTML, AJAX is really a development approach using a number of different technologies in combination. The DHTML elements such as (X)HTML, CSS, DOM and JavaScript are very much part of the AJAX world but server-side elements such as HTTP, PHP, MySQL and XML are also very much part of the fabric of the landscape. In fact, while AJAX can use plain text as the transfer format, XML is the one implied by the AJAX acronym and XSLT is used to transform XML in HTML. However, AJAX is not limited to the aforementioned technologies; for instance, I cannot see why Perl cannot play a role in place of PHP and ASP can be used for the same things.

Even in these standards-compliant days, browser support for AJAX remains diverse, to say the least, and it is akin to having MSIE in one corner and the rest in the other. Mind you, Microsoft did introduce the tools in the first place but they used ActiveX and Mozilla created a new object type rather than continue this method of operation. Given that ActiveX is a Windows-only technology, I can see why Mozilla did what they did and it is a sensible decision. In fact, IE7 appears to have picked up the Mozilla way of doing things.

Even with the apparent convergence, there will continue to be a need for the AJAX JavaScript libraries that are currently out there. Incidentally, Adobe has included one called Spry with Dreamweaver CS3. Nevertheless, I still like to find out how things work at the basic level and feel somewhat obstructed when I cannot do this. I remember perusing Wrox’s Professional AJAX and found the constant references to the associated function library rather grating; the writing style didn’t help either.

My taking a more granular approach has got me reading SAMS Teach Yourself AJAX in 10 Minutes as a means for getting my foot in the door. As with their Teach Yourself … in 24 Hours series, the title is a little misleading since there are 22 lessons of 10 minutes in duration (the 24 Hours moniker refers to there being 24 lessons, each of one hour in length). Anything composed of 10 minute lessons, even 22 of them, is never going to be comprehensive but, as a means for getting started, I have to say that the approach seems effective on the basis of this volume. It has certainly whet my appetite for giving AJAX a go and it’ll be interesting to see how things progress from here.

SQL Developer Java error

6th June 2007

I tried starting up Oracle’s SQL Developer last night so that I could add a listing if my hillwalking blog posts to my website’s site map with a spot of PHP scripting. However, all that I got was something like that which you see below:

Java Error returned on launching Oracle SQL Developer

I must confess that this one threw me. The solution, though hard to find (they often are, even with the abilities of Google) was to use a batch file called sqldeveloper.bat than you can find in the [installation directory]\sqldeveloper\bin directory. It does start the thing when all else seems to fail and got me up and running again. I did get that blog post listing added to the site map after all; Having more visibility of the MySQL tables was a definite plus point.

Oracle SQL Developer and MySQL

17th April 2007

Because of my work, I recently have had a bit of exposure to Oracle SQL Developer, which I have been using as part of application development and testing activities. For further investigation, I decided to have a copy at home for further perusal (it’s a free download) and it was with some interest that I found out that it could access MySQL databases. To do this, you need Connector/J for MySQL so that communication can occur between the two. Though you quickly notice the differences in feature sets between Oracle and MySQL, it seems a good tool for exploring MySQL data tables and issuing queries.

Oracle SQL Developer

A web development toolbox

23rd March 2007

Having been on a web-building journey from Geocities to having a website with my own domain hosted by Fasthosts, it should come as no surprise that I have encountered a number of tools and technologies over this time and that my choices and knowledge have evolved too. I’ll muse over the technologies first before going on to the tools that I use.

Technologies

XHTML

When I started building websites, HTML 4 was not long in existence and I devoured most if not all of Elizabeth Castro’s Peachpit Visual Quickstart guide to the language within a weekend. Having previously used fairly primitive WYSIWYG tools like Netscape Composer and Claris Home Page, it was an empowering experience and the first edition (it is now on its third) of Jennifer Niederst Robbins’ Web Design in a Nutshell took things much further, becoming something of a bible for a number of years.

When it first appeared, XHTML 1.0 wasn’t a major change from HTML 4, but its stricter more XML-compliant syntax was meant to point the way to the future and semantic markup was at its heart at least as much as it was for HTML 4. XHTML 2.0 is on the horizon and after the modular approach of XHTML 1.1 (which I have never used), it will be interesting to see how it develops. Nevertheless, there is a surprising development in that some people are musing over the idea of having an HTML 5. Let’s hope that the (X)HTML apple cart doesn’t get completely overturned after some years of relative stability. I still bear scars from the browser wars raging in the 1990’s and don’t want to see standards wars supplanting the relative peace that we have now. That said, I don’t mind peaceful progression.

CSS

Only seems to be coming into its own in the last few years and is truly an amazing technology in spite of the hobbles that MSIE places on our ambitions. CSS Zen Garden has been a major source of ideas; I wouldn’t have been able to customise this blog as much as I have without them. I was an early adopter of the technology and got burnt by inconsistent browser support; Netscape 4 was the proverbial bête noir back then, fulfilling the role that MSIE plays today. In those days, it was the idea of controlling text display and element backgrounds from a single place that appealed. Since then, I have progressed to using CSS to replace table-based layouts and to control element positioning. It can do more…

JavaScript

Having had a JavaScript-powered photo gallery before my current Perl-driven one, I can say that I have definitely sampled this ever-pervasive scripting language. Being a client-side language rather than a server-side one, it does place you rather at the mercy of the browser purveyors and it never ceases to amaze me that there is a buzz around AJAX because of this. In fact, the abundance of AJAX cross-browser function libraries is testimony to the need for browser-specific code. Despite my preferences for server-side scripting, I still find a use for JavaScript and its main use for me these days is to dynamically control CSS elements to do such things as control the height of a page element or whether it is shown or not. Apparently, CSS may get some dynamic capabilities in the future and reduce my dependence on JavaScript. In the meantime, Jeremy Keith’s DOM Scripting (Friends of Ed) will prove as much of an asset as it has done.

XML

These days, a lot of the raw data underlying my personal website is stored in XML. I did try to dynamically transform the display of the XML into something meaningful with CSS and XSLT when I first scaled its dizzy heights but I soon resorted to other techniques. Browser support and the complexity of what I required were the major contributors to this. The new strategy involved two different approaches. The first was to create PHP/XHTML pages from the precursor XML offline and this is how I generate the website’s directory pages. The other one is to process the XML as text to dynamically supply an XHTML page as the user visits it; this is the way that the photo gallery works.

Perl

This still powers all of my photo gallery. While thoughts of changing it all to PHP linger, there is a certain something about the Perl language that keeps it there. I suppose it is that PHP is entangled in the HTML while Perl encases the whole business and I am reasonably familiar with its syntax these days which is why it still does a lot of the data processing grunt work that I need.

PHP

PHP is everywhere these days, though it doesn’t attract quite the level of hype that used to be the case. It still appears with its sidekick MySQL in many website applications. Blogging software such as WordPress and content management systems like Drupal, Mambo and Joomla! wouldn’t exist without the pair. It appears on my website as the glue that holds my visitor directories together and is the processing engine of my WordPress blog. And if I ever get to a Drupal element to the site, by no means a foregone conclusion though I am spending a lot of time with it at the moment, PHP will continue its presence in my website scripting as it powers that too.

Applications

Macromedia HomeSite

I have a liking for hand coding, so this does most of what I need. When Macromedia (itself since taken over by Adobe, of course) took over Allaire, HomeSite sadly lost its WYSIWYG capability, but the application still soldiers on even though Dreamweaver offers a lot to code cutters these days. Nevertheless, it does have certain advantages over Dreamweaver: it is a fleeter beast to start up and colour codes Perl syntax.

Macromedia Dreamweaver

There was a time when Dreamweaver was solely a tool for visual web page development, but the advent of Dreamweaver UltraDev added server-side development capabilities to the Dreamweaver family. These days, there is only one Dreamweaver version, but UltraDev’s capabilities still live on in the latest version and I would not be surprised if they were taken further in these database-driven times.

Nowadays, Dreamweaver isn’t an application where I spend a great deal of time. In former times, when my site was made up of static HTML pages, I used Dreamweaver a lot even if its rendering capabilities were a step behind the then-current browser versions. I suppose that it didn’t fit the way in which I worked, but its template-driven workflow would have been a boon back then.

However, my move from a static site to a dynamic one, starting with my photo gallery, has meant that I haven’t used it as much since then. However, with my use of PHP/MySQL components on my site. Its server-side abilities could get the level of investigation that its PHP/MySQL capabilities allow.

Altova XMLSpy Professional

Adding MySQL databases to my web hosting costs money, not a lot but it could be spent on other (more important?) things. Hence, I use XML as the data store for my photo gallery and XML files are pre-processed into XHTML/PHP pages for my visitor directories prior to uploading onto the server.

I use XMLSpy to edit and manage the XML files that I use: its ability to view XML in grid format is a killer feature as far as I am concerned and XML validation also proves very useful; particularly with regard to ensuring that DTD’s and XML files are in step and for the correct coding of XSLT files. There are other features that I need to explore and that would also take my knowledge of the XML further to boot, not at all a bad thing.

Saxon

For processing XML into another file format such as XHTML, you need a parser and I use the free version of Saxon to do the needful, Saxonica offers commercial versions of it. There is, I believe, a parser in XMLSpy but I don’t use it because Saxon’s command line interface fits better into my workflow. This is a Perl-driven process where XML files are read and XSLT files, one per XML file, are built before both are fed to Saxon for transforming into XHTML/PHP files. It all works smoothly and updating the XML inputs is all that is required.

AceFTP

If I were looking for an FTP client now, it would be FileZilla but AceFTP has served me well over the last few years and it looks as if that will continue. It does have some extra features over FileZilla: transfers between remote sites, and scheduling, for example. I have yet to use either but they look valuable.

Hutmil

In bygone days when I had loads of static HTML files, making changes was a bit of a chore if they affected every single file. An example is changing the year on the copyright message on the page footers. Hutmil, which I found on a magazine cover-mounted disc, was a great time saver in those days. Today, I achieve this by putting this information into a single file and getting Perl or PHP to import that when building the page. The same “define once, use anywhere” approach underlies CSS as well and scripting very usefully allows you to take that into the XHTML domain.

Apache

Apache is ubiquitous these days and both the online and offline versions of my site are powered by it. It does require some configuration but it is a very powerful piece of kit. The introduction of 2.2.x meant a big change in the way that configuration files were modularised and while most things were contained in a single file for 2.0.x, the settings are broken up into different files in 2.2.x and it can take a while to find things again. Without having it on my home PC, I would not be able to use Perl, PHP or MySQL. Apart from this, I especially like its virtual site capability; very useful for offline development.

WordPress

My hosting supplier offers blogs on Blogware, but that didn’t offer the level of configuration that I would have liked. It is true that this is probably true of any host of blogs. I can’t speak for Blogger but WordPress.com does have its restrictions too. To make my hillwalking blog fit in with the appearance of my photo gallery, I went popped over to WordPress.org to download WordPress so that I could host a blog myself and have maximum control over its appearance. WordPress supports themes so I created my own and got my blog pages looking as if they are part of my website, rather than looking like something that was bolted on. Now that I think of it, what about WordPress supporting user-created themes? I support that there is the worry of insecure PHP code but what about it?

MySQL

I am between minds on whether this is a technology or a tool. SQL certainly would be a technology standard but I am not so clear on what MySQL would be. In any case, I have classed it as a tool and a very useful one at that. It is the linchpin for my WordPress blogs and, if I go for a content management system like Drupal, its role would surely grow. While I do have a lot of experience with using SAS SQL and this helps me to deal with other varieties, there is still a learning curve with MySQL that gets me heading for a good book and Kofler’s The Definitive Guide to MySQL5 (Apress) seems to perform more than adequately in this endeavour.

Paint Shop Pro

As someone who hosts an online photo gallery, it won’t come as a surprise that I have had exposure to image editors. Despite various other flirtations, Paint Shop Pro has been my tool of choice over the years, but it is now set to be usurped by a member of Adobe’s Photoshop family. Paint Shop Pro does have books devoted to it but it seems that Photoshop gets better coverage and I feel that my image processing needs to be taken up a gear, hence the potential move to Photoshop

Open source CMS options

18th March 2007

After reading an article in the latest issue of PC Plus, I got curious about the world of content management systems again. I went over to OpenSourceCMS to sample the CMS demos that they have got on there. Mambo and Joomla! are fully fledged CMS’s and look impressive too, though how they would fit into my online presence is something of an open question. I spied that PHP-Nuke uses themes so that is an attraction; I am already used to that mindset thanks to WordPress. Drupal seems to be less slick than the others but that could be an attraction of itself; it does offer themes but no rich text editing is available.

All of the above are base upon PHP/MySQL but I ignored them for some reason when I last took a look at open source CMS’s. That does seem a strange thing to do but this was a while ago and the moderate cost of adding database functionality to my website was not something that I was willing to pay, though I have done so since for HennessyBlog. I therefore ended up taking a look at Plone (built on Zope and using the Python programming language). What I was had in mind at the time was a replacement for Perl-powered photo gallery and a CMS was never going to fit the bit; it still doesn’t. In any case, I like coexistence of website components on a single server and Plone left me with the impression that it was an all or nothing affair. Things may have changed since so giving it another go remains an option.

I have now decided to take a look at Drupal but the emphasis this time is not on using it as a photo gallery platform; if I wanted that, I’d go with the API for something like Flickr or Zooomr. This time, the emphasis on using a CMS to manage the visitor information directories on my website. It does coexist with the other website components, including WordPress and the aforementioned bespoke built photo gallery. Interestingly, Drupal does offer blogging functionality if I wanted it.

Set up involved a spot of work with mySQL before moving onto other things:

mysql -u adminuserid -p /* logging in*/

create database drupal; /*creating new database*/

grant ALL on drupal.* to adminuser identified by “**********” /* granting access to new database */

quit; /* exiting */

I prefer command line working with mySQL; it is easier to see what’s going on (not wrong, hopefully). For some reason, Drupal comes only in tar.gz archives but I extracted this into the web server directory and opened up the site in Firefox. Installation only requires set up of database access and is very quick. A few things turned up in the status report that needed attention: cron, this can be run manually; activation of PHP Unicode and GD library (PHP’s gd_info function is a real help in testing this) extensions, editing of php.ini to remove commenting semicolons activated them and restarting Apache made them available; having a place to store uploads, the directory files was created.

I have since set about bending it to my will, not always an easy thing to do with software. The first thing to do was to give it a static home page. By default, Drupal places tasters for any nominated pages and stories on its home page and shows configuration instructions until you allow some content to filter through. However, adding the Front Page module allows you to override this behaviour and have something more static. It was an entry on Kehan’s Blog that set me heading in the right direction.

The next steps are be to persuade the thing to allow external links to exist in menus (patches exist but I have yet to learn how to apply them other than finding the nefarious piece of code and replacing, a considerable challenge that makes me wonder if there is not a better way to do it: with a module, perhaps?) and carry on the theme editing until it ties in with the rest of my site. Then, I’ll make the decision as to whether to replace my current work flow (Perl-powered pre-processing of XML into PHP/XHTML using XSLT and the Saxon parser followed by FTP upload to the web server) with this one. The automation of the former argues in its favour. We’ll see how things pan out…

  • All the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. As regards editorial policy, whatever appears here is entirely of my own choice and not that of any other person or organisation.

  • Please note that everything you find here is copyrighted material. The content may be available to read without charge and without advertising but it is not to be reproduced without attribution. As it happens, a number of the images are sourced from stock libraries like iStockPhoto so they certainly are not for abstraction.

  • With regards to any comments left on the site, I expect them to be civil in tone of voice and reserve the right to reject any that are either inappropriate or irrelevant. Comment review is subject to automated processing as well as manual inspection but whatever is said is the sole responsibility of the individual contributor.