Temp tables are accessible across JdbcTemplate calls in a Transaction, but not across NamedParameterJdbcTemplates

[springbootspelunker]

Driver version

12.4.2

SQL Server version

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 11

JAVA/JVM version

java 17.0.10 2024-01-16 LTS Java(TM) SE Runtime Environment (build 17.0.10+11-LTS-240) Java HotSpot(TM) 64-Bit Server VM (build 17.0.10+11-LTS-240, mixed mode, sharing)

Table schema

None

Problem description

`
@Transactional
void foo() {
    jdbcTemplate.update("CREATE TABLE #foo ( foo INT )");
    jdbcTemplate.query("SELECT * FROM #foo", (rs)->{});
    parameterizedTemplate.update("CREATE TABLE #bar ( bar INT )", new MapSqlParameterSource());
    parameterizedTemplate.query("SELECT * FROM #bar", new MapSqlParameterSource(), (rs, rowNum) -> rs.getInt(1));
}
`

Where jdbcTemplate is a plain vanilla JdbcTemplate and the other is a plain vanilla NamedParameterJdbcTemplate, with a plain vanilla transaction manager, and the data source is a BasicDataSource.

Expected behavior

Finding the #foo and #bar tables in the second statement in both cases

Actual behavior

JdbcTemplates properly are able to access local temp tables in a second statement, named parameter ones are not. Note that global temp tables like ##foo work fine in both, just not local ones that need to be in the same session.

Error message/stack trace

PreparedStatementCallback; uncategorized SQLException for SQL [SELECT * FROM #bar]; SQL state [S0002]; error code [208]; Invalid object name '#bar'.

Any other details that can be helpful

Add any other context about the problem here.

[lilgreenbird]

hi @springbootspelunker

It seems you are looking for Spring boot support? We provide support for the Microsoft JDBC Driver for SQL Server in this repo we are unable to comment on Spring boot issues as we are not familiar with their APIs it's best you contact their support so they can better assist you.

If you are experiencing problems with JDBC driver could you please provide repro code of how you are using the driver? Please provide the schema along with a standalone java app that shows what JDBC API you are using and the JDBC driver error you are getting. Please also make sure to include the connection string used to connect to the server.

[lilgreenbird]

hi @springbootspelunker

pls let us know if you have more info or any other driver related questions otherwise we will be closing this issue shortly

[Jeffery-Wasty]

Closing for inactivity.