procedure sql_ex;

with SQL_STANDARD ; use SQL_STANDARD ;
with SQL; use SQL ;
with Host_Character_Pkg; use  Host_Character_Pkg;
with Datacom_Data_Types;
with Table_definition;
with Text_IO;

procedure sql_ex is
--
-- open with po,li,vendr where late=' ' and vendr='abc'
-- also insert new record, update it, delete it (cursor-based)
--
    package HC  renames Host_Character_Pkg;
    package DT  renames Datacom_Data_Types;
    
    Cursor_Info : SQL.Cursor_Ptr;
    query_info  : SQL.query_expr_ptr ;
    po          : char(1..5);
    li          : char(1..3);
    vendor      : char(1..3);
    tpvalue     : Real;
    late        : char(1..1);
    dbl         : Double_Precision;
    real_str    : String (1..14);
    package real_io is new text_io.float_io(real) ;
begin
    table_definition.def_table;  -- define table/database used
    
    dbl := 999.99;
    SQL.Insert  (
          Into     => SQL.T ("PURCHASE"),
          Columns  => SQL.All_Columns,
          Values   => V(Host_String'("87654")) & V(Host_String'("000")) &
                      V(Host_String'("ABC"))   & V(dbl) &
                      V(Host_String'("X"))
          );
    dbl := 111.11;
    SQL.Insert  (
          Into     => SQL.T ("PURCHASE"),
          Columns  => SQL.All_Columns,
          Values   => V(Host_String'("98765")) & V(Host_String'("000")) &
                      V(Host_String'("ABC"))   & V(dbl) &
                      V(Host_String'("X"))
          );
                              
    query_info := SQL.select_table
       (values     => SQL.All_Columns,
        from       => SQL.T(Host_String'("PURCHASE")),
        where      =>
                        SQL."And" (
                          SQL.Equal (SQL.C(Host_String'("VENDER")),
                                     SQL.V(Host_String'("ABC"))),
                          SQL.Equal (SQL.C(Host_String'("LATE")),
                                     SQL.V(Host_String'("X")))
                        )
        );
          
    SQL.Declare_and_Open (
        Name       => "cursor",
        Query      => query_info,
        Ordered_By => SQL.Desc (SQL.C(Host_String'("VENDER"))) &
                      SQL.Desc (SQL.C(Host_String'("TPVALUE"))),
        Cursor     => Cursor_Info);
        
    while SQL.SQLCODE = 0 loop
        
      SQL.Fetch ( cursor_info) ;
          
      if SQLCODE = 0 then
        SQL.INTO ( cursor_info,po) ;
        SQL.INTO ( cursor_info,li) ;
        SQL.INTO ( cursor_info,vendor) ;
        SQL.INTO ( cursor_info,tpvalue) ;
        SQL.INTO ( cursor_info,late) ;
        
        text_io.put_line ("PO =" & HC.To_String (Host_String (po)));
        text_io.put_line ("LI =" & HC.To_String (Host_String (li)));
        text_io.put_line ("VENDOR =" & HC.To_String(Host_String(vendor)));
        real_io.put(real_str,tpvalue) ;
        text_io.put_line ("tpvalue=" & real_str) ;
        text_io.put_line ("LATE =" & HC.To_String (Host_String(late)));
      
        if po = "87654" then
          SQL.Update (
                Name   => SQL.T (Host_String'("PURCHASE")),
                Set    => SQL.C (Host_string'("VENDER")) &
                          SQL.C (Host_String'("LATE")),
                Val    => SQL.V (Host_String'("QRS")) &
                          SQL.V (Host_String'(" ")),
                Where  => Cursor_info
                );
          if SQLCODE /= 0 then
              TEXT_IO.Put_Line ("WARNING: Update failed");
          end if;
        elsif po = "98765" then
          SQL.Delete (
                SQL.T (Host_String'("PURCHASE")),
                Cursor_info
                );
          if SQLCODE /= 0 then
              TEXT_IO.Put_Line ("WARNING: Delete failed");
          end if;
        end if;
      end if;
      
    end loop;
    
    SQL.Close (cursor_info) ;
    if SQLCODE /= 0 then
        TEXT_IO.Put_Line ("WARNING: Close of cursor failed");
    end if;
    text_io.put_line ("cursor closed");
    TEXT_IO.New_Line;
    
    SQL.Update (
          Name   => SQL.T (Host_String'("PURCHASE")),
          Set    => SQL.C (Host_string'("VENDER")) &
                    SQL.C (Host_String'("LATE")),
          Val    => SQL.V (Host_String'("PRS")) &
                    SQL.V (Host_String'(" ")),
          Where  => SQL.Equal (SQL.C(Host_String'("VENDER_K")),
                               SQL.V(Host_String'("QRS")))
          );
    if SQLCODE /= 0 then
        TEXT_IO.Put_Line ("WARNING: Update with search_cond failed");
    end if;
    
    SQL.Delete (SQL.T (Host_String'("PURCHASE")),
                SQL.Equal (SQL.C(Host_String'("VENDER_K")),
                          (SQL.V(Host_String'("PRS")))));
    if SQLCODE /= 0 then
        TEXT_IO.Put_Line ("WARNING: Delete with search_cond failed");
    end if;
    
    TEXT_IO.Put_Line ("Test completed");
    
end sql_ex;