CODEWITHSUNDEEP
javaspring
suraj bahl
suraj bahl

Reputation: 3296

Spring Transactional not using same JDBC connection

Can someone please tell me why do i sql get exception that "Invalid object #EMP_TEMP" even if i am running both queries under same transaction?

@Transactional
public Map<String, EventType> findEventsByDateRange(final Date startTimestamp, final Date endTimestamp) throws Exception {
    log.debug("Fetching Events Data");
    String EVENT_QUERY = "Select ID, Name, Status, JoinDate into #EMP_TEMP from EMPLOYEE where JoinDate >= ? and JoinDate < ?";

    this.jt.execute(EVENT_QUERY, new PreparedStatementCallback<Boolean>() {
        @Override
        public Boolean doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
            preparedStatement.setTimestamp(1, new java.sql.Timestamp(startTimestamp.getTime()));
            preparedStatement.setTimestamp(2, new java.sql.Timestamp(endTimestamp.getTime()));
            return preparedStatement.execute();
        }
    });
    //this.jt.execute(EVENT_QUERY);

    return this.jt.query("SELECT * from #EMP_TEMP "
            , DataExtractor.eventDataExtractor);
}

However if i change code as below then it doesn't complaint. but problem in this approach is that i cannot pass any parameters into first query:

@Transactional
public Map<String, EventType> findEventsByDateRange(final Date startTimestamp, final Date endTimestamp) throws Exception {
    log.debug("Fetching Events Data");
    String EVENT_QUERY = "Select ID, Name, Status, JoinDate into #EMP_TEMP from EMPLOYEE where JoinDate >= '2015-07-13 00:00:00.000' and JoinDate < '2015-07-14 00:00:00.000'";

    /*this.jt.execute(EVENT_QUERY, new PreparedStatementCallback<Boolean>() {
        @Override
        public Boolean doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
            preparedStatement.setTimestamp(1, new java.sql.Timestamp(startTimestamp.getTime()));
            preparedStatement.setTimestamp(2, new java.sql.Timestamp(endTimestamp.getTime()));
            return preparedStatement.execute();
        }
    });*/
    this.jt.execute(EVENT_QUERY);

    return this.jt.query("SELECT * from #EMP_TEMP "
            , DataExtractor.eventDataExtractor);
}

Upvotes: 2

Views: 579

Answers (1)

suraj bahl
suraj bahl

Reputation: 3296

Finally i found out that root cause of the problem is not spring but sql server.

In SQL Server 2005, SQL Server 2000, and SQL Server 7.0, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Since i was trying to create the temp table by jdbcTemplate method execute(String sql, PreparedStatementCallback action) which uses prepared statement therefore it was not working.

Instead of that when i created temp table using execute(String sql) it is working.

Upvotes: 1

Related Questions