string filter issue using odbc with SAP HANA ODBC Drivers

[washcycle]

The issue:

The Goal:

I want the sql generated to have this form or equivalent:

SELECT COLUMN1
FROM SCHEMA.TABLE
WHERE (LOCATE(COLUMN1, 'pattern') = 1) 
LIMIT 6;

I am trying to find strings that start with a specific pattern.

** The Issue**

If I use a dplyr filter(...) these solutions don't work...

stringr::str_locate, string::str_detect I tried a whole slew of things, these all create some malformed SQL.

str_detect gets translated into INSTR which is a MySQL specific function from what I can tell.

<SQL>
SELECT *
FROM SCHEMA.TABLE
WHERE (INSTR('pattern', "COLUMN") > 0)
LIMIT 6

This last one below will work, but isn't documented; I found it by trying things until something worked.

locate(COLUMN, 'pattern') this translates into the expected LOCATE

I am not even sure how this works because locate isn't a function from what I can tell.

library(DBI)                                                     
library(dplyr)                                                   
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)                                                  
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
library(readr)                                                   
                                                                 
con <- DBI::dbConnect(odbc::odbc(),                              
driver = "/Applications/sap/hdbclient/libodbcHDB.dylib",         
servernode = "host:port",                                        
uid = rstudioapi::askForPassword("UID"),                         
pwd = rstudioapi::askForPassword("PWD"),                         
CHAR_AS_UTF8 = TRUE # need this set because ASCII default is dumb
)                                                                
#> Error: RStudio not running
                                                                 
tbl_ref <- tbl(con, in_schema("schema","table"))                 
#> Error in tbl(con, in_schema("schema", "table")): object 'con' not found
                                                                 
tbl_ref %>%                                                      
filter(stringr::str_locate(COLUMN1, "pattern") == 1) %>%                      
head() %>% show_query()                                      
#> Error in eval(lhs, parent, parent): object 'tbl_ref' not found

Database is SAP HANA, there is a free version now if you wanted to replicate down to the database layer.
https://www.sap.com/developer/topics/sap-hana-express.html

Thanks,
Matt

[edgararuiz-zz]

Hi @washcycle ,this makes sense since there are no HANA specific translations yet in dbplyr. Also, your workaround makes sense, and it's probably the best way going forward until we have a customization for HANA. At this point, a new page in the site, which contains the workaround, is the best short-term solution, until we can get a customization for this connection.

[washcycle]

Gotcha. I think, I was going to start working on making the translation. Seems those translation files are pretty straight forward. If I get around to it I'll send ya'll a pull request. Cheers!

…

On Sat, Feb 10, 2018, 20:44 Edgar Ruiz ***@***.***> wrote: Hi @washcycle <https://github.com/washcycle> ,this makes sense since there are no HANA specific translations yet in dbplyr. Also, your workaround makes sense, and it's probably the best way going forward until we have a customization for HANA. At this point, a new page in the site, which contains the workaround, is the best short-term solution, until we can get a customization for this connection. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#3343 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABBp_nAGpu34TTH56-44T4gNv8Qbui8xks5tTlQhgaJpZM4R7t9s> .

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/