/*
   The program FundamentalExample1.sqlj illustrates how to connect to a
   database, how to embed SQL DML operations in SQLJ executable statements,
   and how to use host expressions.
*/

// import required packages
import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class FundamentalExample1 {

  public static void main(String [] args) {

    try {

      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );

      // add a new customer
      int customer_id = 6;
      String first_name = "Jerry";
      String last_name = "Fieldtop";
      Date dob = new Date(80, 1, 1);
      String phone = "650-555-1222";
      #sql {
        INSERT INTO
          customers (id, first_name, last_name, dob, phone)
        VALUES
          (:customer_id, :first_name, :last_name, :dob, :phone)
      };

      // display new customer
      #sql {
        SELECT
          first_name, last_name, dob, phone
        INTO
          :first_name, :last_name, :dob, :phone
        FROM
          customers
        WHERE
          id = :customer_id
      };
      System.out.println("Customer with id " + customer_id +
        " has the following details:");
      System.out.println("  First name: " + first_name);
      System.out.println("  Last name: " + last_name);
      System.out.println("  DOB: " + dob);
      System.out.println("  Phone: " + phone);

      // delete the customer
      #sql {
        DELETE FROM
          customers
        WHERE
          id = :customer_id
      };

      // commit the transaction
      #sql { COMMIT };

      // update the first product price
      int product_id = 1;
      double product_price = 11.25;
      #sql {
        UPDATE
          products
        SET
          price = :product_price
        WHERE
          id = :product_id
      };

      // display the first product
      int type_id = 0;
      String name = null;
      String description = null;
      double price = 0.0;
      #sql {
        SELECT
          type_id, name, description, price
        INTO
          :type_id, :name, :description, :price
        FROM
          products
        WHERE
          id = :product_id
      };
      System.out.println("Product with id " + product_id +
        " has the following details: ");
      System.out.println("  Type id: " + type_id);
      System.out.println("  Name: " + name);
      System.out.println("  Description: " + description);
      System.out.println("  Price: " + price);

      // rollback the update
      #sql { ROLLBACK };

      // create a table to hold customer addresses
      #sql {
        CREATE TABLE addresses (
          id            NUMBER CONSTRAINT addresses_pk PRIMARY KEY,
          customer_id   NUMBER CONSTRAINT addresses_fk_customers
            REFERENCES customers(id),
          street        VARCHAR2(255) NOT NULL,
          city          VARCHAR2(255) NOT NULL,
          state         CHAR(2) NOT NULL,
          country       VARCHAR2(255) NOT NULL
        )
      };
      System.out.println("Successfully created addresses table.");

      // drop the addresses table
      #sql {
        DROP TABLE addresses
      };

    } catch ( SQLException e ) {

      System.err.println("SQLException " + e);

    } finally {

      try {

        Oracle.close();

      } catch ( SQLException e ) {

        System.err.println("SQLException " + e);

      }

    }

  } // end of main()

}