How to prepend SQL to generated SELECT clauses

[eschwegs]

My database guy is insisting I add "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" before the SELECT statement in my queries to avoid database blocking. I have searched but as of yet have not found a way to do this. Using the <SelectClause> of an <operationBinding> with ds.xml files appends the defined text. I need a way to prepend, or even entirely replace the select clause with the select variant described. For some queries I could accept code added to a server side SQLDataSource.executeFetch() solution if one exists. However a client side solution would be preferred as only a handful of queries have needed to involve server side logic.

Thanks
Eric

[tece321]

Is there an answer available for this question?

Thanks
Tony

[Isomorphic]

It's unclear if what you're adding is consider a statement on its own, or is just prefixing text for the select statement.

If it's just prefixing text, you should be able to define your selectClause as your prefix plus $defaultSelectClause.

If it's a distinct statement, see the Custom JDBC Operations sample (EE Showcase). However, bear in mind that connection pooling means the same connection will be provided to other operations, so if your statement changes the status of the connection, you may need to change it back when done.

[eschwegs]

Yes, I've tried what you are suggesting for prefixing text, but it did not work as expected. I'm presently using SmartClient Version: SNAPSHOT_v11.1d_2017-05-21/PowerEdition Deployment (built 2017-05-21)

I'm looking to generate statements such as :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT column1 FROM TableA

Using the $defaultSelectClause as you suggest, i.e. :

<operationBinding operationType="fetch" >
<selectClause>
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED $defaultSelectClause
</selectClause>
</operationBinding>

generates a syntax error because the SELECT text is out of order, It appears that SELECT is not considered part of the $defaultSelectClause. smartGWT generates:

SELECT SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED column1 FROM TableA

[Isomorphic]

This is as expected per docs. To pretend in this way you need to actually fully customize the clause using <customSQL>. However, at that point, since we have no way of knowing what you did, we turn off cache sync and SQL- based data paging (see sqlPagingStrategy).

So you would have to manually re-enable them, and depending on your database, pretending such text may make it impossible to use the "sqlLimit" paging approach.

So, first thing is probably to talk to your DBA to see if there is any other way to inject this text (a "hint" perhaps, or seperate statement, or similar) and check if sqlPagingStrategy:"sqlLimit" still works with the text injected via the approach above.