Justkez

(Using C# .NET, SQL and Hashtables)

It can be particularly cumbersome setting properties in an object, especially if there are a lot of them, and you want to add/remove/change properties over time.

We can use key/value pairs in a Hashtable to populate an object's properties "automatically" by using reflection and some consistent naming of properties and variables.

Specifically, in this example, we will use SQL query field naming (e.g. "SELECT field1 AS Name") to populate a Hashtable, which is then sent to an object's constructor, and the constructor "self populates" its own properties.

The class object

Let's use a simple person class...

    public class Person {
  public string Name { get; set; }
  public int Age { get; set; }
  public bool IsMale { get; set; }

  public Person() {
    // default constructor
  }
}
  

Nothing exciting there. However, if we want to pass through a list of key/value pairs (of an unspecified length) to automatically assign properties, we need a second constructor. This constructor is going to take a single argument - a Hashtable.

    public Person(Hastable ht_Options)
  {
    Type MyType = this.GetType();
    PropertyInfo[] a_Properties = MyType.GetProperties();

    foreach (string s_Key in ht_Options.Keys)
    {
      PropertyInfo pi = a_Properties.Single(item => item.Name == s_Key);

      Type t = pi.PropertyType;

      if (pi.PropertyType == typeof(System.Int32))
      {
        pi.SetValue(this, int.Parse(ht_Options[s_Key].ToString()), null);
      }

      else if (pi.PropertyType == typeof(System.Boolean))
      {
        pi.SetValue(this, bool.Parse(ht_Options[s_Key].ToString()), null);
      }

      else
      {
        pi.SetValue(this, ht_Options[s_Key], null);
      }
    }
  }
  

When we pass a Hastable to create a new Person object, the constructor will loop through each property (via Reflection) and look for that property name in the Hashtable (by comparing the property names with the Hashtable keys); if the property is an integer or boolean, it will provide the relevant casting.

Obviously there could be a good deal more validation here, but it serves the purpose of demonstration.

Getting our data

If we assume that we want to populate a number of Person objects from some pre-existing data (from, say, a database), we can use SQL named parameters:

    SELECT name [Name], age [Age], gender [Gender] FROM people
  

We can then pass this query to a SqlDataREader object, and loop through each row, adding the name of the column and the value for each row in the query result set.

    using (SqlDataReader _reader = _com.ExecuteReader())
{
  if (_reader.HasRows)
  {
    while (_reader.Read())
    {
      Hashtable ht_Options = new Hashtable();

      for (int i = 0; i < _reader.FieldCount; i++)
      {
        string s_Key = _reader.GetName(i);
        string s_KeyValue = _reader[i].ToString();

        if (s_Key == "Gender" && s_KeyValue == "Male")
          s_KeyValue = "true";

        ht_Options.Add(s_Key, s_KeyValue);

      } // reader loop

      Person p = new Person(ht_Options);

    } // reader read
  } // has rows
} // execute reader
  

Such that we now have several Person objects with "automatically" populated properties.

Having spent not only the good part of today, but many hours of many other days, I thought it prudent to share some findings on where the world stands when it comes to creating and writing to a new Shapefile from a C# .NET environment.

As you probably know, a Shapefile is a file format synonymous with ESRI's ArcGIS/Map/Info suite of products; it has been around for ages. The file format is open, in that it is well documented if you want to construct the file programatically (as in, "let's create a binary file from scratch") - sod that.

So what are the options? Well, there are many options, and I spent some time exploring them in depth...

Create the file manually, according to the file spec

This is appealing if you have all the time in the world, are willing to learn all the nitty gritty of a file spec and understand all the niggles and caveats associated with doing this by hand. You can have a look at the file spec and make your own mind up. The idea of doing this would probably sit well with seriously experienced C/C++ old schoolers - not really what you want to be doing in something as high level as C# .NET 3.5.

SharpMap - A Geospatial Application Framework for the CLR

I really admire the idea of the project - bringing everything GIS together in one nice (albeit large) package for .NET developers. However, the reality for me was somewhat different. Considering myself at an average to intermediate programmer, I was full of frustration and anguish as I a) tried to navigate the site and find relevant documentation and b) match any forum posts/hints to a meaningful version of the distribution. The 0.9 release officially has no support for writing Shapefiles, whilst the 2.0 version does. It seems that SVN trunk is NOT current, so after getting the v2.0 branch, I fiddled around for a bit but still nothing - the (relatively) recent forum posts on the matter were referencing interfaces that didn't even show up.

So, this one was a no go. Perhaps when the project unites a bit more, something will come of it.

NetTopologySuite - port of the popular Java JTS GIS Suite

This one was a bit more promising - good sources and some unit tests to get some ideas from. Unfortunately it was again an issue with finding the right version of the right files, then interfacing them with seemingly non-existent classes. It was getting late in the day when I tried this, but it all just seemed so big and bulky for writing one simple polygon Shapefile. This suite implements some of GeoTools.NET, which it supposedly uses for "capabilities of read/write data from and to file formats such as Shapefile format" - this may be the case, but there are some many levels of abstraction away from what you actually want to do, that you get embroiled in a confusing web of inheritance, abstraction and implementation.

I'm sure it's possible, but it happens to be very hard to make possible.

GeoTools.net - set of .Net classes useful when handing geographic information

It is never very inspiring to see "recent activity" from 2005, but the Shapefile format is pretty tried and tested, so I gave it a spin.

By this point I was getting remarkably frustrated. I remember that this package didn't work, just not sure what the reason was.

Convert simple polygon shapes to KML using ogr2ogr

Seriously, I was starting to implement this thinking it was the easiest way around. Installed ogr2ogr and FWTools before I realised this was a ridiculous route to take.

.NET Wrapper for shapelib

shapelib is a C library for reading and writing Shapefiles - someone has done a .NET wrapper for it. I got pretty far on with this, but kept cocking up the sync between DBF and SHP; started to come a bit of a headache so I carried on looking.

Free tools included with shapelib

There are some multi-platform tools available freely from the shapelib author(s) - specifically in shapelib128_bin_win.zip over on the download page. Basically you get command line applications which allow you to create and write to Shapefiles and the associated DBF data files.

At last, a solution that understood my simple problem of "draw a polygon from a list of lat/longs". The final implementation is tiny static class in C# to create new Shapefiles and write polygons to them. I wasn't that keen on relying one external utilities to accomplish this, but after my ordeal I would happily ship a CD full of utilities with the software if it meant it worked quickly and easily. Also worth a look is this little user guide on AntholoGIS which gives a few more examples.

In conclusion

I may be a failed hardcore developer, but I have all my Shapefile creation needs satisfied in less than 100 lines of code. This is a trade off I can live with.

Thank you to everyone who was ever involved in the shapelib project - it is truly a perfect, elegant solution to what seems like it is a common problem (in the GIS programming world, anyway).

This post outlines how to get database driven, session based user registration, authentication and login working with Ramaze and Sequel.

Introduction

Ramaze is, in my opinion, the leading Ruby web framework. Whilst the documentation and community examples are still gaining momentum, you can really benefit by spending some time learning the framework.

This post is a guide to getting user registration and authentication working. We are talking proper, database driven, session based authentication - not just a single secure login area.

Requirements

This guide is written based on Ramaze 2009.06 and Sequel 3.2.0 - earlier versions of both of these will work, but you will need the "Innated" version of Ramaze (something after 2009.05 would be best) and ideally Sequel 3.1.0. (Sequel is the database abstraction/ORM library, so you can be using SQLite, MySQL, Pg - whatever floats your database boat)

Getting started

We will be creating and editing several files - one Sequel-based database model, two controllers and a couple of view files. To save you some effort, I have provided a basic SQL schema for creating the user table.

    create table users(
  id integer primary key,
  login varchar default null,
  email varchar default null,
  crypted_password varchar default null,
  salt text default null
  );
  

N.B. this is a SQLite formatted query.

We also need to make sure the Ramaze application is using Sequel, and we connect Sequel to the database with the above table. In this example, I have created tutorial.db3 with the above table.

In app.rb, add in the following lines:

    require 'sequel'
# ...
DB = Sequel.connect('sqlite://tutorial.db3')
  

The controller

The controller, as you probably know, ties the interface (view) and database (model) together, we don't actually need to do much with regards to controller editing, as Ramaze has pretty sophisticated helpers for managing user logins.

With a new Ramaze project (ramaze --create my-project), open the controller/init.rb file and make it look like this:

    class Controller < Ramaze::Controller
  layout :default
  helper :xhtml, :user
  engine :Etanni
end

private

 def login_first
   return if logged_in?
   redirect MainController.r(:login)
 end

require __DIR__('main')
  

Important things to note are the inclusion of the :user helper; you can read up on this built-in helper here.

As a final step, we need to add two methods to controller/main.rb - this will handle the login and registration process:

    def login
  @title = "Login"
  redirect_referer if logged_in?
  return unless request.post?
  user_login(request.subset(:login, :password))
  redirect MainController.r(:index)
end

def register
  @title = "Register for an account"
  
  if request.post?
    @user = ::User.new
    @user[:email] = request[:email]
    @user.password = request[:password]
    @user.password_confirmation = request[:password_confirmation]
    @user.salt = Digest::SHA1.hexdigest("--#{Time.now.to_f}--#{user.email}--") 
    
    if @user.save
        flash[:message] = 'Account created, feel free to login below'
        redirect MainController.r(:index)
    end
  end
end
  

And whilst we're at it, we might as well add in the very, very basic logout action:

    def logout
  flash[:message] = "Logged out"
  user_logout
