Get SQL Connection From DSRequest

[aderosso]

I'm trying to get a java.sql.Connection from a DSRequest. I've read the javadocs for RPCManager and SQLTransaction, but none of the methods seem to be working for me. Here's a utility method that I have started, but doesn't work:

    public static Connection getConnection(final DSRequest dsRequest) throws Exception {
        if (!dsRequest.getDataSource().getType().equals("sql")) {
            throw new IllegalStateException("getConnection() only works on SQL data sources");
        }
        Connection connection = SQLTransaction.getConnection(dsRequest.getRPCManager());
        if (connection == null) {
            connection = dsRequest.getRPCManager().startSQLTransaction(dsRequest);
        }
        if (connection == null) {
            connection = SQLTransaction.getConnection(dsRequest.getRPCManager());
        }
        if (connection == null) {
            connection = (Connection) dsRequest.getDataSource().getTransactionObject(dsRequest);
        }
        return connection;
    }

Am I doing something wrong here? Or is there an easier method for this?

[Isomorphic]

The docs link to this sample demonstrating how to do it.

It looks like you don't have a transaction started, and we don't know where you've stuck this code, but your attempt to start a transaction is probably invalid in terms of the lifecycle (see docs, and check the logs for warnings, which are probably telling you this is invalid).

[aderosso]

Thanks for the link, I got it half working now. Is there a way to get the SQL connection for a fetch DSRequest? I have a fetch that needs to execute a stored procedure call and modify the DSRequest criteria before it executes. I just don't want to tie up 2 database connections for the same request. Is this possible?

[Isomorphic]

It works the same, except that you may need to set a different Transaction Policy either system-wide or on that particular request, since the default would not start a transaction for a queue that consists only of fetches. If a transaction has not been started, a SQLConnection will not have been created yet.