Version:0.9 StartHTML:0000000105 EndHTML:0000036288 StartFragment:0000001037 EndFragment:0000036272 mXScriptasHTML 
{****************************************************************
 * Project  : DBX ADO SQL Demo - DataBaseSeries maXbase
 * App Name : 195_SQL_DBExpress2.txt, 301_SQL_DBfirebird3, loc's = 225
 * Purpose  : Demonstrates 3 ways Queries in Datasets (DBX, BDE, ADO(ODBC)) on 64bit!
 * History  : TSQLConnection, TSQLDataSet, TSQLQuery
            : testcase AVERP with Firebird 2.5, max@kleiner.com
 ****************************************************************}

program SQL_DataBaseDemo_Firebird;

var E: Exception;

Const  
  //ADBNAME = 'D:\Program Files\Common Files\Borland Shared\Data\MASTSQL.GDB';
  //ADBNAME = 'C:\Program Files (x86)\AVERP\AVERP_DEMO.FDB';  64bit
   
  ADBNAME = 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
  BDEALIAS = 'Firebird_EMPLOY';
  CONNECTSTRING = 'Provider=MSDASQL;DSN=firebird;Uid=sysdba;Pwd=masterkey';

  SQLQUERY = 'SELECT * FROM Customer';          
  //SQLQUERY = 'SELECT * FROM Customer WHERE Company like "%SCUBA%"''';       
  

{For this demo you need some provider libraries, DemoDB EMPLOYEE and InterBase, Firebird
 which is part of an installation, here's some example code:
 http://www.firebirdsql.org/en/afterinstall/
 }


 //////////////DBX///////////////////
function DataSetQuery(acnect: TSQLConnection): TSQLDataSet;
var i: integer;
begin
  result:= TSQLDataSet.Create(self);
  with result do begin
    SQLConnection:= acnect;
    CommandText:= SQLQUERY;
    Open; 
    Writeln(intToStr(Recordcount)+' Rec found:')
    for i:= 0 to Recordcount - 1 do begin
      Writeln('Rec: '+intToStr(i)+' '+Fields[1].asString)
      Next;
    end;
  end;
end;      
  
