package com.javatunes.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

import org.apache.derby.client.am.SqlWarning;

import oracle.jdbc.pool.OracleDataSource;

public class OraWarning {
	public static void main(String[] args) {
		OracleDataSource ods;
		Connection cnn = null;
		try {
			ods = new OracleDataSource();
			ods.setUser("hr");
			ods.setPassword("hr");
			ods.setServerName("SRVХХ");
			ods.setServiceName("orcl");
			ods.setPortNumber(1521);
			ods.setDriverType("thin");
			cnn = ods.getConnection();
			
			String sqlOne = "call DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION')";

			Statement stmOne = cnn.createStatement();
			
			int resultSql=stmOne.executeUpdate(sqlOne);

			sqlOne = "alter procedure prc_work compile";
			resultSql=stmOne.executeUpdate(sqlOne);
			
			SQLWarning swrSql=stmOne.getWarnings();
			if (swrSql==null) {
				System.out.println("no warningS");
			}
			while(swrSql!=null ) {
				System.out.println("~~");
				swrSql.getNextWarning();
			}

			
			sqlOne = "call DBMS_WARNING.set_warning_setting_string ('DISABLE:ALL', 'SESSION')";
			resultSql=stmOne.executeUpdate(sqlOne);
			
			
			//implicit convertation varchar2->number
			String strRes;			
			byte btRes;
			sqlOne="select 1e1 f1 from dual";
			ResultSet r = stmOne.executeQuery(sqlOne);
			while (r.next()) {
				strRes=r.getString("f1");
				btRes=r.getByte("f1");
			}

			swrSql=stmOne.getWarnings();
			if (swrSql==null) {
				System.out.println("no warningS");
			}
			while(swrSql!=null ) {
				System.out.println("~~");
				swrSql.getNextWarning();
			}
			r.close();
			
			//Prepared statement with implicit convertion varchar2->number;
			sqlOne="select first_name from employees where employee_id=?";			
			PreparedStatement psOne = cnn.prepareStatement(sqlOne);
			psOne.setString(1, "100");
			r = psOne.executeQuery();
			while (r.next()) {
				strRes=r.getString("first_name");
				System.out.println(strRes);
			}			

			swrSql=stmOne.getWarnings();
			if (swrSql==null) {
				System.out.println("no warningS");
			}
			while(swrSql!=null ) {
				System.out.println("~~");
				swrSql.getNextWarning();
			}
			r.close();
			
			
		} catch (SQLException e) {
			logException(e);
			if (cnn!=null) {
				try {
					cnn.commit();
					cnn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
		}

	}

	static void logException(SQLException ex) {
		while (ex != null) {
			System.out.println(ex.getSQLState());
			System.out.println(ex.getErrorCode());
			System.out.println("----");
			System.out.println(ex.getMessage());
			System.out.println("====");
			ex = ex.getNextException();
		}
	}

}