Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Executing a Command


The Connection object supports three types of Statement objects that can be used to execute an SQL statement or stored procedure:

Using a Statement Object

The Connection method createStatement is used to create a Statement object that can be used to execute SQL statements without parameters.

The executeUpdate method of the Statement object is used to execute an SQL DELETE, INSERT, or UPDATE statement, i.e. a statement that does not return a result set, it returns an int indicating the number of rows affected by the statement, for example:

 int rowCount;
 
 stmt = con.createStatement();
 
 rowCount = stmt.executeUpdate(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * 1.05"
                  + "   WHERE code = 'USD'");
                               
 System.out.println(rowCount + " rows have been updated");

Using a PreparedStatement Object

Where an SQL statement is being repeatedly executed, a PreparedStatement object is more efficient than repeated use of the executeUpdate method against a Statement object.

In this case the values for the parameters in the SQL statement (indicated by ?) are supplied with the setXXX method, where XXX is the appropriate type for the parameter.

For example:

 PreparedStatement pstmt;
 int rowCount;
 
 pstmt = con.prepareStatement(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * ?"
                  + "   WHERE code = ?");
                    
 pstmt.setFloat(1, 1.05f);
 pstmt.setString(2, "USD");
 rowCount = pstmt.executeUpdate();
 
 pstmt.setFloat(1, 1.08f);
 pstmt.setString(2, "GBP");
 rowCount = pstmt.executeUpdate();   

Using a CallableStatement Object

Similarly, when using stored procedures, a CallableStatement object allows parameter values to be supplied, for example:

 CallableStatement cstmt;
 
 cstmt = con.prepareCall("{CALL mimer_store.order_item( ?, ?, ? )}");
 
 cstmt.setInt(1, 700001);
 cstmt.setInt(2, 60158);
 cstmt.setInt(3, 2);
 cstmt.executeUpdate();
 

The setNull method allows a JDBC null value to be specified as an IN parameter. Alternatively, use a Java null value with a setXXX method.

For example:

 pstmt.setString(4, null);
 

A more complicated example illustrates how to handle an output parameter:

 CallableStatement cstmt;
 
 cstmt = con.prepareCall("{CALL mimer_store.age_of_adult( ?, ? )}");
 
 cstmt.setString(1, "US");
 cstmt.registerOutParameter(2, Types.CHAR);
 
 cstmt.executeUpdate();
 System.out.println(cstmt.getString(2) + " years");


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX