Thursday, July 17, 2008

Using Sqlite3 with Fcl-Db

In this tutorial, I'll try to explain how to access Sqlite3 databases using
FreePascal and Fcl-Db. Fcl-Db is the standard way of accessing databases using FreePascal, but you can find some other methods to connect to them, such as LibSql and Zeos.

The first step is to install Sqlite versión 3.x.x, please go to http://www.blogger.com/www.sqlite.org to download a stable version and install in your computer. I'll don't explain how to install it, you are a programmer, you should know how to install a program.

Creating the "tutorial" database

Creating a database using the command line SQLite Client is as easy as:
>sqlite3 tutorial.db
With that command, you created a file named "tutorial.db" and started the command line SQLite Client. Note I'm using sqlite3, not sqlite, the older is a client for 2.8.xx version.

Now, create the customers table using this command:
sqlite> create table customers(id integer not null primary key,
...> firstname varchar(100),
...> lastname varchar(100),
...> borndate date);
After creating the table, return to the shell:
sqlite> .quit
You can do a 'ls' in Linux to check if the file tutorial.db exists.

Sample program

The following sample program assumes you have created the tutorial.db database in the same directory as the compiled program, just type it in any text editor, then save as sqlitetest.pp and call FreePascal compiler from the command line using this command:

fpc -Sd -B -CX -Xs sqlitetest.pp

The program

program SqliteTest;

uses
db, sqlite3ds, SysUtils;

var
dsTutorial: TSQLite3Dataset;
mSql: string;
(* TFields declarations
You can use Field[n].AsString but its slower *)

mId: TIntegerField;
mFirstName: TStringField;
mLastName: TStringField;
mBornDate: TDateTimeField;

begin
dsTutorial := TSqlite3Dataset.Create(nil);

try
with dsTutorial do
begin
FileName := 'tutorial.db';
TableName := 'customers';
PrimaryKey := 'Id';

(* Define the Insert skleton -uptate and delete works the same way-*)
mSql := 'insert into customers(firstname, lastname, borndate) ' +
'values(''%s'', ''%s'', %f)';

(* Non transactional method *)
(* Insert first customer *)
Sql := Format(mSql, ['Leonardo', 'Ramé', Now]);
ExecSql;

(* Insert second customer *)
Sql := Format(mSql, ['Michael', 'Stratten', Now]);
ExecSql;

(* Transactional method *)
(* I don't really know why I must populate the TFields using a Select,
if you know an elegant way to accomplish this, please tell me. *)
Sql := 'select Id, FirstName, LastName, BornDate from customers limit 1';
Open;

(* Assign TFields *)
mId := TIntegerField(Fields[0]);
mFirstName := TStringField(Fields[1]);
mLastName := TStringField(Fields[2]);
mBornDate := TDateTimeField(Fields[3]);

(* Append, Edit or Insert for the first field *);
Append;
mFirstName.Value := 'Juan';
mLastName.Value := 'Pérez';
mBornDate.Value := Now;
Post;

(* Append, Edit or Insert for the second field *);
Insert;
mFirstName.Value := 'Johan';
mLastName.Value := 'Arndth';
mBornDate.Value := Now;
Post;

(* This commits the data to the db. *)
ApplyUpdates;

(* Now, select all fields *)
Close;
Sql := 'select Id, FirstName, LastName, BornDate from customers';
Open;

