//  import sqlj packages.
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

//  JDBC packages
import java.sql.*;

import java.io.*;

//   define iterator.
#sql iterator SupplierRecord (
    String snum,
    String sname,
    String scity,
    Integer status
);

public class ListAllSuppliers {
   public static void main(String args[]) throws SQLException {
      //  Database connection
      DefaultContext ct = Oracle.getConnection(
                             "jdbc:oracle:thin:@129.7.163.114:1521:d703", "....",
                             "....");

      //  Read in the data.
      String in_scity = readEntry("City -> ");

      //  Create an iterator object
      SupplierRecord suppliers = null;

      //  Executing the sql using host variables.
      #sql [ct] suppliers = { select SNum, SName, SCity, Status 
                              from Suppliers
                              where upper(SCity) = upper(:in_scity) };

      System.out.println("The suppliers in the city " + in_scity + " :");

      while (suppliers.next()) {
          System.out.print("  supplier #" + suppliers.snum() + " ("
                           + suppliers.sname() + ")");
          Integer status = suppliers.status();
          if (status == null)
             System.out.println(" has no status.");
          else
             System.out.println(" has a status of " + status.intValue() + ".");
      }
   }

  //readEntry function -- to read input string 
  static String readEntry(String prompt) { 
     try { 
       StringBuffer buffer = new StringBuffer(); 
       System.out.print(prompt); 
       System.out.flush(); 
       int c = System.in.read(); 
       while(c != '\n' && c != -1) { 
         buffer.append((char)c); 
         c = System.in.read(); 
       } 
       return buffer.toString().trim(); 
     } catch (IOException e) { 
       return ""; 
       } 
   } 
}