CODEWITHSUNDEEP
springsap-asedbunittemp-tables
egbokul
egbokul

Reputation: 3974

Accessing a temp table from DBUnit

I have a stored procedure in Sybase that returns some data in a temp table (let's call it #results). If I create this table from my own code and call the stored procedure and check the #results everything is fine (I use Python for this purpose). I can generate the expected result set for my test.

However when I try to create a DBUnit test case so that the code

  1. creates the temp table via the Spring JdbcTemplate

  2. calls this procedure and checks the returned data

I get a NoSuchTableException from DbUnit when checking the result set.

15:55:52.980 [main] DEBUG c.m.e.e.dbtest.helper.SQLTestHelper - *** expected table: #results
15:55:52.980 [main] DEBUG org.dbunit.dataset.FilteredDataSet - getTable(tableName=#results) - start
15:55:52.980 [main] DEBUG o.d.d.filter.SequenceTableFilter - accept(tableName=#results) - start
15:55:52.980 [main] DEBUG o.dbunit.dataset.OrderedTableNameMap - getTableName(tableName=#results) - start
15:55:52.980 [main] DEBUG o.dbunit.dataset.OrderedTableNameMap - getTableName(tableName=#results) - end - result=#RESULTS
15:55:52.980 [main] DEBUG org.dbunit.database.DatabaseDataSet - getTable(tableName=#results) - start
15:55:52.980 [main] DEBUG org.dbunit.database.DatabaseDataSet - initialize() - start
15:55:52.980 [main] DEBUG org.dbunit.database.DatabaseDataSet - Initializing the data set from the database...
15:55:52.980 [main] DEBUG o.d.d.DatabaseDataSourceConnection - getConnection() - start
15:55:52.980 [main] DEBUG m.b.t.TransactionResourceManager - Fetched TransactionResourceResolver [null]
15:55:52.980 [main] DEBUG msjava.pool.BasicObjectPool - Attempting to retrieve object from pool 'ConnectionPoolOf-dbunitDS'
15:55:52.980 [main] DEBUG msjava.pool.BasicObjectPool - Retrieved object 'TransactionInfoCachingConnection on DBCallSavingConnection on com.sybase.jdbc3.jdbc.SybConnection@170984c' from pool 'ConnectionPoolOf-dbunitDS'
15:55:53.402 [main] DEBUG org.dbunit.database.DatabaseDataSet - 
    database product name=Adaptive Server Enterprise
    database major version=12
    database minor version=5
    jdbc driver name=jConnect (TM) for JDBC (TM)
    jdbc driver version=jConnect (TM) for JDBC(TM)/6.05(Build 26564)/P/EBF16903/JDK14/Sun May 31  1:05:35 2009
    jdbc driver major version=6
    jdbc driver minor version=0

15:55:53.402 [main] DEBUG org.dbunit.database.DatabaseDataSet - metadata resultset=com.sybase.jdbc3.jdbc.SybResultSet@1b595f3
15:55:53.402 [main] DEBUG org.dbunit.database.DatabaseDataSet - getTableMetaData(tableName=#results) - start
15:55:53.402 [main] DEBUG org.dbunit.database.DatabaseDataSet - initialize() - start
15:55:53.402 [main] DEBUG o.dbunit.dataset.OrderedTableNameMap - getTableName(tableName=#results) - start
15:55:53.402 [main] DEBUG o.dbunit.dataset.OrderedTableNameMap - getTableName(tableName=#results) - end - result=#RESULTS
15:55:53.402 [main] ERROR org.dbunit.database.DatabaseDataSet - Table '#results' not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]

Checking the database in parallel with the test code execution (at a breakpoint) I can see that the temp table is indeed created in tempdb and filled with data.

Has anyone done such a thing successfully (eg. used a Sybase temp table from DbUnit)? If yes, how?

Upvotes: 1

Views: 1142

Answers (1)

Bala
Bala

Reputation: 4547

If you create temporary tables by using “#” as the first character of the table name:

Temporary tables:

  • Exist only for the duration of the user session or for the scope of the procedure that creates them

  • Cannot be shared between user connections

  • Are automatically dropped at the end of the session or procedure (or can be dropped manually)

You can create regular user tables in tempdb by specifying the database name in the command that creates the table:

Regular user tables in tempdb:

  • Can persist across sessions

  • Can be used by bulk copy operations

  • Can be shared by granting permissions on them

  • Must be explicitly dropped by the owner (otherwise, they are removed when Adaptive Server is restarted)

Upvotes: 1

Related Questions