function SQLReturn(acnect: TSQLConnection): TSQLQuery;
var i,z: integer;
begin
  result:= TSQLQuery.Create(self);
  with result do begin
    SQLConnection:= acnect; //may before active:= false;
    SQL.Add(SQLQuery)
    Open;
    Writeln(intToStr(Recordcount)+' Records found:')
    for i:= 0 to Recordcount - 1 do begin
      for z:= 0 to Fieldcount - 1 do 
        Write(Fields[z].asString+' ');
      Writeln(#13#10)
      Next;
    end;
  end;
end;

procedure ConnectInterBase(const aDBname: string);
var
  connect: TSQLConnection;
  DataSet: TSQLDataSet;
  dataQuery: TSQLQuery;
begin
  connect:= TSQLConnection.Create(NIL);
  try
    with connect do begin
      ConnectionName:= 'VCLScanner';
      DriverName:= 'INTERBASE';     //or Firebird
      LibraryName:= 'dbxint30.dll';
      VendorLib:= 'GDS32.DLL';
      GetDriverFunc:= 'getSQLDriverINTERBASE';
      Params.Add('User_Name=SYSDBA');
      Params.Add('Password=masterkey');
      Params.Add('Database='+ADBNAME);
      LoginPrompt:= True;
      Open;
      dataQuery:= SQLReturn(connect)
      dataSet:= DataSetQuery(connect) 
      //finalize objects 
      if Connected then begin
        DataSet.Close;
        DataSet.Free;
        dataQuery.Close;
        dataQuery.Free;
        Close; //connect
        Free;
      end; 
    end;  //with
  except
    E:= Exception.Create('SQL Connect Exception: ');
    Showmessage(E.message+'SQL or connect missing')
  end;
end;
///////////////DBX end///////////////////


//////////////////BDE////////////////////
function GetQuery(SQLCommand: string; aQuery: TQuery):TFields;
begin
  aQuery.SQL.Text:= SQLCommand;
  aQuery.Open;
  aQuery.Active:= true;
  result:= aQuery.Fields;
end; 

procedure DoQuery(aDB: TDataBase);
var i,z: integer;
    dQuery: TQuery;
begin
  dQuery:= TQuery.Create(self); 
  with dQuery do begin
    DatabaseName:= aDB.DataBaseName;
    try
      SQL.Clear;
      GetQuery(SQLQuery, dQuery)
      //SQL.Text:= SQLQuery;  direct way
      Writeln(inttostr(RecordCount)+' Records found: '+#13#10)
      for i:= 0 to RecordCount - 1 do begin
        for z:= 0 to Fieldcount - 1 do 
          Write((Fields[z].asString)+' ');
        Writeln(#13#10)
       Next;
      end;  
    finally
      Close;
      Free;
      CloseDataBase(aDB)
    end;
  end //dQuery  
end; 

procedure BDEDataBaseConnect;
var aParams, aTblNames: TStringList;
    aSession: TSession;
    dbMain: TDatabase;
    i: integer;
begin 
  AParams:= TStringList.Create;  
  aTblNames:= TStringList.Create; 
  //create a session to get alias parameters list 
  ASession:= TSession.Create(NIL);  
  ASession.SessionName:= 'Session4';  
  dbMain:= TDatabase.Create(NIL);  
  try  
    ASession.GetAliasParams(BDEAlias, AParams);
    for i:= 0 to aparams.count -1 do 
      writeln(aParams[i]);
    with dbMain do begin  
      Params.Assign(AParams);  
      dbMain:= ASession.OpenDatabase(BDEAlias);
      Writeln('Database is: '+dbMain.DataBaseName)
      KeepConnection:= True;
      GetTableNames(aTblNames, false)
      Writeln('Tables are: ');
      for i:= 0 to atblNames.count-1 do 
        Write(atblnames[i]+' ');
      doQuery(dbMain);
    end;
  finally  
    DBMain.Free;
    ASession.Free;  
    AParams.Free;
    aTblNames.Free;
  end;  
end;
////////////////////BDE end////////////////////


//////////////////ODBC ADO ////////////////////
Procedure SetADOSETAccess2;
var i,z: integer;
    ws: TWideStrings;
begin  
  with TAdoDataSet.Create(self) do begin
    cachesize:= 500;
    commandText:= SQLQUERY;
    //String:= 'Provider=MSDASQL;DSN=mx3base;Uid=sa;Pwd=admin';
    connectionString:= CONNECTSTRING;
    //try
    Open;
    //except   
    Writeln(intToStr(Recordcount)+' records found:')
    //end;
    for i:= 0 to Recordcount - 1 do begin
      for z:= 0 to Fieldcount - 1 do 
        Write((Fields[z].asString)+'  ');
      Writeln(#13#10)
      Next;
    end;
    Close;
    Free;        
  end; //TAdoDataSet
  Writeln('List Provider Names: ')
  ws:= TWideStringList.Create;
  getProviderNames(ws)
  for i:= 1 to ws.Count-1 do 
    writeln(inttostr(i) +' '+ws.strings[i]);
  ws.free;  
end;
//////////////////ODBC ADO end////////////////////

begin    //main app
  Writeln('ShowTime for Firebird SQL in 3 Modes: '+datetostr(Today)+#13#10)
  //DBX3 InterBaseConnect with Queries and Resultset
  Writeln('DBX Starts: ')
  ConnectInterbase(ADBNAME);
  Writeln('');
  Writeln('BDE Starts: ')
  BDEDataBaseConnect;
  Writeln('');
  Writeln('ADO ODBC Starts: ')
  SetADOSETAccess2;
  
 //maxform1.ShellStyle1Click(self)
 memo2.lines.savetoFile(ExePath+'SQL3TestResult.txt');
 SearchAndOpenDoc(ExePath+'SQL3TestResult.txt');
End.



//***************Code Snippets*******************************3
//In this example we use dbdemos database with Country table  

object ADODataSet1: TADODataSet
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Co' +
      'mmon Files\Borland Shared\Data\dbdemos.mdb;Persist Security Info' +
      '=False'
    CommandText = 'select Name  from country'
  end
  
  

Method 	Description
Append 	Creates an empty record and adds it to the end of the dataset.
AppendRecord 	Appends a record to the end of the dataset with the given field data and posts the edit.
ApplyUpdates 	Instructs the database to apply any pending cached updates. Updates are not actually written until the CommitUpdates method is called.
Cancel 	Cancels any edits to the current record if the edits have not yet been posted.
CancelUpdates 	Cancels any pending cached updates.
ClearFields 	Clears the contents of all fields in the current record.
CommitUpdates 	Instructs the database to apply updates and clear the cached updates buffer.
Close 	Closes the dataset.
Delete 	Deletes the current record.
DisableControls 	Disables input for all data controls associated with the dataset.
FieldByName 	Returns the TField pointer for a field name.

FindFirst

Example:

  {set up database component}
  ABSDatabase1.DatabaseName := 'emp_db';
  ABSDatabase1.DatabaseFileName := 'c:\data\employee_db.abs';

  {set up query component}
  ABSQuery1.DatabaseName := 'emp_db';
  ABSQuery1.SQL.Text := 'select * from employee';
  ABSQuery1.RequestLive := True;
   
  if (not ABSDatabase1.Exists) then
    raise Exception.Create('Database file does not exist');

  {execute and open query}
  ABSQuery1.Open;


   ...
   for i := MaiList.Count - 1 downto do begin
       MaiQuery1.close;
       MaiQuery1.SQL.clear;
  MaiQuery1.SQL.text := 'Select * from MaiTable.db where (MaiFeld like ''' + MaiList[i] + ')''';
       MaiQuery1.open;
       if MaiQuery1.recordcount > 0 then
          MaiList.delete(i)
   end:
   ...

begin
for i := MaiList.Count - 1 downto 0 do
begin
MaiQuery1.close;
//MaiQuery1.SQL.clear; nicht notwendig
MaiQuery1.SQL.text := 'Select * from MaiTable.db where MaiFeld like "%' + MaiList[i] + '%"';
MaiQuery1.open;
if (i > 1537) and (i < 1541) then
ShowMessage(MaiQuery1.SQL.text);
if MaiQuery1.recordcount > 0 then
MaiList.delete(i)
end;
end;
[/delphi:1:da2a96482f]



TSQLDataSet is a forward only read only dataset. As you move the cursor
through the dataset by calling TSQLDataSet.Next it fetches the rows one
at a time. It does not buffer multiple rows on the client. That is the
function of TClientDataSet.

Many thanks for that. So I take it that SQLDataset.Open causes the SQL


thi is a Sample of insert, in a table with 4 fields, with the SQLQuery.


  //CommandText:= Format('INSERT INTO CUSTOMER VALUES("%d","%s","%s","%s","%s")',
                             //[34,Email,FirstName,LastName,datetoStr(date)]);

begin
TableSQL.Active := False;
TableSQL.SQL.Clear;
TableSQL.SQL.Add('INSERT INTO EXAMPLE_TABLE(FIELD1, FIELD2, FIELD3,
FIELD4) VALUES (:PFIELD1, :PFIELD2, :PFIELD3,:PFIELD4);
TableSQL.ParamByName('PFIELD1').AsString := 'TEST';
TableSQL.ParamByName('PFIELD2').AsInteger := 10;
TableSQL.ParamByName('PFIELD3').AsFloat := 10.34;
TableSQL.ParamByName('PFIELD4').AsDateTime := Now;
TableSQL.ExecSQL;

end;

In the table, the FIELD1 is a string, the 2 is an integer, the 3 is a
flating and the last is a timestamp.

 label1.Caption := tempSQL.FieldByName('CustomerName ').value;
 Function dStringPad(InputStr, FillChar: String; StrLen: Integer;
   StrJustify: Boolean): String;
Var
   TempFill: String;
   Counter : Integer;
Begin
   If Not (Length(InputStr) = StrLen) Then Begin
     If Length(InputStr) > StrLen Then Begin
       InputStr := Copy(InputStr,1,StrLen) ;
     End
     Else Begin
       TempFill := '';
       For Counter := 1 To StrLen-Length(InputStr) Do Begin
         TempFill := TempFill + FillChar;
       End;
       If StrJustify Then Begin
         {Left Justified}
         InputStr := InputStr + TempFill;
       End
       Else Begin
         {Right Justified}
         InputStr := TempFill + InputStr ;
       End;
     End;
   End;
   Result:= InputStr;
End;


ttimer

myform