end
  

The model

We need to define several model-level methods to help register, authenticate and login users. This will model will be a representation of the users table that you should have created in the first step.

Create a file, "user.rb", in model/, and you can make it look something like this:

    class User < Sequel::Model(:users)  

  attr_accessor :password
  attr_accessor :password_confirmation

  def after_create
    self.crypted_password = encrypt(password)
    @new = false
    save
  end

  def authenticated?(password)
      crypted_password == encrypt(password)
  end

  def encrypt(password)
    self.class.encrypt(password, salt)
  end

  def self.encrypt(password, salt)
    Digest::SHA1.hexdigest("--#{salt}--#{password}--")
  end

  def self.authenticate(hash)
    email, pass = hash['login'], hash['password']

    if user = User[:email => email]
      return user unless pass
      user if user.authenticated?(pass)
    end
  end

end
  

Some interesting things to note...

• We have user defined attributes for the password and password_confirmation fields - we do not store these in the database, but we need to assign and access them to verify the password and then hash it

• The password is hashed by using SHA1, with a seed/key determined from the user password and another user-specific value stored as the salt. This means that each password is hashed differently, making things a bit more secure.

• There is a bit of overlap between login and email here, as there will be in the following sections. I have chosen to demonstrate logging in with an email address, not a login name.

Don't forget...

You need to edit model/init.rb to require the model you just created.

The views

We will need to create two views, in addition to the default views that Ramaze creates for you. (Remember, a view is the webpage/interface that you actually see in the browser)

The login form

Create login.xhtml in view/, and use the following as a guide:

    <form action="#{MainController.r(:login)}" method="POST">
  <fieldset>
    <legend>Login form</legend>

    <label for="login">E-mail address</label>
    <input type="text" name="login" />

    <br /><br />

    <label for="password">Password</label>
    <input type="password" name="password" />

    <br /><br />

    <input type="submit" value="Login" />

  </fieldset>
</form>
  

Believe it or not, we have nearly finished adding in user authentication; the final steps being the ability to register, and securing parts of your application.

The registration form

It is more than likely that you will want a slightly more exotic registration than the one below, but it provides a good starting point. This code should go in view/register.xhtml:

    <form action="#{MainController.r(:register)}" method="POST">
  <fieldset>
    <legend>Register</legend>

    <label for="email">E-mail address</label>
    <input type="text" name="email" />

    <br /><br />

    <label for="password">Password</label>
    <input type="password" name="password" />

    <br /><br />

    <label for="password_confirmation">Password confirmation</label>
    <input type="password" name="password_confirmation" />

    <br /><br />

    <input type="submit" value="Register" />

  </fieldset>
</form>
  

Securing actions, accessing the logged in user

There are a couple of ways you can secure actions from within Ramaze - one of which you can see in the login method above. These techniques are:

    def secret
  login_first
  # ... Secret stuff
end
  

Which will redirect the user to the login page. Alternatively, you can do a "manual" check to see if the user is logged in:

    def secret
  redirect_referer unless logged_in?
  # .. or ..
  return if !logged_in?
  # .. or ..
  if logged_in?
    print 'You are logged in'
  end
end
  

Who is logged in?

It can be very useful to access information about the logged in user; if we use an example whereby a user wants to edit their comment on a blog...

    def edit_comment id = nil
  login_first
  redirect_referer unless @comment = Comment[:id => id]
  redirect_referer unless @comment[:user_id] == user[:id]
end
  

This hypothetical snippet would ask the user to login first, then it would check to see if the comment exists. Finally it will check the user object to see if the ID lines up with the owner of the comment.

It is worth noting that user is accessible throughout almost all of the Ramaze application, so you can do checks within layouts, views, controllers (but not models).

Questions/problems?

Feel free to leave me a comment below with any issues you're having with the above, or if you have some improvements that can be made.

Sequel is a pretty nifty database toolkit for Ruby. I have been using it lately in combination with Ramaze - an incredibly fast and powerful web framework (also for Ruby).

Sequel has perfect documentation in terms of an API, and the cheatsheet and readme give a good summary of how to use the features, but there's not too much out there that can gives a gentle introduction.

There will be a few posts published here that try and give such an introduction to various aspects of Sequel.

This one is a quick run down of how you validate database fields in Sequel.

The model to validate

For this example, we will use a simple User model - not very original, I know:

    class User < Sequel::Model(:users)
  set_schema do
    primary_key     :id
    varchar         :username
    varchar         :email
    int             :house_number
    varchar         :zip_code
  end
end
  

Basic validation

Sequel comes with some built in validation rules which will satisfy most people's needs straight off. They are mentioned at the bottom of the readme, and detailed in the RDoc page (scroll down a bit to see everything).

A brief demonstration of some of these validations, in context:

    validations.clear
