Pooled Connections for Dynamic DB Switching

Database Support
#1

Hello Helical Insight,

I am using Helical Insight 3.1. I have implemented dynamic database switching. However, I notice that the connections created are not terminated for a long time. How can this be fixed?

Thank You,
Netta

#2

For using dynamic db switching groovy connection is used. Till version 3.1 groovy simple connection is only avalaible, groovy managed connection is avalaible from version 4.0 onwrds. This groovy managed connection when used can manage connection pooling, closing connections which are not being used etc.

We can implement pooled connections for the groovy datasource connections that are established during dynamic database switching with older version 3.1 using the below mentioned steps. You also have got the option to migrate to latest version (on or after version 4.0)

STEP 1: Take DBSwitching_Manged.zip (11.5 KB)
STEP 2: Extract the zip
STEP 3: Copy “cache” and “datasource” folders
STEP 4: Paste inside “helicalinsight” folder
…/hi/apache-tomcat-7/webapps/hi-ee/WEB-INF/classes/com/helicalinsight
STEP 5: Restart the application
STEP 6:Create any groovy datamap or reuse the existing groovy datamap (no need to change datamap in order to toggle connection for groovy)
STEP 7:Create a tag in for groovy datamap, provide “type” as “sql.jdbc.groovy” simply provide globalId in condition tag as json response no need to provide Driver,url,username,password finally connect the connection with datamap

Ex: Database is switching based on Logged in user in efw report: For hiadmin, use connection id 10 and for hiuser, use connection id 11.

<?xml version="1.0" encoding="UTF-8"?>
<EFWD>
    
    <DataMaps>
        <DataMap connection="1" id="1" type="sql">
            <Name>report1</Name>
            <Query><![CDATA[select * from emp]]></Query>
        </DataMap>
    </DataMaps>
<DataSources>
          <Connection id="1" type="sql.jdbc.groovy">
		  <Condition>
		  <![CDATA[
	      import groovy.sql.Sql;
	      import net.sf.json.JSONObject;
	      import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
	      public JSONObject evalCondition() {
		JSONObject responseJson = new JSONObject();
		String userName = GroovyUsersSession.getValue('${user}.name');
		userName  = userName .replaceAll("'","");
		if (
			userName.equals("hiadmin")
			){
			responseJson.put("globalId", 10);
		}
		
		if (
			userName.equals("hiuser")
			){
			responseJson.put("globalId", 11);
		
		}
		responseJson.put("type","global.jdbc");
	          return responseJson;
	      }
	    ]]>
		  </Condition>
</Connection>        
     </DataSources>
</EFWD>

Please Note: This will work with EFW reports and ReportsCE only. For Adhoc reports, it is not applicable.
Version 4.0 onwards, Helical Insight provides a connection type: Groovy Managed Datasource. You can use this for dynamic database switching. In that case, the above steps need not be followed