使用 Informix 驱动程序在 R 中进行 ODBC 连接的 SQL 查询错误
利用 RODBC 包的功能，我已成功创建 ODBC，但在尝试查询数据库时收到错误消息。我正在使用 INFORMIX 3.31 32 位驱动程序（版本 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 Driver][Informix]发生语法错误。” ...'
具体来说，我编写了一个表达式来循环访问数据库中的所有表，检索 10 行，并研究返回对象的结构。
for (i in 1:153){res <- sqlFetch(channel, sqlTables(channel, tableType="TABLE")$TABLE_NAME[i], max=10); str(res)}
每次迭代都会返回相同的错误消息。有什么想法从哪里开始吗？
附加信息：当我返回对象“res”时，我收到以下信息 -
> res
[1] "42000 -201 [Informix][Informix ODBC Driver][Informix]A syntax error has occurred."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM \"exampleTABLE\"'"
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")
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." ...'
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?
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\"'"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您引用的错误消息是：
如果在服务器或客户端（或两者）的环境中设置了环境 DELIMIDENT，则 Informix 仅识别用双引号括起来的表名。设置成什么并不重要；当我需要分隔标识符时，我使用
DELIMIDENT=1。您是如何在 Informix 数据库中创建表的？除非您创建的表设置了 DELIMIDENT，否则表名将不区分大小写；您不需要在表名称周围加引号。
事实上，您收到错误 -201 意味着您已完成连接过程；这是一个好的开始，并且简化了接下来的事情。
我不确定您使用的是 Unix 机器还是 Windows 机器 - 指出这一点通常会有所帮助。在 Windows 上，您可能必须使用 SETNET32（一个 Informix 程序）设置环境，或者可能有一种方法可以在连接字符串中指定 DELIMIDENT。在 Unix 上，您可能在您的环境中设置它，并且 R 软件会拾取它。但是，如果您在 GUI 环境中通过某种菜单按钮或选项启动 R，则可能会出现问题；很可能配置文件没有在 R 程序之前执行。
The error message you quote is:
Informix only recognizes table names enclosed in double quotes if the environment DELIMIDENT is set in the environment, either of the server or the client (or both). It doesn't much matter what it is set to; I use
DELIMIDENT=1when I want delimited identifiers.How did you create the table in the Informix database? Unless you created the table with DELIMIDENT set, the table name will not be case sensitive; you do not need the quotes around the table name.
The fact that you're getting error -201 means you've got through the connection process; that is a good start, and simplifies what follows.
I'm not sure whether you're on a Unix machine or a Windows machine - it often helps to indicate that. On Windows, you might have to set the environment with SETNET32 (an Informix program), or there may be a way to specify the DELIMIDENT in the connect string. On Unix, you probably set it in your environment and the R software picks it up. However, there might be problems if you launch R via some sort of menu button or option in a GUI environment; the chances are that the profile is not executed before the R program is.
您可以尝试使用 RODBC 中的
sqlQuery()函数来检索结果。这是我在工作中使用的函数，从来没有遇到过问题：您应该能够将所有查询放入一个列表中，并像以前一样迭代它们：
其中查询是您的查询列表，频道是您的查询打开 ODBC 连接。我想我还应该鼓励您在完成 odbcCloseAll() 后关闭所述连接
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:You should be able to put all of your queries into a list and iterate through them as you were before:
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()