how to execute both select & insert query in same place
Hi,
I want to select the data from table before going to insert the data into the same table.
But I am not able to insert the date after execute the select query.
Please anyone help me. ..how can I use both select and insert query in same java file.
pstmt = conn.prepareStatement ("select curr_date from telreport");
rs=pstmt.executeQuery();
String pre_date ="";
while(rs.next())
{
pre_date = rs.getString(1);
}
System.out.println("pre_date "+pre_date);
pstmt = conn.prepareStatement ("insert into Telreport (calldate ) values (sysdate);
pstmt.executeUpdate();
if (conn !=null )
{
pstmt.close();
}
}
Here i have used the one connection for select the data from table and again i have used insert statement I don抰 know wheatear its possible
It says the Error Like " maximum open cursors exceeded"
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
Please help to to exectue bothe queries
Thanks
Merlina Rosin
try this.
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
try {
connection = ... // Get connection from datasource or whatever
String pre_date = "";
stmt = connection.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select curr_date from telreport");
if (rs.next()) {
pre_date = rs.getString(1);
}
System.out.println("pre_date " + pre_date);
if (stmt != null)
stmt.close();
if (rs != null)
rs.close();
stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
int updatedRows = stmt.executeUpdate("insert into Telreport (calldate ) values (sysdate)");
if (updatedRows <= 0)
throw new SQLException("INSERT failed to update any rows.");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
stmt = null;
connection = null;
}
}
Seems as if Oracle is not allowing you to have more than 1 open cursor. On your code, you could try simply closing the statement after the first select to force the resources to be released.
> The above is good advise which should always be
> followed, but the statement will be closed by the
> driver when it becomes out of context.
I wouldn't rely on that. I have seen the error message mentioned above just because applications weren't closing rs and statements.
Kaj
