Question
li scott · Apr 26, 2020

How to use setQueryTimeout in Ensemble JDBC

Everyone

There is a setQueryTimeout method in the% Net.Remote.Java.JDBCGateway class of IRIS, how to use Ensemble2016 to achieve this function?

0
0 426
Discussion (7)0
Log in or sign up to continue

Call it?

First argument is the statement index and the second is how long to wait in seconds.

I would recommend setting this parameter as a part of JDBC connection string if applicable - it is available in some DBMS implementations.

In IRIS as you said, but there is no such method in Ensemble2016, I use Ensemble2016

First of all, I'd really like to recommend you to upgrade to InterSystems IRIS.

You can still set query timeout as a part of JDBC connection string if it is available in external DBMS implementations.

Due to objective reasons, I cannot upgrade to IRIS.Can you give me an example of setting query timeout through JDBC in Ensemble2016.

thank you very much!

Check StatementAttrs setting. It's a set of SQL Statement Attribute Options of the form attr:val,attr:val,...
Do this in your Business Operation's OnInit method:

Set ..Adapter.StatementAttrs="QueryTimeout:12"

Thank you very much, but I am not using production.There is a setQueryTimeout method in the% Net.Remote.Java.JDBCGateway class of IRIS, but this method does not exist in Ensemble2016, what should I do?

The code in IRIS is as follows:
ClassMethod jdbc() As %String
{
    
    #dim %JDBCGateway as %Net.Remote.Java.JDBCGateway
    s SQLConName="INC0001"
    s gc=$$GetJConnection^%apiGTW(SQLConName)
    i gc="" QUIT "-1^"_$$$ERROR($$$GTWCConnectionError)
    set myquery= "select top 1 * from SQLUser.OE_OrdTeeth"
    "
    q:myquery="" "-1^sql can't empty"
    
    /// prepare sql
    s command=$ZCONVERT($p(myquery," ",1),"U") 
    If (command["CALL") {
        s sqlhstmt=%JDBCGateway.prepareCall(gc,myquery)
    } else {
        s sqlhstmt=%JDBCGateway.prepareStatement(gc,myquery)
      }
      if (sqlhstmt < 0) {
        q "-1^"_%JDBCGateway.getErrorText()
      }
       s sqlerr=%JDBCGateway.setQueryTimeout(sqlhstmt,5)
      
    if sqlerr<0{
        q "-1^"_%JDBCGateway.getErrorText()
    }
   
    s sqlerr=%JDBCGateway.execQuery(sqlhstmt)
    if sqlerr<0{
        q "-1^"_%JDBCGateway.getErrorText()
    }
  
    /// dump result 
    s colcount=%JDBCGateway.getColumnCount(sqlhstmt)
    s hasdata=%JDBCGateway.next(sqlhstmt)
    while hasdata = 1 {
        
        s tpseudoid=%JDBCGateway.getString(sqlhstmt,1)
    
        
        
        s hasdata=%JDBCGateway.next(sqlhstmt)
    } //matches while hasdata
    
    d %JDBCGateway.removeResultSet(gc)
    
    q
}

I don't know if it's officially supported.

You can ask in WRC for support/help.

Back to my previous idea, you can check if connection string for external DBMS supports Query Timeout and edit URL parameter in INC0001 gateway connection accordingly: