com.mimer.jdbc
Class Statement

java.lang.Object
  |
  +--com.mimer.jdbc.Statement

All Implemented Interfaces:

Statement

Direct Known Subclasses:

PreparedStatement

public class Statement

extends Object

implements Statement

A Statement object is used for executing a static SQL statement and obtaining the results produced by it.

Only one ResultSet per Statement can be open at any point in time. Therefore, if the reading of one ResultSet is interleaved with the reading of another, each must have been generated by different Statements. All statement execute methods implicitly close a statements current ResultSet if an open one exists.

With a few exceptions Statement objects supports all Mimer SQL constructs introduced up until the driver was released. When the driver is older than the server, there is no guarantee that new SQL constructs will work with the driver.

The below Mimer SQL constructs are not supported by the JDBC driver, and also why they aren't supported.

SQL statement	Explanation
SET TRANSACTION START EXPLICIT SET TRANSACTION START IMPLICIT	In JDBC transactions are always started automatically when the first statement of a transaction is executed. There is no need to set the transaction in implicit start mode.
START	Since transactions are automatically started when the first statement is executed, there is no need to explicitly start them.
ROLLBACK COMMIT	In JDBC transactions are managed using the JDBC interface. Use the methods Connection.rollback and Connection.commit instead.
SET TRANSACTION READ WRITE SET TRANSACTION READ ONLY SET SESSION READ WRITE SET SESSION READ ONLY	Use the JDBC method Connection.setReadOnly instead.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET TRANSACTION ISOLATION LEVEL REPEATABLE READ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET SESSION ISOLATION LEVEL READ UNCOMMITTED SET SESSION ISOLATION LEVEL READ COMMITTED SET SESSION ISOLATION LEVEL REPEATABLE READ SET SESSION ISOLATION LEVEL SERIALIZABLE	Use the JDBC method Connection.setTransactionIsolation instead.

Field Summary

Fields inherited from interface java.sql.Statement

CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, EXECUTE_FAILED, KEEP_CURRENT_RESULT, NO_GENERATED_KEYS, RETURN_GENERATED_KEYS, SUCCESS_NO_INFO

Method Summary

void	addBatch(String sql) Adds an SQL-statement to the batch.
void	cancel() Requests to cancel the currently executing statement.
void	clearBatch() Clears the current batch.
void	clearStatement() Clear attributes in this statement object.
void	clearWarnings() Clears all warnings reported on the object.
void	close() Closes the statement.
boolean	execute(String parm1) Executes any SQL query or statement.
int[]	executeBatch() Executing the current batch.
ResultSet	executeQuery(String sql) Executes a query that returns a result set.
int	executeUpdate(String sql) Executes an SQL-statement that don't return a result set.
Connection	getConnection() Returns the Connection object related to this Statement.
int	getFetchDirection() Gets the current fetch direction hint.
int	getFetchSize() Retrieves the current fetch size.
int	getMaxFieldSize() Gets the max field size.
int	getMaxRows() Getting the maximum number of rows to be returned in a result set.
boolean	getMoreResults() Check if there are moew result sets open on this statement.
int	getQueryTimeout() Gets the query timeout in seconds.
ResultSet	getResultSet() Returns the result set created by the last statement.
int	getResultSetConcurrency() Gets the current result set concurrency.
int	getResultSetType() Gets the current result set type.
int	getUpdateCount() Returns the number of rows updated by the last statement.
SQLWarning	getWarnings() Returns the first warning posted to this object.
void	setCursorName(String name) Sets the cursor name of the statement.
void	setEscapeProcessing(boolean enable) Enable or disables SQL escape clause processing.
void	setFetchDirection(int direction) Sets the current fetch direction hint.
void	setFetchSize(int rows) Sets the current fetch size.
void	setMaxFieldSize(int max) Sets the max field size.
void	setMaxRows(int max) Setting the maximum number of rows to be returned in a result set.
void	setQueryTimeout(int timeout) Sets the query timeout in seconds.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface java.sql.Statement

execute, execute, execute, executeUpdate, executeUpdate, executeUpdate, getGeneratedKeys, getMoreResults, getResultSetHoldability

Method Detail

addBatch

public void addBatch(String sql)
              throws SQLException

Adds an SQL-statement to the batch.

Performance considerations: Batching several SQL-statements in one batch can have a tremendous effect on performance. A batch is transferred to the server in one network call. Executing the same statements one by one most probably involve one request and one return packet. Batching 10 statements both saves transmission times as well as server communications.

A word of caution. If many semantically identical SQL-statements are to be executing, but with different data, better execution times and scalability will be experienced by using a batched PreparedStatement instead. One should avoid batching Statement like below:

Statement stmt = con.createStatement();
stmt.addBatch("insert into OURTAB values (1,'First man')");
stmt.addBatch("insert into OURTAB values (2,'Second man')");
stmt.addBatch("insert into OURTAB values (3,'Third man')");
stmt.addBatch("insert into OURTAB values (4,'Fourth man')");
stmt.addBatch("insert into OURTAB values (5,'Fifth man')");
stmt.executeBatch();


Instead, use a PreparedStatement:

PreparedStatement pstmt = con.prepareStatement("insert into OURTAB values (?,?)");
pstmt.setInt(1,1);
pstmt.setString(1,"First man");
pstmt.addBatch();
pstmt.setInt(1,2);
pstmt.setString(1,"Second man");
pstmt.addBatch();
pstmt.setInt(1,3);
pstmt.setString(1,"Third man");
pstmt.addBatch();
pstmt.setInt(1,4);
pstmt.setString(1,"Fourth man");
pstmt.addBatch();
pstmt.setInt(1,5);
pstmt.setString(1,"Fifth man");
pstmt.addBatch();
pstmt.executeBatch();


Specified by:

addBatch in interface Statement

Parameters:

sql - The SQL statement to batch.

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown.

See Also:

executeBatch()

cancel

public void cancel()
            throws SQLException

Requests to cancel the currently executing statement.

Whenever this method is called, the client sends a cancel request to the server. Once the request is transmitted, control is returned to the caller. Therefore, the client doesn't know that the server for sure has cancelled the operation. The actual cancellation can take some time depending on load and what the server is doing. In most cases, however, the cancellation should be instantaneous.

This method should be called from a separate thread. The thread that started the operation to be cancelled is waiting for the server to complete its work. Once the operation is cancelled, SQLSTATE S1008 is thrown in the thread that initiated the work.

Specified by:

cancel in interface Statement

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open.

clearBatch

public void clearBatch()
                throws SQLException

Clears the current batch.

Specified by:

clearBatch in interface Statement

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown.

See Also:

addBatch(java.lang.String), executeBatch()

clearStatement

public void clearStatement()
                    throws SQLException

Clear attributes in this statement object.

Clear all warnings. Close the current result set if there is one.

Throws:

SQLException - when the statement is closed

clearWarnings

public void clearWarnings()
                   throws SQLException

Clears all warnings reported on the object.

Specified by:

clearWarnings in interface Statement

Throws:

SQLException - if an error occurs.

close

public void close()
           throws SQLException

Closes the statement.

Specified by:

close in interface Statement

Throws:

SQLException - if an error occurs.

execute

public boolean execute(String parm1)
                throws SQLException

Executes any SQL query or statement.

Specified by:

execute in interface Statement

Returns:

True if a result set was returned. If so use the Statement.getResultSet to obtain the ResultSet object.

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open. HY000 is thrown if the statement contains parameter markers, in this case you must use a PreparedStatement object instead. HY000 may also be thrown when the SQL statement was not supported by the driver, see Statement for an overview on which statements that are not supported and why. 42000 is thrown if the SQL contained a syntax error. 34000 is thrown if a UPDATE/DELETE WHERE CURRENT OF specified a cursor that doesn't exist. S1008 is thrown if the execution is cancelled.

See Also:

executeUpdate(java.lang.String), executeQuery(java.lang.String), getResultSet()

executeBatch

public int[] executeBatch()
                   throws SQLException

Executing the current batch.

Specified by:

executeBatch in interface Statement

Returns:

An integer array containing update counts of each statement.

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown. This call may execute a lot of server side code and may return any server runtime error. S1008 is thrown if the execution is cancelled.

See Also:

addBatch(java.lang.String)

executeQuery

public ResultSet executeQuery(String sql)
                       throws SQLException

Executes a query that returns a result set.

Specified by:

executeQuery in interface Statement

Parameters:

sql - The SQL query.

Returns:

The update count.

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open. HY000 is thrown if the statement contains parameter markers, in this case you must use a PreparedStatement object instead. HY000 may also be thrown when the SQL statement was not supported by the driver, see Statement for an overview on which statements that are not supported and why. 42000 is thrown if the SQL contained a syntax error or the statement wasn't a query. 34000 is thrown if a UPDATE/DELETE WHERE CURRENT OF specified a cursor that doesn't exist. S1008 is thrown if the execution is cancelled.

See Also:

executeUpdate(java.lang.String), execute(java.lang.String)

executeUpdate

public int executeUpdate(String sql)
                  throws SQLException

Executes an SQL-statement that don't return a result set.

Specified by:

executeUpdate in interface Statement

Parameters:

sql - The SQL-statement.

Returns:

