Include a hashtag in dbGetQuery()

Question

I'm trying to use RJDBC to connect to a SAP HANA database and query for a temporary table, which is stored with a #-prefix:

test <- dbGetQuery(jdbcConnection,
      "SELECT * FROM #CONTROL_TBL")

# Error in [...]: invalid table name:  Could not find table/view #CONTROL_TBL in schema USER

If I execute the SQL statement in HANA, it works perfectly fine. I'm also able to query for permanent tables. Therefore I assume that R doesn't pass over the hashtag. Inserting escapes like "SELECT * FROM \\#CONTROL_TBL" however didn't solve my problem.

score 1 · Answer 1 · answered Feb 24 '17 at 11:34

It's not possible to query for the data of a local or global temporary table from a different session, since they are by definition session-specific. In the case of a global temporary table one can query for the metadata of the table because they are shared across sessions.

Source: Tutorial for HANA temporary tables

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

You have to double-quote the table because it contains special characters, see SAP Help, identifiers for details.

test <- dbGetQuery(jdbcConnection, 'SELECT * FROM "#CONTROL_TBL"')

See also related discussion on stackoverflow.

edited May 23 '17 at 12:16

Community

1
1

answered Feb 24 '17 at 06:38

Christoph G

555
2
8

Thanks. Ive tried that, but it doesn't seem to work. I still get the same error message. Have you ever succeeded in reading out a temporary table. Maybe its not possible cause they only live in the session they were created in? – laterstat Feb 24 '17 at 09:17
Just checked, for local temp. tables double quoting is optional.The following code also works when leaving out the quotes: create local temporary table "#LTTEST" (key nvarchar(10), value nvarchar(100)); insert into "#LTTEST" (key, value) values ('1', 'some value'); select * from "#LTTEST"; The HANA SQL Reference says a simple identifier has to start with { | }, I would still quote it. You are right about session. You can only query a loc. temp. table in the same session, don't know if this is possible in R. – Christoph G Feb 24 '17 at 15:24

score 0 · Answer 3 · answered Feb 25 '17 at 03:38

Ok, local temporary tables are always only visible to the session in which they've been defined, while global temporary tables are visible just like normal tables, but the data is session private.

So, if you created the local temp. table (name starts with #) in a different session, then no wonder it cannot be found.

For your example, the question is: why do you need a temporary table in the first place? Instead of that, you could e.g. define a view or a table function to select data from.

Include a hashtag in dbGetQuery()

3 Answers3