R连接Db2 on Cloud执行条件查询仅返回列名无数据问题

操作背景 #

• 已完成R与Db2 on Cloud的连接：

library(RODBC)
dsn_driver <- "{IBM DB2 ODBC Driver}"
dsn_database <- "bludb"            # e.g. "bludb"
dsn_hostname <- "**" 
dsn_port <- "***"   # e.g. "32733" 
dsn_protocol <- "TCPIP"            # i.e. "TCPIP"
dsn_uid <- "**"        #
dsn_pwd <- "**"      #
dsn_security <- "ssl"
conn_path <- paste("DRIVER=",dsn_driver,
                  ";DATABASE=",dsn_database,
                  ";HOSTNAME=",dsn_hostname,
                  ";PORT=",dsn_port,
                  ";PROTOCOL=",dsn_protocol,
                  ";UID=",dsn_uid,
                  ";PWD=",dsn_pwd,
                  ";SECURITY=",dsn_security,        
                    sep="")
conn <- odbcDriverConnect(conn_path)
conn

• 成功创建Annual_Crop表：

myschema <- "**" #
tables <- c("Annual_Crop")
    
for (table in tables){  
  # Drop School table if it already exists
  out <- sqlTables(conn, tableType = "TABLE", schema = myschema, tableName =table)
  if (nrow(out)>0) {
    err <- sqlDrop (conn, paste(myschema,".",table,sep=""), errors=FALSE)  
    if (err==-1){
      cat("An error has occurred.\n")
      err.msg <- odbcGetErrMsg(conn)
      for (error in err.msg) {
        cat(error,"\n")
      }
    } else {
      cat ("Table: ",  myschema,".",table," was dropped\n")
    }
  } else {
      cat ("Table: ",  myschema,".",table," does not exist\n")
  }
}
df1 <- sqlQuery (conn, "CREATE TABLE Annual_Crop(
                CD_ID char (6) NOT NULL,
                YEAR CHAR (20),
                CROP_TYPE varchar (50),
                GEO varchar (50),
                SEEDED_AREA CHAR (50) ,
                HARVESTED_AREA CHAR (50),
                PRODUCTION CHAR (50),
                AVG_YIELD CHAR (50),
                PRIMARY KEY (CD_ID))",
                errors = FALSE)
if(df1 == -1){
    cat ("An error has occured.\n")
    msg <- odbcGetErrMsg(conn)
    print (msg)
    } else {
    cat ("Table was createdd successfuly.\n")
}

• 从CSV加载数据到表：

anual_cropdf <- read.csv("/resources/labs/MYDATA/data1.csv")
sqlSave(conn, anual_cropdf, 'Annual_Crop', append=TRUE, fast=FALSE, rownames=FALSE, colnames=FALSE, verbose=FALSE)

• sqlFetch可正常获取表中数据：

FARMDB <- sqlFetch(conn, "Annual_Crop")
tail(FARMDB)

• 异常情况：执行以下条件查询仅返回列名，无数据：

info <- paste('select * from Annual_Crop
           where Geo = 41600')
query <- sqlQuery(conn,info,believeNRows = FALSE)
query

原因分析 #

1. 数据类型不匹配：
   表中GEO字段定义为varchar(50)（字符串类型），但查询里直接把41600当作数值来匹配。Db2会严格校验数据类型，字符串和数值直接对比不会匹配到任何数据。

2. 字段名大小写（潜在隐患）：
   创建表时字段名是大写GEO，查询里用了小写Geo。虽然Db2默认不区分标识符大小写，但特殊配置下可能出现匹配问题，建议保持字段名大小写一致。

解决方案 #

修改查询语句，把41600用单引号包裹成字符串，同时统一字段名大小写：

info <- paste('select * from Annual_Crop
           where GEO = \'41600\'')
query <- sqlQuery(conn,info,believeNRows = FALSE)
query

额外排查点 #

如果修改后还是没有数据，可以做以下验证：

• 查看表中GEO列的实际值，确认是否存在41600（注意是否有空格、额外字符等格式差异）：

  unique(FARMDB$GEO)
  

• 用模糊查询验证是否能匹配到数据：

  info <- paste('select * from Annual_Crop
             where GEO like \'%41600%\'')
  query <- sqlQuery(conn,info,believeNRows = FALSE)
  query
  

内容的提问来源于stack exchange，提问作者HAJ