1with AdaBase;
2with Connect;
3with CommonText;
4with Ada.Text_IO;
5with AdaBase.Results.Sets;
6
7procedure Query_Select is
8
9   package CON renames Connect;
10   package TIO renames Ada.Text_IO;
11   package ARS renames AdaBase.Results.Sets;
12   package AR  renames AdaBase.Results;
13   package CT  renames CommonText;
14
15begin
16
17   CON.connect_database;
18
19   CON.DR.set_trait_column_case (AdaBase.upper_case);
20
21   declare
22      stmt : CON.Stmt_Type := CON.DR.query_select
23               (tables    => "nhl_schedule as S " &
24                             "JOIN nhl_teams T1 ON S.home_team = T1.team_id " &
25                             "JOIN nhl_teams T2 ON S.away_team = T2.team_id",
26               columns    => "S.event_code, " &
27                             "T1.city as home_city, " &
28                             "T1.mascot as home_mascot, " &
29                             "T1.abbreviation as home_short, " &
30                             "S.home_score, " &
31                             "T2.city as away_city, " &
32                             "T2.mascot as away_mascot, " &
33                             "T2.abbreviation as away_short, " &
34                             "S.away_score",
35               conditions => "S.yyyswww < 1085011",
36               order      => "S.yyyswww ASC",
37               limit      => 10,
38               offset     => 20);
39
40   begin
41      if not stmt.successful then
42         TIO.Put_Line ("  Driver message: " & stmt.last_driver_message);
43         TIO.Put_Line ("     Driver code: " & stmt.last_driver_code'Img);
44         TIO.Put_Line ("       SQL State: " & stmt.last_sql_state);
45      else
46         for c in Natural range 1 .. stmt.column_count loop
47            TIO.Put_Line ("Column" & c'Img & " heading: " &
48                          stmt.column_name (c));
49         end loop;
50         TIO.Put_Line ("");
51      end if;
52
53      --  Demonstrate bind/fetch_bound
54      declare
55         event_code : aliased AR.NByte2;
56         home_town, home_mascot  : aliased AR.Textual;
57         away_town, away_mascot  : aliased AR.Textual;
58         home_score, away_score  : aliased AR.NByte1;
59      begin
60         stmt.bind (1, event_code'Unchecked_Access);
61         stmt.bind ("HOME_CITY", home_town'Unchecked_Access);
62         stmt.bind ("AWAY_CITY", away_town'Unchecked_Access);
63         stmt.bind (3, home_mascot'Unchecked_Access);
64         stmt.bind ("AWAY_MASCOT", away_mascot'Unchecked_Access);
65         stmt.bind ("HOME_SCORE", home_score'Unchecked_Access);
66         stmt.bind ("AWAY_SCORE", away_score'Unchecked_Access);
67
68         loop
69            exit when not stmt.fetch_bound;
70
71            TIO.Put ("In event" & event_code'Img & ", the " &
72                     CT.USS (away_town) & " " & CT.USS (away_mascot) &
73                     " visited the " &
74                     CT.USS (home_town) & " " & CT.USS (home_mascot) &
75                     " and ");
76            if Integer (away_score) > Integer (home_score) then
77               TIO.Put ("won");
78            elsif Integer (away_score) < Integer (home_score) then
79               TIO.Put ("lost");
80            else
81               TIO.Put ("tied");
82            end if;
83            TIO.Put_Line (away_score'Img & " to" & home_score'Img);
84         end loop;
85         TIO.Put_Line ("");
86      end;
87   end;
88
89   declare
90      --  demonstrate fetch_all
91      stmt : CON.Stmt_Type := CON.DR.query_select
92               (tables    => "fruits",
93               columns    => "fruit, calories, color",
94               conditions => "calories > 50",
95               order      => "calories",
96               limit      => 10);
97
98      rowset : ARS.Datarow_Set := stmt.fetch_all;
99   begin
100      for row in Natural range 1 .. rowset'Length loop
101         TIO.Put_Line (rowset (row).column (1).as_string & ":" &
102                       rowset (row).column ("calories").as_nbyte2'Img &
103                       " calories, " & rowset (row).column (3).as_string);
104      end loop;
105   end;
106
107   CON.DR.disconnect;
108
109end Query_Select;
110