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 GridInstead 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 := ‘’; 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/bashfpc -XX -Xs -b -v ./customerslist.ppcp ./customerslist /var/www/cgi-binIn 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 are closed.