(* To go to the first record of the DataSet, use First.
This isn't usefull here since Open points to the first record,
but you'll need in your projects so I keep it in the example. *)
First;
while not Eof do
begin
mId := TIntegerField(Fields[0]);
mFirstName := TStringField(Fields[1]);
mLastName := TStringField(Fields[2]);
mBornDate := TDateTimeField(Fields[3]);

writeln (IntToStr(mId.Value) + ' - ' + mFirstName.Value + ', ' +
mLastName.Value + ' - ' + DateToStr(mBornDate.Value));
(* Move to the next record *)
Next;
end;
end;
finally
dsTutorial.Free;
end;

end.

Sunday, July 6, 2008

Powerful CGI applications

In the article Myths About CGI Scalability published some time ago by Lars from z505, one of the authors of Powerful Web Utilities, he exposes a problem every CGI programmer faces with database applications. As you can see in that document, every time a CGI is called by a web browser, the operating system execute it and wait until its end showing the results to the client's browser. This operation is cached by the operating system after the first execution of the CGI, resulting in a faster execution of following calls.

If a web application uses a database, it's very common to create a CGI that connects to the database, perform a query then format the results and return them to the client. Using this approach, every time a user try to execute the CGI program a new connection to the database is created, resulting in slow and inefficient response times.

To resolve this problem, I created a simple CGI that sends socket commands to a program (I'll call it AppServer) wich takes care of database connections and heavy processes and returns its results to the CGI to be shown in the client's browser, using only one database connection to handle every CGI request. If the CGI finds the AppServer isn't running, it starts an instance and send the commands.

One interesting part of this approach is it runs in cheap hosting accounts, using Apache, IIS or any CGI capable web server. Also it can be compiled using FreePascal or Delphi.

The example uses PWU and Synapse for the AppServer, but you can use WebBroker or any other CGI technology.

You can download the CGI client and server to give it a try.

The CGI side is this:
uses
classes,
PwuMain,
HttpSend,
Windows;

function GetHtml: Boolean;
var
lHttp: THTTPSend;
lHtml: TStringList;

begin
Result := False;
lHtml := TStringList.Create;
lHttp := THTTPSend.Create;

with lHttp do
begin
HTTPMethod('GET', 'localhost:85/');
lHtml.LoadFromStream(Document);
Result := lHtml.Text <> '';
out(lHtml.Text);
end;
lHttp.Free;
lHtml.Free;
end;

var
lHandle: longint;

begin
if not GetHtml then
begin
(* If not connected, start the process *)
{$IFDEF WIN32}
lHandle := ShellExecute(0 , 'open', '.\httpserver.exe',
nil, nil, SW_HIDE);
{$ELSE}
lHandle := Shell('./httpserver');
{$ENDIF}
while True do
begin
if GetHtml then
Break;
end;
end
end.

Friday, July 4, 2008

Vonage calling from ObjectPascal

Last week, a customer asked if he could call using his Vonage phone to call from an application I developed some time ago. After searching for a way to do this, I stumbled upon this tutorial, then adapted the examples to ObjectPascal.

The results

The first problem I found was how to connect to an HTTPS site. Of course the answer was Synapse with the help of OpenSSL libraries.

I created couple of small functions that first log in to a Vonage account, then place the call. The functions are contained in this unit:

unit vonagecall;

interface

(* Calls to Vonage phone *)
function CallToVonage(AUserName, APassword, AFromNumber, AToNumber: string): string;

implementation

uses
Classes,
ssl_openssl,
httpsend;

function GetVonageNumbers(AUserName, APassword: string): string;
(* This function must be allways called from CallToVonage *)
var
lHttp: THTTPSend;
lParams: string;
lResponse: TStringStream;
begin
Result := '';
lHttp := THTTPSend.Create;
lResponse := TStringStream.Create('');
try
lParams := 'username=' + AUserName;
lParams := lParams + '&password=' + APassword;
(* Get Phone numbers *)
lHttp.HTTPMethod('GET', 'https://secure.click2callu.com/tpcc/getnumbers?' + lParams);
lHttp.Document.SaveToStream(lResponse);
Result := lResponse.DataString;
if Pos(':', Result) > 0 then
Result := 'Failed to retrieve Vonage phone numbers. Check your username and password.';
finally
lResponse.Free;
lHttp.Free;
end;
end;

function CallToVonage(AUserName, APassword, AFromNumber, AToNumber: string): string;
(* This makes the real call *)
var
lHttp: THTTPSend;
lParams: string;
lResponse: TStringStream;
begin
Result := '';
lHttp := THTTPSend.Create;
lResponse := TStringStream.Create('');
try
lParams := 'username=' + AUserName;
lParams := lParams + '&password=' + APassword;
lParams := lParams + '&fromnumber=' + AFromNumber;
lParams := lParams + '&tonumber=' + AToNumber;
GetVonageNumbers(AUserName, APassword);
lHttp.HTTPMethod('GET', 'https://secure.click2callu.com/tpcc/makecall?' + lParams);
lHttp.Document.SaveToStream(lResponse);
Result := lResponse.DataString;
finally
lResponse.Free;
lHttp.Free;
end;
end;
end.


The unit requires you to deploy libeay32.dll and ssleay32.dll, you can download these files from OpenSSL.org project. There are Windows and Linux versions.

To use it, just include the vonagecall unit in the uses of your program and call the function CallToVonage passing UserName, Password, FromNumber and ToNumber parameters.

In this zip file, I packaged everything you need to test the function.

Hasta la próxima!

Ajax from ObjectPascal!

If you dreamed of creating Ajax/Web 2.0 applications using your favorite ObjectPascal compiler, and thought it's a difficult task because of JavaScript/CSS/DOM, and the lack of wrappers similar to those found in another languages. Now your dream come true thanks to ExtPascal, an Object Pascal wrapper to ExtJs.

ExtPascal allows to build Ajax applications pretty much the same way you program GUI based applications using Delphi, Lazarus or MseIde/MseGUI...not visually, but coding (trust me, it's very easy).

Some examples

To begin internalizing this concept, you can see some examples from the ExtPascal site, or going to ExtJs's samples site. So, if you liked what you saw, then start by reading this Getting Started tutorial and have fun!

Delphi plugin by example

One of the top asked questions in Delphi newsgroups is how to place a form contained in a Dll into our application forms. This method allows to create pluggable applications, easier to maintain and customize.

To accomplish our task, first of all we'll create a Dll using File -> New -> Other -> Dll Wizard. Delete the comment aboud memory managment then Save the Dll as "dllform.dpr".

Now create a form inside the Dll with File -> New -> Form. Go to the Object Inspector and change the name of the new form to MyDllForm then add some controls like buttons, labels and everything you want, then remove the "var Form1: TForm1;" reference, you don't need it. Save the form as "myform".

Go back to the Dll source by going to Project -> View Source, and look at the uses clause, a reference to the recently created form unit must be there. Try to compile by hitting Ctrl + F9, if anything fails, re-check the previous paragraphs.

Adding exportable code

As you can't export a Form directly, you must create an exportable function who can export your form class.

Go to your form's source and add just above the "implementation" section, this code:
  // To get a reference of your form's class
TMyFormClass = class of TMyDllForm;
// To be able to export the form class.
function MyFormClass: TFormClass; stdcall; export;
Then create the body of MyFormClass function, go below the "implementation" section and write this:
function MyFormClass: TFormClass; stdcall;
begin
Result := TMyDllForm;
end;
Now, you must tell your library what functions to export. This is easy, just go to Project -> View Source and add this before the "end.":
exports
MyFormClass;
Before compiling be sure to activate "Build with Runtime Packages" in Project -> Options -> Packages. When you click the checkbox, a ton of packages separated by a comma appears just below, leave only the "vcl" package.

The main form

Create a new application by going to File -> New -> VCL Forms Application. This creates a new application with a main form called Form1. Go to the form and add a TButton and a TPanel.

Why a TPanel?, whell, the TPanel will contain the form we'll load from the Dll. You can use a TPageControl with a TTabSheet instead, or any other container.

Now we'll add a couple of private fields in the TForm1 class:

private
FLoadedForm: TForm;
FLibHandle: Cardinal;
Now we'll implement a method to dynamically load the Dll. Go to the Form1, then to the Object Inspector -> Events and double click on "OnCreate" and "OnDestroy" to create these two events, then write this code inside them:

procedure TForm5.FormCreate(Sender: TObject);
begin
FLibHandle := LoadLibrary('dllform.dll');
end;

procedure TForm5.FormDestroy(Sender: TObject);

begin
FLoadedForm.Free;
FreeLibrary(FLibHandle);
end;

After that, double click on the TButton to add the code needed to call the exported function in the Dll and create an instance of TMyForm:

procedure TForm1.Button1Click(Sender: TObject);
type
TMyFormClass = function: TFormClass; stdcall;

var
lMyFormClass: TMyFormClass;
begin
if not Assigned(FLoadedForm) then
begin
lMyFormClass := GetProcAddress(FLibHandle, 'MyFormClass');
if @lMyFormClass <> nil then
begin
// Create a TMyForm instance (not visible)
FLoadedForm := lMyFormClass.Create(nil);
// Place the Panel from TMyForm in Panel1
FLoadedForm.Controls[0].Parent := Panel1;
end;
end;
end;
Again, before compiling remember to enable "Build with Runtime Packages", as you did when compiling the Dll. If you don't do this, an "Cannot assign a TFont to a TFont" error will be raised when you click Button1.

You can download a sample project from here.

Thursday, July 3, 2008

Auto Update application

A very common problem for desktop application programmers is the method of upgrading/installing new versions of our applications, when you have, say 10 customers with 5 PC's running your apps and you add a new feature, you have to upgrade 50 computers!, a real pain.

Last week, I was thinking of a method to solve that problem, and taught the way should be to move my applications to the web with IntraWeb or something similar, but after a couple of tests I came to the conclusion that my applications (specially the Medical applications) can't run on a web browser, as they use many technologies very tied to the underlying operating system (multimedia recording cames to my mind). Then I dreamed about creating something like a browser for desktop applications, who connects to a server via Http then the server responds to events requested by the client to draw the dialogs and forms, that way, the client is allways up to date, just like the web pages we look every day. I made a couple of examples, but it turned out to be a very complicated task.

After my failed attempt, I started to look for someone's else's work and found a couple of similar projects for Object Pascal, they are MseIfi (from the author of MseIde/MseGui) and RTC WAC (from RealThinClient), very promising, but both are in its early alpha stages of development.

Then, I went for XUL (Mozilla's rendering engine) and Adobe AIR, but I didn't like them because they are very complicated to program (lots of hand made XML/JavaScript) and too slow to compile/run, at least tha't was my impression compared to native Delphi programs.

Well, let's see...My desktop apps run very well, and my customers love them. Why should I start using a different technology just because I found a stone in my way?. The solution was ages simplier than re-programming the whole thing, just include an Auto Update mechanism!.

TAutoUpdate was born

The AutoUpdate mechanism needed was easy to create, I made a class I called TAutoUpdate, now implemented it in my currently running systems, and It works really neat!. Because of that, I wanted to share with anyone interested and created a new Google Code project for that matter.

If you like it, or have any suggestion, please don't hesitate to share with me and the community around TAutoUpdate.

Again, the link is this: http://code.google.com/p/tautoupdate/

Wednesday, July 2, 2008

Transferring objects over the network

Introduction


A couple of weeks ago, I was talking with a friend about a software he uses to book bus trips, he told me his company needs something similar, also he thinks I could adapt one of my applications to work like this program. The software is installed on more than one hundred sites, and it's connected to a central server over the Internet.


What resulted interesting to my friend was its speed, it works like if it was connected to a database in a local network (LAN), but it isn't. Also he added the server's Internet access is a cheap cable connection with ~300kb/s upload speed, and the clients connects using standard DSL or Cable connections. I asked what database and programming language the developers used. It's connected to a Firebird database and was programmed in C#, he replied. Then, I decided to try to replicate the scenario with a simple Delphi program.


My first attempt was a Delphi client connected to a Firebird 2.1 database using IBX controls. In my LAN, the app screams, but when I connect it over Internet it is as slow as a turtle. It seems to be that Firebird sends too much information over the network and it slows down the connection.


In my second attempt, I changed my app to use RemObject's SDK, I created an application server connected to the same database, it improved a lot...but if I'd choose the RO approach, I'd must rewrite the whole application.


Persistence


My application is based on classes derived from TPersistent, and uses TCollection/TCollection Item to store data, it doesn't uses any DbAware component and to persist it's data, it dynamically loads a Dll containing what I call a Database Access Driver who knows, thanks to RTTI and a (not so) complex mechanism, how to talk to the database. The Driver it's using currently, contains the IBX controls to talk to Interbase/Firebird, and I thought it could be great if I can create a similar Driver to use RemObjects SDK, but it turned out to be a real pain (and I hadn't time to investigate RO SDK in detail).


The application server


After the failed attemt to use RemObjects, I decided to create a simple Application Server, based on Indy's TCP Server control. The server should receive a request from the client asking for an object, or collection of objects, and the params to filter the result, then the server dynamically creates an instance of the requested class, fill its data, then converts it to XML and sends the result to the client, who must un-serialize the XML to reconstruct the instance.


I created the server side of the application, and found that the XML serialization part was too slow, resulting in more than 5 seconds for a collection of 500 items, maybe the serializer I used isn't optimized, and I didn't have time to improve by myself, so I decided to find a new serialization method...Thanks god and Roland Beenhakker I found this article on how to write and read the content of a TPersistent descendant to/from a stream. The first time I tested the trick Roland exposed in his article, I was shocked, it serialized my collection instantaneously, the de-serialization was also super fast.


How the server store the objects in the database?, it uses the same Data Access Driver the older Client Application used to connect to the database.


The client's Data Access Driver


Knowing the Serialization/Deserialization method I'll use for the application, I focused on writing a new Data Access Driver for connecting to the Application Server. The task was pretty easy, just serialize objects, then use the WriteStream and ReadStream method of Indy to send and receive the streamed objects to/from the server.


The real test


When I finished writing the new Data Access Driver, I decided to try it in a real environment, connecting the client from a remote machine to my local PC by using my home Internet connection. The results where amazing, the whole round trip only took 1.5 seconds!, impressive.


A little improvement


Knowing that the serialization/deserialization process is instantaneous, and the only possible bottleneck is the network. I decided to compress the streams sent over the network, and used InflateStream and DeflateStream from the great TurboPower Abbrevia library. The reduced stream size improved even more the results.


Side effects


As the data sent over the network is binary, it is also very (if not impossible) difficult to be intercepted and used by a unauthorized people. And if you are very concerned about security, after compressing the stream you can encrypt it.


One negative side effect of this approach, is that it only works with clients and servers written in Delphi and C++ Builder. To me, this is not a problem, because I maintain both, the server and the client, but if the server must be accessed by some other language, you could improve the protocol to let the server determine in which format the data should be transferred.


In the next post, I'll show a minimalistic version of the program.

Tuesday, July 1, 2008

Launch application as a service

Sometimes you need an application to start before the user logs in to Windows, just as Services do. If you have the source code, this is not a problem, but what if you don't have access to it?.

With this small Delphi program you can launch a list of applications as windows services very easily.

How to use it?

After creating the service with the code below, you'll have to create the file AppAsService.conf containing the list of applications you want to start using this structure: [program];[parameters].

Sample AppAsService.conf file:

c:\program files\filesharer\filesharer.exe;autostart
c:\myprograms\utils\clock.exe

This is the code:

program AppAsService;

uses
SvcMgr,
main in 'main.pas' {Service1: TService};

{$R *.RES}

begin
Application.Initialize;
Application.CreateForm(TService1, Service1);
Application.Run;
end.



unit main;

interface

uses
Windows, Messages, SysUtils, Classes, SvcMgr, ShellApi, TlHelp32;

type
TService1 = class(TService)
procedure ServiceStart(Sender: TService; var Started: Boolean);
procedure ServiceStop(Sender: TService; var Stopped: Boolean);
private
FAppList: TStringList;
function pKill(ExeFileName: string): Boolean;
public
function GetServiceController: TServiceController; override;
end;

var
Service1: TService1;

implementation

{$R *.DFM}

procedure ServiceController(CtrlCode: DWord); stdcall;
begin
Service1.Controller(CtrlCode);
end;

function TService1.GetServiceController: TServiceController;
begin
Result := ServiceController;
end;

procedure TService1.ServiceStart(Sender: TService; var Started: Boolean);
(*
AppAsService.conf example: ;
c:\program files\Mozilla Firefox\firefox.exe;http:\\www.google.com
*)

var
lPath: string;
lName: string;
I: Integer;
lApp: PChar;
lParams: PChar;
begin
lPath := ExtractFilePath(ParamStr(0));
lName := ExtractFileName(ParamStr(0));
lName := Copy(lName, 0, Pos('.', lName) - 1);

FAppList := TStringList.Create;
FAppList.LoadFromFile(lPath + lName +'.conf');
for I := FAppList.Count - 1 downto 0 do
begin
lApp := PChar(Copy(FAppList[I], 0, Pos(';', FAppList[I]) - 1));
if not FileExists(lApp) then
FAppList.Delete(I)
else
begin
(* ShellExecute the app *)
lParams := PChar(Copy(FAppList[I], Pos(';', FAppList[I]) + 1, Length(FAppList[I])));
ShellExecute(0, 'open', lApp, lParams, PChar(ExtractFilePath(lApp)), SW_NORMAL);
(* Replace FApplist[I] by the AppName *)
FAppList[I] := ExtractFileName(lApp);
end;
end;
end;

procedure TService1.ServiceStop(Sender: TService; var Stopped: Boolean);
var
I: Integer;
lProcess: Cardinal;
lProcHandle: Cardinal;
lExitCode: Cardinal;
begin
for I := 0 to FAppList.Count - 1 do
pKill(FAppList[I]);
FAppList.Free;
end;


function TService1.pKill(ExeFileName: string): Boolean;
const
PROCESS_TERMINATE=$0001;
var
ContinueLoop: BOOL;FSnapshotHandle: THandle;
FProcessEntry32: TProcessEntry32;
begin
result := False;
FSnapshotHandle := CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0);
FProcessEntry32.dwSize := Sizeof(FProcessEntry32);
ContinueLoop := Process32First(FSnapshotHandle,FProcessEntry32);
while integer(ContinueLoop) <> 0 do
begin
if ((UpperCase(ExtractFileName(FProcessEntry32.szExeFile)) = UpperCase(ExeFileName))
or (UpperCase(FProcessEntry32.szExeFile) = UpperCase(ExeFileName))) then
Result := TerminateProcess(OpenProcess(PROCESS_TERMINATE, BOOL(0),
FProcessEntry32.th32ProcessID), 0);
ContinueLoop := Process32Next(FSnapshotHandle,FProcessEntry32);
end;
CloseHandle(FSnapshotHandle);
end;

end.