Cannot read temporary tables with IDataReader

Question

Is it possible to read temporary tables using IDataReader? I am connecting to Snowflake through ODBC.

//e.g.
CREATE TEMPORARY TABLE TEMPTABLE1 (ID INT);

sting query = "SELECT * FROM DB1.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'LOCAL TEMPORARY' OR TABLE_TYPE = 'GLOBAL TEMPORARY' OR TABLE_TYPE = 'TEMPORARY' OR TABLE_TYPE = 'VOLATILE'";
using (IDataReader reader = Connection.Query(query, null))
{
    while (reader.Read()) {...}
}

Reader is empty, did I make mistake somewhere, or there is some technical limitations on this one?

user_12480431 · Answer

You are creating the temporary table and THEN you are connecting with IDataReader afterwards. Thats why you don't see the table - its another session and temporary tables only exist within the session in which they were created.

https://docs.snowflake.com/en/user-guide/tables-temp-transient.html#temporary-tables

user_5070879 · Answer

Temporary tables are visible in context of the same session.

Optional Parameters:

TEMP[ORARY] | LOCAL TEMP[ORARY] | GLOBAL TEMP[ORARY] | VOLATILE
Specifies that the table is temporary. A temporary table persists only for the duration of the user session in which it was created and is not visible to other users.

Alternative apporach is the usage of SHOW TABLES(all in context of the same session):

CREATE TEMPORARY TABLE temp(i INT);

SHOW TABLES;

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "kind" = 'TEMPORARY';

Cannot read temporary tables with IDataReader

2 Answers