Leonardo's blog

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