interception/reuse of database connection

[chris1978]

Hi,


for the login procedure on the server side I have to execute a call against a plsql package in front of the execution of the actual sql statement.


For test purpose I created a simple DMI Class like the following:


public DSResponse validateUser(DSRequest dsRequest) throws Exception {

log.info("processing validateUser");
SQLDataSource ds = (SQLDataSource) dsRequest.getDataSource();
CallableStatement cs = ds.getConnection().prepareCall("call auth_package.login(?,?)");
cs.setString(2, "test");
cs.setString(1, "test123");
cs.execute();

return dsRequest.execute();
}


The problem seems to be the recreation of connections. The call to the auth package is bound to a database connection, but in “dsRequest.execute()” a different connection is used. Therefore the call to the auth package is without effect.

Is it possible to use the same database connection for the first call to the auth package and for the subsequent sql statement?


If it’s not possible, what would be your proposal?

Christian

[Isomorphic]

Note that you're calling unsupported internal APIs subject to change at any time - we do not use the "public" qualifier in Java to mark an API as supported, we use whether it appears in the JavaDoc or not.

Official support for participating in the default transaction, including error handling / rollback APIs and samples, is imminent. If you want a short-term hack that depends on APIs that will *probably* be the supported ones, call the static method SQLTransaction.startTransaction(), then look for the shared connection in a servletRequest attribute.

[chris1978]

Thank's a lot!

I haven't found the "shared connection in a servletRequest attribute".

I did it over the following way:

public DSResponse validateUser(DSRequest dsRequest) throws Exception {

log.info("processing validateUser");

SQLTransaction.startTransaction(dsRequest.rpc);
Connection c = SQLTransaction.getConnection(dsRequest.rpc);
CallableStatement cs = c.prepareCall("call login(?,?)");
cs.setString(2, "test");
cs.setString(1, "test123");
cs.execute();
DSResponse dsResponse = dsRequest.execute();
SQLTransaction.commitTransaction(dsRequest.rpc);

return dsResponse;
}


If this way is lesser correct than your proposal, then please let me know.

Christian

[Isomorphic]

That works too and is also fairly close to the final APIs (one change is that there will be a dsRequest.getRPCManager() getter)

[pmac]

If I follow the above code example to reuse the DB connection pool, should the basic structure be as follows? (to ensure releasing connection, etc)

public void doSomethingWithDB(DSRequest dbRequest) {
	Connection conn = null;
	try {
		SQLTransaction.startTransaction(dsRequest.rpc);
		conn = SQLTransaction.getConnection(dsRequest.rpc);

		// use the connection...select/insert/update/delete...

		// All done, commit changes to the DB
		SQLTransaction.commitTransaction(dsRequest.rpc);
	}
	catch(Exception e) {
		// Something wrong...rollback!
		try{SQLTransaction.rollbackTransaction(dsRequest.rpc);}
			catch(Exception e2) {}
	}
	finally {
		// Release pooled connection
		try{conn.close();} catch(Exception e) {} // redundant???
		try{SQLTransaction.endTransaction(dsRequest.rpc);}
			catch(Exception e) {}
	}
}

And does the endTransaction() actually release the Connection, so that the conn.close() is redundant?

Thanks!

[Isomorphic]

It depends what is happening here:

// use the connection...select/insert/update/delete...

The SQL transaction feature shares a single database connection (and a single transaction) across all the requests in a queue; we provide a means of registering your code with RPCManager, so that an onSuccess() or onFailure() method will be called as appropriate when the RPCManager has finished processing the entire queue, and you can commit or rollback in there. But generally you would not need to do that: the framework will automatically rollback the transaction if any single request failed, and commit it if everything was OK.

Please see the Transaction samples and latest Javadocs for RPCManager and DSRequest in the latest SmartClient Eval nightly - the "Transactional User Operations" sample will probably be of interest.

Oh, and the conn.close() is redundant, as you suspected.

[chris1978]

Version: Power-Edition 2.3 Build: 2010_08_27

Problem:
After inserts/updates the the gui elements did not receive the new values.