validates do
  presence_of       :username, :email, :house_number
  uniqueness_of     :email
  numbericality_of  :house_number
  length_of         :username, :minimum => 6, :maximum => 10
  format_of         :zip_code, :with => '/(^\d{5}$)|(^\d{5}-\d{4}$)/'
end
  

Pretty self explanatory stuff when you see it in action.

The presence_of makes sure all fields specified have been given a value, uniqueness_of makes sure that there is only one instance of that value in the table (useful when registering users by e-mail address, for example).

The format_of allows you to validate a specified field against a regular expression. I'm not actually sure if that ZIP code regex works, don't quote me!

These will be run when you try and save a new model or update an existing one - if the model fails to save, the instance of the model will be returned with the errors property set - you can inspect this to see what the problem is.

Advanced validation

As mentioned, the basic validation should be sufficient for most people, but not everyone. I have found myself needing to validate fields with a higher degree of control. For example, you might want to use some obscure criteria, or need to perform some action on the field to see if it is valid or not.

To demonstrate this, we will only allow odd house numbers to be saved:

    validates_each :house_number do |object, attribute, value|
    object.errors[attribute] << 'No even house numbers allowed!' unless value % 2 == 1
  end
  

You would put this underneath the previous code block.

You could call a method that returns true or false based on whatever critera you choose:

    #...
unless checkMyValue value
  

There are several good user authentication/registration/login systems available for CakePHP (especially version 1.2), but I decided to roll my own. This offers a good learning exercise, in addition to the benefit of knowing where all your code is and what it's doing.

The latest little addition I've made to the code allows you to redirect the user back to where they wanted to go before they logged in. For example, I want to add a recipe into my online multi-user cookbook web application. If I click an "Add Recipe" button and get taken to a login form, I don't want to then be redirected to the front page once I'm logged in - I want to go straight back to adding a recipe.

The basic logic of the login system is

• A function in /app/app_controller.php to check if the user is logged in. If they're not logged in, redirect them to the login page.
• A login form to talk to a controller and model which do all the processing/checking to see if the details are correct.
• A line of code at the end of all this to redirect the user to the front page (or members area).

By adding a tiny snippet of code to the check-if-user-is-logged-in function, and revising the redirect after login, the user will get a much more pleasant experience:

    function checkIfLoggedIn() 
{
  if(!$this -> Session -> check('user')) 
  {
    $this -> Session -> write('lastPageVisited', $this -> params['url']['url']);
    $this -> redirect('/users/login', null, true);
  }
}
  

