(*
  Author Huet Bartels
  Verion Delphi CS 2.0
  Date   27/05/97

  Purpose To show you how to uses some of the Oracle Functions,
          Stored Procedures,and standard Select Statements.

  Mail    Huet1Cix@Compulink.co.uk (Home)
  Mail    Huet1@mdx.ac.uk (Work)

  This demo is freeware, do what ever you what with it.

  I would like feedback, to keep this demo upto date, if
  you have any examples of functions or routines I have not used
  please mail me them so I can add them to the demo.

  How to get this work.

  You will need the C/S version of Delphi.
  You will need to config the BDE you use the Oracle Driver
  I have include a BDE CFG file, if you use the ORA32.CFG you will
  need to change the following fields.

  1) Server Name. **If you are using SQL*NET 2 make sure you
     have the .WORLD added after the server name

  2) NET PROTCOL should be TNS if using SQL*NET V2 otherwise TCP/IP or
     what ever you default protocol is

  3) VENDOR INIT Change it to match you version of Oracle  ie ORA72
     is for Oracle Version 7.2 ORA73 is version 7.3....
  You will also need to execute the Stored Proecdure script named emproc.sql
  included with this demo in the SCOTT/TIGER ACCOUNT
  *)
unit main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBTables, DB, StdCtrls, ComCtrls, Grids, DBGrids, ExtCtrls;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    StoredProc1: TStoredProc;
    Query1: TQuery;
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    DBGrid1: TDBGrid;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Panel3: TPanel;
    Memo1: TMemo;
    Panel4: TPanel;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Query2: TQuery;
    DataSource1: TDataSource;
    Query1EMPNO: TFloatField;
    Query1ENAME: TStringField;
    Query1JOB: TStringField;
    Query1SAL: TFloatField;
    Query1DEPTNO: TFloatField;
    Memo2: TMemo;
    ComboBox1: TComboBox;
    Q1: TQuery;
    Q1ENAME: TStringField;
    Query3: TQuery;
    Query3ENAME: TStringField;
    Query3JOB: TStringField;
    Query3DNAME: TStringField;
    Query3LOC: TStringField;
    Label3: TLabel;
    Button4: TButton;
    Query4: TQuery;
    TabSheet2: TTabSheet;
    Query4TERM: TStringField;
    Query4SESS: TFloatField;
    Query4LAN: TStringField;
    PageControl2: TPageControl;
    TabSheet3: TTabSheet;
    Label4: TLabel;
    Bevel1: TBevel;
    Label5: TLabel;
    Edit1: TEdit;
    Label7: TLabel;
    Edit3: TEdit;
    Label6: TLabel;
    Edit2: TEdit;
    Button6: TButton;
    TabSheet4: TTabSheet;
    Query5: TQuery;
    Button5: TButton;
    Edit5: TEdit;
    Label11: TLabel;
    Query5DAYNAME: TStringField;
    Query5FULLDATE: TStringField;
    Edit4: TEdit;
    Label8: TLabel;
    Query5FULLDATE1: TStringField;
    Edit6: TEdit;
    Query5FULLDATE2: TStringField;
    Edit7: TEdit;
    Label9: TLabel;
    Label10: TLabel;
    Edit8: TEdit;
    Label12: TLabel;
    Query5FULLDATE3: TStringField;
    Query5FULLDATE4: TStringField;
    Query5FULLDATE5: TStringField;
    Edit9: TEdit;
    Edit10: TEdit;
    Label13: TLabel;
    Label14: TLabel;
    TabSheet5: TTabSheet;
    Query6: TQuery;
    Query6I1: TStringField;
    Query6I2: TStringField;
    Query6I3: TStringField;
    Edit11: TEdit;
    Edit12: TEdit;
    Edit13: TEdit;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    Button7: TButton;
    TabSheet6: TTabSheet;
    Edit14: TEdit;
    Label18: TLabel;
    Query7: TQuery;
    Button8: TButton;
    Query7LD: TDateTimeField;
    Edit15: TEdit;
    Label19: TLabel;
    Query7ND: TDateTimeField;
    Label20: TLabel;
    Label21: TLabel;
    TabSheet7: TTabSheet;
    Edit16: TEdit;
    Label22: TLabel;
    Edit17: TEdit;
    Label23: TLabel;
    Edit18: TEdit;
    Label24: TLabel;
    Edit19: TEdit;
    Label25: TLabel;
    Query8: TQuery;
    Query8CH: TFloatField;
    Query8CO: TFloatField;
    Query8SI: TFloatField;
    Query8SH: TFloatField;
    Button9: TButton;
    Edit20: TEdit;
    Label26: TLabel;
    Query3SAL: TFloatField;
    procedure Database1Login(Database: TDatabase; LoginParams: TStrings);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure ComboBox1DropDown(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure PageControl1Change(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Database1Login(Database: TDatabase;
  LoginParams: TStrings);
begin
(*The OnLogin event is activated whenever a TDatabase component assigned to an
  SQL database is opened and the LoginPrompt property is True. Use the OnLogin
  event to set login parameters. The OnLogin event gets a copy of the
  TDatabase's login parameters array, Params. Use the Values property to change
  these parameters:
*)
LoginParams.Values['USER NAME'] := 'SCOTT';
LoginParams.Values['PASSWORD'] := 'TIGER';
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
DataSource1.DataSet:=Query1;          //Assign Query to DBGrid
Query1.Open;
If Query2.active then Query2.Close;
If Query3.active then Query3.Close;
Memo2.lines:=Query1.Sql;
Memo2.Lines.Add('');
Memo2.Lines.Add('This is a standard Query that will display all rows in a table');
Memo2.Lines.Add('The select will only show the columns that have been defined');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
If Query1.active then Query1.Close;
If Query3.active then Query3.Close;
DataSource1.DataSet:=Query2;       //Assign Query to DBGrid
Query2.Open;
Memo2.Lines:=Query2.Sql;
Memo2.Lines.Add('');
Memo2.Lines.Add('This query show you how two join to tables together, the query');
Memo2.Lines.Add('joins the EMP & DEPT tables within Oracle on the DEPTNO field');
Memo2.Lines.Add('By joining the tables you can display the Department Name (DNAME) and');
Memo2.Lines.Add('Location (LOC) of the employee from the EMP table');

end;

procedure TForm1.Button3Click(Sender: TObject);
begin
If ComboBox1.Text = '' then
ShowMessage('You must select a name from the drop down list for this example to work.')
else
 begin
  DataSource1.DataSet:=Query3; //Assign Query to DBGrid
  Query3.Close;
  (* The Prepare method sends a parameterized query to the database engine for
     parsing and optimization. A call to Prepare is not required to use a
     parameterized query. However, it is strongly recommended, because it will
     improve performance for dynamic queries that will be executed more than
     once. If a query is not explicitly prepared, each time it is executed,
     Delphi automatically prepares it.
   *)
  Query3.Prepare;
  Query3.Params[0].AsString:=ComboBox1.Text;
  Query3.Open;
  Memo2.lines:=Query3.Sql;
  Memo2.Lines.Add('');
  Memo2.Lines.Add('This query takes 1 Parameter which is selected from the combolist box');
  Memo2.Lines.Add('The Param is passed into the query via the variable :User');
  Memo2.Lines.Add('The ComboBox is acting like a DBLookup list without the overhead');
 end;
end;

procedure TForm1.ComboBox1DropDown(Sender: TObject);
begin
(* This procedure is used to fill a standard Combobox with values taken from
   the Oracle database.  I intened to use no Database aware objects, to keep the
   size of the exe as small as I can
*)
Q1.Open;
While not Q1.Eof do
 begin
  ComboBox1.Items.Add(Q1Ename.Text);
  Q1.next;
 end;
end;
procedure TForm1.Button6Click(Sender: TObject);
begin
Query4.Open;
Edit1.Text:=Query4Term.text;
Edit2.Text:=Query4Sess.text;
Edit3.Text:=Query4Lan.text;
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
Query5.Open;
Edit4.Text:=Query5FullDate.text;
Edit5.Text:=Query5DayName.Text;
Edit6.Text:=Query5FullDate1.text;
Edit7.Text:=Query5FullDate2.text;
Edit8.Text:='Total days so far this year '+Query5FullDate3.text;
Edit9.Text:='Total weeks so far this year '+Query5FullDate4.text;
Edit10.Text:='Week number this month '+Query5FullDate5.text;
Query5.Close;
end;

procedure TForm1.PageControl1Change(Sender: TObject);
begin
if PageControl1.Activepage.Pageindex = 0 then
DBGrid1.Visible:=True
else
DBGrid1.Visible:=False;
end;

procedure TForm1.Button7Click(Sender: TObject);
begin
Query6.Close;
Query6.Open;
Edit11.Text:=Query6I1.Text;
Edit12.Text:=Query6I2.Text;
Edit13.Text:=Query6I3.Text;
end;

procedure TForm1.Button8Click(Sender: TObject);
begin
Query7.Open;
EDit14.Text:=Query7LD.Text;
EDit15.Text:=Query7ND.Text;
end;

procedure TForm1.Button9Click(Sender: TObject);
begin
Query8.Open;
Edit16.Text:=Query8CH.Text;
Edit17.Text:=Query8CO.Text;
Edit18.Text:=Query8SI.Text;
Edit19.Text:=Query8SH.Text;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
If ((ComboBox1.Text = '') or (Edit20.text = '' ))then
ShowMessage('You must select a name from the drop down list, and enter a salary for this example to work.')
else
 begin
 (*
   There seems to be a bug in the C/S Version of Delphi 2.17.53.0 or my
   version any way, may work ok in 2.01.  You seem to have to reverse the
   params in-order to get the stored procedure to work correctly.  I
   tested the stored procedure from within SQL*PLUS with the execute statement
   under ORACLE 7.2 for NT and it works fine ie
   EXECUTE UPDATEEMP('JAMES','3000');  So it must be Delphi at fault :-)

   Any ideas ???
 *)

  StoredProc1.Params[1].asString:=Edit20.Text;    //This should be Param[0]
  StoredProc1.Params[0].asString:=ComboBox1.Text; //This should be Param[1]
  StoredProc1.Prepare;
  StoredProc1.ExecProc;

 end;
  DataSource1.DataSet:=Query3; //Assign Query to DBGrid
  Query3.Close;
  (* The Prepare method sends a parameterized query to the database engine for
     parsing and optimization. A call to Prepare is not required to use a
     parameterized query. However, it is strongly recommended, because it will
     improve performance for dynamic queries that will be executed more than
     once. If a query is not explicitly prepared, each time it is executed,
     Delphi automatically prepares it.
   *)
  Query3.Prepare;
  Query3.Params[0].AsString:=ComboBox1.Text;
  Query3.Open;
  Memo2.Clear;
  Memo2.Lines.Add('');
  Memo2.Lines.Add('This is the results from executing a stored procedure');
  Memo2.Lines.Add('The Value you typed into the salary field, should now');
  Memo2.Lines.Add('be display in the DBGRID below.');
end;

end.