SQL“哪里"子句与R JDBC HANA连接失败 [英] SQL "where" clause failing with R JDBC HANA connection

问题描述

除了通过R连接到公司的HANA数据库外，我什么都没有，但是终于有了突破，但是现在我的sql语句无法使用"where"语句对数据进行子集设置.

I've had nothing but trouble connecting to my company's HANA db through R but finally had a breakthrough, however now my sql statement is failing in subsetting data using a "where" statement.

以下代码返回一个包含9个变量的10个观测值的数据框

The following returns a data frame of 10 observations across 9 variables

# Fetch all results
rs <- dbSendQuery(jdbcConnection, 'SELECT TOP 10 
VISITTYPE,
ACCOUNT,
PLANNEDSTART,
PLANNEDEND,
EXECUTIONSTART,
EXECUTIONEND,
STATUS, 
SOURCE, 
ACCOUNT_NAME
                  FROM "_SYS_BIC"."cona-reporting.field-sales/Q_CA_R_SpringVisit"')

a <- dbFetch(rs)

但是，当我在其中放一个地方时，会收到一个错误消息.

However when I throw a where into it, I receive an error.

rs <- dbSendQuery(jdbcConnection, 'SELECT TOP 10 
VISITTYPE,
ACCOUNT,
PLANNEDSTART,
PLANNEDEND,
EXECUTIONSTART,
EXECUTIONEND,
STATUS, 
SOURCE, 
ACCOUNT_NAME
                  FROM "_SYS_BIC"."cona-reporting.field-sales/Q_CA_R_SpringVisit" WHERE VISITTYPE = ZR')

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT TOP 10 
VISITTYPE,
ACCOUNT,
PLANNEDSTART,
PLANNEDEND,
EXECUTIONSTART,
EXECUTIONEND,
STATUS, 
SOURCE, 
ACCOUNT_NAME
                  FROM "_SYS_BIC"."cona-reporting.field-sales/Q_CA_R_SpringVisit" WHERE VISITTYPE = ZR (SAP DBTech JDBC: [260] (at 222): invalid column name: ZR: line 11 col 101 (at pos 222))

这是什么意思? ZR不是列，而是列中的值.试图将ZR放在引号中没有其他作用.

What does this mean? ZR is not a column, it is a value within the column. Tried placing ZR in quotes to no other effect.

我的双引号和单引号语法基于我问过的另一个问题.

My double and single quote syntax is based on this other question I've asked.

>使用许多特殊方法将R连接到HANA数据库的问题字符

从未与RODBC一起使用过，所以尝试过JODBC.

Never got it working with RODBC so tried JODBC.

推荐答案

就像在嵌入式引号括起来的字符串中处理引号一样，SQL中用于标识符的双引号符号使情况更加复杂.但是，请考虑参数化(每当在诸如R之类的应用程序层中运行SQL时，行业最佳做法)，以避免需要使用引号标点或串联.与大多数JDBC API一样，RJDBC支持参数化.另请注意，dbGetQuery概括等于dbSendQuery + dbFetch:

Likely it is handling of quotes within an embedded quote-enclosed string, further complicated by the double quote symbols used in SQL for identifiers. However, consider parameterization (an industry best practice whenever running SQL in application layer such as R) to avoid the need of quote punctuation or concatenation. Like most JDBC APIs, RJDBC supports parameterization. Also note, dbGetQuery summarily equates to dbSendQuery + dbFetch:

sql <- 'SELECT TOP 10 VISITTYPE,
                      ACCOUNT,
                      PLANNEDSTART, 
                      PLANNEDEND,
                      EXECUTIONSTART,
                      EXECUTIONEND,
                      STATUS, 
                      SOURCE, 
                      ACCOUNT_NAME
         FROM "_SYS_BIC"."cona-reporting.field-sales/Q_CA_R_SpringVisit" 
         WHERE VISITTYPE = ?'

param <- 'ZR'

df <- dbGetQuery(jdbcConnection, sql, param)

这篇关于SQL“哪里"子句与R JDBC HANA连接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！