Then when, in your controller, you decide that the user is all logged in (in this example you'd set the user variable in the session), use the following snippet:

    $this -> redirect('/' . $this -> Session -> read('lastPageVisited'), null, true);
  

Pretty rough and ready (forgive the prepending slash), but it works for my needs. If you do this, you'll probably want to check the lastPageVisited variable to ensure it exists.

Do you use CakePHP and go about this a different way? Would love to hear different methods...

There was a lot of positive feedback from the last tutorial which has spurred on a newer, revised tutorial. The contents of this post relate to Cake 1.2 (still in beta, but very usable). Whilst much of the steps are the same, there are a few subtle differences, and I have tried to clean things up a little better. So, without further ado...

Overview

This is a very high level overview of what the tutorial will delve into - nothing too advance, but many people pointed out with the last tutorial that it was a bit hard to keep track of.

• Livesearch
• How it's implemented
• File structure
• Database, table and Model
• Controller and views
• Don't forget the layout

1. Livesearch

If you're not too sure what Livesearch is, you probably don't need to be reading this tutorial. A quick overview of a livesearch would describe how search results are presented to you before you've finished typing - no need to click submit, just type and watch the results appear. Livesearch is part of the Web 2.0 thing, and has come around thanks to the popularisation of AJAX. To see an example of a livesearch in action head on over to Google Suggest.

2. How it's implemented

If you'd rather head straight for the demo (put together by following the steps in this tutorial), then go right ahead.

We will be walking through a simple system of searching a table of first and second names - the controller we'll build later on searches both the first name and the second name field for whatever you type in. Obviously however you wish to implement a livesearch will be slightly different, but the principles are the same.

The basic process of livesearching is as follows:

• Start typing in an input box
• Javascript monitors what you type and periodically sends a request off in the background
• This request is picked up by the controller, which searches the database for matches to your query
• The controller sends the results to a small template file, which appears under the search box

The mileage is going to vary on this - results could be slow to appear because of a sluggish connection, a large database or an overloaded server. I'm never that keen on websites that implement livesearching when it doesn't add any value to the search. However, for quickly looking up a list of possible matches it is ideal.

3. File structure

I have made the entire CakePHP project tree available for download, but it would be good for you to have some understanding of what the files do before diving in.

The tutorial covers the creation of 1 model, 1 controller, 2 views for the model and 1 stylesheet. The layout is not really important in this scenario, and the one included in the project tree is very, very simple.

The model simply maps the database table (created in the next step) into CakePHP. The controller is responsible for directing the visitor to the relevant views, and of course handling the search request. It is quite surprising how little code there is in the controller.

The first view displays the search box and includes the AJAX functionality necessary to fire off the search query to the controller. It also contains a <div> element which is populated by the results. The second view is a simple template for formatting the search results. In this case it is just an unordered list.

From here on in things get very specific to the tutorial.

4. Database, table and Model

I'm assuming you have an operational CakePHP environment, and the database is all setup and ready to go. If this is not the case, you'd better get yourself over to the excellent CakePHP manual and brush up.

We are going to create one table (people) and put a few records into it:

    CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name_first` varchar(20) DEFAULT NULL,
`name_second` varchar(20) DEFAULT NULL,
PRIMARY KEY  (`id`)
);
  

Now we can populate it with a handful of British Prime Ministers:

    INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('7','James','Callaghan');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('6','Margaret','Thatcher');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('1','Tony','Blair');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('5','John','Major');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('8','Edward','Heath');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('9','Harold','Wilson');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('10','Alec','Douglas-Home');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('11','Harold','Macmillan');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('12','Anthony','Eden');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('13','Clement','Attlee');
INSERT INTO `people` (`id`,`name_first`,`name_second`) VALUES ('14','Winston','Churchill');
  

The final step is to create a model which CakePHP will know to map to the table we just created:

    <?php
/* File: /app/models/person.php */
class Person extends AppModel
{
    var $name = 'People';
}
?>
  

The next steps are where something actually starts to happen.

5. Controller and views

In order to interact with the model we just created, there has to be a controller associated with it. If you've got to this point and have no idea what a model, view or controller is, it's time you got yourself over to Wikipedia to read up. Our controller is only going to have two actions (and two associated views), so it's all pretty simple:

    <?php
/* File: /app/controllers/people_controller.php */
uses ('sanitize');
class PeopleController extends AppController
{
    var $Sanitize;
    var $name = 'People';

    function index() { }

    function beforeFilter()
    {
        $this->Sanitize = new Sanitize();
    }

}
?>
  

CakePHP's magic will automatically associate the model Person (singular) to the controller People (plural), so you don't have to worry about that.

Now we need to tell CakePHP to use the JavaScript and AJAX helpers that will give us the ability to fire off a livesearch request and do various displaying/hiding magic. Add the following line below the $name declaration:

var $helpers = array('Html', 'Javascript', 'Ajax');

It is important to also include the Html helper, as CakePHP will override the default helper inclusions with what you specify here. It's also worth noting that you can set default helper inclusions in /app/app_controller.php, but we're keeping everything wrapped up in one controller, here.

The final step of constructing the controller is to process a search request. The second view created in this step (further down) contains a form that will be used to submit data to our search action. This action will be responsible for fetching the results accordingly, setting the layout to a CakePHP-provided Ajax layout and passing the results to the search results view (created after this step):

    <?php
function search() {
if (!empty($this->params['form']['query']))
{
$query = $this -> Sanitize -> paranoid($this->params['form']['query']);
if (strlen($query) > 0)
{
    $result = $this -> Person -> findAll("name_first LIKE '%".$query."%' OR name_second LIKE '%".$query."%'");
    $this->set('result', $result);
    $this->layout = 'ajax';
}
}
}
?>
  

There are a few sanity checks here. Firstly we sanitize the query data to prevent any SQL injection style attacks - this is done through the inbuilt Sanitize library, which helps strip out any nasty characters. Secondly we want to make sure that the query field from the view is not empty, and that it is of a sensible length (greater than 1 here, just for demonstration purposes). If these checks pass, the results from the query get sent onto the results view.

The first view we'll take a look at is the one that will format the search results - we're going to call this search.ctp and it will live in /app/views/people/ - you may need to create the subdirectory if you haven't already. Copy the following into the contents of the file:

    <?php
/* File: /app/views/people/search.ctp */
if (isset($result)) {
print '<ul>';
foreach ($result as $user)
{
    $display = $user['Person']['name_first'] . ' ' . $user['Person']['name_second'];
    print '<li>';
        print '' . $display . '';
    print '';
}
print '</ul>';
}
?>
  

As you can (hopefully) deduce, this checks for the presence of a $result variable and populates an ordered (a.k.a. numbered) list with the contents, and spitting out the first and second name of each result.

The second view is somewhat more complicated, and initiates the various Javascript related functions needed for the Ajax-powered livesearching. This view is stored in index.ctp, and should look something like the following:

    <?php /* File: /app/views/people/index.ctp */?>
<form accept-charset="UNKNOWN" enctype="application/x-www-form-urlencoded" method="get">
<input id="query" maxlength="2147483647" name="query" size="20" type="text" />
<div id="loading" style="display: none; "><!--p echo $html--> image("spinner.gif") ?></div>
</form>

<?php
$options = array(
    'update' => 'view',
    'url'    => '/people/search',
    'frequency' => 1,
    'loading' => "Element.hide('view');Element.show('loading')",
    'complete' => "Element.hide('loading');Effect.Appear('view')"
);

print $ajax -> observeField('query', $options);
?>

<div id="view" class="auto_complete">
    <!-- Results will load here -->
</div>
  

The first thing to note here is the form. This is a very, very simple chunk of HTML that gives us one input box - no location to submit the form to, no submit button; just the input box. Next to that is the spinner graphic (you all know what this is...) that we want to toggle display of depending on what the Ajax handler is up to.

The $options array could well be integrated into the statement below it, but this makes things a bit more readable. The observeField is a function provided to us by the Ajax helper that will watch a form field and fire off an Ajax request when it changes. The $options array has all sorts of information about the request to make, and what to do when it's being made...

...The update field tells the Ajax handler where to display the results it receives. In this case, they are going into the DIV at the bottom of the view.

The url field tells the Ajax handler where to submit the contents of the watched field to. This is the action we created earlier.

The frequency field tells the Ajax handler how often it should re-check the watched field and submit the contents to the url specified above. This is in seconds.

The loading and complete fields tell the Ajax handler what it should do when it's waiting for results and what to do when it loads them. The values assigned to these are script.aculo.us calls. In this instance it will show the spinner graphic when results are being fetched, and will hide it again and display the results when it has got everything it needs.

Don't forget the layout

The final obstacle to having a working livesearch (from following this tutorial), is the layout. The first thing you'll need to do is download the prototype and scriptaculous libraries - you can get them from here. You'll need to extract the scriptaculous.js and prototype.js to /app/webroot/js.

I opted to use a very simple layout, so as not to distract from what was going on. You're welcome to use the following, or create your own. Whichever way you go, do not include the Javascript and stylesheet includes!

    <?php /* File: /app/views/layouts/default.ctp */
    e ($html -> css('screen'));
    e ($javascript -> link('prototype'));
    e ($javascript -> link('scriptaculous'));

    print $content_for_layout;
?>
  

The stylesheet is, again, very simple: (if you're using this, save it to /app/webroot/css/screen.css)

    * { font-family: Verdana, sans; }
a { color: #000000; }
a:hover { color: #FF0000; }
p { font-size: 12px; }
div { display: inline; font-size: 12px;}
input { padding: 4px; }
li { margin: 5px; }
div.auto_complete { display: block; }
  

That's it for the tutorial. You might want to check out the demo of everything you seen above in action. You can also download the CakePHP project tree - this contains a vanilla CakePHP 1.2 setup and everything you see above. There is also a SQL dump file in there to speed up table creation & population.

If you think I've missed anything important - explanation, technical detail or otherwise then please comment below or send me an email (address is over on the left in the sidebar). There was a good deal of feedback from the last tutorial, so thanks to all.

CakePHP is a PHP framework which is turning out to be most useful, and very flexible. I've embarked on a few little projects with it, and thought I would spread a little understanding with regards to the session component - as I struggled to find a concise set of examples to help me on my way.

By default, a CakePHP application will automatically create a session instance when you're browsing through it - this is good, but it's not that obvious about where to go after this. If you were to put the following code into a controller:

    print_r($this -> Session -> read());
  

The contents of the session variable would be printed out (print_r() dumps the contents of an array to screen). Without any influence over the session data, it would look like this:

    Array ( [Config] => Array ( [rand] => 262820453 [time] => 1161876896 [userAgent] => c7f575cbe5a4b7ad0efb748d54124611 ) )
  

(Bear in mind that the numbers & characters will all be different for you.)

If you are looking to disable this default session behaviour, set the AUTO_SESSION constant to false in /app/config/core.php.

Writing a variable into the session

Anyway, let's get onto some more interesting subject stuff - making the Session component work for us. If you want to write one value into the session, it's merely a case of doing:

    $this -> Session -> write("variable", "value");
  

If you look at a dump of the session array (print_r($this -> Session -> read())) you will see that right at the end of the array, there's the text "[variable] => value" - magic.

Reading a variable from the session

If you want to use this session variable anywhere in your application, you can simply do:

    $this -> Session -> read("variable");
  

This will return the string "value".

Putting objects into the session

Handily, you can store entire objects in the Session component - useful for passing information backwards and forwards regarding a user account/profile or the contents of a shopping cart. All you need to do is change the "value" part of the above example to be an array instead of a standard string value. For example:

    $user_email = $this -> data['User']['email'];
$user  = $this -> User -> find("email = '$user_email'");
$this -> Session -> write('User', $user['User']);
  

In the above working example we expect a form to have been submitted to the controller containing a "User/email" value - we then look in the Users database table to find a row with the given e-mail address. After we've found it, we store it into the Session component. Please note the $user['User'] is not the entire array as we only want the user information - there may well be other rows returned by CakePHP depending on your model associations (e.g. A Profile could belong to a User, and our database query above would return the Profile data too.)

Considering the above example, how would we get specific data out of the Session component? Easy, but not obvious:

    $this -> Session -> read("User.email");
  

Closing/destroying a session

Most uses of destroying a session will for logging users out of a system - and it's made very easy. For neatness it's better to check and make sure the session is still valid before attempting to destroy it:

    if ($this -> Session -> valid())
{
    $this -> Session -> destroy();
    $this -> redirect('/');
}
  

It's also courteous to redirect the user somewhere useful.

Other stuff

There's a little more to session handling within CakePHP - it's worth looking into it. Features such as checking values stored in a session, storing flash() messages in a session, deleting specific values etc etc. Have a gander at the CakePHP API documentation on the Session component to get up to speed. If you're not so sharp on CakePHP yet, check back to the examples above - they should outline the steps needed to use the other functions.

If anything has been unclear, post a comment and I'll get back to you - I went through the whole thing with a shade of briskness.

A while ago I attempted to document what was needed to get VBScript to interact with Oracle. That piece covered how to insert rows into an Oracle table, and how to execute a stored procedure. Now I'd like to tackle the task (illiteration, awesome) of getting data OUT of an Oracle table and into a VBScript process.

I set about researching how this could be done for a small system I was tasked to work on, and found it to be surprisingly well documented. You should have some good results from Googling around for examples. I've put together my findings and implementation here in order to serve as a working example.

Connecting to Oracle

Before we can look at the code involved in retrieving rows from an Oracle table, we need to establish a connection to an Oracle instace - we do this with an ADODB.Connection object, and pass a connection string to its Open function. Have a look at the code below to see an example of this:

    Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=oracle_user;Password=oracle_password;"
Dim connection: Set connection = CreateObject("ADODB.Connection")
  

Once this code is in place, and you have updated the connectionString variable to account for your server & account details you can initiate the connection to Oracle using the following line:

    connection.Open connectionString
' Stuff will happen here...
connection.Close
  

In between those lines, we'll start developing some magic which will enable you to get your data out of Oracle. Please note that it's good practice to use the connection.Close line to disconnect from Oracle - especially so in a testing situation, as you'll end up with many concurrent connections to Oracle.

Getting our records

Once you've established a connection to an Oracle instance the next step is to create and populate an ADODB.Recordset object - something which stores all the data returned from an SQL query you specify.

Ideally, you need to add the following line to where you initialise the rest of your object variables:

    Dim rows: Set rows = CreateObject("ADODB.Recordset")
  

The next step is where we execute an SQL query to populate rows - our new Recordset object. If you're just playing around with this code at the moment, I'd reccommend using a query with a limited set of results - otherwise you could be waiting around for a while for your results to come back, and potentially be innundated with message boxes!

    Set rows = connection.Execute("SELECT name,age FROM people WHERE age >= 18";)
  

This is telling rows to be populated by the results of our query - selecting two columns (name & age) of data from a made up table (people) who are 18 or older.

Using our records

By this point we have got a set of records/rows stored in our ADODB.Recordset object - named rows. Obviously they're pretty useless just sitting there - chances are you'll want to loop through and something with them...

The process for doing this is to perform a simple while loop on the rows object - saying "do something with each record then move on to the next one, until we're at the end of our record set". In code - this looks like the following:

    While Not rows.EOF
MsgBox rows.Fields("name").Value
rows.MoveNext
Wend
  

This will show a series of message boxes, depending on how many records there are, with the persons name - simple. There is another way to access the fields; you can pass a number to the Fields() method, and it will bring back a column value according to your query. For example:

    Set rows = connection.Execute("SELECT name,age FROM people");
While Not rows.EOF
  MsgBox rows.Fields(1).Value
  ' ... is the same as ...
  MsgBox rows.Fields("age").Value
Wend
  

These two are the same because age is the second column requested in our query (counting starts at 0, so second value has an index of 1). Using the column name is generally going to make more sense, especially when you have to go back and edit the code after a period of time.

That's it for the time being! It's pretty simple stuff, but it's nice to have a really basic example explained (I think). Full code is below, and a zip file is there if you want to download the code. Don't forget the housekeeping at the end!

    Option Explicit

Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=;User Id=;Password=;"
Dim connection: Set connection = CreateObject("ADODB.Connection")
Dim rows: Set rows = CreateObject("ADODB.Recordset")

connection.Open connectionString
Set rows = connection.Execute("SELECT name,age FROM people")

While Not rows.EOF
  MsgBox rows.Fields("name").Value
  rows.MoveNext
Wend

connection.Close
Set rows = Nothing
Set connection = Nothing
  

Whilst I'm the mood to share VBScript experiences with the world, I thought I would put up a small piece about how you can use VBScript to get data out of Excel. You may think it's a bit stupid using VBScript to access Excel when you can script directly into Excel with VBA (Visual Basic for Applications) - but I find it can be pretty useful as it requires minimal interaction (double clicking a file instead of opening a spreadsheet and running macros).

To work through this I have tried to construct a reasonable real life scenario where you might need to extract data from Excel - I am no expert in the world of biscuit sales, but I've tried my best:

The data file

The data file used can be XLS, CSV, or anything else that Excel can read into cell values. There are far more complex things you can do with VBScript & Excel than this example, but it provides a good starting point. We're going to be using a standard XLS file with a small amount of data in.

As you can see from the screenshot of the file, the data does not start in the first cell (A1) - instead it starts in A3. We need to bear this in mind when we start to think about parsing out this data - we obviously don't want to include these first two rows.

Basic program structure

    Option Explicit
On Error Resume Next

Dim loopCount, directory, objExcel, workbook
Set objExcel = CreateObject("Excel.Application")

'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)

'Open our XLS file
Set workbook = objExcel.Workbooks.Open(directory & "\product-performance-2006.xls")
'We want to skip the header row, and then the blank row below
loopCount = 3

Do while not isempty(objExcel.Cells(loopCount, 1).Value)
  Dim i       'For looping through the columns on each row
  Dim value   'Value extracted from each cell

  'Spreadsheet is 6 columns across
  For i = 1 To 6
    value = objExcel.Cells(loopCount, i).Value
    'Do something with your value here!
    MsgBox value
  Next
  loopCount = loopCount + 1
Loop

objExcel.Workbooks.Close
objExcel.quit
objExcel = Empty
workbook = Empty
  

Download VBScript & Excel VBS

That's quite a bit to take in all at once - I've put comments in the code to help out a bit.

Before running a copy of this script you may want to comment out line 18, or put in something more useful - otherwise you'll be bombarded with alert boxes giving you each cells' value.

Also of note is value = objExcel.Cells(loopCount, i).Value - this is the part of the program which actually pulls a cells value (not formula) from the spreadsheet. In this example, we loop through each row (loopCount) until we've finished with the sheet, and for each row we recurse through the 6 columns (i) and extract the value.

Wrapping up

Slightly related to my VBScript & Oracle article, it's possible to construct SQL queries from looping through each row, and then send them off to a database. I'll hopefully come to this in a later post.

This should have given you a very basic introduction as to how you can start using VBScript to interact with Excel - leave a comment if you need any more clarification or help!

Fairly recently I was approached to create a system which parses data out of a CSV file and dumps said data into an Oracle table so that it could be reported on. The standard procedure for this is to send the source files to a Unix system and run them through with SQL Loader, but I didn't fancy going down that route...

There's a fair amount of information around The Internet on getting VBScript to communicate with an Oracle database and to perform various operations, but it's all a little fragmented. I've tried to consolidate what I've learnt from my experience, covering:

1. Connecting to an Oracle database with VBScript
2. Sending INSERT statements to the Oracle database
3. Executing a stored procedure on the Oracle database to manipulate the inserted data

Connecting to an Oracle database

Getting VBScript to connect to Oracle is a pretty easy step - it's just a bit tricky to get your head around the various constants involved, and the order in which to setup various aspects of the connection:

    Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString
  

Depending on your ODBC setup, the driver name might be slightly different from "Microsoft ODBC for Oracle", so you may need to change it. The above script should connect your script to your given database, provided all the connection parameters are correct.

Sending an INSERT statement

Having an active connection to Oracle is pretty pointless unless you're actually going to send a query to it. We'll just work with a basic INSERT statement here, to get the point accross - obviously you'd need to adapt this to your situation:

    const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO people (name, email) VALUES ('John Doe', 'john@doe.com');"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute
  

As an example, this would create a new row in the people table with the given values - easy.

Executing a stored procedure

The database driver also allows you to execute stored procedures on your Oracle database. In my scenario, I sent data to a staging table, where it was then manipulated by a stored procedure which enforced some rules and output to a final table, which is used for the reports.

    const cnstStoredProcedure = 4 'Command type - 4 is for stored procedure
commandString = "STORED_PROCEDURE_NAME"
thecommand.CommandText = commandString
thecommand.CommandType = cnstStoredProcedure
thecommand.ActiveConnection = connection
thecommand.Execute
  

Voila, stored procedure executed.

I'm happy to go into slightly more detail in another post or two, if anyone needs further elaboration or to see a full program structure for running this properly. You may also want to implement some error trapping - you never know when someone else might lock out your table or user account!