request for help with database to data frame conversion error

[tmoorman]

Conversion from a database list produced by DBI/dplyr to a data frame results in the following error:

Error in new_result(connection@ptr, statement, immediate) : nanodbc/nanodbc.cpp:1374: 00000: [SAP][ODBC Driver][SAP IQ]Syntax error near '(end of line)' on line 3

The database I'm using is SAP's Sybase IQ SQL Anywhere 17. Any help you can provide is appreciated. Relevant code is below. Please let me know if I can provide any additional helpful details.

require(odbc)
require(DBI)
require(dplyr)
require(dbplyr)

con <- dbConnect(odbc::odbc(),
                 driver="/opt/app/SybaseIQ/IQ-16_1/lib64/libdbodbc17.so",
                 host="host",
                 server="server",
                 database="database",
                 uid="uid",
                 pwd="pwd")

#Or, alternatively,
con <- dbConnect(odbc::odbc(),
                 dsn = "dsn",
                 UID = "uid",
                 PWD = "pwd")

#This works fine:
irmf_8300 <- tbl(con, "IRMF_F8300")

#This works fine:
few <- irmf_8300 %>%
  top_n(2)

#The following statements produce an error
few <- irmf_8300 %>%
  top_n(2) %>%
collect()

#or, alternatively,
few <- irmf_8300 %>%
  top_n(2) %>%
as.data.frame()

[cderv]

@tmoorman

# This works fine: 
few <- irmf_8300 %>% 
    top_n(2)

Can you check the syntax of the SQL query generated by using show_query(few) ?

Explain details of a tbl — explain

This way you'll be able to see the SQL generated, and see what syntax error there is. It is possible that translation for your SAP's Sybase IQ SQL Anywhere 17 is not correct. I am not sure it is a supported SQL variant yet

[tmoorman]

Thank you for the reply, Chris - and great comment. show_query() produced the following SQL code:

SELECT *
FROM `IRMF_F8300`
WHERE (top_n_rank(2.0, `UPDT_JLN_DT`))

I ran this SQL code in Interactive SQL Version 12.0.1, build 3473, a Sybase product. It produced the following error:

Could not execute statement.
Syntax error near '(end of line)' on line 3
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1

Thank you again!

[cderv]

This means that the SQL is not correct. I think there is a translation issue because you db does not support the default dbplyr translation.
You need to come up with the correct SQL to use. You can then open an issue in dbplyr to support this SQL variant.

[tmoorman]

From Sybase's website: "The dialect of SQL supported by SQL Anywhere is referred to as Watcom SQL. The original version of SQL Anywhere was called Watcom SQL when it was introduced in 1992. The term Watcom SQL is still used to identify the dialect of SQL supported by SQL Anywhere.
SQL Anywhere also supports a large subset of Transact-SQL, the dialect of SQL supported by Sybase Adaptive Server Enterprise." I will open an issue here. Thank you for helping me get to the bottom of this, Chris. It's been a long road to this point!