Sunday, March 21, 2010

 

Web 2.0 programming with Object Pascal (Part 2)


As I promised in my last article, here I'll show you how to add CRUD (Create, Read, Update and Delete) operations to the sample application.

The first step is to add a toolbar to the Grid, with three buttons, btnAdd, btnEdit and btnDelete in charge of Inserting, Updating and Deleting data. Also I'll create a new popup form where the user will work with that data.

The new Grid

Instead of overwriting the files used in the last example, I recommend to create a new directory called samples2, containing all the files showed in this article.

NOTE: the file grid2.html, has the same contents of grid1.html, so just copy it from the last example and paste in the new directory, and don't forget to rename to grid2.html. After that, you'll have to rename the div id "grid1" by "grid2".

This is the code for grid2.js:


Ext.onReady(function(){

var dataStore = new Ext.data.JsonStore({
//url: '/samples/customerslist.json',
url: '/cgi-bin/customerslist',
root: 'rows',
method: 'GET',
fields: [
{name: 'id', type: 'int'},
{name: 'firstname', type: 'string'},
{name: 'lastname', type: 'string'},
{name: 'age', type: 'int'},
{name: 'phone', type: 'string'}
]
});

var btnAdd = new Ext.Toolbar.Button({
text: 'Add',
handler: function(){
var win = new MyPeopleWindow();
// to refresh the grid after Insert
win.afterPost = function(){
dataStore.load();
};
win.show();
}
});

var btnEdit = new Ext.Toolbar.Button({
text: 'Edit',
handler: function(){
var win = new MyPeopleWindow(selRecordStore.id);
// to refresh the grid after Update
win.afterPost = function(){
dataStore.load();
};
win.show();
}
});

var btnDelete = new Ext.Toolbar.Button({
text: 'Delete',
handler: function(){
Ext.Msg.confirm(
'Delete customer?',
'Are you sure to delete this customer?',
function(btn){
if(btn == 'yes'){
var conn = new Ext.data.Connection();
conn.request({
url: '/cgi-bin/customerslist',
method: 'POST',
params: {"delete_person": selRecordStore.id},
success: function(response, options) {
// refresh the grid after Delete
JSonData = Ext.util.JSON.decode(response.responseText);
if(JSonData.success)
dataStore.load();
else
Ext.Msg.alert('Status', JSonData.failure);
},
failure: function(response, options) {
Ext.Msg.alert('Status', 'An error ocurred while trying to delete this customer.');
}
});
}
}
);
}
});

var myGrid1 = new Ext.grid.GridPanel({
id: 'customerslist',
store: dataStore,
columns: [
{header: "First Name", width: 100, dataIndex: "firstname", sortable: true},
{header: "Last Name", width: 100, dataIndex: "lastname", sortable: true},
{header: "Age", width: 100, dataIndex: "age", sortable: true},
{header: "Phone", width: 100, dataIndex: "phone", sortable: true}
],
sm: new Ext.grid.RowSelectionModel({
singleSelect: true,
listeners: {
rowselect: function(smObj, rowIndex, record){
selRecordStore = record;
}
}
}),
tbar: [
btnAdd,
btnEdit,
btnDelete
],
autoLoad: false,
stripeRows: true,
height: 200,
width: 500
});

dataStore.load();

myGrid1.render('grid2');
});


Now, the editor form:


MyPeopleForm = Ext.extend(Ext.FormPanel, {
initComponent: function(){
Ext.apply(this, {
border:false,
labelWidth: 80,
defaults: {
xtype:'textfield',
width: 150
},
items:[
{xtype:'numberfield',fieldLabel:'Id',name:'id'},
{fieldLabel:'First Name',name:'firstname'},
{fieldLabel:'Last Name',name:'lastname'},
{xtype:'numberfield',fieldLabel:'Age',name:'age'},
{fieldLabel:'Phone',name:'phone'}
]
});
MyPeopleForm.superclass.initComponent.call(this, arguments);
},
setId: function(idPerson) {
this.load(
{
method: 'POST',
url: '/cgi-bin/customerslist',
params: {'idperson': idPerson}
}
);
}
});

