Category Archives: Web Development

Moving from Microsoft Access to MySQL

We recently decided to make a public release of an old web-based application coded in ASP (classic, using VBScript) and using Microsoft Access. In order to make this application public we need to make a few modifications, not the least of which is moving from Microsoft Access to MySQL. Using Microsoft Access on the back-end would significantly hamper the ability of the application to support concurrent users, among other issues.

The majority of the coding modifications have yet to be made, but the database switch has already occurred. In the process of moving from MS Access to MySQL I discovered a few settings that would be helpful should this action need to be performed for other applications. These settings should enable similar applications to be moved with minimal modification to the programming.

First, let’s review some settings related to the MySQL ODBC driver. The settings are relevant to all versions of the driver, but the name of the setting may be different on different versions (I’m using 5.1.6). Here are the options which should be selected:

  • Return matched rows instead of affected rows
  • Treat BIGINT columns as INT columns
  • Enable safe options

The following information relates more generally to changes that may have to be made in the code:

  • MySQL doesn’t really support server-side cursors so the ODBC drivers fakes it. This is, mostly, fine except that some properties of the Recordset object are not available (namely RecordCount). In order to get full cursor support you should change the location from the server to the client (adUseClient or the literal value 3).
  • ASP doesn’t understand non-signed integers. This causes problems when performing operations using these values unless you manually type the value in your script, e.g. scriptvar = CInt(objrs("dbcol")). The other solution is to make all integers signed. Otherwise you will see the error: Variable uses an Automation type not supported in VBScript.
  • Related to the above is the usage of values from the database in comparison functions. VBScript variables are typed (e.g. integer, string, boolean, etc.). Though you can’t specify the type during variable instantiation (with Dim) VBScript does pay attention to type when performing comparisons. When two variables of different types are compared you will get a “Type mismatch” error. The resolution is to Ctype your variables if you run into this type of error.
  • Finally, check your SQL statements for any VBA function calls. These will either have to be modified into MySQL-compatible function calls or removed from the SQL code altogether.

There are a number of issues that may be encountered when attempting to convert an ASP-based application from MS Access to MySQL. The issues addressed here are only those relevant to this particular application. Other applications may require additional or different solutions and settings.


Subversion and Third-Party Code

Often in the course of developing a project it is desirable to use code from a  third party. The main benefit, of course, is being able to add functionality without developing that functionality from scratch. Some third-party projects we have used in the past include FCKEditor (now CKEditor), the Yahoo! User Interface library, and, more recently, the CakePHP framework.

When our code is maintained in a subversion repository there are two options for including third-party code: externals and vendor branches. Continue reading Subversion and Third-Party Code


As of cakePHP you can’t use CURRENT_TIMESTAMP as the default for a column. With MySQL when the default is set to CURRENT_TIMESTAMP the current date+time is inserted for a new row that doesn’t specifically define the value of the column.

From what I can tell, when CakePHP specifies the values for all columns when it creates a new record. A column with a defined default that is not specifically set by the user is manually set by CakePHP (rather than let MySQL handle defaults upon record insertion). But CakePHP doesn’t understand the CURRENT_TIMESTAMP keyword and so treats it as a string and wraps it in quotes. This breaks the resulting INSERT statement and you receive an error:

Incorrect datetime value: ‘CURRENT_TIMESTAMP’

Interestingly, columns that are named “created” and “modified” receive special handling by CakePHP. These columns are treated like auto-update columns by CakePHP and it sets them as expected. With the special handling of these columns in mind it is possible to get around the CURRENT_TIMESTAMP bug by following the recommended settings for created/modified columns, i.e. specifying the field as DATETIME with a default of NULL. CakePHP will automatically update the columns when inserting/updating records.


Authentication & Authorization with Scaffolding

Though scaffolding is not recommended for production sites, I’ve found it quite handy when just getting started. Unfortunately, it doesn’t appear that the authentication/authorization (auth^2) mechanism works with scaffolding. You can, however, get auth*2 working manually with just a few lines of code.

