Custom Software Development

UPDATE 3/25/09: Paul Robertson from the AIR team stopped by and writes that declaring your SQLite column affinity (ie column type) as “DATE” will instruct AIR to handle all date conversions for you automatically.  The problems I experienced mainly surfaced in a DataGrid when using a DateField and I have not had a chance to poke around with that yet.  Another approach is to extend DateField and override the “data” setter which is how the DataGrid supplies item editors with their value.  Then you can deal with casting issues manually, however that may be more of a hack.  The article below still has some good information that is still helpful for dealing with Dates in AIR/SQLite.

Working with SQLite and Flex/AIR Date values can be tricky and various caveats are not particularly well documented. The confusion (for me) is that ActionScript is loaded with UTC functions, and SQLite will happily insert them into DATETIME columns. Everything appears fine, however SQLite does not actually recognize this format as a Date and treats it as plain text. You have no way to see this happened until you try to apply some date formatting functions and notice SQLite returning NULL. SQLite is so lax about data integrity that you can insert anything into any column type and will never receive any warnings. AIR, though, will attempt to cast values behind the scenes based on column types and so you will run into ‘Invalid Date’ errors and weird glitches when attempting to update data.

The magic solution is the Julian Date Format which both SQLite and AIR recognize as a date value. This is somewhat surprising as ActionScript has no built-in support for outputting Julian dates. If you’re like me, you may have already hacked up workarounds using int fields with timestamps, however your matching ActionScript class properties have to be hacked to match, and the hacking can trickle down throughout your code. This also prevents you from using the SQLStatement.itemClass functionality, which is nice when using Cairngorm, DAOs, value objects, etc.

To avoid the pain follow these rules when working with dates:

1. If you want a strongly typed Date field in AIR, the relevant SQLite column must be defined as DATETIME.  The interesting thing about this is that DATETIME is not technically a recognized SQLite column type and according to the SQLite docs it will be considered numeric.  But AIR is obviously looking at the column definition somewhere in the framework because it will refuse to automatically cast any value where the column type is not DATETIME.

2. Whenever inserting or updating DATETIME fields, you must store it in Julian format (or NULL).  SQLite will happily accept many common date formats. However AIR will behave inconsistently. Here is how to insert Julian dates in a variety of ways:

Inserting a Julian date manually via SQL:

To do this, simply have SQLite format your date value to Julian format using ‘%J’

UPDATE my_table SET my_column = STRFTIME('%J','2008-01-02 03:04:05')

Inserting a Julian date via AIR (with parameters):

Parameters are the best way to build SQL statements as you can use strongly typed Date variables and AIR will deal with the formatting for you.

statement.text = "UPDATE my_table SET my_column = :my_value";
statement.parameters[":my_value"] = new Date(2008,0,2,3,4,5); // Jan 02, 2008 03:04:05

Inserting a Julian date via AIR (without parameters):

If you are not using parameters, you have to pre-format the date into something that SQLite can parse. This is surprisingly obnoxious and requires you to write a couple of helper functions. (Note – if you know of an easier way to do this, please post a comment.)

public function lpad(original:Object, length:int, pad:String):String
{
var padded:String = original == null ? "" : original.toString();
while (padded.length < length) padded = pad + padded;
return padded;
}
 
public function toSqlDate(dateVal:Date):String
{
return dateVal == null ? null : dateVal.fullYear
+ "-" + lpad(dateVal.month + 1,2,'0')  // month is zero-based
+ "-" + lpad(dateVal.date,2,'0')
+ " " + lpad(dateVal.hours,2,'0')
+ ":" + lpad(dateVal.minutes,2,'0')
+ ":" + lpad(dateVal.seconds,2,'0')
;
}
 
var myDate:Date = new Date(2008,0,2,3,4,5); // Jan 02, 2008 03:04:05
statement.text = "UPDATE my_table SET my_column = strftime('%J','" + toSqlDate(myDate) + "')";

Fudging data to work around AIRs validation

If you absolutely refuse to change your schema (for example you insist on using timestamps, or you have to maintain compatibility with other clients) you can get AIR to play along during READ operations by altering your select statement like so:

SELECT STRFTIME('%J',my_column) AS my_column FROM my_table

