Hibernate with temporary table

Hibernate ORM
21

Just run the statements individually:

em.createNativeQuery(hql1).executeUpdate();
em.createNativeQuery(hql2).executeUpdate();
em.createNativeQuery(hql3).executeUpdate();
em.createNativeQuery("insert into #DS_ZAHI_TMP (id) select id from DS_ZAHI where city = 'zahi'").executeUpdate();
22

Hi Beikov,

This is not the problem, but you are closer to understand it, my problem is that when I use executeUpdate on hql1, the remaining statements are not able to see the temp table

We already tried all the possible syntax combinations, but the issue is solely with MS SQL Server, maybe if you try and reproduce the issue, you Currently we are using (Hibernate 6.1.5, mssql-jdbc 11.2.1.jre11, and Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64).)

23

Are you running this within a transaction? Because if you use auto-commit, the table might be dropped right after executing the statement.

24

Dear Beikov,

Yes I am within a transaction, and the problem is the same with or without auto-commit. Find below the connection string I’m using:

  url: jdbc:sqlserver://server-name;databaseName=dbName;encrypt=true;trustServerCertificate=true;
25

Seems like the JDBC driver doesn’t like it when you run create table statements with the Connection#prepareStatement API. You have to use the Statement.execute API. So with Hibernate, you have to use this:

session.doWork(
	connection -> {
		try (Statement s = connection.createStatement()) {
			s.execute( "CREATE TABLE #DS_ZAHI_TMP(id int not null, primary key (id))" );
		}
	}
);
26

Thank you Beikov!
Will try it and get back to you!

27

Dear Beikov,
It works thank you!!!

28

Thanks for your reply; however SQL server does not require semicolons..