How to Use SQL Server Temporary Tables with Cincom® ObjectStudio® ODBC Wrapper
In SQL Server, temporary tables are created in tempdb. They are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT and DELETE statements.
The scope of any local temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table using the same name (e.g., #aTable) and any queries running simultaneously would not affect one another while the Global temporary tables are visible to all sessions.
In ObjectStudio ODBC wrapper, we use a dedicated session to process an SQL Statement (see method ODBCDatabase>>privateExecSql: vars: template: onError: for example). However, in SQL Server, the temporary tables are only accessible within the session that created them. That is why you get an “Invalid object name” error if you try to query the temporary table in another session. It also explains why it works if you put multiple statements in one request.
Let’s start with creating a table and inserting some test data:
” Logon to the database server.”
db := ODBCDatabase logOnServer: #DSNToSQLServer user: #username password: #password.
“Drop the test table if it existed.”
db execSql: ‘DROP TABLE test_table’.
“Create a test table.”
res := db execSql: ‘create table test_table (cid int, cf float, cbigInt bigint, cvc varchar(100))’.
listInt := Array with: 1 with: 2 with: 3.
listFloat := Array with: 1.2 with: 2.34 with: 3.456.
listBigInt := Array with: -9223372036854775808 with: 9223372036854775807 with: 1234567890123456789.
listVarchar := Array with: ‘test1234567’ with: ‘test12345678’.
bindList := Array new: 4.
bindList at: 1 put: listInt.
bindList at: 2 put: listFloat.
bindList at: 3 put: listBigInt.
bindList at: 4 put: listVarchar.
“Insert the test data.”
sqlString := ‘INSERT INTO test_table VALUES (?, ?, ?, ?)’.
db execSql: sqlString vars: bindList.
“Create a local temporary table.”
db execSql: ‘SELECT cid, cf, cvc into #aTempTable FROM test_table’.
“The following code will result in the ‘Invalid object name’ error since it was processed by a different session.”
db execSql: ‘SELECT * FROM #aTempTable’.
“The following code will work fine since the two SQL statements are processed by the same session.”
db execSql: ‘SELECT cid, cf, cvc into #testTempTable FROM test_table;
SELECT * FROM #testTempTable’.
“The usage of a global temporary table will work fine since it is visible from all sessions.”
db execSql: ‘SELECT cid, cf, cvc into ##aGlobalTempTable FROM test_table’.
db execSql: ‘SELECT * FROM ##aGlobalTempTable ‘.
However, since the global temporary tables are accessible from all sessions, it may raise security concerns. Therefore, we have to find a way for the local temporary tables to work.
The key is to take a session out and use the same session to access the temporary tables created.
See the following example:
“Create a session and use it to generate a local temporary table.”
sess := db connection getSession.
sess executeDirect: ‘SELECT cid, cf, cvc into #testTempTable FROM test_table’.
ans := sess answer.
sess answer.
“Then, use the same session to access the temporary table.”
sess executeDirect: ‘SELECT * FROM #testTempTable’.
ans := sess answer.
ans upToEnd inspect.
sess answer.
One thing that needs to be pointed out is that when creating the temporary table, we need to avoid the “prepare” step and use the “executeDirect” method; otherwise, it will not work.
Therefore, the following code will not work:
sess1 := db connection getSession.
sess1 prepare: ‘SELECT cid, cf, cvc into #testTempTable1 FROM test_table’.
sess1 execute.
ans := sess1 answer.
sess1 answer.
sess1 executeDirect: ‘SELECT * FROM #testTempTable1’.
ans := sess1 answer.
ans upToEnd inspect.
sess1 answer.
However, this works:
sess := db connection getSession.
sess executeDirect: ‘SELECT cid, cf, cvc into #testTempTable FROM test_table’.
ans := sess answer.
sess answer.
sess prepare: ‘SELECT * FROM #testTempTable’.
sess execute.
ans := sess answer.
ans upToEnd inspect.
sess answer.