Connecting to a Database using the dbExpress Driver Framework

Go Up to Using dbExpress

This procedure tells how to use the dbExpress driver framework to connect to a database and read its records. In the sample code, the dbExpress ini files contain all the information about the particular database connection, such as driver, user name, password, and so on.

To connect to a database and read its records

1. Configure the connections ini file with the information about the database you are connecting to. This includes setting the driver name, user name, password, and so on.
2. Obtain a Data.DBXCommon.TDBXConnectionFactory, which is returned by GetConnectionFactory.
3. Get a Data.DBXCommon.TDBXConnection object returned by GetConnection.
4. Open the database connection by calling Open on the Data.DBXCommon.TDBXConnectionFactory instance.
5. Get a Data.DBXCommon.TDBXCommand object by calling CreateCommand on the Data.DBXCommon.TDBXConnection instance.
6. Set the Data.DBXCommon.TDBXCommand's Text property to the desired SQL command. Call Prepare on the Data.DBXCommon.TDBXCommand instance.
7. Execute the SQL query by calling ExecuteQuery, which returns a Data.DBXCommon.TDBXReader instance.
8. Read the first database record by calling Next. Call this method to retrieve successive database records.
9. Get whatever information you want from the database. For instance, GetColumnCount returns the number of database columns. The Data.DBXCommon.TDBXReader properties ValueType and Value contain the data type and value for a given column number in the current record.

{ 
  This sample connects to a database using the ini files.
  These files must be configured for the database.
  Once connected, the sample reads values and displays the
  ANSI values for the first 100 records in a list box.
}
 
{ Get a TDBXConnection using a TDBXConnectionFactory... }
{ ConnectionName = section in the connections ini file }
class function TForm1.BuildConnectionFromConnectionName(ConnectionName: WideString): TDBXConnection;
var
  ConnectionFactory: TDBXConnectionFactory;
  ConnectionProps: TDBXProperties;

begin
  ConnectionFactory := TDBXConnectionFactory.GetConnectionFactory;
  ConnectionProps := ConnectionFactory.GetConnectionProperties(ConnectionName);
  Result := ConnectionFactory.GetConnection(ConnectionProps);

  { ...or you can use this line instead of the above three lines. }
  { Result := TDBXConnectionFactory.GetConnectionFactory.GetConnection(TDBXConnectionFactory.GetConnectionFactory.GetConnectionProperties(ConnectionName)); }
end;

procedure Connect;
var
  connection: TDBXConnection;
  command: TDBXCommand;
  reader: TDBXReader;
  value: TDBXValue;
  valueType: TDBXValueType;
  colCountStr: string;
  i, j: Integer;
  numCols: integer;
  ListBox1: TListBox;

const
  sqlCommand = 'select * from employee';
 
begin
  { Open connection to DB. }
  connection := BuildConnectionFromConnectionName('ConnectionName'); { the name of the DB connection }
  try
    { Get command. }
    command := connection.CreateCommand();
    try
      command.Text := sqlCommand;

      { Execute query. }
      command.Prepare;
      reader := command.ExecuteQuery;
      try
        { Get values from DB. }
        if reader.Next then
        begin
          numCols := reader.ColumnCount;
          Str(numCols, colCountStr);

          ListBox1.Items.Add('Number of columns = ' + colCountStr);

          j := 1;
          repeat
            for i := 0 to reader.ColumnCount - 1 do
            begin
              valueType := reader.ValueType[i];
              if valueType.DataType = TDBXDataTypes.AnsiStringType then
              begin
                value := reader.Value[i];
                ListBox1.Items.Add(valueType.Name + ' = ' + value.GetString);
              end else
                ListBox1.Items.Add(valueType.Name);
            end;
            Inc(j);
          until (j > 100) or not reader.Next;

          reader.Next;
        end;

      { Free resources. }
      finally
        reader.Free;
      end;
    finally
      command.Free;
    end;
  finally
    connection.Free;
  end;
end;