A WordPress MySQL Database reader to recover from a dead site

This application was developed on Linux Debian 8 and Typhon64, but the same code should work on Windows or any other platform supported by Lazarus / Typhon / FreePascal.

In Ubuntu with Unity front end you may run into difficulties running visual applications with menus and other visual components.  To solve this issue just execute the following script before calling your application.

#!/bin/bash
export LIBOVERLAY_SCROLLBAR=0
export UBUNTU_MENUPROXY=0
./yourapplication

For more information check here.

We invoke Lazarus / Typhon. When it opens click on File | New … A form appears, then select Application and OK.

File New… Application

This creates a skeleton of an application and a form for a visual application. In Lazarus the form appears in a separate window, while in Typhon it appears in the Designer tab. If the form does not appear it may have been out of the limits of the Designer form. At least it happens to me when I work in an environment with many screens.

New Application

To bring the form into the Designer window, press the center button.

There we have our form.

Form Visible

The component and main menu bar is usually on top of the forms (it can be down if you have configured it like this). There are the main operations for developing, compiling and editing the code. There is also an important set of visual components that will be added to the code simply by clicking on the component and then on the form where it is added.

Main and Component Frame

The basics of our project are built. Now we must save it and give it a name. Let’s go to File | Save As …

Save As …

Navigate to the directory of our projects and create a new directory for this. Pressing [Create Forlder] then give the name ‘wordpressreader’.

WordPressReader Save

We have already saved the project. Now we must put a menu to our form. In the [Standard]  tab we click on the first item (TmainMenu) and add it to the Form.

TMainMenu

To edit the Menu, we right click MainMenu1 and select ‘MenuEditor’.

Menu Editor

We add two ‘MenuItem’. To do this, press the [Add Menu Item] button twice. Then right-click MenuItem1, select [Edit Caption].

Rename Menu Item

This allows us to rename in item 1 of the menu. We put ‘Configure’. Next we put ‘Exit’ MenuItem.

Configure Exit Menu Items

Do not forget to press [Save] {Ctrl + S} or {Shift + Ctrl + S} from time to time.

We add an event handler. Right click and [Add OnClick Handler]

Add Event Handler

For the Item Exit Menu we add in the code:

Application.Terminate;

Note that if we wait a little, contextual help will pop-up with the attributes and methods of the object. A dropdown combo shows up with the possible values. Selecting Terminate and giving [Enter] completes the code including the semicolon.

Application.Terminate;

We also generate an Event Handler for Item Configure. For now we do not put anything there. Return to the Form1 Designer, and choose the SQLdb tab from the Component Bar, and add the following components to Form1: TMySQL40Connection, TMySQL41Connection, TMySQL50Connection, TMySQL51Connection, TMySQL55Connection, TMySQL56Connection, and TMySQL57Connection.

TMySQLxyConnection

We also add a TSQLTransaction and a TSQLQuery from the tab. In the ‘Data Access’ tab we add a TDataSource. We add a TPairSplitter from the Additional tab.

Add Components

We select the PairSplitter (otherwise it is invisible) and we change the orientation attribute. In the Splitter Type attribute we put pstVertical ..

PairSplitter horizontal

The Pair Splitter consists of a container that includes two other inner containers of graphic components with a TSplitter in the middle, which will allow us to enlarge one component while shrinking the other. By clicking on the top and bottom we can identify both containers.

Pair Splitter Side 2

We add in the upper container a TDBGrid and in the lower one a TDBMemo of the tab from ‘Data Controls’ tab.

DBGrid Upper Container
DBGrid Lower Container

Add a DBMemo (DBMemo2) in the lower container. Modify attribute Align = alTop, and Height=48. This will allign DBMemo2 to the top of the lower container.

We modify all the other components to fill the space of the container, for each of PairSplitter and DBGrid and DBMemo. We put them Align = alClient. Also put ScrollBars=ssAutoBoth.

Align alClient

Add we get the right look.

Form1

Now we must configure the attributes of the related objects so that everything works correctly. We assume that we will connect to a MySQL database named ‘MySQLDatabase’, with the user ‘database-user’, and password ‘MyV3rYC0mp73xP45w0rd’, on the server ‘localhost’ and port ‘3306’ (default). For MySQLxyConnection objects we will have to modify the following parameters at runtime.

DatabaseName:='MySQLDatabase';
KeepConnection:=true;
Password:='MyV3rYC0mp73xP45w0rd';
Transaction:=SQLTransaction1;
UserName:='database-user';
Connected:=true;

But we’re going to have to test one by one to check what version of MySQL client we have on the system.

For SQLTransaction1 we have to configure the following:

DataBase:=MySQLxyConnection1; //La que esté activa
Active:=true

Forl SQLQuery1 we must configure it as following:

DataBase:=MySQLxyConnection1; //La que esté activa
SQL:='SELECT * FROM MySQLDatabase.wp_posts ORDER BY wp_posts.post_modified ASC;';
Transaction:=SQLTransaction1;
Active:=true;

The DataSource attribute in SQLQuery1 is not assigned because it would give a circular dependency error.

For the DataSource1 component, the DataSet attribute is configured.

DataSet:=SQLQuery1;

The following items are configured at design time, as explained here. The DBGrid1 within the PairSplitter must be bound by the DataSource1. Add DataSource and DataField as shown for DBMemo1 component.

PairSplitter1

PairSplitterSide1

DBGrid1

DataSource:=DataSource1;

PairSplitterSide2

DBMemo2

DataSource:=DataSource1;

DataField:=’post_title’;

DBMemo1

DataSource:=DataSource1;

DataField:=’post_content’;

Let’s add a second form (Form2).

New Form

Then a new form shows up.

Form2

To the new form we must add five TLabeledEdit from the ‘Additional’ tab.

Labeled Edit

We must modify the LabelPosition attribute to lpLeft so that the label is to the left of the text box.

Labeled edit left

The size is corrected to make them look elegant on the form. As much as you can. We need to edit the label to make it meaningful. EditLabel.Caption is the content of the label. And Text is the content of the Edit Box.

Edit SubLabel

We add a Button with Caption Activate or Login or Connect. We also edit the text of the edit boxes to leave it this way:

Form2 Labeled Edits

We have to renamw the Labeled Edit Boxes with the following names:

LabeledEditUser;

LabeledEditPassword;

LabeledEditDatabase;

LabeledEditServer;

LabeledEditPort;

On Unit2 we must add “Uses Unit1;” in ‘Implementation’ section.

Include Unit1 in Unit2.

To include Unit2 in Unit1, has to be declared in uses sentence at Unit1.

Insert Uses Unit2

This is the correct way to avoid errors by circular dependencies.

In Form2 we select the OnClick event of the Button and add the following code.

procedure TForm2.Button1Click(Sender: TObject);
begin
 Form1.user:= LabeledEditUser.Text;
 Form1.password:= LabeledEditPassword.Text;
 Form1.database:= LabeledEditDatabase.Text;
 Form1.server:= LabeledEditServer.Text;
 Form1.port:= LabeledEditPort.Text;
 self.Close;
end;

This will take the parameters of the MySQL client connection and pass them to Form1 in the corresponding variables.

In Form1 we must edit the event of the Configure menu item. In the public section we must create the parameter passing variables.

 public
      user, password, database,server,port: string;
 end;

Between {$R *.frm} and { TForm1 } create and auxiliary procedure with the following code:

{$R *.frm}

procedure MyConnected;
var messagestring :string;
begin
 messagestring:= '';
 if Form1.MySQL40Connection1.Connected then messagestring:= messagestring + 'MySQL40 ';
 if Form1.MySQL41Connection1.Connected then messagestring:= messagestring + 'MySQL41 ';
 if Form1.MySQL50Connection1.Connected then messagestring:= messagestring + 'MySQL50 ';
 if Form1.MySQL51Connection1.Connected then messagestring:= messagestring + 'MySQL51 ';
 if Form1.MySQL55Connection1.Connected then messagestring:= messagestring + 'MySQL55 ';
 if Form1.MySQL56Connection1.Connected then messagestring:= messagestring + 'MySQL56 ';
 if Form1.MySQL57Connection1.Connected then messagestring:= messagestring + 'MySQL57 ';
 if (messagestring='')
 then messagestring:= 'Connected '+ messagestring
 else messagestring:='Not Conencted';
 ShowMessage(messagestring);
end;

{ TForm1 }

This function is for diagnostics only, in the final version is not used. And then we add the following code in the function of the Configure Menu event (MenuItem1):

{ TForm1 }