First, follow the steps of the Simple Acl controlled Application tutorial from the CakePHP cookbook up to the section on logging in.

Next, we need to insert the code that updates the ARO when a user is added or edited. Normally you would place this code in your add/edit action in the users controller, but for scaffolded actions we’ll use another callback function. Add the following code to your users controller:

function _afterScaffoldSave($action) {
  $aro =& $this->Acl->Aro;
  $user = $aro->findByForeignKeyAndModel($this->data['User']['id'], 'User');
  $group = $aro->findByForeignKeyAndModel($this->data['User']['group_id'], 'Group');
  $aro->id = $user['Aro']['id'];
  $aro->save(array('parent_id' => $group['Aro']['id']));
  return TRUE;

Note: for scaffold callbacks you must return TRUE; or the scaffold will not finish building the page.

The above code has been modified from the original in the tutorial, which included a conditional that checked for a change in the user’s group. As far as I can tell scaffolding causes CakePHP to return only the updated record (even when using the _beforeScaffold method) so you’re unable to compare the old and new values. As a result you have to update the ARO with every update, even if the user’s group is not updated.

Finally, for scaffolded actions we need a way to determine if the user is authorized. The AuthComponent has all the functionality we need. Add the following function to any controller using scaffolding that needs auth*2:

function _beforeScaffold($action) {
  if ($this->Auth->user() == NULL && !in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions)) {
    $this->Session->write('Auth.redirect', '/' . $this->name . '/' . $action);
    $this->Auth->loginRedirect = array('controller' => $this->name, 'action' => $action);
    $this->redirect($this->Auth->loginAction, NULL, TRUE);
    return FALSE;
  } else if (!in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions) && $this->Auth->user() !== NULL && !$this->Acl->check($this->Auth->user(),$this->Auth->action())) {
    $url = '/' . implode('/',$this->Auth->loginAction) == $this->referer() ? '/' : $this->referer();
    $this->Session->setFlash('You do not have permission to perform that action.');
    $this->redirect($url, NULL, TRUE);

This function checks to see if the user is logged in when accessing restricted actions. If not, the user is redirected to the login page. If so, and if the user is attempting to access a page for which he has no permissions, then the user is bounced back to the referring page.

Of course, you can skip all this if you build a skeleton CRUD using cake bake and specify not to use scaffolding.

Updates for IE8

With the release of IE8 getting closer I took a moment to check out our web site and Benchmarks Online in the new browser. As I suspected, the incompatibilities on our web site were of the same ilk as when IE7 was released and required minor updates. The updates to Benchmarks were also fairly minor, though did require a bit of effort to track down. Luckily IE8 includes developer tools right in the browser, making debugging web content much easier.

While I was at it, I decided to see how the JS+CSS was working in Safari (latest version on Windows and Mac) and Opera (latest versions on Windows). There were some minor issues with these browsers that I fixed as well. Quickly, Safari and Opera both were having trouble displaying the bullets in my ordered list when the pseudo multi-column-styling was applied. The odd thing is that the numbers were there, just not visible until an element was forced on top of them.

Finally, I decided to update the multi-column list code on the web site to that used in Benchmarks (which is more robust). I did run across one problem in the update that I may have to investigate further. Essentially, the script was not correctly positioning the second+ columns if a margin was specified on the container OL/UL.

Excel drops empty columns on CSV export

I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel  and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.

This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:

Put a formula that evaluates to empty (=”” ) in the last column of  the rows that are empty:

  1. select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
  2. type the formula: =””
  3. hit ctrl-enter

Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.

Internet Explorer blocks Firefox downloads

When designing the school reports I decided to use a GET request on form submission so that it would be easier to bookmark or share a report. I knew at the time that using GET might cause problems down the road, but the utility/programming trade-off seemed worthwhile.

Long URLs

The main problem with GET is that the total size a URL is allowed to be (and thus the amount of data passed) is fairly limited when compared to POST. The exact limit varies by browser and web server, but a practical limit is the lowest limit across platforms. In this case that is 2083 characters in Internet Explorer.

Since each report has to pass the list of included packets and items, the URL could easily surpass this limit. Recently one user generated a report that went over the IE limit. Figuring this out, though, proved to be somewhat difficult.

Held up at security

The user in question was using Firefox to access the utility. The report in question was generating and displaying just fine. However, when the user tried to download the report an error would result. It seemed odd that the browser would display the report on screen without issue then error out when attempting to download the same report.

I tried many options, but no matter what I did I was unable to download the report in Firefox. Next I tried the same report in IE and the situation started to clarify. IE would not even display the report. So now I was beginning to think that maybe the GET request was causing problems. Still it was difficult to fathom Firefox displaying the report on screen but refusing to download it.

A little web research finally clarified everything. Firefox attempts to honor the Windows Internet security zone settings for downloaded files. In order to apply the security zone settings Windows has to pass the URL to the IE engine for analysis. Firefox allows URLs of significantly larger size than IE, so the same URL that displays correctly on Firefox will thus cause IE to error out. Since Firefox attempts to honors the security settings, when IE coughs up a hairball due to the URL length the download is aborted.

Solving the problem

Initially I thought to just shorten the request by modifying the variable names on the form. This worked well enough for the problem at hand, but I could easily see having to address the problem again in the future. In the end I decided to modify the code behind the page so that the report parameters are submitted by POST instead of GET. It required more work up front, but will prevent this problem from cropping up in the future.

Plus, the utility has an option to save reports, so bookmarking/sharing is not as much of an issue.


CakePHP, Apache, and Ampersands

I’m still learning CakePHP. My most recent experimentation is with forms and URL redirecting. In the process I noticed that if the URL includes an ampersand, even in URL-encoded form, CakePHP will read in the URL incorrectly.

In my case, I was taking a form submission and redirecting it to a new page, appending the submitted data as URL parameters (e.g. of the form /controller/action/name:value). CakePHP handles ampersand in POST-submitted forms just fine, but during the redirect I noticed that the value of the parameter was cut off at the ampersand. For example, I’m creating a search form for a list of items (located at /items/index). When you submit the form with a value of “ball & chain” the controller sees it just fine. The form is submitted to the search action (/items/search) which takes the values, constructs a new URL, and redirects back to the item list (/items/index/Search.keywords:ball+%26+chain). However, the index action only sees “ball ” … the rest of the value is assumed to be additional key/value pairs in the querystring.

This problem seems like a pretty major issue to me. A bug report has already been filed, but I don’t believe it will be addressed anytime soon. And for good reason, the bug is actually in Apache’s mod_rewrite module rather than in CakePHP. When mod_rewrite processes a URL it unescapes any escaped characters (newer releases unescape to two levels). What this means for CakePHP is that any escaped characters in the URL become normal characters in the querystring (%26 becomes &).

The best overview of the problem has a good work-around, but it requires modification of the core CakePHP scripts. I’ll leave that to the experts. On our install I’ve found that a triple-escaped value will only be unescaped twice, resulting in the proper escaping after being parsed by mod_rewrite.


Your float is showing

Float styling is a useful aspect of CSS … until it’s not. When a floated element occurs inside a container and you want it to be wholly in that container you have to resort to hacks to do so. Or do you? Never content to assume that a question has been resolved, I decided to see if anyone had come up with a new method of clearing a float. Lo and behold a solution has emerged, and the solution is so simple as to defy logic: apply overflow: auto to the container element. I’ll leave the full explanation (with examples, alternatives, and warnings) to the experts.

Of course, there can be advantages to the default float rendering. Most notably I found it useful for creating some nice formatting around blockquotes, such as in our textbook analysis report.