Creating CallableStatement Objects with INOUT Parameters

// Creating CallableStatement Objects with INOUT Parameters

This section describes how to create CallableStatement objects with INOUT parameters.

// // //

MySQL procedures do support INOUT parameters, which should be handled like IN parameters and OUT parameters as shown in the previous tutorial.

In a previous tutorial, I defined a store procedure called, C2F(), with 2 INOUT parameters. The program below shows you to create a CallableStatement object to execute this stored procedure:

<pre>/**
 * MySqlCallInoutParameter.java
 */
import java.sql.*;
public class MySqlCallInoutParameter {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
        = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// Create CallableStatement
      CallableStatement cs = con.prepareCall("CALL C2F(?,?)");

// Register INOUT parameters
      cs.registerOutParameter(1, java.sql.Types.REAL);
      cs.registerOutParameter(2, java.sql.Types.REAL);

// Provide values for INOUT parameters
      double celsius = 100.0;
      cs.setDouble(1,celsius);

// Execute the CALL statement and ignore result sets
      cs.executeUpdate();

// Retrieve values from INOUT parameters
      double fahrenheit = cs.getDouble(2);
      System.out.println("First test:");
      System.out.println("  Celsius = "+celsius);
      System.out.println("  Fahrenheit = "+fahrenheit);

// Second test
      celsius = 0.0;
      cs.setDouble(1,celsius);
      cs.executeUpdate();
      fahrenheit = cs.getDouble(2);
      System.out.println("First test:");
      System.out.println("  Celsius = "+celsius);
      System.out.println("  Fahrenheit = "+fahrenheit);

// Close resource
      cs.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}
</pre>

The execution gave me one surprise. The second test failed with an error that I don’ understand:

<pre>C:\>java MySqlCallInoutParameter

First test:
  Celsius = 100.0
  Fahrenheit = 212.0
</pre>
Advertisements

Database Table Alteration

In database side, Sometimes we come across a situation, where we have to add new column or alter existing column in the table. We are doing all this thing by using GUI that SQL Server or Oracle/MSSQL provides us. But we should know that what is happening behind the scene when the table have a millions of records, lots of constraints etc.

-> the GUI will do the following:
· build a new table to the correct specification
· migrate the data from the old table to the new.
· drop all the constraints against the original table
· recreate all the constraints against the new table
· drop the original table
· rename the new table to the original name

So GUI does all the grunt work behind the scene. So the suggested way is, we have to use “Query” to accompish that type of task.
For exa.

