Connecting to a Database using the dbExpress Driver Framework

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

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.
Obtain a TDBXConnectionFactory, which is returned by TDBXConnectionFactory.GetConnectionFactory.
Get a TDBXConnectionobject returned by TDBXConnectionFactory.GetConnection.
Open the database connection by calling TDBXConnection.Open on the TDBXConnection instance.
Get a TDBXCommandobject by calling TDBXConnection.CreateCommand on the TDBXConnection instance.
Set the TDBXCommand's Textproperty to the desired SQL command. Call TDBXCommand.Prepare on the TDBXCommand instance.
Execute the SQL query by calling TDBXCommand.ExecuteQuery, which returns a TDBXReader instance.
Read the first database record by calling TDBXReader.Next. Call this method to retrieve successive database records.
Get whatever information you want from the database. For instance, TDBXReader.GetColumnCount returns the number of database columns. The TDBXReader properties ValueTypeand Value contain the data type and value for a given column number in the current record.

Copy Code

// 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 listbox.

// 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,
   ConnectionProps.Values[TDBXPropertyNames.UserName], ConnectionProps.Values[TDBXPropertyNames.Password] );
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');
 connection.Open;

 // Get command
 command := connection.CreateCommand();
 command.Text := sqlCommand;

 // Execute query
 command.Prepare;
 reader := command.ExecuteQuery;

 // Get values from DB
 if reader.Next then
 begin
 numCols := reader.GetColumnCount;
   Str(numCols, colCountStr);
   ListBox1.Items.Add('Number of columns = ' + colCountStr);
   j := 1;
   repeat
     for i := 0 to reader.GetColumnCount - 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
 command.Free;
end;

What do you think about this topic? Send feedback!