SOLVED: Rails with MySQL on Windows

For the last couple of days, I was struggling to get Rails to talk to MySQL on Windows. Though I was able to install the "mysql" gem, I got an error message when I tried to run the "rake db:create" command for creating the database tables needed for my model. Web searches revealed many detailed discussions on the problem, but no definite solution. Whatever approach I tried only resulted in a different error message. Finally, against the run of play (as cricket commentators would say), I was able to get things working. So here are the steps I followed, for the benefit of others who may be facing the same issue:

1. Install Ruby and Rails using the RailsInstaller - I used version 1.3, which provides Ruby 1.8.7 and Rails 3.0.7, along with other related packages like Bundler and DevKit

2. Install MySQL Community Server - I already had version 5.5.23

3. Install the mysql gem:

gem install mysql

4. Carefully read the message from the mysql gem installer - I got:

You've installed the binary version of mysql2. It was built using MySQL Connector/C version 6.0.2. It's recommended to use the exact same version to avoid potential issues.

At the time of building this gem, the necessary DLL files where available in the following download:

http://dev.mysql.com/get/Downloads/Connector-C/mysql-connector-c-noinstall-6.0.2-win32.zip/from/pick

And put lib\libmysql.dll file in your Ruby bin directory, for example C:\Ruby\bin

5. Download the exact same version of the MySQL C connector as mentioned in the message you see. Get the zip archive, not the MSI installer. Simply extract the "lib\libmysql.dll" file and put it in your "Ruby\bin" directory (you don't really need the rest of the C connector files).

6. Add a new line "gem mysql" to the Gem file of your Rails project.

7. Replace the (sqlite) contents of your config\database.yml file with:

development:

  adapter: mysql

  encoding: utf8

  database: blog_development

  pool: 5

  username: root

  password:

  socket: /tmp/mysql.sock

Now, when you run the "rake db:create" command, it should work without any issues. Even before that, you can test the MySQL connectivity using irb (the Interactive Ruby shell):

C:\Documents and Settings\User>irb

irb(main):001:0> require "Rubygems"

=> true

irb(main):002:0> require "mysql"

=> true

irb(main):003:0> Mysql.connect("localhost", "root", "", "phpwork")

=> #<Mysql:0x2cdfca8>

irb(main):004:0>

Hi, Ruby!

In the last couple of months, I've more or less completed my target of revising the basic concepts of Perl, Python and PHP. So I decided to check out a relatively newer member of the Open Source Programming Languages family. Ruby, along with the Rails framework that is based on it, has captured the imagination of web developers worldwide.

Ruby is said to be a truly object-oriented language that is easy to learn, and has rich libraries that are easy to extend. The Rails framework includes everything that you would need to create a database-driven web application using the MVC pattern. As it needs minimal configuration, you can develop applications faster than with other frameworks.

At least that's what the tutorials say. Today I'll put these statements to the test by (a) learning the basics of Ruby, (b) installing Ruby, (c) running a Hello World program, (d) running a simple Embedded Ruby program, (e) installing and testing the Rails framework, and (f) creating a Rails + MySQL version of my good ol' music database project.

Note that I'll follow the "Ruby on Rails Introduction" tutorial on tutorialspoint.com, which was the first Ruby tutorial I found, and use their sample code. After going through the first few pages of this tutorial, I installed Ruby from http://rubyinstaller.org/

I then tested the Hello World program and the simple Embedded Ruby program from the tutorial. Next, I tried to install the Rails framework using the "gem" utility that comes with Ruby. This utility can be used to install Ruby packages, called "Gems".

But I ran into an error during the Rails installation, due to the absence of the necessary build tools for the 'atomic' native gem. To get around this issue, I had to first install the Ruby Developer Kit. I downloaded the installer from the same URL as Ruby - http://rubyinstaller.org/ - and then followed the step by step installation instructions given by http://learnwebtutorials.com/.

(Note: An easier way is to visit the RailsInstaller website and install Ruby, Rails and many other packages at one go)

Next I had to create a demo Rails project as given in the tutorial, run the WEBrick web server that is bundled with Rails, and test the demo page from my browser. Note that the right command to create a demo Rails project is "rails new demo" and not "rails demo" as given in the tutorial. Also, the right commands to start the WEBrick web server are "cd demo" followed by "rails server". In both these cases, the commands given in the tutorialspoint.com web tutorial seem to be outdated ones.

Thus I've completed 5 of my 6 objectives for today, viz.: (a) learnt the basics of Ruby, (b) installed Ruby, (c) ran a Ruby Hello World program, (d) ran a simple Embedded Ruby program, and (e) installed and tested the Rails framework. Creating a Rails + MySQL version of my music database project will take more study and, quite possibly, a lot of trial and error. So I'll try and complete that objective later this week. Meanwhile let me search for some more good Ruby tutorials on the Web.

Skills for Starting a Company

A young trekker friend recently asked me what are the key skills he would need for starting his own company, which is something he intends to do in the future. Since we were just chatting, I asked him for some time to put together my thoughts on this subject. Not that I'm an expert on the topic but I can surely speak from second-hand experience, having been part of many startups and small IT companies. Some of these have done reasonably well, others have done spectacularly badly and some have just maintained a status quo (which I personally believe is worse than a spectacular failure, but that's another blog post). So here are the 5 top skills that I believe my friend needs to develop before he starts his own company:

Communication

Any company needs to sell something - an idea, a product or a service - in order to succeed. So the very first skill you need is the ability to communicate your company's offerings. Of course, you can always employ a sales guy to handle the actual selling but as an entrepreneur, you need to be in the forefront of marketing your company and its products. Years before you actually start your firm, you need to start building a network of contacts. This network will eventually give you the leads that your sales team would exploit, to win business for your company. Once you get the deals, you need to keep communicating with your customers to ensure timely payments, to get contract renewals or new deals and to gather more leads.

Leadership

In most fields, you cannot be a one-man company. That may be how you start off but eventually you have to build and lead a team of specialists in various functions like sales, delivery, support, admin, etc. Leading is not just about coordinating - you have to define your company values, communicate those to the team, and lead by example. Communication plays a very key role in keeping your team engaged. Initially there may be hiccups and setbacks. But if you keep your team informed about the problems, they will support you through thick and thin. Even when the business is doing well, you cannot afford to neglect your communication duties as a leader. Many promising companies have floundered by losing the support of their core team.

Problem Solving

Many people wonder whether technical skills are necessary to start your own company. For example, do you need to be a baker to start a cake shop? Or do you need to be a web programmer to start a dot-com? The answer is clearly No. But at the same time, you cannot be totally unaware of the subject. Even if you have experts to do the dirty work, you still need to understand the problems your business is facing and you need the skills to solve those problems. Simply put, problem solving is the process of identifying, analyzing and solving problems. This is a skill that you pick up through work experience more than anything else. So I would advise my friend to work in the chosen field for 5-10 years before he starts his own company.

Empathy

Earlier, I talked about the importance of communicating with your team about your successes and failures. But mere communication is not enough, especially in tough times. Telling your employees about your problems will not automatically buy you their support. For example, if the company runs into losses in a recession year, you cannot just expect the team members to work for a fraction of their salary. You need to be able to put yourself in their shoes and understand their problems, especially financial ones. Then you need to provide solutions for those problems, even at the cost of your profit calculations. At the same time, you need to ensure that you are investing in the right people, those who would stick with you in the long run. 

Humility

In my view, it is easier to be a leader in a period of failure than in a period of success. The intoxicating sweetness of success has a tendency to go to your head, impairing your judgement and taking you to the edge of pitfalls. Maybe CXOs of large corporations can afford their eccentric ego trips and subsequent losses, but you cannot. Keep yourself humble at all times and resist the temptation to believe the larger than life image of your company that is created by your marketing team. Remember that the competition is always out there, waiting for you to make one false move. Stay focussed on your customers, stay in touch with your employees and stay rooted to your company's values and core competencies and your business plan. 

Excel output from Perl script

In an earlier post, I had explained how to output some tabular data from a PHP script into an Excel sheet using PHPExcel. Today, I shall explain the Perl equivalent, using a module named Spreadsheet::WriteExcel. This module can be easily installed using PPM. By looking at the documentation and the code examples, we can quickly rustle up our own Perl code to create an Excel sheet containing the songs data from our famous music database.

    # Create a new Excel workbook
    my $workbook = Spreadsheet::WriteExcel->new('music_list.xls');

    

    # Add a worksheet

    my $worksheet = $workbook->add_worksheet();

    

    #  Add and define a format for title row

    my $format = $workbook->add_format(); # Add a format

    $format->set_bold();

    $format->set_color('black');

    my $slate_grey = $workbook->set_custom_color(40, '#6D7B8D'); # slate grey color

    $format->set_bg_color($slate_grey);

    $format->set_align('center');

    

    # Write the Excel title row using the new format

    $worksheet->write(0, 0, "Song Title", $format);

    $worksheet->write(0, 1, "Artist Name", $format);

    $worksheet->write(0, 2, "Album Title", $format);

    $worksheet->write(0, 3, "Year Title", $format);

    

    # Increase the column width for clarity

    $worksheet->set_column('A:D', 25);

    # Define the format for the cell borders

    my $cell_border_format = $workbook->add_format(border => 1);

    if ($row_count == 0) {

        $worksheet->write(1, 0, "No records");

    } else {

        for (my $i=0; $i < $row_count; $i++) {

            # Write the actual table contents and also set the cell borders

            $worksheet->write($i+1, 0, $excel_content->{"a" . $i}, $cell_border_format);

            $worksheet->write($i+1, 1, $excel_content->{"b" . $i}, $cell_border_format);

            $worksheet->write($i+1, 2, $excel_content->{"c" . $i}, $cell_border_format);

            $worksheet->write($i+1, 3, $excel_content->{"d" . $i}, $cell_border_format);

        }

    }