This does assume that the data is in a format that SQLite recognizes as a date. If SQLite can’t parse the date value, then it will just return NULL. For hilarity sake, you can also use this ridiculous date format which surprisingly works with AIR. A word of warning about this workaround is that, even though you will be able to read data, you may not be able to update data via SQLCommand parameters if your column types are DATETIME because AIR will complain about an invalid date (see errors below). You will have either have to write your own SQL statements without parameters or else change your column types to int or varchar.

Formatting a Julian date manually in SQL so you can read it:

Julian values are great and all that, but it’s pretty much impossible to eyeball them when you’re working at the command line. SQLite recognizes Julian formatting as a valid date, so you can use the STRFTIME function to format and output it any way you like. Below is a simple example that is easier to read:

SELECT STRFTIME('%Y-%m-%d %H:%M:%S',my_column) AS my_column_formatted FROM my_table

Common errors that occur while working with dates:

Invalid Date

You may see this in a DataGrid instead of the expected date value. This is because you have a DATETIME column in SQLite, however the value is not in Julian format. Even though SQLite may recognize it as a date value, AIR does not. The solution is to clean your data so that all dates are Julian format, or alternatively change the column type to VARCHAR.

‘Error #3115: SQL Error.’, details:’could not convert string value to date’

This error occurs when you try to update a record that has one or more DATETIME columns that do not have the date stored in Julian format. The weird part is that even if you are not touching that specific column in your insert/update statement – AIR will still validate the Date and throw this error. The solution is to clean your data so that all dates are Julian format, or alternatively change the column type to VARCHAR.

If you have any tips or corrections please post a comment and I’ll incorporate it into the article.

After Installing PHP 5 using the Windows installer on Windows 2003 you may find that IIS displays a “Page Not Found” 404 error for every .php page. This is a perplexing error because is it not actually a real 404 error. The file is really there, but IIS is unable to process it based on how the installer configures the extension mapping. Instead of providing any useful information or even a 500 error; however, IIS throws out a 404.

Steps to Fix the Problem:

Before you troubleshoot further, you may want to read #5 about how the Application Pool effects PHP configuration changes.

1. Replace the old DOS format path to the PHP Executable with a full path w/ quotes
2. Move php.ini to C:\Windows
3. Edit php.ini to set cgi.force_redirect = 0 (only necessary for CGI mode)
4. Make sure php-cgi.exe and/or phpisapi.dll are included in Web Service Extensions
5. Recycle the Application Pool

1. Replace the old DOS Path Format

The default path to PHP is C:\Program Files\PHP. When creating the IIS extention mapping, the PHP Installer uses the old DOS format path to the PHP ISAPI or CGI executable such as “C:\PROGRA~1\PHP\PHP5IS~1.DLL”. IIS does not seem to like this format.

One simple solution for this is to simply reinstall PHP to C:\PHP, or another location that doesn’t use long filenames. This will generally save you a lot of grief as PHP and its installer do not seem to handle windows long file names consistently.

If you prefer to keep things in Program files, go to the IIS Extension Mapping screen and locate the value for “.php” (See screenshot above). Click the browse button, select the executable and put quotes around it the entire path. So the value for this field should look like this “C:\Program Files\PHP\php5isapi.dll” (WITH the quotes around it). If you have installed PHP in CGI mode instead, the file name would be php-cgi.exe instead of php5isapi.dll

While you’re at it you may want to check the box for “Verify that file exists” as well. This allows IIS to handle actual missing pages (ie broken links) and return a 404. Otherwise IIS will just pass the request to PHP without verifying the .php file really exists and PHP throws a CGI error when the file isn’t found. People seem to have inconsistent results with this setting.

