I created a SQL script to dynamically change a table's column names to its column comments.
Here is my code:
sql1 <- sql(
"
DECLARE
res RESULTSET;
query_string VARCHAR;
tbl VARCHAR;
tbl_sch VARCHAR;
BEGIN
tbl := 'V02_SAP_TVOS';
tbl_sch := 'DRTRV02';
query_string := CONCAT(
'SELECT ',
(
SELECT
LISTAGG(COLUMN_NAME || ' AS \"' || COMMENT || '\"', ', ')
WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS dynamic_query
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = :tbl
AND TABLE_SCHEMA = :tbl_sch
),
' FROM ', :tbl_sch, '.', :tbl
);
res := (EXECUTE IMMEDIATE :query_string);
RETURN TABLE(res);
END;
"
)
myconn <-
DBI::dbConnect(
odbc::odbc(),
Driver = "SnowflakeDSIIDriver",
Server = "xxxxx.snowflakecomputing.com",
Authenticator = "externalbrowser",
uid = "yyyyy",
database = "dev_main"
)
DBI::dbGetQuery(myconn, sql1)
I can run this query on app.snowflake.com no problem but in R, this result in an empty dataframe (0x0). I can query normal SELECT statements no problem in R.
What's going on here? Is there something I need to do to the Snowflake ODBC driver parameter to be able to use scripts?