SQL query error with ODBC connection in R using Informix driver
With functionality from RODBC package, I have successfully created ODBC but get error messages when trying to query the database. I am using 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 examine the structure of the returned object, I get the 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 examine 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 INFORMATION: When I return the 'res' object, I get 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\"'"
source to share
You can try using a function sqlQuery()
in RODBC to get the results. This is the function I use at work and has never had a problem:
sqlQuery(channel, "select top 10 * from exampleTABLE")
You should be able to put all your queries in a list and loop over them the way you did before:
dat <- lapply(queries, function(x) sqlQuery(channel, x))
where requests is your request list and pipe is your open ODBC connection. I guess I should also advise you to close the mentioned connection when you are done withodbcCloseAll()
source to share
The error message you indicate:
"[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM \"exampleTABLE\"'"
Informix only recognizes table names enclosed in double quotes if DELIMIDENT is installed in the environment, whether on the server or client (or both). It doesn't really matter what it is installed on; I use DELIMIDENT=1
when I need delimited IDs.
How did you create a table in an Informix database? If you did not create a table with DELIMIDENT set, the table name will not be case sensitive; you don't need quotes around the table name.
The fact that you are getting a -201 error means that you have gone through the connection process; this is a good start and makes things easier.
I'm not sure if you are on a Unix machine or a Windows machine - it often helps to point this out. On Windows, you may need to set the environment using SETNET32 (Informix program), or there may be a way to specify DELIMIDENT in the connection string. On Unix, you probably install it in your environment and the R software picks it up. However, problems can arise if you start R using a button or menu option in a GUI environment; there is a possibility that the profile will not be executed before the R program is executed.
source to share