CODEWITHSUNDEEP
jdbcsnowflake-cloud-data-platformmybatiswebsphere-libertysnowflake-schema
Debrup Majumdar
Debrup Majumdar

Reputation: 114

snowflake temp table connection using JDBC

I am trying to put temp values in the snowflake temporary table using Mybatis. Connection details configured at WebSphere Application server's server.xml file.

Ideally values should be retained at server request level.

But as long as application running , Temp table and its data remains available. With every request I am able to see previous insert data.

Same code is working fine for Oracle global temp table.

public class TempDao{

@Inject
SqlSession sqlSession;

public int saveValues(List<TemporaryValue> values) {
        logger.debug("Saving items on temporary table");
        try {
            final Map<String, Object> parameters = new HashMap<>();
            parameters.put("jobList", values);
            return sqlSession.insert("com.some.sql.insertJobs", parameters);
        } catch (Exception e) {
            ExceptionUtils.propagate(e);
        }
        return 0;
    }


}

JNDI Connection:

<dataSource id="SnowflakeDataSource" jndiName="jdbc/BM_SF" maxPoolSize="100" queryTimeout="300s" statementCacheSize="1000" type="javax.sql.DataSource">
        <properties URL="jdbc:snowflake://adpdc_cdl.us-east-1.privatelink.snowflakecomputing.com" databaseName="***" password="****" schema="**" user="**" warehouse="****strong text**" />
        <jdbcDriver javax.sql.DataSource="net.snowflake.client.jdbc.SnowflakeBasicDataSource" libraryRef="SharedLib"/>
    </dataSource>

Upvotes: 1

Views: 405

Answers (1)

njr
njr

Reputation: 3484

It is possible to turn off connection pooling in Liberty if it interferes with the ending of sessions in Snowflake. See the connectionManager element with agedTimeout of 0 (immediate timeout) that I inserted to your config snippet,

    <dataSource id="SnowflakeDataSource" jndiName="jdbc/BM_SF" maxPoolSize="100" queryTimeout="300s" statementCacheSize="1000" type="javax.sql.DataSource">
        <connectionManager agedTimeout="0"/>
        <properties URL="jdbc:snowflake://adpdc_cdl.us-east-1.privatelink.snowflakecomputing.com" databaseName="***" password="****" schema="**" user="**" warehouse="****strong text**" />
        <jdbcDriver javax.sql.DataSource="net.snowflake.client.jdbc.SnowflakeBasicDataSource" libraryRef="SharedLib"/>
    </dataSource>

Upvotes: 1

Related Questions