ServerSide-Code:

public DSResponse update(DSRequest dsRequest) throws Exception {

dsRequest.setJoinTransaction(Boolean.TRUE);
SQLTransaction.setAutoEndTransactions(false);
SQLTransaction.startTransaction(dsRequest.rpc);
c = SQLTransaction.getConnection(dsRequest.rpc);

//do something with connection
DSResponse dsResponse = dsResponse = dsRequest.execute();
//do some checks

SQLTransaction.commitTransaction(dsRequest.rpc);

return dsResponse;
}

Logs:

public com.isomorphic.datasource.DSResponse de.....frontend.server.dmi.EmployeeUnitDMI.update(com.isomorphic.datasource.DSRequest) throws java.lang.Exception

requiredArgs: [] optionalArgs: [com.isomorphic.servlet.RequestContext, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, javax.servlet.ServletContext, javax.servlet.http.HttpSession, com.isomorphic.rpc.RPCManager, com.isomorphic.datasource.DSRequest, com.isomorphic.datasource.DataSource, java.sql.Connection, com.isomorphic.log.Logger, java.util.Map]
com.isomorphic.datasource.DSResponse de.tmobile.sdom.frontend.server.dmi.EmployeeUnitDMI.update(com.isomorphic.datasource.DSRequest) throws java.lang.Exception

with arg types: com.isomorphic.datasource.DSRequest

public com.isomorphic.datasource.DSResponse de.....frontend.server.dmi.EmployeeUnitDMI.fetch(com.isomorphic.datasource.DSRequest) throws java.lang.Exception

requiredArgs: [] optionalArgs: [com.isomorphic.servlet.RequestContext, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, javax.servlet.ServletContext, javax.servlet.http.HttpSession, com.isomorphic.rpc.RPCManager, com.isomorphic.datasource.DSRequest, com.isomorphic.datasource.DataSource, java.sql.Connection, com.isomorphic.log.Logger, java.util.Map]			
=== 2010-09-21 17:10:08,619 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.employee_unit_update, builtinApplication.null] Returning pooled Connection
=== 2010-09-21 17:10:08,619 [l0-0] INFO  SQLDriver - [builtinApplication.employee_unit_update, builtinApplication.null] Executing SQL query on 'Oracle': SELECT ...
=== 2010-09-21 17:10:08,634 [l0-0] INFO  DSResponse - [builtinApplication.employee_unit_update, builtinApplication.null] DSResponse: List with 1 items

Conclusion:
Within the method "dsRequest.execute" a fetch is executed with a new DB connection. So the old values are still read by the fetch because the commit was after the fetch.

Can I enforce the use of the same connection throughout the complete request?

Thanks
Chris

[Isomorphic]

Could you clarify what you're trying to do? Are you trying to do a fetch that's part of the current transaction or after the current transaction? If you're trying to do a fetch after the end of the current transaction, why aren't you ending it manually first?

Also, bigger picture, you mentioned this is related to gui elements not receiving new values. This is normally automatic - have you looked at the FAQ on grids not updating? (the FAQ is a sticky thread on this forum).

[chris1978]

Sorry, basically I have a DynamicForm and a Listgrid with the same Datasource. I want to add/update a record in the Datasoure over the DynamicForm and expect that the Listgrid automatically shows the changed record.

With autoCommit=on it’s working, but sadly I have to do some more actions in a transaction. So I start a new transaction as described in the code snippet and the Listgrid is not updated automatically.

Server DMI for update/add:

dsRequest.setJoinTransaction(Boolean.TRUE);
SQLTransaction.setAutoEndTransactions(false);
SQLTransaction.startTransaction(dsRequest.rpc);
c = SQLTransaction.getConnection(dsRequest.rpc);
//do authentification/authorization in the database

//within the execute the normal add/update is executed
DSResponse dsResponse = dsResponse = dsRequest.execute();
SQLTransaction.commitTransaction(dsRequest.rpc);

My assumption is that the fetch operation of the ListGrid/Datasource (?) uses a new database connection so that the Listgrid reads still the old values.

