iBatis invalid columnName with temporary tables

0
votes

I am getting an invalid column name when I try to run an SQL map from my application. Basically I have two separate mapping files, which basically do the same thing, except create a column with a different name. Note that I have left the DROP TABLE out on purpose to illustrate this issue. I know that this is a bad practice but I am looking for why this happens. We have since corrected this behavior by explicitly dropping the tables in every SQL map.

Query1:

<select id="Query1"   parameterClass="query1Criteria">   

    IF Object_Id('tempdb..##severity') IS NOT NULL     
    BEGIN
      DROP TABLE ##severity
    END     

    CREATE TABLE ##severity( valueCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)
    INSERT INTO ##severity
        SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
        WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
        GROUP BY valueCode 

    SELECT valueCode FROM ##severity

</select>

Query 2:

<select id="Query2" parameterClass="query2Criteria">   

    IF Object_Id('tempdb..##severity') IS NOT NULL     
    BEGIN
      DROP TABLE ##severity
    END     

    CREATE TABLE ##severity( severityCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)
    INSERT INTO ##severity
        SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
        WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
        GROUP BY valueCode 

    SELECT severityCode FROM ##severity

</select>

From the application, I run Query1. Executes fine, and I see the temporary table created in SQL Management Studio. Then I execute Query2, and get the following stack trace:

org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/test/model/safety/reports/Query2Map.xml.  
--- The error occurred while applying a parameter map.  
--- Check the Query2.getResults-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: Invalid column name 'severityCode'.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:249)
    at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:296)
    at com.test.model.BaseSqlMapDao.executeQueryForList(BaseSqlMapDao.java:53)
    at com.test.model.safety.reports.Query2SqlMapDao.getOpenIssues(Query2SqlMapDao.java:124)
    at com.test.model.safety.reports.Query2SqlMapDao.getResults(Query2SqlMapDao.java:50)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

If I run the SQL of Query2 in SMS, it will execute correctly. Both of these queries, in fact, will execute correctly in SMS. I do not understand how iBatis works in detail, however it seems like iBatis sees the temporary table created in Query1, and validates Query2's syntax against that schema, in which case the invalid column name exception is thrown. However, this would mean that iBatis is keeping a reference to the temporary table, which contradicts SQL servers paradigm where temporary tables are scoped to the connection.

Can anyone explain what is happening here?

2
sql-serveribatis

2 Answers

0
votes

Maybe you can use table variables instead of temporary tables.

It will looks like:

DECLARE @severity as TABLE ( valueCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)

INSERT INTO @severity
    SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
    WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
    GROUP BY valueCode 

SELECT valueCode FROM @severity

Also you can drop DISTINCT keyword. GROUP BY valueCode ensures distinct valueCode values already.

0
votes

It is throwing error because of the mismatch in the column names of the table where you are inserting the data and the table from where you are fetching the data. 

 CREATE TABLE ##severity( severityCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)
INSERT INTO ##severity
    SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
    WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
    GROUP BY valueCode 

SELECT severityCode FROM ##severity

In the preceding code snippet, you are selecting valueCode in the inner query and inserting it into ##severity temp table, which doesn't have the valueCode column name.