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