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.

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.

This post details how to calculate the latitude and longitude for the south east corner of a world file. This means you can calculate all you need to overlay a correctly scaled image in systems such as Google Earth.

World files

A world file is a simple text file that accompanies a raster image enabling you to plot the image in it's correct geographic extent. It was introduced by GIS mammoth ESRI.

The snippet below represents the world file we will work on:

    0.004437536880778
0.000000000000000
0.000000000000000
-0.004437792567264
-103.206013320465620
40.059054557563236
  

It is a bit incomprehensible at first, but if we add a bit of descriptive text to it for demonstration purposes, it becomes a little clearer:

    0.004437536880778      <- Each X pixel represents this much longitude
0.000000000000000
0.000000000000000
-0.004437792567264     <- Each Y pixel represents this much latitude (negative)
-103.206013320465620   <- Longitude of the upper left pixel (West point)
40.059054557563236     <- Latitude of the upper left pixel (North point)
  

The information here allows you to easily understand the geographic position of the image (i.e. that area of the Earth that it represents), but it does not contain sufficient information to accurately plot the image in Google Earth, for example.

A world file image

A world file is accompanied by an image file - be it a BMP, GIF, JPG, PNG, TIFF - it doesn't really matter to the world file. In order to calculate the east and south coordinates we need to know the width and height of the image in computer terms - how many pixels wide and high it is.

For this example we will use an image that is 6526 x 5208.

Calculating the eastern point

Running on the above example of plotting a world file and it's image in Google Earth, we need to calculate the most easterly and southerly points. Along with the image file itself, we have enough information to make this simple calculation:

    east = west coordinate + (image width * line 1 of world file)
  

Remember that line 1 of the world file contains a number describing how much longitude 1 horizontal pixel of the image represents. So this would translate to:

    east = -103.206013320465620 + (6526 * 0.004437536880778)
east = -74.246647636508392
  

Calculating the southern point

Now all we need is the southerly point - as you might infer from the above point, it's pretty straightforward:

    south = north coordinate + (image height * line 4 of world file)
  

Again, line 4 contains the amount of latitude each pixel in the image's height represents - the geographic distance.

    south = 40.059054557563236 - 5280 * (-0.004437792567264)
south = 16.947031
  

And now we have enough information to plot the world file and it's image onto Google Earth, or any mapping system of choice.

I'm sure there are countless tools that do this as part of GIS packages, but it is nice to know how to go about it step by step. If you're going to be working with mapping without using other packages, this is also an essential process if you wish to convert latitude and longitude coordinates to the screen, as it gives them a bounding box in which to scale themselves.

Feel free to post any comments or insights below, and any links to interesting GIS programming related posts or pages - a domain fairly new to me.

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 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!