Click here to Skip to main content
15,944,733 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My project has some requirement to create temp table using IBATIS. I need to perform some operation on temp table like: -
1. Create table
2. Insert some data
3. Perform some operation on that data and update same.
4. Get that data from temp table.

I am able to perform all the operations while writing all queries in single statement, my sql mapper file look like this see below: -

XML
<sqlMap namespace="xxx" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

	<select id="GetTempTableData" resultClass="xxx" parameterClass="System.String">
		Create table ##TestTable(MatterID varchar(20), ShortDesc varchar(100))
		Insert into ##TestTable values('1001-101','ABC')
		Insert into ##TestTable values('1001-102','XYZ')
		Select * from ##TestTable
	</select>
	
</sqlMap>

But i have a requirement to split all queries into seprate sections, see below: -
XML
<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="xxx" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

	<select id="GetTempTableData" resultClass="xxx" parameterClass="System.String">
			Create table ##TestTable(MatterID varchar(20), ShortDesc varchar(100))
	</select>
	<insert id="InsertIntoTempTable" resultClass="xxx" parameterClass="System.String">
			Insert into ##TestTable values('1001-101','ABC')
			Insert into ##TestTable values('1001-102','XYZ')
	</insert>
	<select id="SelectFromTempTable" resultClass="xxx" parameterClass="System.String">
			Select * from ##TestTable
	</select>
	
</sqlMap>

But in 2nd approach i am getting exception "Invalid object name #TestTable".
Can someone please suggest me, what should i do in that case?
Quick solution will highly appreciated.

Thanks in advance.

What I have tried:

I am able to perform all the operations while writing all queries in single statement, my sql mapper file look like this see below: -

<pre lang="xml"><sqlMap namespace="xxx" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

	<select id="GetTempTableData" resultClass="xxx" parameterClass="System.String">
		Create table ##TestTable(MatterID varchar(20), ShortDesc varchar(100))
		Insert into ##TestTable values('1001-101','ABC')
		Insert into ##TestTable values('1001-102','XYZ')
		Select * from ##TestTable
	</select>
	
</sqlMap>
Posted
Comments
Maciej Los 19-Jun-18 2:46am    
I'd suggest to ask such of question in IBATIS forum. BTW: Ibatis mapper is "retired apache software". See: iBATIS Home[^] An author recommeds to change it to mybatis ;)
Good luck!
Member 14790054 2-Apr-20 3:26am    
Can i add some conditions before those inserts?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900