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!.
Comments:
Cool.. great article.

Looking forward to the DataSnap and ExtJS-Pascal articles.

Thanks for sharing.. Much appreciated.
 
I can not load files.
 
Thanks pikhovkins for ponting this out. Now you can Download files.
 
Thank you for the article! Very interesting and useful.
 
Hello!
Somewhere in source error.
I can normally view the database through the table on page grid2.html, but I can not edit, add and delete records. I think that customerslist.exe not handle POST-requests.
 
Win XP SP3
Apache 2.2
Lazarus 0.9.28.2 beta
 
pikhovkins, the source is tested and it works, it also handle POST requests.

Could you tell us what error are you receiving. To capture server-side errors try to use Firefox with Firebug.

Maybe the problem is the table structure you are using. Take a look at SavePerson method in the CGI program, where I use the fields
IdCliente, Apellido, Nombres, TelFijo, I think you are using different fields, and your database is raising an error when trying to Insert or Update data.
 
Also, I am looking forward to article about ExtPascal and FastCGI-applications.
 
This comment has been removed by the author.
 
Maybe you have Apache as a specially configured?
 
pikhovkins, one difference between your setup and mine is that I'm using Linux and FPC 2.5.1, maybe your version of custcgi unit has a bug.

Now, let's try to debug your situation. When you click on Edit button, a new instance of MyPeopleWindow is created, passing the Id of the selected record (did you select a row in the grid right?), with that info, the method setId of MyPeopleForm, does a POST requiest to "/cgi-bin/customerslist" with the param idPerson.

In the cgi program, each time a GET or POST request is made, the event HandleRequest is called, there you can see this code:

lIdPerson := ARequest.ContentFields.Values['idperson'];
if lIdPerson <> '' then
AResponse.Content := GetCustomer(lIdPerson)
else
AResponse.Content := GetCustomersList;

So, if the param idperson is sent, then the GetCustomer method is called, this method *should* return a JSon string...BUT, what happens if an exception is raised inside this method or in "fillJsonObject" that is called from GetCustomer?. The CGI app will never return the JSon string the caller is waiting for. That could be the cause of your problem.
 
You are right about the bug in the module custcgi in Lazarus. I have collected your example in FPC 2.4.0 and everything worked! :)
 
By the way, I downloaded the daily snapshot of Lazarus with the FPC 2.4.0 - your module is also earned.
I am happy :)
 
hii.. leonardo.
how about db connection on multiple module.
is it each module should create new connection?
 
No, you can create one datamodule containing the connection, and share it (by including it on the "uses" clause) to other modules.
 
Post a Comment



<< Home

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