[mssql] Issue with Temporary tables

[DavideD]

The creation of a temporary table doesn't throw any exception but when we try to do something else with the table we have the error Invalid object name....

I've tried this testacase (for MSSQLQueriesTest):

  @Test
  public void testCreateTempTable(TestContext ctx) {
    connection.query("drop table if exists Basic")
      .execute(ctx.asyncAssertSuccess(drop -> {
        connection.preparedQuery("create table Basic (id int, dessimal numeric(19,2), primary key (id))")
          .execute(ctx.asyncAssertSuccess(create -> {
            connection.preparedQuery("INSERT INTO Basic (id, dessimal) values (3, @p1)")
              .execute(Tuple.of(NullValue.BigDecimal), ctx.asyncAssertSuccess( insert -> {
                connection.preparedQuery( "create table #BasicTemp (id int not null)"  )
                  .execute(ctx.asyncAssertSuccess(createTemp -> {
                    connection.preparedQuery("insert into #BasicTemp select id as id from Basic")
                      .execute(ctx.asyncAssertSuccess(insertTemp -> {
                        connection.preparedQuery("drop table #BasicTemp if exists")
                          .execute(ctx.asyncAssertSuccess());
                      }));
                  }));
              }));
          }));
      }));
  }

Output:

io.vertx.mssqlclient.MSSQLException: {number=208, state=0, severity=16, message='Invalid object name '#BasicTemp'.', serverName='65fe4221cb1c', lineNumber=1, additional=[io.vertx.mssqlclient.MSSQLException: {number=8180, state=1, severity=16, message='Statement(s) could not be prepared.', serverName='65fe4221cb1c', lineNumber=1}]}

[tsegismont]

@DavideD actually this is not a bug. When you create the temporary table, you must use execute a query, not a prepared query:

connection.query( "create table #BasicTemp (id int not null)"  )

Otherwise, the temporary table is dropped at the end of the prepared statement scope.
You will get similar behavior with JDBC if you create the temp table with a PreparedStatement instead of a simple Statement.

[DavideD]

THanks! I will have another look