During debugging I see that after update/insert automatically a fetch is executed (with dsRequest.rpc=null).

public com.isomorphic.datasource.DSResponse de.....frontend.server.dmi.EmployeeUnitDMI.fetch(com.isomorphic.datasource.DSRequest) throws java.lang.Exception
=== 2010-09-21 17:10:08,619 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.employee_unit_update, builtinApplication.null] Returning pooled Connection
=== 2010-09-21 17:10:08,619 [l0-0] INFO  SQLDriver - [builtinApplication.employee_unit_update, builtinApplication.null] Executing SQL query on 'Oracle': SELECT ...

With the following configuration it is working, but with autoCommit=false (see above) it is not. Do I misuse the API or is there a Defect?

dsRequest.setJoinTransaction(Boolean.FALES);
SQLTransaction.setAutoEndTransactions(TRUE);
SQLTransaction.startTransaction(dsRequest.rpc);
c = SQLTransaction.getConnection(dsRequest.rpc);

The DataSource Definition:

<?xml version="1.0" encoding="UTF-8"?>
<DataSource ID="employee_unit" serverType="sql"
	tableName="sdm$vi_web_employee_unit">
	<fields>
		<field name="eun_id" type="sequence" primaryKey="true" detail="true"
			sequenceName="SDM$SE_EMPLOYEE_UNIT" />
		<field name="eun_emp_id" type="integer" required="true" detail="true" />
		<field name="eun_uni_id" title="Team" type="integer" required="true" />
		<field name="eun_valid_from" title="Gültig von" type="date"
			required="true" />
		<field name="eun_valid_to" title="Gültig bis" type="date"
			required="true" />
		<field name="uni_name" type="text" customSQL="true" tableName="sdm$vi_web_unit" />
	</fields>
	<serverObject lookupStyle="new"
		className="de.....frontend.server.dmi.EmployeeUnitDMI" />	
	
	<operationBindings>
		<operationBinding operationType="fetch"
			customValueFields="uni_name" customCriteriaFields="uni_name">
			<tableClause>...</tableClause>			
		</operationBinding>		
	</operationBindings>
</DataSource>

Regards
Chris

[Isomorphic]

Again, could you clarify what you're trying to do? It's quite typical to do some extra queries in a DMI. Why does this involve any calls to transaction-related APIs? You realize that if the user is not allowed to perform the operation, throwing an exception is sufficient to abort the transaction?

[chris1978]

I just want to use the build in add/update opertions of a SmartGWT – SQLDataSource.
But in front of every operation I have to do an oracle package call with the same database connection as used in the SQLDataSource.

Use-Case:
1. Store a record through a SQLDataSource with an extra Oracle package call
2. All ListGrids with the same datasource should be updated automatically


The suggested way to receive the database connections was this:

SQLTransaction.startTransaction(dsRequest.rpc);
Connection c = SQLTransaction.getConnection(dsRequest.rpc);

Therefore I have to use the transactional API. But over this way and the described datasource the automatically update process of a ListGrid is not working anymore.
The ListGrid will not receive the new added/updated value.

In my test case I removed the special fetch-clause from my datasource xml defintion and the update of the ListGrids is suddenly working.

So it seems that the breaking point is the additional fetch in the datasource definition.

Do you have any ideas?

Regards
Christian

[Isomorphic]

Using the getConnection() API makes sense, we were trying to understand why you would use other APIs like disabling autoCommit.

But, it sounds like that wasn't the problem regardless. Can you show the DataSource before and after the change to the "fetch clause" that corrected the problem, as well as the data returned for the transaction, as shown in the RPC tab of te Developer Console, then we can explain what's going on and how to avoid the problem.

[Isomorphic]

Actually, before posting all that information, look at this FAQ item and see if that explanation and those steps help you solve the problem.

If they don't, please post the changes to your DataSource and what you're seeing when you enable the ResultSet logs (as explained in the FAQ).

[chris1978]

The FAQ has no explanation for my problem. In the secod test case you can see, that the response has still the old values. The second test case is only working if I activate autoCommit on the connection.

