Using SQLDB to access databases

SQLDB can be used to connect to any SQL capable database. It allows to execute SQL statements on any supported database type in a uniform way, and allows to fetch and manipulate result sets (such as returned by a SELECT statement) using a standard TDataset interface. SQLDB takes care that updates to the database are posted automatically to the database, in a cached manner.

When using SQLDB, 3 components are always needed:

1. A TSQLConnection descendent. This represents the connection to the database: the location of the database, and the username and password to authenticate the connection must be specified here. For each supported database type (Firebird, PostgreSQL, MySQL) there is a separate connection component. They all descend from TSQLConnection.
2. A TSQLTransaction component. SQLDB allows you to have multiple active but independent transactions in your application. (useful for instance in middle-tier applications). If the native database client library does not support this directly, it is emulated using multiple connections to the database.
3. A TSQLQuery component. This encapsulates an SQL statement. Any kind of SQL statement can be executed. The TSQLQuery component is a TDataset descendent: If the statement returns a result set, then it can be manipulated using the usual TDataset mechanisms.

The 3 components must be linked together: the connection must point to a default transaction (it is used to execute certain queries for metadata), the transaction component must point to a connection component. The TSQLQuery component must point to both a transaction and a database.

So in order to view the contents of a table, typically the procedure goes like this:

{$mode objfpc}{$h+}
uses sqldb, ibconnection;

Var
  C : TSQLConnection;
  T : TSQLTransaction;
  Q : TSQLQuery;
  
begin
  // Create a connection.
  C:=TIBConnection.Create(Nil);
  try
    // Set credentials.
    C.UserName:='MyUSER';
    C.Password:='Secret';
    C.DatabaseName:='/home/firebird/events.fb';
    // Create a transaction.
    T:=TSQLTransaction.Create(C);
    // Point to the database instance
    T.Database:=C;
    // Now we can open the database.
    C.Connected:=True;
    // Create a query to return data
    Q:=TSQLQuery.Create(C);
    // Point to database and transaction.
    Q.Database:=C;
    Q.Transaction:=T;
    // Set the SQL select statement
    Q.SQL.Text:='SELECT * FROM USERS';
    // And now use the standard TDataset methods.
    Q.Open;
    While not Q.EOF do
      begin
      Writeln(Q.FieldByName('U_NAME').AsString);
      Q.Next
      end;
    Q.Close;  
  finally
    C.Free;
  end;
end.  

The above code is quite simple. The connection type is TIBConnection, which is used for Firebird/Interbase databases. To connect to another database (for instance PostgreSQL), the exact same code could be used, but instead of a TIBConnection, a TPQConnection component must be used:

{$mode objfpc}{$h+}
uses sqldb, pqconnection;

Var
  C : TSQLConnection;
  T : TSQLTransaction;
  Q : TSQLQuery;
       
begin
  // Create a connection.
  C:=TPQConnection.Create(Nil);

The rest of the code remains identical.

The above code used an SQL SELECT statement and the Open method to fetch data from the database. Almost the same method applies when trying to execute other kinds of queries, such as DDL queries:

{$mode objfpc}{$h+}
uses sqldb, ibconnection;

Var
  C : TSQLConnection;
  T : TSQLTransaction;
  Q : TSQLQuery;
  
begin
  C:=TIBConnection.Create(Nil);
  try
    C.UserName:='MyUSER';
    C.Password:='Secret';
    C.DatabaseName:='/home/firebird/events.fb';
    T:=TSQLTransaction.Create(C);
    T.Database:=C;
    C.Connected:=True;
    Q:=TSQLQuery.Create(C);
    Q.Database:=C;
    Q.Transaction:=T;
    // Set the SQL statement. SQL is a tstrings instance.
    With Q.SQL do
      begin
      Add('CREATE TABLE USERS ( ');
      Add(' U_NAME VARCHAR(50), ');
      Add(' U_PASSWORD VARCHAR(50) ');
      Add(' ) ');
      end;
    // And now execute the query using ExecSQL 
    // There is no result, so Open cannot be used.
    Q.ExecSQL;
    // Commit the transaction.
    T.Commit;   
  finally
    C.Free;
  end;
end.  

As can be seen from the above example, the setup is the same as in the case of fetching data. Note that TSQLQuery can only execute 1 SQL statement during ExecSQL. If many SQL statements must be executed, TSQLScript must be used.

There is much more to TSQLQuery than explained here: it can use parameters (see UsingParams) and it can automatically update the data that you edit in it (see UpdateSQLs).

See also