Delphi 7: ADO, need basic coding example

Question

I am a complete beginner here. Can someone please post some Delphi code to

• create a database
• add a simple table
• close the database

then, later

• open a database
• read each table
• read each field of a given table
• perform a simple search

Sorry to be so clueless. I did google, but didn't find a useful tutorial ...

In addition, it would be useful if the underlying database were MySql (5.1.36) (I don't even know if that makes any difference)

Answer 1

@mawg, i wrote an simple program for you to ilustrate how work with ADO and Delphi. this is an console application, but explains the basics.

before you execute this code you must download and install the odbc connector from this location.

You can improve and adapt this code to your requirements.

program ProjectMysqlADO;

{$APPTYPE CONSOLE}

uses
  ActiveX,
  DB,
  ADODB,
  SysUtils;

const
//the connection string
StrConnection='Driver={MySQL ODBC 3.51 Driver};Server=%s;Database=%s;User=%s; Password=%s;Option=3;';


var
AdoConnection : TADOConnection;

procedure SetupConnection(DataBase:String);//Open a connection
begin
  Writeln('Connecting to MySQL');
  AdoConnection:=TADOConnection.Create(nil);
  AdoConnection.LoginPrompt:=False;//dont ask for the login parameters
  AdoConnection.ConnectionString:=Format(StrConnection,['your_server',DataBase,'your_user','your_password']);
  AdoConnection.Connected:=True; //open the connection
  Writeln('Connected');
end;

procedure CloseConnection;//Close an open connection
begin
  Writeln('Closing connection to MySQL');
  if AdoConnection.Connected then
  AdoConnection.Close;
  AdoConnection.Free;
  Writeln('Connection closed');
end;

procedure CreateDatabase(Database:string);
begin
  Writeln('Creating Database '+database);
  AdoConnection.Execute('CREATE DATABASE IF NOT EXISTS '+Database,cmdText);
  Writeln('Database '+database+' created');
end;

procedure CreateTables;
begin
  Writeln('Creating Tables');
  AdoConnection.Execute(
  'CREATE TABLE IF NOT EXISTS customers ('+
  'id      INT,'+
  'name    VARCHAR(100),'+
  'country VARCHAR(25) )',cmdText);
  Writeln('Tables Created');
end;


procedure DeleteData;
begin
  Writeln('Deleting dummy data');
  AdoConnection.Execute('DELETE FROM customers');
  Writeln('Data deleted');
end;

procedure InsertData;

    Procedure InsertReg(id:integer;name,country:string);
    var
    ADOCommand : TADOCommand;
    begin
      ADOCommand:=TADOCommand.Create(nil);
      try
       ADOCommand.Connection:=AdoConnection;
       ADOCommand.Parameters.Clear;
       ADOCommand.CommandText:='INSERT INTO customers (id,name,country) VALUES (:id,:name,:country)';
       ADOCommand.ParamCheck:=False;
       ADOCommand.Parameters.ParamByName('id').Value      := id;
       ADOCommand.Parameters.ParamByName('name').Value    := name;
       ADOCommand.Parameters.ParamByName('country').Value := country;
       ADOCommand.Execute;
      finally
      ADOCommand.Free;
      end;
    end;

begin
    Writeln('Inserting Data');
    InsertReg(1,'Lilian Kelly','UK');
    InsertReg(2,'John and Sons','USA');
    InsertReg(3,'William Suo','USA');
    InsertReg(4,'MARCOTEC','UK');
    Writeln('Data Inserted');
end;

procedure ReadData;
var
  AdoQuery : TADOQuery;
begin
   AdoQuery:=TADOQuery.Create(nil);
   try
    AdoQuery.Connection:=AdoConnection;
    AdoQuery.SQL.Add('SELECT * FROM customers');
    AdoQuery.Open;
    while not  AdoQuery.eof do
    begin
      Writeln(format('%s %s %s',[AdoQuery.FieldByname('id').AsString,AdoQuery.FieldByname('name').AsString,AdoQuery.FieldByname('country').AsString]));
      AdoQuery.Next;
    end;
   finally
   AdoQuery.Free;
   end;
end;

begin
  CoInitialize(nil); // call CoInitialize()
  try
       Writeln('Init');
       try
         SetupConnection('mysql'); //first will connect to the  mysql database , this database always exist
         CreateDatabase('Mydb'); //now we create the database
         CloseConnection; //close the original connection
         SetupConnection('Mydb'); //open the connection pointing to the Mydb database
         CreateTables; //create a sample table
         DeleteData; //Delete the dummy data before insert
         InsertData; //insert a dummy data
         ReadData; //read the inserted data
         CloseConnection; //close the connection
       except
         on E : Exception do
           Writeln(E.Classname, ': ', E.Message);
       end;
      Readln;
  finally
   CoUnInitialize; // free memory
  end;
end.