One catch is that the Spreadsheet::WriteExcel module cannot overwrite an existing Excel file (by design, to avoid accidental data loss). So let us put in a bit of code to delete the existing file, if any.

    # Check if Excel file already exists

    if (-e 'music_list.xls') {

        # Try and delete old Excel file

        if (unlink('music_list.xls') == 0) {

            # Show error message if delete fails

            print "Old Excel file could not be deleted";

            # Show the HTML footers and exit

            &tail($dbh);

            exit(0);
        }
    }

Small Retailers as a Market for Tech Startups

Last week, ET published an article on how small and medium retailers wanting to take their business online are turning to technology startups for help. These typically charge Rs 1,000 to Rs 4,000 a month for technology services ranging from building websites to linking them with payment gateways and logistics providers. When compared to the potential earnings from doing big projects for large customers, these amounts are negligible. Still some of these companies are confident of being profitable soon. This got me thinking on how it makes business sense for these startups to target retailers as a market for cloud-based services. To be profitable with this model they would need huge volumes, meaning a large number of retailers. But are Indian retailers ready to take the Web plunge?

Let's look at it from a retailer's point of view. Here shall assume that the retailer in question is not just a grocer or a hardware store owner. He/she has one or more specialized products or services and seeks to target a particular segment of customers. Consider an exercise equipment shop, for example. For such an entrepreneur, normal Above The Line advertising alone will not bring in customers. Nor will word of mouth publicity get them the large number of leads they require, given their low conversion ratio of leads to sales. Since theirs is a lifestyle product, they would need to reach out to their target audience through micro level focused messaging. An online presence, including social media outreach, would be an essential part of their strategy. Here's where a tech startup can help.

Social media advertising and reviews on popular forums can attract the customer's attention to either the physical shop (where it is arguably easier to close a sale) or at least a website. If the website is designed and built well, and has payment gateway integration, it is possible to push the customer into buying the product online itself, while a tie-up with a logistics provider ensures that the product is delivered to the customer's house within an acceptable timeline. If the customer can be coaxed into writing a Web review of the product, that in turn would bring in more leads for the entrepreneur. On seeing that all these services are available for a moderate monthly payment, other retailers would jump on the bandwagon. So it's a  win-win situation for the entrepreneurs as well as the tech startup.

Object-Oriented Programming in Perl

My first and last brush with Perl OO programming was back in 2008. I was in the Netherlands with a couple of colleagues, getting "KT" for a set of small e-commerce applications whose support was being outsourced to India. We were horrified to see that the code was object-oriented, with many layers of inheritance, which made it extremely difficult to learn. As part of the Knowledge Transfer, we were asked to create installers and use them to get the applications running on a new server. But we would inevitably run into errors, which would send us hunting from file to file and class to class to try and find the ultimate parent class. Even the Dutch team that had been supporting the code for a few years did not know the nitty-gritty of the inheritance - they  had only learnt enough to support the applications on a day to day basis.

Having learnt C++ and Java earlier, I had realized then that Perl is not the best language for object-oriented programming. But the fact that it supports OO means that you need to know the basics, just in case you face a situation like I did. There are many online tutorials available on the subject but the best ones come straight from the horse's mouth, viz: the perlootut tutorial and  the perlobj language reference. After going through these, you may feel confident enough to create your own class and then test it with a simple script. I first created a very basic class called Muzak to represent the songs from my music collection. Each Muzak object has up to 4 attributes - a song name (mandatory), artist name, album title and year. It has 1 method - artist() - that returns the artist name. I put the class in a Perl module called Muzak.

Muzak.pm

package Muzak;

# Constructor