ALTER TABLE [Table1] ALTER COLUMN [col_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

This will save our time and do not affect the table’s existing constraint.
Hope this info may help u…

Database Connection using JDBC/JAVA

Here some code for database connection which one help to create database connection….

DataTransaction.java


public class DataTransaction  
{
	private static final String userName = "mtnl";
	private static final String passWord = "matix";
	
	/*********************************** DRIVER **************************************/
	/** ORACLE **/
	private static final String driver = "oracle.jdbc.driver.OracleDriver"; // Option 1
	private static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // Option 2
		
	/** MySQL **/
	private static final String driver = "com.mysql.jdbc.Driver";
	
	/*********************************** URL **************************************/
	/** ORACLE **/
	private static final String url = "jdbc:oracle:thin:@192.168.100.63:1521:DBName"; 
	/** MySQL **/	
	private static final String url = "jdbc:mysql://192.168.50.89/DBName";
		
	// private static final String url = "jdbc:oracle:thin:@192.168.100.63:1521:RUBY;failoverPartner=192.168.100.114:1521:RUBY;integratedsecurity=true";
	// private static final String url = "jdbc:oracle:thin(DESCRIPTION = (FAILOVER = on ) (ADDRESS_LIST =  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.63)(PORT=1521)) (ADDRESS=(PROTOCOL=IPC)(HOST= 192.168.100.114)(PORT=1521)) (LOAD_BALANCE = ON)) (CONNECT_DATA=(SERVICE_NAME = RUBY ) (FAILOVER_MODE = (TYPE=session) (METHOD=basic) (RETRIES = 180) (DELAY =5) )))" ;
	
	public static Connection connection=null;
	public static int connectionCount=0;
	public Statement statement;
	public PreparedStatement pstmt = null;
	private int id;

	/**
	*	Constructor
	*/
	public DataTransaction(){}

	public void createConnection(){}

	public DataTransaction(boolean setCon)
	{
		try
		{
			setConnection();
		}
		catch(Exception e)
		{
			System.out.println("Error in Connection:"+ e.toString());
		}
	}

	/**
	*	setConnection method for setting connection with drivers and database
	*/
	public void setConnection() throws SQLException
	{
		try
		{
			try
			{
				Class.forName(driver); 
				/**
				*  Create a connection to the database
				*/
				if(connection == null || connection.isClosed())
				{
					System.out.println(" MAKING CONNECTION WITH FIRST SERVER.");
					connection = DriverManager.getConnection(url,userName,passWord);
				}
			}
			catch (SQLException e)
			{
				try
				{
					System.out.println(" MAKING CONNECTION WITH SECOND SERVER.");
					connection = DriverManager.getConnection(url,userName,passWord);
				}
				catch(SQLException ex)
				{
					System.out.println ("Could not connect to the database msg second connection failed :" + ex.getMessage());
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Connection Open Exception"+e);
		}
	}
	
	/**
	*	Closes the connection with database and drivers
	*/
	public void closeConnection() throws SQLException
	{
		try
		{
			if(statement != null)
			{
				statement.close();
			}
			else
			{
				System.out.println("Close Connection else  of DataTransaction ");
			}
		}
		catch(Exception e)
		{
			System.out.println("Fianlize of DataTransaction in catch : "+e);
		}
	}
	
	/**
	*	Pass SQL query through this method. this method returns Result Set containing requested data
	*/
	public ResultSet viewQuery(String query) throws SQLException, Exception
	{
		if(isLowerCaseReq)
		{
			query = query.toLowerCase();
		}
		else
		{
			isLowerCaseReq = true;
		}	
		
		try
		{
			if(connection.isClosed())
			{
				setConnection();
			}
			
			statement=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs=statement.executeQuery(query);
			connection.commit();
		}
		catch(Exception e)
		{
			System.out.println("Error in dt.viewQuery() " + e.getMessage() + " : " + query);
		}
		return rs;
	}
	
	/**
	*	InsertInTo method to insert in database from JSP PAGE  returns true if inserted in database otherwise false using synchronisation
	*/
	
	public synchronized boolean insertQuery(String query) throws SQLException, Exception
	{
		try
		{
			if(connection.isClosed())
			{
				setConnection();
			}
			
			statement=connection.createStatement();
			
			if(!(statement.execute(query)))
			{
				connection.commit();
				return true;
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println("Insert exception data transaction "+e  );
			return false;
		}
	}	
	
	/**
	*	Delete method to delete row in database from JSP PAGE returns true if inserted in database otherwise false
	*/
	public boolean deleteQuery(String query) throws SQLException, Exception
	{    
		return insertQuery(query);        
	}

	/**
	*	Update method to update data in database from JSP PAGE accepts string from user and returns returns true if inserted in database otherwise false
	**/	
	public boolean updateQuery(String query) throws SQLException, Exception
	{  
		return  insertQuery(query);        
	}
	
	/**
	*	For Search Functionality Search query passed through this method and it returns Result Set containing requested data
	*/
	public ResultSet searchQuery(String query) throws SQLException, Exception
	{      
		try
		{
			searchRS = viewQuery(query);                
		}
		catch(Exception e)
		{
			System.out.println("Search exception data transaction "+e  );
		}
		return searchRS;
	}
	 
	public static void main(String[] args){}

	public Connection getConnection()
	{
		return this.connection;
	}
	
	public Statement getStatement()
	{
		try
		{
			this.setConnection();
			statement = this.connection.createStatement();
			return this.statement;
		}
		catch(Exception e)
		{
			System.out.println("Cannot create Statement" + e.getMessage());
			return null;
		}
	}
	
	public void gccall()
	{
		System.gc();
	}
	
	protected void finalize()
	{
		try
		{
			if(statement != null)
			{
				statement.close();
			}	
		}
		catch(Exception e)
		{
			System.out.println("Fianlize of DataTransaction in catch");
		}
		System.gc();
	}
	
}