procedure TForm1.MenuItem1Click(Sender: TObject);
begin
 Form2.ShowModal; //Add Unit2 in uses...
 ShowMessage('This is a database' + database + ' password:'+ password + ' user:'+ user + ' server:'+ server + ' port:' + port );
 //use parameters
 try
 MySQL40Connection1.DatabaseName:=database;
 MySQL40Connection1.KeepConnection:=true;
 MySQL40Connection1.Password:=password;
 MySQL40Connection1.UserName:=user;
 MySQL40Connection1.HostName:=server;
 MySQL40Connection1.Port:=StrToInt(port);
 MySQL40Connection1.KeepConnection:=true; //<----
 MySQL40Connection1.Connected:=true;
 MySQL40Connection1.Open;
 finally
 if MySQL40Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL40Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL40Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL41Connection1.DatabaseName:=database;
 MySQL41Connection1.KeepConnection:=true;
 MySQL41Connection1.Password:=password;
 MySQL41Connection1.UserName:=user;
 MySQL41Connection1.HostName:=server;
 MySQL41Connection1.Port:=StrToInt(port);
 MySQL41Connection1.KeepConnection:=true; //<----
 MySQL41Connection1.Connected:=true;
 MySQL41Connection1.Open;
 finally
 if MySQL41Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL41Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL41Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL50Connection1.DatabaseName:=database;
 MySQL50Connection1.KeepConnection:=true;
 MySQL50Connection1.Password:=password;
 MySQL50Connection1.UserName:=user;
 MySQL50Connection1.HostName:=server;
 MySQL50Connection1.Port:=StrToInt(port);
 MySQL50Connection1.KeepConnection:=true; //<----
 MySQL50Connection1.Connected:=true;
 MySQL50Connection1.Open;
 finally
 if MySQL50Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL50Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL50Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL51Connection1.DatabaseName:=database;
 MySQL51Connection1.KeepConnection:=true;
 MySQL51Connection1.Password:=password;
 MySQL51Connection1.UserName:=user;
 MySQL51Connection1.HostName:=server;
 MySQL51Connection1.Port:=StrToInt(port);
 MySQL51Connection1.KeepConnection:=true; //<----
 //MySQL51Connection1.Connected:=true;
 MySQL51Connection1.Open;
 finally
 if MySQL51Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL51Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL51Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL55Connection1.DatabaseName:=database;
 MySQL55Connection1.KeepConnection:=true;
 MySQL55Connection1.Password:=password;
 MySQL55Connection1.UserName:=user;
 MySQL55Connection1.HostName:=server;
 MySQL55Connection1.Port:=StrToInt(port);
 MySQL55Connection1.KeepConnection:=true; //<----
 MySQL55Connection1.Connected:=true;
 MySQL55Connection1.Open;

finally
 if MySQL55Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL55Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL55Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL56Connection1.DatabaseName:=database;
 MySQL56Connection1.KeepConnection:=true;
 MySQL56Connection1.Password:=password;
 MySQL56Connection1.UserName:=user;
 MySQL56Connection1.HostName:=server;
 MySQL56Connection1.Port:=StrToInt(port);
 MySQL56Connection1.KeepConnection:=true; //<----
 MySQL56Connection1.Connected:=true;
 MySQL56Connection1.Open;

finally
 if MySQL56Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL56Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL56Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 try
 MySQL57Connection1.DatabaseName:=database;
 MySQL57Connection1.KeepConnection:=true;
 MySQL57Connection1.Password:=password;
 MySQL57Connection1.UserName:=user;
 MySQL57Connection1.HostName:=server;
 MySQL57Connection1.Port:=StrToInt(port);
 MySQL57Connection1.KeepConnection:=true; //<----
 MySQL57Connection1.Connected:=true;
 MySQL57Connection1.Open;

finally
 if MySQL57Connection1.Connected then
 begin
 SQLTransaction1.DataBase:=MySQL57Connection1;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQL57Connection1;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;
 end
 else
 begin
 ShowMessage(' Did not find a suitable version of MySQL Client.');
 end;
 end; //try
 end; //57
 end; //finally
 end; //56
 end; //finally
 end; //55
 end; //finally
 end; //51
 end; //finally
 end; //50
 end;//finally
 end; //41
 end;//finally

MyConnected; //tell which database is connected

{SQLTransaction1.DataBase:=MySQLConnection;
 SQLTransaction1.Active:=true;

SQLQuery1.DataBase:=MySQLConnection;
 SQLQuery1.Transaction:=SQLTransaction1;
 SQLQuery1.SQL.Text:='SELECT * FROM '+ database + '.wp_posts ORDER BY wp_posts.post_modified ASC;';
 SQLQuery1.Active:=true;}

DataSource1.DataSet:=SQLQuery1;

DBGrid1.DataSource:=DataSource1;

DBMemo1.DataSource:=DataSource1;
 DBMemo1.DataField:='post_content';

end; //MenuItem1Click

We save everything and we can compile it and run it.

Clicking on Configure shows us the form2 and we can enter the parameters of connection to the database. Clicking on Activate, it connects. A Message will appear that will modify the connection parameters to the database. We give it OK. In Typhon, component 51 gives an unrecoverable error in a message if it does not find its MySQL client installed.

TMySQL51Connection can not work with the installed MySQL client version: Expected (5.1), got (5.5.57).

Press OK to ignore and risk data corruption.
Press Abort to kill the program.

It is a problem of that component, since the other components are captured by the try finally. Clicking on OK continues. And we can go through the records of the base and look for the text of our lost posts. We go through the records in the upper DBGrid, and the post information appears in the DBMemo below.

WordPressReader

This is the Typhon Project. Updated!!

This is the Lazarus Project. Updated!! Tested on lazarus-1.8.0RC4-fpc-3.0.4rc1-win32

Related posts

Leave a Comment