Tweet RSS

Database access in Delphi?

Div

Both object-oriented programming and relational database management systems (RDBMSs) are extremely common in software today. 

Since relational databases don't store objects directly (though some RDBMSs have object-oriented features to approximate this), there is a general need to bridge the two worlds. The problem of bridging object-oriented programming accesses and data patterns with relational databases is known as Object-Relational impedance mismatch. There are a number of approaches to cope with this problem, but no general solution without downsides. One of the most common approaches is object-relational mapping, as found in libraries like Java Data Objects and Ruby on Rails' ActiveRecord.

— En.Wikipedia.org OOP

Div

It is imperative that one has a good understanding of Object Oriented Programming, before they attempt to incorporate databases in their program. By general consensus OOP and Database Access go hand in hand. 

It is also important that one has a background in SQL in order to import and export data between the program and the database. There are many database programs available, either free-ware or proprietary software, the most common are MySQL, MS Access. A new yet robust and powerful database is Oracle 10g.    

Databases are often abbreviated to Db.

Div

TADOquery Component & Field by Name:

Field by Name:

Is generally a method used to teach database linking through the use of the field names within in a Db. However, this would be extremely inefficient if a Db consists of tens or hundreds of fields. This is the simplest method of extracting data from a database in Delphi but it is not the most effective. 

Field by name, as an introduction to Delphi database linking, usually utilizes a TADOquery component, rather than a Database Unit, which will be discussed later.

The basic code is as follows:

Procedure change(TSQL: String);
begin
 With FrmFriends do //The TADOQuery is a component on the form.
  begin
    MyDB.Close; //Ensures no corruption of the Db.
    MyDB.SQL.Text:= TSQL;
    MyDB.ExecSQL;
  end;
end;
 
Function Query(TSQL: String): String;
Var
  temp: String;
begin
  temp:='';
  With FrmFriends do  //Group statments so object applies to all OR could use FrmFriends.MyDB.
  begin
    Change(TSQL);  //Essential before a new SQL statement is used. Closes the DataSet
    MyDb.Open;  //Open DataSet so you can use it.
    MyDb.First; //Internal pointer set to first dataset.
    While not MyDB.EOF do //EOF -End of File.
    Begin //Extracting each field, by heading.
      Temp:= Temp+MyDB.FieldByName('Name').AsString+#9; //The exact field name as in the DB
      Temp:= Temp+MyDB.FieldByName('Surname').AsString+#9;
      Temp:= Temp+MyDB.FieldByName('Class').AsString+#9;
      Temp:= Temp+MyDB.FieldByName('CellNumber').AsString+#13;
      MyDB.Next //Moves pointer on.
    End;
  end;
  Result:= temp;
end;
Div

DBU - Database unit:

The database unit, is a user created, but general, unit that stores the linking procedures and functions to the Db. It serves the purpose of avoiding the use of the TADOquery, the DBU is a user coded version of the TADOquery component.

Here we use Field by Number:

The Database Unit is an independent unit which holds no other code, the DBU can also be a class but this is not imperitive.  

These include:

procedure opendb;
Function Query(TSQL: String): String;
Procedure change(TSQL: String);


Procedure Opendb: Example

procedure opendb;
begin
  MyDB := TADOQuery.Create(Application);
  MyDB.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
    '"MyFriends.mdb";Persist Security Info=False'; //Used to link to MS access database, the database should be in the same directory as the application.
end;


Procedure Change(TSQL:String): Example  

Procedure change(TSQL: String);
begin
  begin
    MyDB.Close;
    MyDB.SQL.Text:= TSQL;
    MyDB.ExecSQL;
  end;
end;


Procedure Query(TSQL:String): Example 

Function Query(TSQL: String): String;
Var
  temp: String;
  Loop: Integer;
begin
  temp:='';
    Change(TSQL);  //Essential before a new SQL statement is used. Closes the Data-Set
    MyDb.Open;  //Open Data-Set so you can use it.
    MyDb.First; //Internal pointer set to first data-set.
    While not MyDB.Eof do //EOF -End of File.
    Begin //Extracting each field, by heading.
      For loop:= 1 to MyDB.FieldCount do
        Temp:= Temp+MyDB.Fields.FieldByNumber(loop).AsString+#9;
      MyDb.Next;
      Temp:= temp+#13
    End;
  Result:= temp;
end;

Linking Databases to Delphi: 

>> Next Tutorial

Div