`copy_inline` does not work for HANA DB.

[lschneiderbauer]

Hi, thanks a lot for implementing the extremely useful copy_inline method. Unfortunately, the generated SQL fails for HANA DB.

I cannot create a reprex since it requires a real ocdb connection. But here is the code:

# establish a DBI connection and store in variable `con`

copy_inline(con, tibble(a=c(1,1), b=c(2,3)))

Fehler in new_result(connection@ptr, statement, immediate) : nanodbc/nanodbc.cpp:1412: 42000: [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "WHERE": line 7 col 7 (at pos 140)

The SQL it generates:

copy_inline(con, tibble(a=c(1,1), b=c(2,3))) %>% show_query()

<SQL>
SELECT CAST("a" AS DOUBLE) AS "a", CAST("b" AS DOUBLE) AS "b"
FROM (
  (
    SELECT NULL AS "a", NULL AS "b"
    WHERE (0 = 1)
  )
  UNION ALL
  (VALUES (1.0, 2.0), (1.0, 3.0))
) "values_table"

[mgirlich]

Would be great help if you could provide an example with the correct syntax.

[lschneiderbauer]

It seems to me that HANA DB doesn't allow the VALUES clause in a SELECT statement.

An alternative possibility, not very elegant, but which executes without errors:

SELECT CAST("a" AS DOUBLE) AS "a", CAST("b" AS DOUBLE) AS "b"
FROM (
  SELECT 1 AS "a", 2 AS "b" FROM dummy
  UNION ALL
  SELECT 1 AS "a", 3 AS "b" FROM dummy
  -- ...
  -- attach as many UNION ALL as there are rows
)

[mgirlich]

Okay, so this is the same issue as in #949 just for a different database. It's amazing that such a useful feature which is in the SQL standard from 1992 is not fully supported...

[lschneiderbauer]

Is it standard though? The only references I find are in combination with the INSERT INTO statement (in www.w3schools.com, tbh I don't know if the websites covers the official SQL standard, but I'd assume so).

I would be interested in your references in case you know better. Thanks!

[ejneer]

@lschneiderbauer is the sql generated to fix this issue on redshift (https://github.com/tidyverse/dbplyr/pull/961#issuecomment-1206769045) also valid for hana db? It appears it is given your comment above, but I don't have access to a hana db to test for sure.

[mgirlich]

@lschneiderbauer Would be great if you could test out my implementation (install via install_github("tidyverse/dbplyr#982")) and give feedback.

[lschneiderbauer]

@mgirlich I just tested it, and it executes fine!

copy_inline(con, tibble(a=c(1,1,1,2,3,4), b=c(2,3,3,4,5,3))) %>% show_query()

<SQL>
SELECT CAST("a" AS DOUBLE) AS "a", CAST("b" AS DOUBLE) AS "b"
FROM (
  (
    SELECT NULL AS "a", NULL AS "b"
    FROM "DUMMY"
    WHERE (0 = 1)
  )
  UNION ALL
  (
    SELECT 1.0, 2.0 FROM DUMMY UNION ALL 
    SELECT 1.0, 3.0 FROM DUMMY UNION ALL 
    SELECT 1.0, 3.0 FROM DUMMY UNION ALL 
    SELECT 2.0, 4.0 FROM DUMMY UNION ALL 
    SELECT 3.0, 5.0 FROM DUMMY UNION ALL 
    SELECT 4.0, 3.0 FROM DUMMY
  )
) "values_table"

Output of

copy_inline(con, tibble(a=c(1,1,1,2,3,4), b=c(2,3,3,4,5,3)))

->

# Source:   SQL [6 x 2]
# Database: HDB ******
      a     b
  <dbl> <dbl>
1     1     2
2     1     3
3     1     3
4     2     4
5     3     5
6     4     3

Thanks!