How to Insert/Edit Rows in Firebird Dataset in Delphi using FIBPLUS Components?

How to Insert/Edit Rows in Firebird Dataset in Delphi using FIBPLUS Components?

You can insert a row and edit an existing row in firebird dataset in Delphi. I will use FIBPLUS dataset component in Delphi XE4. Just use Insert and Edit procedures for this purpose. First of all create a dataset of type TpFIBDataset. Lets have a look at this very simple example.

var

dsMyFirebirdDataset : TpFIBDataset;

Insert a row in a dataset

with dsMyFirebirdDataset do

begin

Insert;

dsMyFirebirdDatasetID.AsInteger := 101;

dsMyFirebirdDatasetDESC.AsString := 'Hello';

Post;

end;

You can also use FieldByName like following:

with dsMyFirebirdDataset do

begin

Insert;

FieldByName('ID').AsInteger := 101;

FieldByName('DESC').AsString := 'Hello';

Post;

end;

Similarly, you can edit a record/row in the dataset like following:

Edit a row in a dataset

With dsMyFirebirdDataset do

begin

Edit;

dsHdwPriceGroupDESC.AsString := 'Hello World';

Post;

end;

or

With dsMyFirebirdDataset do

begin

Edit;

FieldByName('DESC').AsString := 'Hello World';

Post;

end;

How to populate data into dataset from Firebird database in Delphi using FIBPLUS components?

How to populate data into dataset from Firebird database in Delphi using FIBPLUS components?

I am using Firebird 2.5.2 database and Delphi XE4. I will be using FIBPlus TpFIBDatabase and TpFIBDataset components to populate data from Firebird database to dataset in Delphi XE4. For this, you have to drag TpFIBDatabase and TpFIBDataset components into your Delphi Form from the Tool Palette. Set various properties of TpFIBDatabase component like DBName, Username, Password and LibraryName to connect to Firebird database. I have written a complete tutorial on this. After successfully creating the database connection, set following properties of TpFIBDataset component:

Database: Provide the name of database component (TpFIBDatabase) in Database property. In my case, I have created dbMyDatabase component in my previous article.

SQLs: Write the query which you want to run. In my case, I am running following query:

SELECT ID, NAME FROM MY_FIREBIRD_TABLE WHERE ID = :ID AND NAME = :NAME;

Now, populate your dataset like this:

var

dsMyDataSet : TpFIBDataSet;

with dsMyDataSet do

begin

  Active := False;

  Params.ParamByName('ID').AsInteger := 1000;

  Params.ParamByName('NAME').AsString := 'Naresh';

  Active := True;

  RecordCount;

end;

Note: You can also set Database and SQLs properties in pas file as following:

with dsMyDataSet do

begin

  Active := False;

  Database := dbMyDatabase;

  SQLs.SelectSQL.Text := 'SELECT ID, NAME FROM MY_FIREBIRD_TABLE WHERE ID =:ID                                               AND NAME = :NAME;';

  Params.ParamByName('ID').AsInteger := 1000;

  Params.ParamByName('NAME').AsString := 'Naresh';

  Active := True;

  RecordCount;

end;

How to create database connection with Firebird in Delphi using FIBPLUS components?

How to create database connection with Firebird in Delphi using FIBPLUS components?

I am using Firebird 2.5.2 with Delphi XE4. I will show you how to create a database connection with Firebird database in Delphi XE4 using FIBPLUS component? Just go into the Tool Palette and search for TpFIBDatabase component under FIBPLUS. Drag it into the form and name it. I have given "dbMyDatabase" name to TpFIBDatabase database component. If you see your pas file, a declaration for TpFIBDatabase will look like this:

var
dbMyDatabase: TpFIBDatabase;

Now create a function "ConnectToDatabae" with return type boolean. This function will return true if Firebird database connection is established successfully otherwise false.