MyPeopleWindow = Ext.extend(Ext.Window, {
constructor: function(idPerson){
MyPeopleWindow.superclass.constructor.call(this, this.config);
// if idPerson is not null, then edit record
// otherwise it's a new record
if(idPerson != null)
this.form.setId(idPerson);
},
afterPost: function(){
this.fireEvent('afterPost', this);
},
initComponent: function(){
Ext.apply(this, {
title: 'Loading data into a form',
bodyStyle: 'padding:10px;background-color:#fff;',
width:300,
height:270,
closeAction: 'close',
items: [ this.form = new MyPeopleForm() ],
buttons: [
{
text:'Save',
scope: this,
handler: function(){
this.form.getForm().submit({
scope: this,
url: '/cgi-bin/customerslist',
method: 'POST',
// here I add the param save_person
// to let the cgi program decide
// a course of action (save person data in this case).
params: {'save_person':'true'},
success: function(form, action){
// on success I just close the form
this.afterPost();
this.close();
},
failure: function(form, action){
Ext.Msg.alert("Error","There was an error processing your request\n" + action.result.message);
}
});
}
},
{
text:'Cancel',
handler: function(){this.close();},
// important!, without "scope: this"
// calling this.close() will try to close the Button!,
// and we need to close the Window, NOT the button.
scope: this
}
]
});
MyPeopleWindow.superclass.initComponent.call(this, arguments);
}
});


That's all for the UI part. Now let's create our new customerslist.pp file, containing all the data required for the CGI application.


program cgiproject1;

{$mode objfpc}{$H+}

uses
Classes,SysUtils,
httpDefs,custcgi, // needed for creating CGI applications
fpjson, // needed for dealing with JSon data
Db, SqlDb, ibconnection; // needed for connecting to Firebird/Interbase;

Type
TCGIApp = Class(TCustomCGIApplication)
Private
FConn: TSqlConnection;
FQuery: TSqlQuery;
FTransaction: TSqlTransaction;
procedure ConnectToDataBase;
function GetCustomersList: string;
function GetCustomer(AIdPerson: string): string;
procedure FillJSONObject(AJson: TJsonObject);
function SavePerson(ARequest: TRequest): string;
function DeletePerson(ARequest: TRequest): string;
Public
Procedure HandleRequest(ARequest : Trequest; AResponse : TResponse); override;
end;

procedure TCGIApp.ConnectToDataBase;
begin
FConn := TIBConnection.Create(nil);
FQuery := TSqlQuery.Create(nil);
FTransaction := TSqlTransaction.Create(nil);
with FConn do
begin
DatabaseName := 'TARJETA';
UserName := 'SYSDBA';
Password := 'masterkey';
HostName := '192.168.1.254';
Connected := True;
Transaction := FTransaction;
FQuery.Database := FConn;
end;
end;

procedure TCGIApp.FillJSONObject(AJson: TJsonObject);
begin
AJson.Add('id', TJsonIntegerNumber.Create(FQuery.FieldByName('IdCliente').AsInteger));
AJson.Add('firstname', TJsonString.Create(FQuery.FieldByName('Apellido').AsString));
AJson.Add('lastname', TJsonString.Create(FQuery.FieldByName('Nombres').AsString));
AJson.Add('age', TJSONIntegerNumber.Create(FQuery.FieldByName('IdCliente').AsInteger));
AJson.Add('phone', TJsonString.Create(FQuery.FieldByName('TelFijo').AsString));
end;

function TCGIApp.GetCustomersList: string;
var
lPerson: TJSONObject;
lJson: TJSONObject;
lJsonArray: TJSONArray;

begin
(* Query the database *)
FQuery.Close;
FQuery.Sql.Text := 'select * from clientes';
FQuery.Open;
FQuery.First;

lJsonArray := TJSONArray.Create;
lJson := TJSONObject.Create;
try
while not FQuery.Eof do
begin
lPerson := TJSONObject.Create;
fillJsonObject(lPerson);
FQuery.Next;
(* Fill the array *)
lJsonArray.Add(lPerson);
end;
(* Add the array to rows property *)
lJson.Add('rows', lJsonArray);
Result := lJson.AsJSON;
finally
lJson.Free;
end;
end;

function TCGIApp.GetCustomer(AIdPerson: string): string;
var
lPerson: TJSONObject;
lJson: TJSONObject;

begin
(* Query the database *)
FQuery.Close;
FQuery.Sql.Text := 'select * from clientes where IdCliente=' + AIdPerson;
FQuery.Open;
FQuery.First;