The update count.

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open. HY000 is thrown if the statement contains parameter markers, in this case you must use a PreparedStatement object instead. HY000 may also be thrown when the SQL statement was not supported by the driver, see Statement for an overview on which statements that are not supported and why. 42000 is thrown if the SQL contained a syntax error or the statement was a query. 34000 is thrown if a UPDATE/DELETE WHERE CURRENT OF specified a cursor that doesn't exist. S1008 is thrown if the execution is cancelled.

See Also:

executeQuery(java.lang.String), execute(java.lang.String)

getConnection

public Connection getConnection()
                         throws SQLException

Returns the Connection object related to this Statement.

Specified by:

getConnection in interface Statement

Returns:

The connection.

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown.

getFetchDirection

public int getFetchDirection()
                      throws SQLException

Gets the current fetch direction hint.

Specified by:

getFetchDirection in interface Statement

Returns:

The fetch direction hint.

Throws:

SQLException - if an error occurs.

See Also:

setFetchDirection(int)

getFetchSize

public int getFetchSize()
                 throws SQLException

Retrieves the current fetch size. A value of 0 implies that the driver uses an appropriate fetch size of its on choice.

Specified by:

getFetchSize in interface Statement

Returns:

The fetch size.

Throws:

SQLException - if an error occurs.

See Also:

setFetchSize(int)

getMaxFieldSize

public int getMaxFieldSize()
                    throws SQLException

Gets the max field size.

Specified by:

getMaxFieldSize in interface Statement

Returns:

The max field size.

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open.

See Also:

setMaxFieldSize(int)

getMaxRows

public int getMaxRows()
               throws SQLException

Getting the maximum number of rows to be returned in a result set.

Specified by:

getMaxRows in interface Statement

Returns:

The maximum number of rows in a result set.

Throws:

SQLException - if an error occurs. HY010 is thrown if the statement is not open.

See Also:

setMaxRows(int)

getMoreResults

public boolean getMoreResults()
                       throws SQLException

Check if there are moew result sets open on this statement.

Mimer SQL procedures may only return one result set. This call will therefore always return false. But since this method is supposed to abandon the current result set and move on to the next, any open result set is closed.

Specified by:

getMoreResults in interface Statement

Returns:

True if there were more result sets available.

Throws:

SQLException - if an error occurred. HY010 if the statement is closed.

getQueryTimeout

public int getQueryTimeout()
                    throws SQLException

Gets the query timeout in seconds.

Specified by:

getQueryTimeout in interface Statement

Returns:

The timeout in seconds. 0 for no timeout.

Throws:

SQLException - if an error occurred. HY010 if the statement is closed.

See Also:

setQueryTimeout(int)

getResultSet

public ResultSet getResultSet()
                       throws SQLException

Returns the result set created by the last statement.

Specified by:

getResultSet in interface Statement

Returns:

The result created by the last statement, or null if there was no result set.

Throws:

SQLException - if an error occurs. SQLSTATE HY010 is thrown if the statement is not open.

getResultSetConcurrency

public int getResultSetConcurrency()
                            throws SQLException

Gets the current result set concurrency.

Specified by:

getResultSetConcurrency in interface Statement

Returns:

The result set concurrency.

Throws:

SQLException - if an error occurs.

See Also:

Connection.createStatement(int,int), Connection.prepareStatement(String,int,int), Connection.prepareCall(String,int,int)

getResultSetType

public int getResultSetType()
                     throws SQLException

Gets the current result set type.

Specified by:

getResultSetType in interface Statement

Returns:

The result set type.

Throws:

SQLException - if an error occurs.

See Also:

Connection.createStatement(int,int), Connection.prepareStatement(String,int,int), Connection.prepareCall(String,int,int)

getUpdateCount

public int getUpdateCount()
                   throws SQLException

Returns the number of rows updated by the last statement.

Specified by:

getUpdateCount in interface Statement

Returns:

The update count.

Throws:

SQLException - if an error occurs. SQLSTATE HY010 is thrown if the statement is not open.

getWarnings

public SQLWarning getWarnings()
                       throws SQLException

Returns the first warning posted to this object.

Specified by:

getWarnings in interface Statement

Returns:

The first warning object or null if no warnings available.

Throws:

SQLException - if an error occurred.

See Also:

clearWarnings()

setCursorName

public void setCursorName(String name)
                   throws SQLException

Sets the cursor name of the statement.

In Mimer, cursor names may be used by other statements to refer to this statement. For example, the first statement creates an updatable result set. A second statement issues UPDATE WHERE CURRENT OF statements to update rows in the result set.

For example, see this pseudocode:

Statement stmt1 = con.createStatement();
stmt1.setCursorName("CURSOR1");
Statement stmt2 = con.createStatement();
ResultSet rs = stmt1.executeQuery("select COLUMN1,COLUMN2 from TABLE1");
rs.next();
...
...
stmt2.execute("update TABLE1 set COLUMN1 = 'new value' where current of CURSOR1");