If you recycle the app pool at this point (see step #5) you *may* solve the 404 error depending on what extensions you installed or whether you had re-run the installer and changed stuff. However, you may still have issues changing php.ini settings in which case keep reading.

2. Copy php.ini to C:\Windows

The PHP installer creates a php.ini file for you based on your selections in the setup process. However the installer saves the file in C:\Program Files\PHP. The problem is that PHP is looking in C:\Windows for the .ini file. So, you need to move the file php.ini to C:\Windows. This may be confusing because PHP seems to run fine. But if you look closely at the phpinfo() output, you may find that php.ini file is not being loaded and all default settings are being used.

One of the critical things when configuring PHP is to actually edit the .ini file that is being used by PHP. The installer creates a worthless file in a location that PHP won’t read and so you may waste a lot of time editing this file. PHP pretty much universally will check the Windows folder for php.ini on all varieties of Windows, so my advice is to use that location and delete any other php.ini files that are hanging around..

3. Set cgi.force_redirect = 0 (Only necessary for CGI mode)

Various people report that you need to edit php.ini and set:

cgi.force_redirect = 0

I haven’t noticed this setting having any effect on my installations, but many people claim it is necessary when you are running PHP in CGI mode. This setting will have no effect if you are running in ISAPI mode.

4. Make sure php-cgi.exe and/or phpisapi.dll are enabled in Web Service Extensions

In IIS Manager, click on “Web Service Extensions” This includes a list of all dll and exe files that IIS is allowed to execute. The extension mapping that is specified for .php files must also be added here. I prefer to just add both php-cgi.exe as well as php5isapi.dll here and enable them both so that if I don’t need to worry about it again.

If the handler is already in the list, make sure that it is “enabled” as well. The enabled services have a green overlay on the service icon.

Lastly, confirm that the file path is exactly the same here as it is in your .php extension mapping configuration. That includes the dos path formatting. If you use junctions, you need to be using the same path in both places. IIS seems to check the path rather than the executable. It will not recognize if you use a slightly different path, even if they both point to the same executable.

5. Recycle the Application Pool

In order for any PHP configuration changes to take effect in Windows 2003, you need to recycle the Application Pool. If you have made changes to php.ini and they don’t seem to take effect, this is likely the reason. Among other things, the pool caches PHP settings and you need to clear it before new configuration settings will take effect. You’ll read people telling you to restart IIS (which doesn’t recycle the app pool) or even reboot your machine (which is overkill). You don’t need to do either of those. Just right-click on the DefaultAppPool in the IIS management interface and “Recycle” is one of the options.

If I’m having trouble with the ini file, I like to have a typical phpinfo.php file on the server while I make some arbitrary change to the php.ini file (like the session timeout or the max upload size). I refresh phpinfo.php and verify that my changes are taking effect. You can also check the Windows Event logs under “System” which will sometimes report errors in the php.ini file.

Notes regarding re-running the PHP installer to make changes:

The PHP installer does not really handle changes all that well. For one thing it will overwrite the path to the PHP executable w/ the old DOS format so you need to fix that after you run it.

The 2nd thing is that it will write changes to C:\Program Files\PHP\php.ini – regardless of the fact that PHP is actually looking at C:\Windows\php.ini

If you had previously moved php.ini to the windows folder, when you run the Change installation feature, it will create a fresh php.ini file that only incorporates the most recent changes. (ie, if you had 10 extensions enabled and you make a change to enable 1 more, your new php.ini file will only have the 1 enabled and the previous 10 will no longer be enabled)

One way around this is to temporarily move C:\Windows\php.ini file to C:\Program Files\PHP. Then run in installer to make changes. The installer will write changes to php.ini in that location. Then, move php.ini back to C:\Windows.

This applies to Windows Server 2003. This error can occur when you have .NET 1.0 and .NET 2.0 applications running on the same server.

This particular error can occur when you haven’t selected .NET 2.0. in the application settings.

Windows 2003 Uses Application Pools which can only support one version of the .NET framework at a time. If you are running both 2.0 and 1.0 applications on the same 2003 server then you have to create at least two application pools – one for each version of the framework. All of your 1.0 apps should be configured to use one pool and the 2.0 apps will use the other. The pool itself isn’t configured to specify which version it will support, but the app that starts first will “grab” the pool and lock it down to whatever version of the framework that particular app uses.  So if you have a 1.0 and 2.0 in the same pool, it will be a race between the two apps to see which can grab the pool first.  The winner will run fine and the loser will crash.  When IIS restarts, the race starts again.

UPDATE – This article was written originally in 2002. The main concept of using Tomcat to handle the JSP pages under-the-hood is still the same and this page is probably still useful, however, there are now products available to do this for you such as JspISAPI (which offers a free lite version). If you want to do it yourself, there is official documentation and another walk-though page here.

The purpose of this document is to provide a walkthough for setting up a windows server running IIS to serve JSP pages alongside asp, html, etc. at the moment, this is a fairly complicated procedure. because microsoft does not natively support a competitive technical solution (JSP) and the java community does not necessarily like microsoft that much, there are few resources. this is surprising to me, as it seems a very natural thing to want as a windows server administrator.

although IIS does not support jsp pages, it does have a plugin architechture which they refer to as ISAPI filters. the solution for serving jsp pages through IIS is to actually install a second web server which runs on another port. the second web server we will install is called Tomcat and is provided through the apache foundation. in addition to Tomcat, you install an ISAPI filter that intercepts any .jsp file, forwards it to Tomcat for processing. Tomcat returns the results to IIS, and IIS spits it back out to the visitor. So IIS doesn’t actually processes the jsp page, however from the perspective of the web visitor, that is how it appears.

this document was written for Tomcat version 3 (Tomcat 5 is the current version) and as a result, the content is probably outdated. however, the basics are still very much the same. also we now have the good fortune of a more detailed how-to provided on the apache site which is found here.
http://tomcat.apache.org/connectors-doc/webserver_howto/printer/iis.html. you will probably have the best luck following the instructions on this page.

——————————————————————————–

original walkthrough below:

——————————————————————————–

This How-To provides start to finish instructions for setting up IIS to serve Java Server Pages (JSP). After successfully completing these instructions, IIS will work as normal, except when a JSP is encountered, it will pass it along to Tomcat, which will process and send the results back to IIS. This how-to doesn’t really explain how or why things work, but rather will get you up and running quickly with a configuration that will serve most people’s needs. You should definitely read the Tomcat ReadMe files for more detailed information.

The following file paths will be used in these instructions. If you install to a different directory, then you’ll want to change the instructions accordingly. These seem to be the default installation directories for the programs, except for Tomcat, which doesn’t mention a default installation directory.

Java: C:\JDK1.3
Tomcat: C:\Tomcat
isapi_redirect.dll: C:\Tomcat\bin
IIS Root: C:\InetPub\wwwroot

Part 1: Installing JDK and Tomcat

This first part is just to get the JDK and Tomcat up and running. Tomcat works fine as a stand-alone application, so the most logical place to begin is to get it running properly by itself.

Download (from sun.com) and Install Win32 JDK 1.3 to “C:\JDK1.3″
Download (from jakarta.apache.org) and unzip Win32 binary version of Tomcat (jakarta-tomcat) to “C:\Tomcat”
Right-Click on My Computer -> Properties, go to Evironmental Variables* and set:

TOMCAT_HOME = C:\Tomcat
JAVA_HOME = C:\JDK1.3

IMPORTANT: In the environmental variables, make sure “C:\JDK1.3\bin” is the first directory in your PATH. If you have installed other Java programs, they may try to put their Java Run-Time directory first, which will mess up your Tomcat installation. (It’s also good to keep in mind that if you install another Java application later, it might try to change the path and mess up Tomcat)

* On Win2000, The environmental variables are found on the Computer Properties Advanced tab. If you are on Win95/98, then you probably have to edit autoexec.bat instead.

Restart the computer to finalize changes to PATH
Go to DOS prompt, CD to C:\Tomcat\Bin and start Tomcat by typing “startup” (without the quotes). You should get a 2nd DOS window that is running Tomcat. If the 2nd window flashes and immediately closes, then something is wrong – most likely your PATH (See C:\Tomcat\logs\tomcat.log for info) You can make sure Tomcat is running by opening your browser to http://localhost:8080/ (NOTE: If you have previously installed Sun’s JavaWebServer, make sure it is not running because it also uses port 8080)
Part 2: Installing The ISAPI Redirector and jakarta Virtual Directory

In this part we are installing an ISAPI filter in IIS. What this really means is that when IIS receives a request for pages that meet certain criteria, it will hand it off to another program (Tomcat) to deal with processing, and that program will then return plain text to IIS, which is what IIS will send to the browser. We will also create a virtual directory that allows this to happen.

Download (from jakarta.apache.org) isapi_redirect.dll and copy to “C:\Tomcat\bin”
Download and double-click isapi_redirect_nt.reg or isapi_redirect_2000.reg to import the information into your registry. (If you have used directories other than the one’s specified here, then you should edit the .reg file appropriately before you double-click it). Note: You may have to reboot your PC after the registry update for changes to take effect.
Open IIS management console and create a new virtual directory called “jakarta” and make the physical path “C:\Tomcat\bin” Make sure that this virtual directory has “Execute” permissions.
In the IIS Management Console right-click on your machine name (not the root web) and select properties. Click the Edit button next to the “Master Properties” for the WWW Service. Select the “ISAPI Filters” tab and click “Add” Name the filter “jakarta” and for the executable, browse to C:\Tomcat\bin\isapi_redirect.dll file.
Now go to the control panel, select Services and restart the IIS Admin service (make sure Word Wide Web Publishing Service restarts as well). After you have restarted, go back to the ISAPI filters screen and make sure that the jakarta filter has a green arrow next to it. If it does, then everything is working.
Make sure that Tomcat is running. Open your browser to http://localhost/examples/ – you should see that Tomcat is serving this directory. There are several JSP examples which you can click on to test.
Part 3: Configuring Tomcat to handle JSP files, and IIS to deal with everything else.

This part we are configuring two things – telling Tomcat where the JSP files are going to be found (using server.xml) and telling the ISAPI filter which pages should be redirected to Tomcat (using uriworkermap.properties)

Open C:\Tomcat\conf\Server.xml in notepad and find the line towards the bottom that looks like this:

<Context path=”" docBase=”webapps/ROOT” debug=”0″ reloadable= “true”/>
and change it to this:
<Context path=”/” docBase=”C:/InetPub/wwwroot” debug=”0″ reloadable=”true”/>
(if that line doesn’t already exist, then just add it)
Open C:\Tomcat\conf\uriworkermap.properties with notepad. Add the following line anywhere in the file:

/*.jsp=ajp12

If you like, you can comment out the other lines that redirect the examples and servlet directories, although it doesn’t hurt anything to leave them there.

Make sure that it is working by browsing to http://localhost/ to see that your normal default page is still showing up as expected. Now place a file called test.jsp (or whatever) in C:\InetPub\wwwroot and browse to http://localhost/test.jsp If you put some JSP code in there, it should execute. If you didn’t, then the blank page should load. If you are prompted to download instead, then something is not working.
That is it. The final thing you might want to do is to install Tomcat as a service so you don’t have to open it in a DOS window every time. Instructions for installing the service can be found on jakarta.apache.org – look for “Working with the Jakarta NT Service” or “jk_nt_service.exe”

Installing Perl Modules on MS Windows Servers (the Easy Way)

This article explains a bit about the process of installing Perl modules and why the typical instructions found in a module’s ReadMe file do not work on Windows servers. If you want to skip all of the details and just get to the installation part, skip to Option 1

Before you begin: In order to install modules, you have to have access to the DOS prompt on the server. If you don’t have access to the DOS prompt (or you don’t understand what this means) then you will probably have to ask your system administrator to install the modules for you.
The Problem:

Do these lines look familiar to you:

perl Makefile.PL
make
make test
make install

Of course, you’ve seen them in the ReadMe file for a Perl modules that you want to install on your Windows server. However, when you get to line 2 and try to “make” the module, you get a “Bad command or filename” error. (If your Windows PATH is not set right, you might not even get past line one!) You look a little deeper into the ReadMe and see that, oh yes, for Windows machines you might have to say “nmake” or “dmake” instead. Still, the results are the same – or you get a bunch of errors. Why is this? Well, the authors of Perl mods often don’t use Windows at all, so they don’t really bother to tell you some important details. In fact, they secretly want you to install Linux instead, so they purposely make it tough on you!

Important Detail:

The ReadMe file mentions the command “make” as if it will just magically work. However, “make” is not a standard DOS command. (It’s actually not even a standard UNIX command) It is a utility program that comes with a C compiler. Although you can get C compilers from a number of places, Windows does not come with one pre-installed. So, you can type “make” until your fingers get blisters, but it won’t do you any good.

Hey, I thought I was programming in Perl – why do I need a C compiler, you ask? Good question. The first reason is that this is simply a familiar way for UNIX admins to install software, even though there may not even be any code to compile. The second reason is that some Perl modules make system calls or other low-level stuff that is not available using pure Perl code. So, they write a some of the code in C to go along with the Perl module. You may already know that C code needs to be compiled into binary form for each specific operating system. It’s not possible for the module author to provide pre-compiled versions for every operating system known to man. So, they just give you the C source code instead and let you compile it yourself for your own particular system. This is no big deal for most UNIX admins because, as I mentioned, this is a familiar way to install software. In fact, UNIX comes with a C compiler all set up and ready to use.

For Windows administrators though, this procedure is probably unfamiliar. Windows software typically comes pre-compiled, so there is no need to compile it. Windows doesn’t even have a C compiler built-in, so you have to buy or download one on your own. It is possible that you are a Windows NT guru and have set up umpteen gigantic corporate networks without ever once compiling a single program.

The good news, holmes, is that you have 4 (count ‘em, 4) options for installing modules on windows. The bad news is that there is no garauntee that any of them will work! Some modules were programmed on UNIX machines and never tested on Windows. They may not even compile correctly on Windows at all without code modification. Hopefully this is not the case for your module, but be prepared to either just accept it, or get really involved in porting that module to Windows!

Option 1

Option 1 is to download nmake.exe from Microsoft, run the executable (which will extract 3 files) and save these 3 files in your Windows directory. Next, download UNIX Utilities for Windows and extract all the files. Locate tar.exe, gunzip.exe and gzip.exe and copy them to your Windows directory as well.

Now your machine is ready to do some mod installing! At this point you should be able to follow the regular Perl module installation instructions, except where it says “make” you type “nmake” instead. Cross your fingers and hope that the module installs properly!! If it does, you’re all set. If not, move on to Option 2 and hope for the best…

Option 2

Option 2 is to use the MCPAN feature that is built into Perl. To do that, you need to have already done Option 1, because MCPAN still requires nmake to be installed on your machine. so, at the command line, you type:

perl -MCPAN -e "YOUR::MODNAME"

(substitute “YOUR::MODNAME” with the name of the mod you’re installing, of course) The first time you run this utility, it will ask if you are ready to continue with the manual configuration. Unless you know your networking stuff pretty well, i’d just hit No at this point and let Perl auto-configure it for you. After that it will attempt to load the module from CPAN and install it. It will look for required modules and try to install those for you as well.

Option 3

Option 3 is actually easier than Option 1 and 2, but only if you have installed ActiveState Perl ( http://www.activestate.com/ ). ActiveState Perl is a nice Windows port and it includes a utility called PPM which is used to install modules. The modules that PPM supports have been compiled for windows and uploaded to the ActiveState module repository. (Note: If you installed Indigo Perl, they created a browser based interface for installing modules which is really nice – check the docs for that). For this step, you do NOT need to have done Option 1 because PPM used pre-compiled modules – you don’t have to compile them yourself. That is one nice feature if Option 2 is crashing during the compilation process.

To use PPM, just go to the DOS command line and type “PPM” (without the quotes). You will be at the PPM command promt. (I should mention that you need to be connected to to the Internet at this point).

At the PPM promt, enter “install YOUR::MODNAME” – you will be prompted if you want to continue. At that point, PPM will connect to ActiveState and see if the module you requested is available in a pre-compiled form. If it is, it will install the module and you are all done! PPM is especially nice because it will even install other required modules for you.

If you get an error message that the module was not found, then it’s not available from ActiveState. You’re once again out of luck. Try Option 1 and 2 if you did not already. Otherwise move to Option 4.

Option 4

Option 4 is a kind of last resort that may not really work. Basically, some modules force you to go about all the MakeFile business, but in reality it is merely one or more .pm files. If this is the case, you can often just copy the .pm file(s) to your C:\Perl\Lib or C:\Perl\Site\Lib directory. Make sure you maintain the directory structure, for example Crypt::RC4 would be saved in C:\Perl\Site\Lib\Crypt\RC4.pm. So you might have to create some sub-directories to maintain the structure.

That’s It!