lJson := TJSONObject.Create;
try
lPerson := TJSONObject.Create;
fillJsonObject(lPerson);
(* Add the array to rows property *)
lJson.Add('success', 'true');
lJson.Add('data', lPerson);
Result := lJson.AsJSON;
finally
lJson.Free;
end;
end;

function TCGIApp.SavePerson(ARequest: TRequest): string;
var
lId: string;
lFirstName: string;
lLastName: string;
lPhone: string;
lSql: string;
begin
lId := ARequest.ContentFields.Values['id'];
lFirstName := ARequest.ContentFields.Values['firstname'];
lLastName := ARequest.ContentFields.Values['lastname'];
lPhone := ARequest.ContentFields.Values['phone'];
if lId <> '' then
lSql := 'update clientes set ' +
'nombres = ''' + lLastName + ''', ' +
'apellido = ''' + lFirstName + ''', ' +
'telfijo = ''' + lPhone + ''' where idcliente=' + lId
else
begin

lSql := 'insert into clientes(IdCliente, Nombres, Apellido, TelFijo) ' +
'values(Gen_Id(SeqClientes, 1),''' + lFirstName + ''', ''' + lLastName + ''', ''' + lPhone + ''')';
end;

try
FQuery.Sql.Text := lSql;
FConn.Transaction.StartTransaction;
FQuery.ExecSql;
FConn.Transaction.Commit;
Result := '{''success'': ''true''}';
except
on E: Exception do
Result := '{''message'': "' + E.message + '"}';
end;
end;

function TCGIApp.DeletePerson(ARequest: TRequest): string;
var
lId: string;
begin
lId := ARequest.ContentFields.Values['delete_person'];
try
FQuery.Sql.Text := 'delete from clientes where idcliente=' + lId;
FConn.Transaction.StartTransaction;
FQuery.ExecSql;
FConn.Transaction.Commit;
Result := '{''success'': ''true''}';
except
on E: Exception do
Result := '{''failure'': ''Error deleting person.''}';
end;
end;

Procedure TCGIApp.HandleRequest(ARequest : TRequest; AResponse : TResponse);
var
lIdPerson: string;
begin
if ARequest.ContentFields.Values['delete_person'] <> '' then
begin
AResponse.Content := DeletePerson(ARequest);
end
else
if ARequest.ContentFields.Values['save_person'] <> '' then
begin
AResponse.Content := SavePerson(ARequest);
end
else
begin
lIdPerson := ARequest.ContentFields.Values['idperson'];
if lIdPerson <> '' then
AResponse.Content := GetCustomer(lIdPerson)
else
AResponse.Content := GetCustomersList;
end;
end;

begin
With TCGIApp.Create(Nil) do
try
Initialize;
ConnectToDatabase;
Run;
finally
Free;
end;
end.


To compile this file, I use a simple Bash script, that copies the compiled program to /var/www/cgi-bin directory, where my Apache2 is configured to host executable CGI programs.


#!/bin/bash
fpc -XX -Xs -b -v ./customerslist.pp
cp ./customerslist /var/www/cgi-bin


In this article, I showed how to create an HTML page containing a grid, with a toolbar that allow CRUD operations.

After I published my last article, some of you told me that this was too much work for showing just a simple grid on a web page, and I agree, but when you start adding complexity to the application, the effort needed to add features is marginal, and the separation of concerns used here allows to use better ways to speed up the code creation. I mean, instead of using a simple text editor to create the ExtJs code as I did (well, VIM is not a simple editor), you could try the new ExtJs Designer, and for the Object Pascal part, it is very easy to replace it with higher level frameworks, like WebBroker or DataSnap.

Some of you asked why I didn't use ExtPascal in this article. I didn't use it because I wanted to show all the parts involved in an ExtJs application (HTML, JS, CGI App), and ExtPascal creates the JavaScript code automatically hiding those internals to the programmer, I think it is very powerfull for programmers who already know how to work with plain ExtJs, and after this article, you already know how it works, so now I can write about ExtPascal!.

Here are the files for this article.


What's next?

Before writing about ExtPascal, I'll show you how to replace the CGI part by a DataSnap Server application. See you in my next post!.

This page is powered by Blogger. Isn't yours?