0

I am trying to execute the sql script shown below via jdbcTemplate.execute(....);

if object_id('#dao_bulk_trade') is not null
  exec('drop table #dao_bulk_trade')

create table #dao_bulk_trade(thread varchar(128),contract_id int null,contract_version int null)

IF EXISTS (SELECT * FROM tempdb..sysindexes WHERE id=OBJECT_ID('#dao_bulk_trade') AND name='ncidx1')
  exec('drop index #dao_bulk_trade.ncidx1')

create unique nonclustered index ncidx1 on #dao_bulk_trade (thread, contract_id)

The first time I call the corresponding DAO endpoint via swagger it executes the SQL script and creates the table. If I however, I then make the same call again via swagger it blows up with the error below.

"Cannot create temporary table '#dao_bulk_trade'. Prefix name '#dao_bulk_trade' is already in use by another temporary table '#dao_bulk_trade'."

I have auto commit set to true on my jdbc connections in the Hikari pool. I am at a loss as to what the problem is.

juckky
  • 493
  • 3
  • 13

1 Answers1

0

The error is generated during the compilation phase when all 4 commands are processed as a single unit of work.

When the temp table exists the 2nd command (create table) fails to compile because the object already exists.

NOTE: While the if tests are considered during the compilation phase the contents of the exec(...) calls are skipped over and will be processed at runtime.

To get around this issue you need to insure the drop table and create table commands are processed in separate command batches; same goes for the drop index and create index commands.

You have a couple options:

  • wrap the create table and create index commands in exec(...) wrappers; this will delay the compilation of these commands until runtime at which point each exec(...) is processed as single standalone commands; in this case all 4 commands will be compiled at runtime as if submitted as 4 separate command batches

  • break this single, 4-command batch into separate command batches

One idea for the 2nd option (separate command batches):

-- 1st batch

if/drop table
go

-- 2nd batch

create table
create index
go

NOTES:

  • the go designates the end of a batch when submitted via the isql command line tool; I'm not familiar with jdbc so not sure what the equivalent would be of the isql/go
  • I'm guessing someone ran into this same issue with the drop table/index commands (eg, 1st time running the batch an error was generated complaining the object couldn't be dropped because it didn't exist); the resolution in that case was the inclusion of the exec(...) wrappers; same explanation applies here, and the same resolution applies ... break the compilation phase into chunks ... either literally or via runtime/exec(...) wrappers
markp-fuso
  • 28,790
  • 4
  • 16
  • 36