function TMyForm.ConnectToDatabase : boolean; 
begin
  result := False;
  try
    try
      with dbMyDatabase do
     begin
        DBName := 'C:\MyProject\MyDB.FDB';
ConnectParams.UserName := 'SYSDBA';
ConnectParams.Password := 'masterkey';
LibraryName := 'C:\Program Files(86)\Firebird\Firebird_2_5\bin\fbclient.dll'; 
Connected := True;
     end;
     result := True;
    except
`     result := False;
end;
  finally
    dbMyDatabase.Connected := False;
  end;
end;

In the above function, I have set different properties of TpFIBDatabase component like DBName, Username, Password, LibraryName etc as following:

DBName := 'C:\MyProject\MyDB.FDB'; //MyDB is the firebird database name
ConnectParams.UserName := 'SYSDBA'; //Default username of firebird database
ConnectParams.Password := 'masterkey'; //Default password of firebird database
LibraryName := 'C:\Program Files(86)\Firebird\Firebird_2_5\bin\fbclient.dll'; //Default library path of firebird database

Note: You can set all these properties at design time in your dfm file. But I would recommend to write all these settings in your pas file.

After setting the above properties, I have set connected property to true. If Firebird database connection is established successfully, above function will return true otherwise false. Finally, I am disconnecting the database.

21 Best Free Open Source Databases

21 Best Free Open Source Databases

As a developer or DBA, you must be using some of the widely used databases like MS SQL Server, MySQL, Oracle, PostgreSQL, MongoDB etc. MySQL is the best free open source database which is used today, that we all know. Beside MySQL there are a lot of free and open source databases which you might not be knowing or never used. Some of the free free and open source databases are PostgreSQL, MongoDB, HBase, Cassandra, Couchbase, Neo4j, Riak, Redis, Firebird and lot more. I am using Firebird in my current project which with Delphi XE4. I have compiled a list of 21 Best, Free and Open Source Databases available to us. Lets have a look at them:

1. MySQL

The most widely used open source database for Web apps (and many other things) remains MySQL. Support for multiple storage engines, clustering, full-text indexing, and plenty of other professional features have allowed numerous other apps profiled here, from WordPress to Movable Type, to rely on MySQL as their default database. Graphical front ends, such as phpMyAdmin and Adminer, make using the database far less of a chore. And for those seeking escape from the long shadow of Oracle, there's a community fork named MariaDB, maintained by MySQL's original lead developer, Monty Widenius.

2. PostgreSQL

When Oracle acquired MySQL, reduced the development staff, and more or less killed the open source nature of the project, it reopened a market that MySQL had locked down. PostgreSQL has a much nicer set of drivers and supports both standard ANSI-SQL and extended features, in many cases better than MySQL. On the downside, its long legacy has left it multiprocess in the era of multithreaded. The high-availability/clustering features of PostgreSQL require a lot of elbow grease and leave much to be desired. Yet while organizations look for a community developed database, one of the eldest starts to look pretty good. Many cloud providers, such as Heroku, have chosen PostgreSQL as their RDBMS storage option as well.

3. MongoDB

NoSQL? Document database? The first name that comes to mind is MongoDB, due to a dual-edged blade from developer 10gen. On one side, MongoDB has strong venture capital and consequently an extensive marketing strategy. On the other side, it is the only comparatively mature document database in the NoSQL world. Highly scalable horizontally with automated sharding and highly available due to autoreplication, MongoDB offers a very reliable and yet simple solution to modern document database problems. The downsides: Working with stored procedures can be difficult, and performing data manipulation can require writing complex JavaScript code.

4. Hadoop (HBase)

Hadoop is the name brand in big data. It is also the convergence of "clustered storage" systems like Gluster and Ceph with NoSQL. Hadoop is really a collection of projects to solve large and complex data problems. In fact, there are multiple types of databases and query languages built on the overall Hadoop framework. Hadoop's complexity is as legendary as its capability, and its lack of high-availability features has both held it back and created a commercial add-on ecosystem.

The project aims to host very large tables like "billions of rows, millions of columns". It has a REST-ful web service gateway that supports XML, Protobuf, and binary data encoding options.

5. Apache Cassandra

Written in Java, this BigTable-based key-value database is getting more popular by the day. Open source and built to integrate with Hadoop, Cassandra offers the column family solution to developers wanting to move away from the relational database model while working with Hadoop. Focusing mainly on getting in very fast writes and providing high availability, Cassandra has slower reads than some alternatives. It is mostly used for logging purposes and real-time analysis.

Cassandra is a highly scalable second-generation distributed database that is used by giants like Facebook, Digg, Twitter, Cisco & more. It aims to provide a consistent, fault-tolerant & highly available environment for storing data.

6. Couchbase

While Couchbase was a fork of CouchDB, it has become more of a full-fledged data product and less of a ball of framework than CouchDB. Its transition to a document database will give MongoDB a run for its money. It is multithreaded per node, which can be a major scalability benefit -- especially when hosted on custom or bare-metal hardware. With some nice integration features, including with Hadoop, Couchbase is a great choice for an operational data store.

7. Neo4j

The database for interconnected data, Neo4j provides a reliable Java-based platform for conquering highly interconnected database problems. Available with full ACID transaction compatibility -- rare in a NoSQL database -- Neo4j has a SQL-like query language called Cypher and a scripting language called Gremlin for graph traversals. Best used to accurately and efficiently model highly complex, interconnected networks like network topologies, social networks, and conditional access control problems, it provides indexes on nodes and relationships. Direct path calculations take hundreds of lines of code for a RDBMS but two lines of code for Neo4j.

8. Riak

An open source distributed database written in Erlang and C, Riak treats all nodes equally. No one is a master or a slave. Thus, there is no fear a master will be a single point of failure. However, the masterless, fully distributed model with SNMP monitoring is not available in the open source version. Much simpler than its peers (such as Cassandra), Riak is optimal for places where even seconds of downtime would hurt.

9. Redis

There are many NoSQL databases, but Redis remains close to our heart because it has so many features that some call it a "data structure store." You don't just store numbers and strings -- you can dump in entire hashes, lists, sets, and other complicated structures. Then, to make the deal sweeter, Redis offers replication and persistence.

Redis is an advanced fast key-value database written in C which can be used like memcached, in front of a traditional database, or on its own. It has support for many programming languages & used by popular projects like GitHub or Engine Yard. There is also a PHP client named Rediska for managing Redis databases.

10. Firebird

Firebird is a relational database that can run on Linux, Windows & various UNIX platforms. It offers high performance and powerful language support for stored procedures and triggers.

11. Memcached 

Memcached is an in-memory key-value store for small chunks of arbitrary data (strings, objects) from results of database calls, API calls, or page rendering. It is intended for use in speeding up dynamic web applications by alleviating database load.

12. Oracle Berkeley DB

It is an embeddable database engine that provides developers with fast, reliable, local persistence with zero administration. Oracle Berkeley DB is a library that links directly into your application & enables you to make simple function calls rather than sending messages to a remote server for a better performance.

13. Hypertable

Hypertable is a high performance distributed data storage system designed to support applications requiring maximum performance, scalability, and reliability. It is modeled after Google's BigTable and mostly focuses on large-scale datasets.

14. Keyspace

It is a consistently replicated, fault-tolerant key-value store that works in Windows OS. Keyspace offers high availability by masking server/network failures & appearing as a single, highly available service.

15. 4store

4store is a database storage and query engine that holds RDF data. It is written in ANSI C99, designed to run on UNIX-like systems & offers a high performance, scalable & stable platform.

16. MariaDB

MariaDB is a backward compatible, drop-in replacement branch of the MySQL® Database Server. It includes all major open source storage engines + the Maria storage engine.

17. Drizzle

It is a fork of MySQL that focuses on being a reliable database optimized for Cloud and Net applications.

18. HyperSQL

It is a SQL relational database engine written in Java. HyperSQL offers a small & fast database engine which has in-memory and disk-based tables, supports embedded/server modes. Also, it has tools such as a command line SQL tool & GUI query apps.

19. MonetDB

MonetDB is a  database system for high-performance applications in data mining, OLAP, GIS, XML Query, text & multimedia retrieval.

20. Persevere

It is an object storage engine and application server (running on Java/Rhino) that provides storage of dynamic JSON data for rapidly develop data-driven JavaScript-based rich internet applications.

21. eXist-db

eXist-db is built using XML technology. It stores XML data according to the XML data model & features efficient, index-based XQuery processing.

How to use TpFIBDataSet, TpFIBQuery and TpFIBTransaction FIBPlus components to connect with Firebird / Interebase database in Delphi XE4?

How to use TpFIBDataSet, TpFIBQuery and TpFIBTransaction FIBPlus components to connect with Firebird / Interebase database in Delphi XE4?

Following is the basic article on Firebird / Interbase database connectivity in Delphi XE4 using FIBPlus database components like TpFIBDataSet, TpFIBQuery and TpFIBTransaction. I will explain all these FIBPlus database components in detail. I have written a small article on TpFIBDatabase before this article. Please go through that before reading this one. Read FIBPlus TpFIBDatabase...

FIBPlus TpFIBQuery Component

An application works with a database by issuing SQL instructions. They are used to get and modify data\metadata. FIBPlus has a special TpFIBQuery component responsible for SQL operator execution. This robust, light and powerful component can perform any actions with the database. 

TpFIBQuery is very easy-to-use: just set the TpFIBDatabase component, fill in the SQL property and call any ExecQuery method (ExecQueryWP, ExecQueryWPS). 

NOTE: The tpFIBQuery is not a TDataset descendant, so it does not act in exactly the same way or exhibit the same methods / properties as you would expect to find in a dataset. 

The example below will show how to create TpFIBQuery dynamically at run-time and thus get data about clients.

 var sql: TpFIBQuery;

 sql := TpFIBQuery.Create(nil);

 with sql do

 try

 Database := db;

 Transaction := db.DefaultTransaction;

 SQL.Text := 'select first_name, last_name from customer';

 ExecQuery;

 while not Eof do begin

 Memo1.Lines.Add(

 FldByName['FIRST_NAME'].AsString+' '+

 FldByName['LASTST_NAME'].AsString);

 Next; end;

 sql.Close;

 finally

 sql.Free;

 end;

FIBPlus TpFIBDataSet component

The TpFIBDataSet component is responsible for work with datasets. It is based on the TpFIBQuery component and helps to cache selection results. TpFIBDataSet is a TDataSet descendant so it supports all TDataSet properties, events and methods.

TpFIBDataSet enables you to select, insert, update and delete data. All these operations are executed by TpFIBQuery components in TpFIBDataSet. 

To select data you set the SelectSQL property. It’s similar to setting the SQL property of the QSelect component (TpFIBQuery type). Define the InsertSQL.Text property to insert data, UpdateSQL.Text to update, DeleteSQL.Text to delete and RefreshSQL.Text to refresh the data. 

Here is a demo database employee.gdb (or .fdb for Firebird) to show how to write Select SQL and get a list of all employees. I will write all queries in InsertSQL, UpdateSQL, etc.

with pFIBDataSet1 do begin

 if Active then Close;

 SelectSQL.Text := 'select CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST from CUSTOMER';

 InsertSQL.Text := 'insert into CUSTOMER(CUST_NO, CUSTOMER, CONTACT_FIRST,                                           CONTACT_LAST )' + 

                              ' values (:CUST_NO, :CUSTOMER, :CONTACT_FIRST, :CONTACT_LAST)';

 UpdateSQL.Text := 'update CUSTOMER set CUSTOMER = :CUSTOMER, '+

                   'CONTACT_FIRST = :CONTACT_FIRST, CONTACT_LAST = :CONTACT_LAST '+

                   'where CUST_NO = :CUST_NO';

 DeleteSQL.Text := 'delete from CUSTOMER where CUST_NO = :CUST_NO';

 RefreshSQL.Text := 'select CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST '                                       + 'from CUSTOMER where CUST_NO = :CUST_NO';

 Open;

end;

To open TpFIBDataSet either execute Open/OpenWP methods or set the Active property to True. To close TpFIBDataSet call the Close method

FIBPlus TpFIBTransaction component

A transaction is an operation of database transfer from one consistent state to another. All operations with the dataset (data/metadata changes) are done in the context of a transaction. To understand special FIBPlus features completely you need to know about InterBase / FIBPlus transactions. 

All the changes done in the transaction can be either committed (in case there are no errors) by Commit or rolled back (Rollback). Besides these basic methods TpFIBTransaction has their context saving analogues: CommitRetaining and RollbackRetaining, i.e. on the client side, these will not close a TpFibQuery or TpFibDataset.

To start the transaction you should call the StartTransaction method or set the Active property to True. To commit the transaction call Commit/CommitRetaing, to roll it back - Rollback/RollbackRetaining. 

TpFIBQuery and TpFIBDataSet components have some properties which help to control transactions automatically. In particular they are: the TpFIBDataSet.AutoCommit property; the poStartTransaction parameter in TpFIBDataSet.Options; qoStartTransaction and qoCommitTransaction in TpFIBQuery.Options.

TpFIBTransaction has three basic transaction types: 

tpbDefault, 

tpbReadCommited, 

tpbRepeatableRead. 

At design time you can also create special types of your own in the TpFIBTransaction editor and use them as internal ones. Set the transaction type to set its 

parameters:

TpbDefault – parameters must be set in TRParams

tbpReadCommited – shows the ReadCommited isolation level

tbpRepeatableRead – shows the RepeatableRead isolation level 

How to use TpFIBDatabase FIBPlus Component to connect with Firebird database in Delphi XE4?

How to use TpFIBDatabase FIBPlus Component to connect with Firebird database in Delphi XE4?

TpFIBDatabase component is used to make database connectivity with Firebird database in Delphi. For using TpFIBDatabase component, you should have FIBPlus and Firebird installed on your system. I am using Delphi XE4, Firebird 2.5.2 and FIBPlus 7.5 to make database connection.

Connection parameters are typical for InterBase/Firebird server:

1) path to a database file;

2) user name and password;

3) user role;

4) charset;

5) dialect;

6) client library (gds32.dll for InterBase and fbclient.dll for Firebird).

To connect to a database you should call the Open method or set the Connected property to True. It’s also possible to use this code to connect to a database:

function Login(DataBase: TpFIBDatabase; dbpath, uname, upass, urole: string): Boolean;

begin

 if DataBase.Connected then DataBase.Connected := False; 

 with FDataBase.ConnectParams do begin

   UserName := uname;

   Password := upass;

   RoleName := urole;

 end;

 DataBase.DBName := dbpath;

 try DataBase.Connected := True;

 except

   on e: Exception do

   ShowMessage(e.Message);

 end;

 Result := DataBase.Connected;

end;

To close the connection either call the Close method or set the Connected property to False. 

You can also close all datasets and connected transactions at once:

procedure Logout(DataBase: TpFIBDatabase);

var i: Integer;

begin

  if not DataBase.Connected then

  Exit;

  for i := 0 to DataBase.TransactionCount - 1 do

    if TpFIBTransaction(DataBase.Transactions[i]).InTransaction then 

      TpFIBTransaction(DataBase.Transactions[i]).Rollback

 DataBase.CloseDataSets;

 DataBase.Close;

end;