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…