sqlserver: requested operation is not supported on forward..

follow up to databases.


Going by:

<http://svn.apache.org/repos/asf/commons/proper/dbcp/branches/dbcp/doc/
ManualPoolingDataSourceExample.java>



The tomcat log shows:


BaseServlet.getMethodEnum:
crud: BEATLES
Feb 26, 2009 6:18:58 AM a00720398.database.NewDbcp setUri
INFO: jdbc:sqlserver://
j2ee.ca:1433;databaseName=jspweb;selectMethod=cursor;
Feb 26, 2009 6:18:58 AM a00720398.servlet.Controller beatles
SEVERE: null
com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation
is not supported on forward only result sets.


I believe that if a different sort of ResultSet were returned, with
different attributes, that would fix the error. Unfortunately, I don't
know how to get a scrollable result set. No matter what arguments are
passed for the booleans in the PoolableConnectionFactory constructor the
same error occurs.

Obviously, a different approach is needed. What's the correct, or at
least a better, approach?


code:

package a00720398.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

public class NewDbcp {

private static final String driver2005 =
"com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String url = "jdbc:sqlserver://";
private static final String serverName = "j2ee.ca";
private static final String portNumber = "1433";
private static final String databaseName = "jspweb";
private static final String userName = "userName";
private static final String password = "Password";
private static final String selectMethod = "cursor";
private static Logger logger = Logger.getLogger(NewDbcp.class.getName
()); //Controller.class.getName()
private static PoolingDataSource poolingDataSource = null;
private static Connection connection = null;
private static String uri = "uri";

private NewDbcp() {
}

private static void setUri() {
uri = url + serverName + ":" + portNumber + ";databaseName=" +
databaseName + ";selectMethod=" + selectMethod + ";";
logger.log(Level.INFO, uri);
}

private static void setConnection() {
try {
Class.forName(driver2005);
withGenericObjectPool();
//connection = DriverManager.getConnection(uri, userName,
password);
connection = poolingDataSource.getConnection();
} catch (SQLException ex) {
Logger.getLogger(NewDbcp.class.getName()).log(Level.SEVERE,
null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(NewDbcp.class.getName()).log(Level.SEVERE,
null, ex);
}
}

private static void withGenericObjectPool() {
setUri();
ObjectPool objectPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(uri, userName, password);
PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory, objectPool, null, null,
true, false);
poolingDataSource = new PoolingDataSource(objectPool);
}

public static Connection getConnection() {
setConnection();
return connection;
}
}



Also, which is better, or more appropriate:

ObjectPool objectPool = new GenericObjectPool(null);
GenericObjectPool genericObjectPool = new GenericObjectPool(null);


and, same question, which is better/more appropiate, DataSource or
DriverManager?

"An alternative to the DriverManager facility, a DataSource object is the
preferred means of getting a connection."
http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/DataSource.html




Maybe changes along those lines would fix this error.





thanks,

Thufir