sub new {

    my $class = shift;

    my ( $song, $artist, $album, $year ) = @_;

    

    if ($song eq "") {

        die "Muzak object requires at least Song Name attribute";

    }

    my $self = bless {

        song => $song,

        artist => $artist,

        album => $album,

        year => $year,

    }, $class;

    return $self;

}

# Method: Get artist

sub artist {

    my $self = shift;

    return $self->{artist};

}

1;

Note that the "1;" at the end of the .pm file is critical. If you miss it, the Perl interpreter will throw an error while testing the script, which would look like "Muzak.pm did not return a true value at test_muzak.pl line 6". Also note that the check for non-blank song name is also not mandatory to create a working class - it is just a logical check that I added.

test_muzak.pl

use Muzak;

use feature qw(say);

# Create new Muzak object with all 4 paramaters

my $muzak1 = Muzak->new('Aqualung', 'Jethro Tull', 'Aqualung', '1971');

# Print the song artist name

say "Artist1 is " . $muzak1->artist();

# Create new Muzak object with only 1 parameter

my $muzak2 = Muzak->new('Bouree');

# Try printing the song artist name

say "Artist2 is " . $muzak2->artist();

# Try creating new Muzak object with 0 parameters

my $muzak3 = Muzak->new();

# Try printing the song artist name

say "Artist3 is " . $muzak3->artist();

There's a lot more to explore in Perl OO programming. I can write code that is much more complicated, like those Dutch applications (which were created, if I remember correctly, by a UK firm). More importantly, I can write code that is more useful than just playing around with song details. But that's another day, another class, another blog post

Excel output from PHP script

Let's say you've written a PHP script that queries a data source (say a MySQL database) and displays the output in the browser. Now you want to export this data to an Excel sheet that the user can print or process further. What are the different ways of doing this?

The first way I ever learned was to simply set the PHP content type header, like "Content-type: application/vnd.ms-excel; name='excel'". This prompts the user to open or save an Excel sheet, which would contain our tabular content. But in this method, you would not have any control over the formatting of the Excel sheet, such as borders, fonts, colors, etc.

If you want to generate a well-formatted Excel sheet, you can use the PHP library called PHPExcel, which can be downloaded from here. Just copy part of the zip file contents (see the "install.txt" file) to a sub-directory on your web server document root and you can get going. It is easy to save your tabular content to an Excel file on the server, using PHPExcel.

The code required for creating the Excel file is fairly simple, and you can put it together by looking at the examples given in the PHPExcel zip file. If you get stuck, say on a formatting issue, you can always google the solution. PHPExcel seems to be quite popular and so, more likely than not, your question would have been answered on one of the coding forums.

Here is my code for saving my Music Database List content as an Excel file:

/* Include PHPExcel */

require_once '../../Classes/PHPExcel.php';

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();

// Set document properties

$objPHPExcel->getProperties()->setCreator("Ajay Ramakrishnan")

    ->setLastModifiedBy("Ajay Ramakrishnan")

    ->setTitle("Music Database List")

    ->setSubject("Music Database List")

    ->setDescription("Music Database List, generated using PHPExcel.")

    ->setKeywords("Music Database PHPExcel")

    ->setCategory("Music Database List");

// Add title row to Excel file

$objPHPExcel->setActiveSheetIndex(0)

    ->setCellValue('A1', $a[0])

    ->setCellValue('B1', $b[0])

    ->setCellValue('C1', $c[0])

    ->setCellValue('D1', $d[0]);

// Set the title row font to bold and background color to light slate grey

$header_range = "A1:D1";
$objPHPExcel->getActiveSheet()->getStyle($header_range)->getFont()->setBold(true);
$bgColorArray = array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => '6D7B8D')
                    )
                );
$objPHPExcel->getActiveSheet()->getStyle($header_range)->applyFromArray($bgColorArray);

// Loop through the existing data rows

for ($i=2; $i <= $row_count; $i++) {

    $objPHPExcel->setActiveSheetIndex(0)

        ->setCellValue('A' . $i, $a[$i-1])

        ->setCellValue('B' . $i, $b[$i-1])

        ->setCellValue('C' . $i, $c[$i-1])

        ->setCellValue('D' . $i, $d[$i-1]);

    }

// Resize the columns

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

// Set the border for all the non-empty cells

$i = $i - 1;

$styleArray = array(

        'borders' => array(

            'left' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'right' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'top' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'bottom' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

        ),

    );

$objPHPExcel->getActiveSheet()->getStyle('A1:D' . $i)->applyFromArray($styleArray, False);

// Rename worksheet

$objPHPExcel->getActiveSheet()->setTitle('Music_DB_List');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save(str_replace('.php', '.xlsx', __FILE__));