Thursday, July 23, 2009

Dynamic Query Parameters for Jasper Reports

Recently I've been working with Jasper Reports. I don't often do a lot of report writing so I enjoyed the opportunity to work on something different.

Once I had a query full of data I began to use the Jasper Parameters to filter out items in my query. For example I might have a query that looks like this.

select * from employees where first='$P{first_name}' and last='$P{last_name)'

Really simple stuff here nothing that Jasper didn't do or support. So I of course took this to the next level and said ok, I'm just looking for a last name so lets put in just the last name, leave the first name blank. Well when I did that the whole thing blew up because I had a query that looked like this at runtime

select * from employees where first='' and last='Smith'

So I have to put a parameter in? That's ridiculous! Searching around the meager JasperReports documentation I couldn't find anything helpful either. Checking with co-workers who use this more than I do I found that most of the time they build SQL in a stored procedure for this. I'm not opposed to a stored procedure but when you just want to filter a few things it's really stupid to have to jump through those kind of hoops.

I came up with a elegant solution to this that I could do within iReport and with no extra classes (as jasper suggests using a scriptlet - which isnt really a script its a java class).

The solution was quite simple once I figured it out. I created the regular parameter for input. This would be first_name, I then created a second parameter (not for input) called first_name_value. Inside the first_name_value "Default Value Expression" I entered this java code:

($P{first_name}.isEmpty()==true ? "" : " and first='" + $P{first_name} +"'" )

Now, once I had that inside my SQL code I used the following

select * from employees where last='$P{last_name)' $P!{first_name_value}

What this does is output NOTHING if first_name is blank. If it is filled with a value we add the entire "and first='John'" at runtime into the SQL statement. This of course has the drawback that you have to have at least 1 qualifier in your sql statement. Try something like where last!='' as a default. This then gives you every row and any "and" clauses are appended to the end. Also notice the $P! with the ! in there. That means it will evaluate before the query.

Why not just use a stored procedure? Well a couple of reasons. First, SP's take time. Sure, you might be good at them and can get your SP written in a short amount of time. Go for it. This solution works well because you don't have to bother with an SP at all. A simple query can be done within iReport and these filters applied.

Get fancy. You could get fancy with this and use your expression code to handle replacements as well. For example you could allow a > 95 and change the code to remove the = sign if something like that is input.

Hope this helps in your report development.

Friday, May 08, 2009

IE8 Developer Tools

I have never been a fan of IE8. Most serious developers (web or otherwise) know that it is rubbish that unfortuneately has a very large adoption. I generally do most of my web development in FireFox and use Fire Bug to do my web site debugging and design. I like of course the on-the-fly features of changing and prototyping different web layouts.

IE has always been a pain my side not only because of their refusal to adopt widely used DOM standards but buggy browsers that are impossible to reasonably debug.

Today I needed to verify a layout issue and with my recent IE8 upgrade noticed the developers tools in the menu. I nearly fell out of my chair in shock to see a robust set of developer tools. This was just amazing that I could now do nearly the same things I could do in Fire Bug. I am in no way going to ditch firebug but I now have a rather decent means to debug IE problems specifically.

I could use Fire Bug Lite but I find that it's performance is poor in IE. This does work well however. I imagine that Microsoft is trying to muscle-in on the developer market but I think most serious developers are going to do ground-up development in FireFox.

Chrome is still my favorite browser to surf in. I can't wait till they get the developer tools in it so I can use it for more web development.

Anyhow, IE8 gained a few (very few) points with me today since they finally got some developer tools that actually work.

Saturday, April 18, 2009

Portable Ubuntu - Take Linux With You

I have to admit that I first thought that portable Ubuntu was going to be a install on a flash drive. It actually runs UNDER windows. The real credit though goes to Cooperative Linux. This allows a linux distro (almost any) to run while windows is running. Now I took the easy route and ran Ubuntu which is a decent distro for most desktop users.

