Compiler Options

First of all you have to set some compiler options depending which type of application you are writing and which database you are using.
Edit the "JvUIB.inc" file in the source directory with a text editor and uncomment compiler options you need.

Database.

Borland Interbase

Interbase 6.0x, you don't need to set anything, you can work with any database version.

Interbase 6.5, {$DEFINE IB65}

Interbase 7, {$DEFINE IB7}

Filrebird

Firebird 1.02, {$DEFINE FB102}

Firebird 1.03, {$DEFINE FB103}

Firebird 1.5, {$DEFINE FB15}

Firebird Embed 1.5, {$DEFINE FB15} & {$DEFINE FBEMBED}

Yaffil

Yaffil 1.0.867 {$DEFINE YF867}

Multithreading and N-Tiers servers application.

You have 2 choices:

Sharing one DB connection with all threads, in this case 2 threads can share the same Database connection or the same Transaction safely without freezing the server. Some examples of server applications are: Web server, CORBA, DCOM, or Delphi ORB provided provided with UIB. For an example of Multithread server try the the sample applications in the "ClentServer" directory. You must set this compiler option: {$DEFINE UIBTHREADSAFE} excepting with Interbase 7 (The library is allready ThreadSafe)

Create one DB Connection per Thread, in this case you should desactivate {$DEFINE UIBTHREADSAFE}, and setting your DB connection to use the remote protocol (excepting with Interbase 7), to have all threads working faster.

Borland C++ Builder 6

You can choose to use original Interbase header files (IBase.h & IBError.h) in this case uncomment {$DEFINE USE_IBERROR_H} & {$DEFINE USE_IBASE_H}.

Dynamic & Static Linking library.

{$DEFINE INTERBASE_DYNAMIC_LINK}is the default option to load Interbase library only when needed.

Language

{$DEFINE UIBLANG_EN}: English language. (Default)

{$DEFINE UIBLANG_FR}: French language

Contact me to add your language.

Transaction Rules

Définitions:

Commit: apply changes and close transaction.
Rollback: cancel changes and close transaction.
CommitRetaining: apply changes and keep the transaction open.
RollbackRetaining: cancel changes and keep transaction open.

Rules

Transactions are started automaticaly when a Query is Open.
Transactions stay alive until all attached queries are closed.
Transaction rollbacked automatically on internal error (open, ExecSQL, next ...).
Transaction commited automatically if a query component is destroyed or detached from transaction.

Example

procedure TForm1.BtOpenClick(Sender: TObject);
var
  Transaction: TJvUIBTransaction;
  Query1: TJvUIBQuery;
  Query2: TJvUIBQuery;
begin
  Transaction := TJvUIBTransaction.Create(nil);
  Query1 := TJvUIBQuery.Create(nil);
  Query2 := TJvUIBQuery.Create(nil);
  try
    Transaction.DataBase := DataBase;
    Query1.Transaction := Transaction;
    Query2.Transaction := Transaction;
    try
      Query1.SQL.Text := 'SELECT * FROM MYTABLE1;
      Query1.Open; // transaction started (Rule 1)
      while not Query1.Eof do
      begin
        // ...
        Query1.Next;
      end;
      Query1.Close; // Stay in transaction (default action)

      Query2.SQL.Text := 'SELECT * FROM MYTABLE2';
      Query2.Open; // transaction not started because Query1 have not closed the Transaction.
                   // On error the transaction is automatically rollbacked(Rule 3)
      
      while not Query1.Eof do
      begin
        // ...
        Query2.Next;
      end;
      Query2.Close(etmCommit); // Transaction Commited because Query1 is closed
                               // if Query1 not closed then CommitRetaining(Rule 2)


    except
      Transaction.RollBack; // on error transaction rollbacked
      // ...
       
    end;
  finally
    Query1.Free; // if transaction active then close & commit(Rule 4)
    Query2.Free; // if transaction active then close & commit(Rule 4)
    Transaction.Free;
  end;
end;

Using TJvUIBQuery

There is 3 methods to use this component.

Open (Allocate statement + Prepare + Execute + Fetch)

Typically used for SELECT statements.
You can't use the QuickScript property with this method.

  
  Query.SQL.Text := 'SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEE WHERE DEPT_NO = ?';
  Query.Params.AsInteger[0] := 623;
  Query.Open;
  while not Query.EOF do
    with Query, Fields do
    begin
      memo.Lines.Add(format('%s %s, Salary: %f'
         ,
        [ByNameAsString['FIRST_NAME'],
         ByNameAsString['LAST_NAME'],
         ByNameAsCurrency['SALARY']]));
      Next;
    end;
  Query.Close(etmCommit);

Execute (Allocate statement + Pepare + Execute)

Typically used for "Data Pump". It is the fastest way to execute the same
operation many times without having to prepare the query for each operation.
You can't use the QuickScript property with this method.

const
  Datas : array[1..10] of TARecord = (
    (COUNTRY: 'blabla0'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla1'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla2'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla3'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla4'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla5'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla6'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla7'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla8'; CURRENCY: 'blabla'),
    (COUNTRY: 'blabla9'; CURRENCY: 'blabla'));
begin
  for i := 1 to 10 do
  begin
    Query.Params.AsString[0] := Datas[i].COUNTRY;
    Query.Params.AsString[1] := Datas[i].CURRENCY;
    Query.Execute;
    // for better performance commit every 1000 records
    // Transaction.Commit;
  end;
  Query.Close(etmRollback); // change to etmCommit to apply.
end;

ExecSQL (Execute Immediate)

The fastest way to execute many differents SQL statements like a script.
Setting the QuickScript property to True you must have one SQL statement
per line. This method can use parammetters but can't return results.

  Query.QuickScript := True;
  Query.SQL.Add('INSERT INTO COUNTRY (COUNTRY,CURRENCY) VALUES (''Test0'',''FFranc'')');
  Query.SQL.Add('DELETE FROM COUNTRY WHERE COUNTRY = ''Test0''');
  .../...
  Query.ExecSQL;
  Query.Close(etmCommit);