Spring Transactional not using same JDBC connection

Question

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);
}

Check out if both these methods is called in same transcaction. Does calling method have @Transactional annotation? — Sergii Lagutin
– Sergii Lagutin, Commented Jul 17, 2015 at 6:08
Why wouldn't you be able to pass parameters just use placeholders and use 'jt.execute(EVENT_QUERY, startTimestamp, endTimestamp)` (no need to convert) and it will execute with the parameters. — M. Deinum
– M. Deinum, Commented Jul 17, 2015 at 6:15

suraj bahl · Accepted Answer · 2015-07-21 13:55:58Z

1

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.

answered Jul 21, 2015 at 13:55

suraj bahl

3,3767 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Transactional not using same JDBC connection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related