使用Informix驱动程序在R中进行ODBC连接时出现SQL查询错误 [英] SQL query error with ODBC connection in R using Informix driver

问题描述

借助RODBC软件包的功能，我已经成功创建了ODBC，但是在尝试查询数据库时收到错误消息.我正在使用INFORMIX 3.31 32位驱动程序(版本3.31.00.10287).

With functionality from the RODBC package, I have successfully created an ODBC but receive error messages when I try to query the database. I am using the INFORMIX 3.31 32 bit driver (version 3.31.00.10287).

channel <- odbcConnect("exampleDSN")
unclass(channel)
[1] 3
attr(,"connection.string")
[1] "DSN=exampleDSN;UID=user;PWD=****;DB=exampleDB;HOST=exampleHOST;SRVR=exampleSRVR;SERV=exampleSERV;PRO=onsoctcp ... (more parameters)"
attr(,"handle_ptr")
<pointer: 0x0264c098>
attr(,"case")
[1] "nochange"
attr(,"id")
[1] 4182
attr(,"believeNRows")
[1] TRUE
attr(,"colQuote")
[1] "\""
attr(,"tabQuote")
[1] "\""
attr(,"interpretDot")
[1] TRUE
attr(,"encoding")
[1] ""
attr(,"rows_at_time")
[1] 100
attr(,"isMySQL")
[1] FALSE
attr(,"call")
odbcDriverConnect(connection = "DSN=exampleDSN")

当我尝试查询和调查返回的对象的结构时，收到一条错误消息'chr [1:2]"42000 -201 [Informix] [Informix ODBC驱动程序] [Informix]发生语法错误. " ...'

When I try to query and investigate the structure of the returned object, I receive an error message 'chr [1:2] "42000 -201 [Informix][Informix ODBC Driver][Informix]A syntax error has occurred." ...'

具体地说，我写了一个表达式来遍历数据库中的所有表，检索10行，并研究返回对象的结构.

Specifically, I wrote an expression to loop through all tables in the database, retrieve 10 rows, and investigate the structure of the returned object.

for (i in 1:153){res <- sqlFetch(channel, sqlTables(channel, tableType="TABLE")$TABLE_NAME[i], max=10); str(res)}

每次迭代都返回相同的错误消息.任何想法从哪里开始?

Each iteration returns the same error message. Any ideas where to start?

附加信息:当我返回对象'res'时，我收到以下消息-

ADDITIONAL INFO: When I return the object 'res', I receive the following -

> res
[1] "42000 -201 [Informix][Informix ODBC Driver][Informix]A syntax error has occurred."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM \"exampleTABLE\"'"

推荐答案

您可以尝试在RODBC中使用sqlQuery()函数来检索结果.这是我在工作中使用的功能，从未遇到过问题:

You can try using the sqlQuery() function in RODBC to retrieve your results. This is the function I use at work and have never had a problem with it:

sqlQuery(channel, "select top 10 * from exampleTABLE")

您应该能够将所有查询放入列表中，并像以前一样遍历它们:

You should be able to put all of your queries into a list and iterate through them as you were before:

dat <- lapply(queries, function(x) sqlQuery(channel, x))

其中querys是查询列表，而channel是打开的ODBC连接.我想我也应该鼓励您在完成odbcCloseAll()

where queries is your list of queries and channel is your open ODBC connection. I guess I should also encourage you to close said connection when your done with odbcCloseAll()

这篇关于使用Informix驱动程序在R中进行ODBC连接时出现SQL查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！