Working Test-Case:
Changed: eun_valid_to – from: 2013-10-22 - to: 2014-10-22

Datasource:

<?xml version="1.0" encoding="UTF-8"?>
<DataSource ID="employee_unit" serverType="sql"
	tableName="sdm$vi_web_employee_unit">
	<fields>
		<field name="eun_id" type="sequence" primaryKey="true" detail="true"
			sequenceName="SDM$SE_EMPLOYEE_UNIT" />
		<field name="eun_emp_id" type="integer" required="true" detail="true" />
		<field name="eun_uni_id" title="Team" type="integer" required="true" />
		<field name="eun_valid_from" title="Gültig von" type="date"
			required="true" />
		<field name="eun_valid_to" title="Gültig bis" type="date"
			required="true" />
		<field name="uni_name" type="text" customSQL="true" tableName="sdm$vi_web_unit" />
	</fields>
	<serverObject lookupStyle="new"
		className="de.tmobile.sdom.frontend.server.dmi.EmployeeUnitDMI"/> 
</DataSource>

DSRequest:

{
    "actionURL":"http://127.0.0.1:8080/frontend/sc/IDACall", 
    "showPrompt":true, 
    "prompt":"Speichere Formulardaten...", 
    "transport":"xmlHttpRequest", 
    "promptStyle":"dialog", 
    "bypassCache":true, 
    "data":{
        "criteria":{
            "eun_id":258562
        }, 
        "values":{
            "eun_emp_id":51034, 
            "eun_id":258562, 
            "eun_valid_to":"2014-10-22", 
            "eun_uni_id":4049, 
            "eun_valid_from":"2012-10-10"
        }, 
        "operationConfig":{
            "dataSource":"employee_unit", 
            "repo":null, 
            "operationType":"update"
        }, 
        "componentId":"isc_DynamicForm_7", 
        "appID":"builtinApplication", 
        "operation":"employee_unit_update", 
        "oldValues":{
            "eun_emp_id":51034, 
            "eun_id":258562, 
            "eun_valid_to":"2013-10-22", 
            "eun_uni_id":4049, 
            "eun_valid_from":"2012-10-10"
        }
    }
}

DSResponse:

[
    {
        isDSResponse:true, 
        invalidateCache:false, 
        status:0, 
        data:[
            {
                eun_emp_id:51034, 
                eun_id:258562, 
                eun_valid_to:new Date(1413928800000), 
                eun_uni_id:4049, 
                eun_valid_from:new Date(1349820000000)
            }
        ]
    }
]

Resultset-Log:

10:46:20.401:XRP2:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):dataSource data changed firing
10:46:20.401:XRP2:INFO:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):updating cache in place after operationType: update, allMatchingRowsCached true
10:46:20.402:XRP2:INFO:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):Updating cache: operationType 'update' submitted by 'isc_DynamicForm_7',1 rows update data:
[
{eun_emp_id: 51034,
eun_id: 258562,
eun_valid_to: Date(22.10.2014),
eun_uni_id: 4049,
eun_valid_from: Date(10.10.2012)}
]
10:46:20.402:XRP2:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):updated cache: 0 row(s) added, 1 row(s) updated, 0 row(s) removed.
10:46:20.402:XRP2:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):full length set to: 20
10:46:20.454:RDQ7:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):getRange(0, 19) satisfied from cache

Not-Working Test-Case
Changed: eun_valid_to – from: 2014-10-22 - to: 2013-10-22

Datasource:

<?xml version="1.0" encoding="UTF-8"?>
<DataSource ID="employee_unit" serverType="sql"
	tableName="sdm$vi_web_employee_unit">
	<fields>
		<field name="eun_id" type="sequence" primaryKey="true" detail="true"
			sequenceName="SDM$SE_EMPLOYEE_UNIT" />
		<field name="eun_emp_id" type="integer" required="true" detail="true" />
		<field name="eun_uni_id" title="Team" type="integer" required="true" />
		<field name="eun_valid_from" title="Gültig von" type="date"
			required="true" />
		<field name="eun_valid_to" title="Gültig bis" type="date"
			required="true" />
		<field name="uni_name" type="text" customSQL="true" tableName="sdm$vi_web_unit" />
	</fields>
	<serverObject lookupStyle="new"
		className="de.tmobile.sdom.frontend.server.dmi.EmployeeUnitDMI" />	
	
	<operationBindings>
		<operationBinding operationType="fetch"
			customValueFields="uni_name" customCriteriaFields="uni_name">
			<tableClause>sdm$vi_web_employee_unit inner join sdm$vi_web_unit on
				sdm$vi_web_employee_unit.eun_uni_id = sdm$vi_web_unit.uni_id
			</tableClause>			
		</operationBinding>		
	</operationBindings>
</DataSource>

DsRequest:

{
    "actionURL":"http://127.0.0.1:8080/frontend/sc/IDACall", 
    "showPrompt":true, 
    "prompt":"Speichere Formulardaten...", 
    "transport":"xmlHttpRequest", 
    "promptStyle":"dialog", 
    "bypassCache":true, 
    "data":{
        "criteria":{
            "eun_id":258562
        }, 
        "values":{
            "eun_emp_id":51034, 
            "eun_id":258562, 
            "eun_valid_to":"2013-10-22", 
            "eun_uni_id":4049, 
            "eun_valid_from":"2012-10-10", 
            "uni_name":"B_K112_Uni_Team_2"
        }, 
        "operationConfig":{
            "dataSource":"employee_unit", 
            "repo":null, 
            "operationType":"update"
        }, 
        "componentId":"isc_DynamicForm_7", 
        "appID":"builtinApplication", 
        "operation":"employee_unit_update", 
        "oldValues":{
            "eun_emp_id":51034, 
            "eun_id":258562, 
            "eun_valid_to":"2014-10-22", 
            "eun_uni_id":4049, 
            "eun_valid_from":"2012-10-10", 
            "uni_name":"B_K112_Uni_Team_2"
        }
    }
}

DSResponse:

[
    {
        isDSResponse:true, 
        invalidateCache:false, 
        status:0, 
        data:[
            {
                eun_emp_id:51034, 
                eun_id:258562, 
                eun_valid_to:new Date(1413928800000), 
                eun_uni_id:4049, 
                eun_valid_from:new Date(1349820000000), 
                uni_name:"B_K112_Uni_Team_2"
            }
        ]
    }
]

Resultset-Log:

10:40:39.664:XRP6:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):dataSource data changed firing
10:40:39.665:XRP6:INFO:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):updating cache in place after operationType: update, allMatchingRowsCached true
10:40:39.665:XRP6:INFO:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):Updating cache: operationType 'update' submitted by 'isc_DynamicForm_7',1 rows update data:
[
{eun_emp_id: 51034,
eun_id: 258562,
eun_valid_to: Date(22.10.2014),
eun_uni_id: 4049,
eun_valid_from: Date(10.10.2012),
uni_name: "B_K112_Uni_Team_2"}
]
10:40:39.666:XRP6:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):updated cache: 0 row(s) added, 1 row(s) updated, 0 row(s) removed.
10:40:39.666:XRP6:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):full length set to: 20
10:40:39.753:RDQ0:DEBUG:ResultSet:isc_ResultSet_4 (created by: isc_ListGrid_1):getRange(0, 19) satisfied from cache

Regards
Chris

[Isomorphic]

You mention the second case is fixed if you allow autoCommit on the transaction, which we assume means it works if you don't call setAutoEndTransactions(false). But we keep asking - why are you calling this API?

If you want your authentication stored procedure to operate within the current transaction, simply don't call this API.

If you want your authentication stored procedure to operate as a separate transaction, a simple way to do this is declare it as it's own operationBinding and set autoJoinTransaction="false" on that operationBinding. You can then use new DSRequest(...).execute() to execute it instead of retrieving the current SQLConnection to do it. This also makes it a separately callable operation that can be re-used.

If you need the transaction boundaries to be set up in some other way, please specify how exactly they need to be set up - we can't recommend an approach until we know what behavior you want.