package oraJdbc;

import java.sql.*;
import java.util.*;

/**
 * @author schestag
 *
 * Diese Klasse enthält Beispiele für alle wichtigen Statement-Klassen 
 * der SQL-API auf der Datenbank flugdb des Teil I der Vorlesung
 * sowie Beisiele für Objekte vom Typ ResultSetMetaData und DatabaseMetaData
 */
public class MyJdbcStatement {

	static private Connection        con;
	
	static private Statement         stmt; 
	static private String            query;
	static private PreparedStatement pStmt;
	static private CallableStatement cStmt;
	
	static private ResultSet         rs;
	
	public static void main(String args[])
	throws SQLException {  
	  
	   // 1. Treiber-Klasse für JdbcOdbcDriver laden und registrieren
	   String driverClass =
		  "sun.jdbc.odbc.JdbcOdbcDriver";
	   try {
		  Class.forName(driverClass);
	   }
	   catch (ClassNotFoundException exc) {
		  System.out.println("c1: "+exc.getMessage());
		  System.exit(1);
	   }
	     
	   // Wieviele Elemente sind derzeit bei der Klasse DriverManager
	   // registriert?
	   Enumeration e = DriverManager.getDrivers();
	   while (e.hasMoreElements())
			System.out.println("Beim DriverManager registriert: " + 
			                    e.nextElement() + "\n");
			
	   // 2. Connection aufbauen
	   try {
		  String url = "jdbc:odbc:MyORA9"; 
		  con = DriverManager.getConnection(url,"ischestag","inge");
		  System.out.println("Jetzt ist die Connection da");
		  // Transaktionsmanagement: AutoCommit off
		  con.setAutoCommit(false);
		  // Isolationlevel setzen
		  con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		  con.commit();
	   }
	   catch (SQLException exc) {
		  System.out.println("c2: "+exc.getMessage());
	   }
	   
	   // Database Metadaten
	   try {
	     DatabaseMetaData dbm = con.getMetaData();
	     ResultSet rsm = dbm.getTableTypes();
	     while (rsm.next())
	        System.out.println(rsm.getString(1));
	   } catch(SQLException exc) {
	     System.out.println("c2a: "+exc.getMessage());
	   }
	   
	   // 3. SLQ-Anweisungen ausführen
	   // Javavariablen zum Auslesen der Daten
	   int vPerNr, vPStd;
	   
	   try { 
		  stmt =con.createStatement();

		  // PILOT-Tabelle lesen
		  rs = stmt.executeQuery("Select per_nr, p_std from pilot");
		  while (rs.next()) {
			vPerNr=rs.getInt(1);
			vPStd =rs.getInt(2);
			System.out.println("Ergebnis: " + vPerNr + " " + vPStd);		  
		  }
		  ResultSetMetaData rsm = rs.getMetaData();
		  int anzCol = rsm.getColumnCount();
		  for (int i=1; i<=anzCol; i++) {
		  	String cn  = rsm.getColumnName(i);
		  	String ctn = rsm.getColumnTypeName(i);
		  	System.out.println(cn + " " + ctn);
		  }
		  rs.close();  
	   }
	   catch(SQLException exc) {
		  System.out.println("c3: "+exc.getMessage());
	   } 
	   
	   // 4. PreparedStatement
	   try {
	   	 pStmt = con.prepareStatement("insert into flug values (?,?,?,?,?)");
	   	 pStmt.setString(1,"LH-999");
	     pStmt.setString(2,"Frankfurt");
	     pStmt.setString(3,"Hamburg");
	     pStmt.setDouble(4,0.5);
	     pStmt.setInt(5,400);
	     pStmt.executeUpdate();
	     con.commit();
	   } catch(SQLException exc) {
	   	 con.rollback();
	     System.out.println("c4: "+exc.getMessage());
	   } 
	   
	   // 5. StoredProcedure-Aufruf (CallableStatement) 
	   boolean type;   
	   try {
       	cStmt = con.prepareCall("{call neugehalt(?,?)}");
       	cStmt.setDouble(1, 1.01);
       	cStmt.setDouble(2, 1.02);
       	// Type == true <=> ResultSet, Type == fals <=> nur Updates
		type = cStmt.execute();
		con.commit();  
		
		while (true) {
       	   System.out.println("so viele Updates: "+cStmt.getUpdateCount());
       	   type = cStmt.getMoreResults();  
       	   if (cStmt.getUpdateCount() == -1)
       	      break;
		}    	   
	   } catch(SQLException exc) {
	   	 con.rollback();
		 System.out.println("c5: "+exc.getMessage());
	   } 
           
       // 6. Connection schliessen
       try {
           con.close();
       }
	   catch(SQLException exc) {
		   System.out.println("close connection: "+exc.getMessage());
	   } 
	}
}