HANA DB: SHORTTEXT casting not supported anymore?

[lschneiderbauer]

I updated the Hana driver recently, and I noticed that some previously working dbplyr queries are failing now.

In particular, a data type conversion using as.character seems to be failing with the SQL error message

Error in `collect()`:
! Failed to collect lazy table.
Caused by error in `new_result()`:
! nanodbc/nanodbc.cpp:1412: 42000: [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "SHORTTEXT": line 1 col 27 (at pos 27) 

An explicit example which returns that error:

some_lazy_table %>% select(some_number) %>% mutate(some_text = as.character(some_number))

which generates the SQL

SELECT "some_number", CAST("some_number" AS SHORTTEXT) AS "some_text"
FROM "MYSCHEMA"."some_lazy_table"

When looking up the specs for CAST, SHORTTEXT is also not mentioned as allowed data type, so I wonder if it was ever supported in the first place.

[lschneiderbauer]

Or is it more likely, that that's a bug of the driver?

[hadley]

Can you suggest what should it be cast to instead?

[lschneiderbauer]

TO_VARCHAR as well as CAST (col AS VARCHAR) works in my case.
That is my suggestion.

[hadley]

Would you mind provide a link to the docs of those functions?

[lschneiderbauer]

I don't mind (the link is already in my initial comment):
CAST
TO_VARCHAR

Or are you looking for something else?