It ran sluggish at first. I was really kind of disappointed and thought that it was because it was running from the flash drive. It really was just not getting enough CPU. I was able to improve the experience considerably on my laptop by setting the colinux and associated exe's to a high priority. This allowed them to get a bit more task scheduling from windows. This was enough to kill the little bit of sluggishness and get me rocking with all that linux has to offer.

On a side note this isnt my first experience with linux or ubuntu. I've run it under VM's and dedicated boxes for some time. It is just very nice to be able to start it up, shut it down and run it while I'm running windows apps. I'm going to go download aptana for linux and try it out under there. Would be nice to be able to take my favorite IDE everywhere regardless of the PC I'm on.

Thursday, April 09, 2009

Quote Curling Function

I recently had to add some curly quotes to text on the page. The following function allows you to add Microsoft Word Style Curly Quotes in replacement of standard quotes. Uses some basic regex to accomplish this quickly and easily.

echo curlQuotes('"This" is a "test", a great "test" I think. Don\'t you?');

// Curl Quote Function
function curlQuotes($curlme) {
// Adds fancy Microsoft Word Style Quotes to standard text.
$curlme = preg_replace('/(.+)"(.+)"(.+)/Ss',"$1“$2”$3",$curlme);
$curlme = preg_replace('/(.+)"(.+)"/Ss',"$1“$2”",$curlme);
$curlme = preg_replace('/"(.+)"(.+)/Ss',"“$1”$2",$curlme);
$curlme = str_replace("'",'’',$curlme);
return $curlme;

Output is:

“This” is a “test”, a great “test” I think. Don’t you?

Monday, February 16, 2009

Same Blog, New Look

You have to be blind not to notice the new look on my blog. It now matches the look and feel for a uniform look across my sites.

Friday, January 23, 2009

On the fly JavaScript Packing - Speed up your site!

Minification of javascript has many benefits. One thing that is a pain for developers is remembering to pack your javascript before deployment. Another big pain is if you need to do an on-server change of some JavaScript code. Using Packer JavaScript en PHP a small PHP file and a tweak to .htaccess you can minify your JavaScript on the fly and never again have to worry about minifying your javascript.

First, download the Packer JavaScript en PHP and extract class.JavaScriptPacker.php to some location on your server.

Now create a .PHP file with the following code.

require 'class.JavaScriptPacker.php';
$script = file_get_contents($_REQUEST['js']);

$packer = new JavaScriptPacker($script, 'Normal', true, false);
$packed = $packer->pack();

echo $packed;


Next, bust open the .htaccess and add the following. Note you should have mod_rewrite enabled and mod_rewrite set to ON. If you dont know how to do this, contact your sysadmin or check it out on apache's website.

RewriteRule ^(.+)\.js$ jsProxy.php?js=/$1.js

Now, the concept here is pretty simple. You take any .js javascript file and route it through the proxy. Once the proxy gets it, the packer compresses it.

Need to exclude a file? Simply make it .jsx or some other extension other than .js, the browser doesn't care but mod_rewrite will ignore it.

Thursday, January 22, 2009

Super Simple AJAX Forms with jQuery and JSON : dataservice.php

Our last installment of the Super Simple AJAX Forms with jQuery and JSON we cover the server side of things. The dataservice.php file covers the business logic behind the web form. Like the webpage.html I boiled this down to the smallest and most reusable code possible.

The very top of the file contains MySQL setup code for connecting to the database. If this is not familiar by now then this tutorial is probably a bit over your head anyhow. In any case set that up, i'm not going to cover it in detail here. Let's get to the meat of our discussion.

PHP Parameters
 <br />// Make sure we have a full Request<br /> if(!isset($_REQUEST['action']) || !isset($_REQUEST['id'])){<br />  echo "Invalid Request";<br /> exit(0);<br /> }<br /><br />// Which action do we take?  If we dont find one we do nothing.<br /> if($_REQUEST['action']=='getuser')<br /> getUser((int)$_REQUEST['id']);  // sanitize  ID by converting to int<br /> if($_REQUEST['action']=='update')<br /> updateUser((int)$_REQUEST['id']);  // sanitize  ID by converting to int<br />

This code is straightforward. We simply make sure we have our unique key (id) and our action (something to do). If we do we proceed to map the parameter action to a function we'll call. We sanitize the ID coming in to prevent SQL injection.

getUser() function
 <br />// Get User Method<br />function getUser($id){<br />  $query  = "select * from users where uid=".$id;<br />  $result = mysql_query($query);<br />  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){<br />  $records[]= $row; // Push into array all records<br /> }<br /> if(!isset($records))<br />  echo "[{error:'No Records Found',status:'error'}]";<br /> else<br />  echo json_encode($records); // Encode everything from SQL<br />  /*<br />   * If you dont have php 5, or pear installed on your host<br />   * check out<br />   */<br />}<br />

This function is called when we load our webpage.html. It's called by the jQuery ready() in the HTML. You can see here, it is a very simple query, then we push the result set into an array. I do this in the example to show that with JSON you can have multiple row results in the response going back. That way you can even persist the data locally and re-use it when you need to. If we have a result set we call json_encode() with our records. This turns it into JSON that we read with javascript on the browser side.

So really, all we do here is build a JSON string out of our MySQL resultset. The big key is to understand that this is stored in a JSON array. You need to get a good understanding of JSON to be effective with this sort of application. Once you do it all falls in line. Basically a JSON array is a container of JSON objects. Check out Introducing JSON for more details.

Now, you may not be on PHP5 which has json_encode() natively. So I put in here a link to the source code for a JSON encode function. Pear also has this type of library but depending on your web host situation you may need to fall back on the JSON.php file itself.

updateUser() function
 <br />function updateUser($id){<br /> // Build update statement<br /> $sql="";<br /> foreach($_REQUEST as $key => $value){<br />   if(strpos($key,"FLD_")!==false){ // Something to post<br />    $sql.=str_replace("FLD_","",$key)."='".mysql_real_escape_string(stripslashes($value))."', ";<br />   }<br />  }<br /> $sql="update users set ".substr($sql,0,-2)." where uid=".$id;<br /> // Update<br /> mysql_query($sql);<br /> // Check for an updated record<br /> if(mysql_affected_rows()>0){<br />  echo "{status: 'OK'}";<br /> }else{<br />  echo "{status: 'Update Failed '}";<br /> }<br /><br />}<br />

Finally, our update statement. This is as simple as our process for filling the FLD_ in the webpage.html file. We scan the entire $_REQUEST array for any FLD_ and then post those to our database string. It works just like how we built our queryString in the webpage.html post. We set the values to update, then we runs the mysql_query() to update records.

Take note of a few things here. First we strip off the FLD_ prefix. We also sanitize the input data by stripping out slashes and escaping special characters for mysql. Again this is to prevent SQL injection. I see many people who don't do this with AJAX services and thus are very open to attack on their back end. I also pull off the last ", " off the end of the update statement.

We last check to see we updated a row or we return an error. The webpage.html code looks for the OK, so if we don't have it then we display the error.

That is it. You should be able to use this as an example and start to your own AJAX code. Remember this is a learning example and that you should make sure you have far better error checking and recovery from SQL errors. You might also want to adapt this code for an insert statement or a delete statement to have full CRUD updates to your database.

My purpose here was to show you that asynchronous web calls are not that complicated with the right library (jQuery). Now get out there and write some code!

Wednesday, January 21, 2009

Super Simple AJAX Forms with jQuery and JSON : webpage.html

In this installment of the super simple AJAX forms with jQuery we look at the webpage.html file. This is a pretty simple HTML file all things considered.  The key components of this HTML file are the .changed style in the header, the form (called testForm) and the block of javascript code at the bottom.  We'll take a look at each of these parts.  You can download the entire set of source code and following along.

CSS Style
<br /> <style><br />  /* apply specific formatting for changed fields */<br />  .changed{<br />   background-color:yellow;<br />  }<br /> </style><br />
The CSS style here outlines the effect applied to the input boxes when they have been changed. We name it changed so you can clearly see how e apply this style and use it as a flag to determine what changes need to be posted to the server. This is really a nice feature of this example. I see far too often entire records updated when just a single field is changed. That or complex PHP code is used to compare what is currently in the database.

The Form
<br /> <form id="testForm"><br />  name: <input type="text" id="FLD_name" class="inputfield" onchange="$(this).addClass('changed')"><br /><br />  mail: <input type="text" id="FLD_mail" class="inputfield" onchange="$(this).addClass('changed')"><br /><br />  <input type="hidden" id="FLD_uid"><br />  <input type="button" onclick="submitChanges('testForm')" value="Submit Changes"><br /> </form><br />

This form is nothing special for the most part. The key items to look at here are the ID names contain a FLD_ prefix. We do this so we can identify in the PHP code which elements are "fields" that need to be processed. In the PHP code we read ALL the data sent and filter out just what we need. So having a unique identifier is critical to make sure our posting of data with MySQL doesnt fail. The onchange attribute is used to apply the "changed" class with jQuery when the field changes. This means that unless someone changes the data, it won't be flagged as an item to update. I added an inputfield class here so you can see that you can have multiple CSS classes applied. The last item to note is that we have an input button with a method call to submitChanges. We pass the form ID name along with it. This is done so you can use the same code regardless of the number of forms. So with this you are not building form specific code, youre writing a very re-usable element.

The Javascript
 <br />  // Function to handle the submission of changed <br />  // only fields on the form we want (multiple form support)<br />  function submitChanges(formName){<br />   // Build list of changed variables<br />   var queryString="";<br />   $('#'+formName+' .changed').each(function(){<br />    // Add new varialbe for each "Changed" item<br />    queryString+="&""="+this.value;<br />   });<br />   if(queryString==""){<br />    alert("Nothing changed on the form");<br />    return;<br />   }<br />   queryString+="&id="+$('#FLD_uid').val(); // Add ID number<br /><br />   // Send Data, get reply<br />   $.getJSON("dataservice.php?action=update"+queryString,function(data){<br />    // Display Message on errors<br />    if(data.status!="OK"){<br />     alert(data.status);<br />    }else{<br />     // Remove Changed Attribute on success<br />     $('#'+formName+' .changed').each(function(){<br />      $('#''changed');<br />     });<br />    }<br />   });<br />  }<br />  // Sample, get 1 row of data from database<br />  $(document).ready(function(){<br />   $.getJSON("dataservice.php?action=getuser&id=2",function(data){<br />    // find fields we have data for, regardless of form.<br />    for(var key in data[0]){<br />         $('#FLD_'+key).val(eval('data[0].'+key));<br />       }<br />   });<br />  });<br />

Finally this is where all the work is done. It is not a large amount of code so it should be easy to follow along. There are 2 items here. The first is "submitChanges" the second is the jQuery "ready()" function. We'll talk about the $(document).ready() at the bottom first.

Once the page loads, jQuery fires a ready method to run some code. We use jQuery $.getJSON to talk to our dataservice.php and pull down 1 record from our users database. You can see here we pass an action of "getuser", and the "id" of 2 which matches the id number in the database. You'll see this is clear by looking at the dataservice.php.

Once we pull this data down, we iterate the "keys" of the JSON data, then using javascript eval() function, we grab the data and post it to any matching #FLD_ on the form. So for example, if the JSON contains a "name" key {name:'Smith'} in the JSON, we map it to #FLD_name. We do this for a few reasons. First, we introduce some logical consistency into how we use our database. This pulls down a database field name, and we use the same field name in our form. We get away from problems like a database field called first_name and a form field name called userFirstName. Later on you'll thank yourself in keeping things consistent and searchable. We also do this to save time mapping data. Since we use the same name (with just a prefix added) its easy to do this programatically vs. having to do a lot of formField=dataFieldValue in the code. Less code for everyone. So this function is pretty simple, pull down the data, map it into the input fields by name.

Now we move onto the submitChanges method. Really, this looks more daunting that it really is. There is a very limited amount of code here I commented it well so you can see just what you would want to change in your own application

We first set an empty queryString which will be used to send the changed data to the dataservice.php. We next tell jQuery to find all the .changed class items in the particular form we just passed. If that code is a little unclear here is what the expression would look like hardcoded.

$('#testForm .changed').each()

Now, lets just talk about what we asked jQuery to iterate through. We said find the ID "testForm" then, find all tags with the "changed" CSS class applied. Then, we ask jQuery to iterate through it with the .each() function. This exposes each tag to us one at a time so we can use it to build our queryString.

queryString is built, or it's not based on each changed item. We pull out the FLD_ names of changed data, apply it to the queryString forming something like


Should we not have any changed data, queryString wont have anything in it and we'll tell the user that nothing changed on the form. If data changes do need to be posted we skip that IF statement, add the "id" of the record manually and prepare for our posting back to dataservice.php. NOTE: The ID is the only hard coded field form element. "FLD_uid" is a unique key in the database that allows you to map it back to the right element. You don't necessarily have to do it this way. Alternatively you could hard code "changed" as a class to it, and it would always be picked up. The down side to this is that if there is no changed data you'll fire an empty query. I also like hard coding the ID number because as a primary key of a DB you should always have it. This type of code ensures I don't forget it.

Next, we call our $.getJSON again, we tell it the action is to update, and append on our queryString. This is posted to the server and we wait for the response asynchronously. Once we receive it, if it's not OK we return the server message. Finally if everythig is good, we pull off all the "changed" values.

You can see here we have some very streamlined code, and not much of it. Adding more form fields requires no changes to our javascript. We can basically include this into a .js file and stamp it on any form we want. A little tweaking and we could make it work for multiple databases as well. You get the idea.

Next, we'll talk about the dataservice.php in detail.

Tuesday, January 20, 2009

Super Simple AJAX Forms with jQuery and JSON : Introduction

I am a huge advocate of SOA architecture. I find it reduces code, encapsulates and makes for a very cohesive web environment.  Wow, what a sales pitch eh?  Seriously if you think that doing an asynchronous web application is more effort than it's worth then you need to keep reading.  I developed this code example to illustrate just how elegant, simple and effective your web application can be.  Furthermore this code you are about to see is completely reusable.  You should be able to use it over and over again as a framework for all your web applications.

To illustrate just how simple this all is I have done it with just 2 files (3 if you count jQuery).  The client side webpage.html and the server side dataservice.php.  You can download the complete source code for these example, upload it to a PHP server and you'll be on your way.  You'll need only to create a simple database called users and some dummy data.  I did not include that in these examples since you should really be able to do all that yourself.  

The entire purpose of this exercise was to create some clean and reusable code for managing a form where users need to edit data.  These type of forms have typically the following types of problems when you do it with traditional HTML and PHP.
  1. How do you know what data changed.  Usually developers replace all the data or do compares manually.
  2. Many times code is placed inside the HTML/PHP to do the update.  Not very easy to read, poor design.
  3. Code sometimes has includes or functions, hard to track things down.  It's better but still very difficult to work with on larger systems.
  4. HTML/PHP often has business logic mixed into the presentation layer.  Validation, etc.
You wont find these types of problems in the code I'm providing you here.  

The only logic in the HTML (and it's just HTML and JavaScript) is the very simple aggregation and posting of data.  It also pulls data down and posts it into the form fields.  It also tracks which fields have been modified and only sends the data that needs to update.  That's it.

This is the meat and potatoes.  This pulls data from the database and posts changes to it.  There is no "insert" code in here but you can easily build your own should you need it.  The framework in this example is designed to show you how easy AJAX/JSON can be.  The dataservice.php can easily be extended to validate and return responses such as "missing data", "bad data", etc.  Nice thing is your business logic is contained here.  It's also not exposed out on the web where someone can use it against you and spam you or worse.  

jQuery is used to save ourselves a boatload of work.  It's free and the AJAX libraries are easy to use.  I only use the simple methods here, I have used the more robust features in jQuery but for 99% of the users/web pages out there this works just fine.

How it works
The application works by pulling down a single record in the database.  After it pulls down the record it uses the JSON data (by parsing the key names) to find HTML ID's using jQuery.  If there is a match with a FLD_ prefix on the field name then we populate it with our JSON data.  Then, anytime a field is changed on the form, we add a CSS class to it.  The CSS class serves two purposes.  First the user can see what data has changed and what will be posted.  Second, the code uses the class to determine which fields need to be sent to the server.  Finally, once the user clicks submit the changed fields are read, tied together in a GET url and sent off to the dataservice.php.  The service updates the record and returns success/failure.

The next post we will disassemble webpage.html and cover how it works in detail.

Saturday, January 10, 2009

Print Buttons Anywhere

On item web developers often have to deal with is printing of web information for meetings. You have information on the web page that looks great but is difficult to print without having a lot of extra information on the page.

Often, this problem is solved by creating a specific HTML page that then is handled by the server which in turn regenerates the information in a limited view and is placed in a popup window. Which is not a bad solution but it does take some time to create that. If you're working in a framework this might be a lot of effort for you.

Here is a solution with jQuery that allows you to basically pick information off the page, stick it into a new document and then allow the user to print it. You'll love the elgance of this small piece of code.

First, lets start with the HTML. Simply include a DIV around any elements you want to pick off the page. Then, somewhere in there include a button, link, etc. which fires off the javascript to create the printable window.

Once you have the content on your site marked up (which you probably had done already) include this tasty nugget on your webpage.

Let me explain what we do here. We use jquery to pick off the webContent HTML. $("#webContent").html() we stuff that into msgHTML. We then use windowHTML to build up the actual HTML page that we will place into the new window.

Note that the windoHTML code could have included the $("#webContent").html() call, but I listed them separately to better illustrate what exactly we are doing here.

After we build the page, we open the web browser window with the call.

Last, we call the print dialog. Now you're probably wondering why I placed that in a settimeout. Well I found on a few browsers (namely firefox 2) that if the page hasnt rendered that the print button can create a blank page. This timeout allows the user to see the page, then a couple of seconds later the print dialog shows.

Alternatively (but not shown in this example) you could add jquery to the printable window then call the window.print() from within the actual page when the document is ready.

You get the idea though, this allows you to quickly be able to print any region of a website without all the extras and without having to create new pages on the server.

Thursday, January 08, 2009

Icons after your links

Well this week I solved a problem with some highly creative jQuery code. The problem was that IE6 doesnt support images in CSS in an anchor tag. Well I came up with some jQuery code that allows you to easily add images after links with a very high level of control. Far more control than CSS.

This example illustrates that once the document is ready, iterate each anchor tag and check to see if it's pointing at a PDF or an XLS file, if so, replace the HTML inside the anchor tag with the existing content plus the image itself.

This helps considerably when you want to enable this site wide. You can see that I use the .match() function to see if the URL contains that piece of information.

This code works great if you place it in a footer and then enable it site wide. This also has the advantage that you can have different CSS files for different pages and not worry about includes or copying references. This works regardless of what page content is.

The down side to using this is that the images are not SEO friendly because this is done in javascript and not part of the page content itself.