See the Mimer SQL Reference manual for more information about UPDATE WHERE CURRENT OF statements.

Specified by:

setCursorName in interface Statement

Parameters:

name - The new cursor name.

Throws:

SQLException - if an error occurs. SQLSTATE HY010 is thrown if the statement is not open.

NullException - if name was null.

setEscapeProcessing

public void setEscapeProcessing(boolean enable)
                         throws SQLException

Enable or disables SQL escape clause processing.

The escape clauses are expanded by a preprocessor before feeding the statement to the SQL compiler. Performance can be slightly improved if this option is turned off.

Specified by:

setEscapeProcessing in interface Statement

Parameters:

enable - True if escape processing should be executed.

Throws:

SQLException - if an error occurs. SQLSTATE HY010 is thrown if the statement is not open.

See Also:

Connection.nativeSQL(java.lang.String)

setFetchDirection

public void setFetchDirection(int direction)
                       throws SQLException

Sets the current fetch direction hint.

Mimer JDBC uses this information to guess which row to be fetched next. The rows most likely to be be fetched next are prefetched. If the fetch direction is FETCH_FORWARD, the prefetch will mostly contain rows after the cursor. If the fetch direction is FETCH_REVERSE, the prefetch will mostly contain rows before the cursor.

This setting affects all subsequently created result sets.

Specified by:

setFetchDirection in interface Statement

Parameters:

direction - The fetch direction hint.

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown. If the fetch direction is illegal HY024 is thrown.

See Also:

getFetchDirection()

setFetchSize

public void setFetchSize(int rows)
                  throws SQLException

Sets the current fetch size.

The JDBC driver prefetches data whenever this is suitable. This means that on the first ResultSet.next() call of a result set, 1000 rows may be fetched and stored in the client. Setting this attribute gives the application programmer means to control the amount of rows fetched.

The default fetch size is always selected to be something appropriate depending on the environment. A Mimer JDBC Engine client mostly selects a fetch size which will contain about 60 kb of data.

Performance consideration: Most of the time, the default fetch size is good enough for us. There are some situations where a small fetch size is suitable.

• The query doing a table scan on a huge table. It might take minutes to find enough rows to fill up the entire prefetch buffer. I would like to have the first rows up in the application as soon as possible, which might take only a few seconds. In this case a small fetch size is appropriate, perhaps as small as 1.
• My application runs on a GPRS device which is charged by the kilobyte. I don't want to transfer more data to the application than necessary. In this situation, perhaps a fetch size of 25 or smaller is appropriate.
• The default fetch size is optimized for a LAN connection. Any slow connection (such as a 65 kbit modem or a 128 kbit ISDN connection) might have unacceptable long response times using the default setting.

The fetch size is different from max rows in the following aspects.

• The max rows defines the size of the entire result set. The fetch size is about how many rows are to be transferred in each network request.
• Max rows is a definite and exact measure on the maximum number of rows. The fetch size is merely a hint to the driver.

Specified by:

setFetchSize in interface Statement

Parameters:

rows - The fetch size.

Throws:

SQLException - if an error occurs. If the Statement object is closed, SQLSTATE HY010 is thrown. HY024 is thrown if the fetch size is negative.

See Also:

setMaxRows(int), getFetchSize()

setMaxFieldSize

public void setMaxFieldSize(int max)
                     throws SQLException

Sets the max field size.

Specified by:

setMaxFieldSize in interface Statement

Parameters:

max - The new max field size.

Throws:

SQLException - if an error occurs. SQLSTATE HY024 is thrown of the parameter was negative. HY010 is thrown if the statement is not open.

setMaxRows

public void setMaxRows(int max)
                throws SQLException

Setting the maximum number of rows to be returned in a result set.

Limiting the number of rows in a result set is useful when the JDBC programmer knows for sure that he is only interested in the first n rows. Some advantages are:

• The server might be able to make some optimization choices based on the fact that only n rows are to be returned.
• On a network where every kilobyte is billed (such as GPRS) we can make sure we don't transfer more data than we need, and thus reducing transfer costs.

This setting affects all subsequently created reesult set.

Specified by:

setMaxRows in interface Statement

Parameters:

max - The new max rows value.

Throws:

SQLException - if an error occurs. SQLSTATE HY024 is thrown if the max parameter is negative. HY010 is thrown if the statement is not open.

See Also:

setFetchSize(int)

setQueryTimeout

public void setQueryTimeout(int timeout)
                     throws SQLException

Sets the query timeout in seconds.

Specified by:

setQueryTimeout in interface Statement

Parameters:

timeout - The timeout in seconds. 0 for no timeout.

Throws:

SQLException - if an error occurred. SQLSTATE HY024 for an illegal timeout value. HY010 if the statement is closed.

See Also:

getQueryTimeout()