[R] odbcConnect won't open a DSN that opens using isql (Linux: Ubuntu)

[Gregg Powell]

Hi Chris,

Your three-machine test tells us a lot. You get the same failure on two Debian releases and on Kubuntu, with two different .mdb files (including your old small one). Meanwhile isql and the mdb-tools command line (via Hmisc::mdb.get) both read the data fine. That rules out the files, your RODBC install, and the unixODBC version. What’s left is how RODBC and the MDBTools ODBC driver work together. I think (possibly- but probably) two separate problems are being mistaken for one.

1. The garbage warning (cosmetic)

Ivan’s reading of the C code is right. As far as I can tell from the driver source, MDBTools’ SQLDriverConnect() connects using the Database= setting and returns SQL_SUCCESS, but it never writes the output connection string (szConnStrOut / pcbConnStrOut). RODBC doesn’t initialise buf1 before the call, so R receives whatever happened to be on the stack.

On your Debian boxes those leftover bytes probably start with a NUL or happen to be valid text, so grepl() stays quiet. On Kubuntu, with GCC 15 and a different stack layout, they aren’t. It’s the same bug on all three machines, but it’s only visible on one. It doesn’t affect the connection itself, and it isn’t why you get no data.

2. No rows returned (the real problem)

odbcConnect() defaults to believeNRows = TRUE and rows_at_time = 100. As far as I can tell, the MDBTools driver doesn’t return a usable row count for a SELECT, and it doesn’t support fetching many rows at once. With those defaults, RODBC can conclude there are zero rows and return an empty result. Give this a shot:

[R]
> library(RODBC)
> connEOS <- odbcConnect("EOS", believeNRows = FALSE, rows_at_time = 1)
> sqlTables(connEOS)
> sqlQuery(connEOS, "SELECT * FROM YourTableName", errors = TRUE)
> odbcGetErrMsg(connEOS)
> close(connEOS)

Use a real table name from mdb-tables yourfile.mdb in a shell. Depending on what you see:

- sqlQuery() returns data but sqlTables() is empty: that’s a separate gap in the driver’s table listing. Just name the tables directly.

- sqlQuery() works but sqlFetch() doesn’t: the likely cause is how RODBC quotes table names, which the MDBTools SQL parser handles poorly. Stick with sqlQuery().

[ODBC]
> Trace = Yes
> TraceFile = /tmp/odbc_trace.log

Rerun your R code, then look in /tmp/odbc_trace.log. You should see whether SQLDriverConnect leaves the output length unset and what SQLRowCount returns. Turn tracing off afterwards, because the log grows quickly. That log is also exactly what a maintainer would want attached to a bug report.

4. Where to report it

- RODBC: it has no public bug tracker. Running maintainer("RODBC") gives you the address. The fix is small: set buf1[0] = '\0' before the call, or check tmp1 before using buf1. Either way, RODBC shouldn’t trust a buffer the driver didn’t fill.

- MDBTools: report it at github.com/mdbtools/mdbtools/issues. Cover SQLDriverConnect not filling the output connection string and SQLRowCount on SELECT statements, and attach the trace.

5. If you’d rather avoid RODBC entirely

The odbc/DBI packages often cope better with drivers that have gaps like these:

[R]
> con <- DBI::dbConnect(odbc::odbc(), dsn = "EOS")
> DBI::dbListTables(con)
> DBI::dbReadTable(con, "YourTableName")
> DBI::dbDisconnect(con)

Hmisc::mdb.get() also remains a perfectly good route, since it already works for you.

If believeNRows = FALSE with rows_at_time = 1 fixes it, I’d still send both reports. The warning is a real, if minor, bug in RODBC, and the next person to use MDBTools will hit the same empty results.

All the Best,
Gregg




On Sunday, November 8th, 2026 at 7:49 AM, Chris Evans via R-help <r-help using r-project.org> wrote:

> I have finally found time to get back to this and I am starting to
> wonder if it is a bug in RODBC.  What I have done:
> 

> * Found that RODBC was installed in /usr/local/lib/R/site-library/. 

>   Not sure that helps but I used `remove.packages("RODBC")` to remove it
> in R and then moved the directory to a safe place in case it's ever of
> any diagnostic use.  Then I installed it again:
> 

> Installing package into ‘/home/chris/R/x86_64-pc-linux-gnu-library/4.6’
> (as ‘lib’ is unspecified)
> trying URL 'https://cloud.r-project.org/src/contrib/RODBC_1.3-26.3.tar.gz'
> Content type 'application/x-gzip' length 942265 bytes (920 KB)
> ==================================================
> downloaded 920 KB
> 

> * installing *source* package ‘RODBC’ ...
> ** this is package ‘RODBC’ version ‘1.3-26.3’
> ** package ‘RODBC’ successfully unpacked and MD5 sums checked
> ** using staged installation
> checking for gcc... x86_64-linux-gnu-gcc
> checking whether the C compiler works... yes
> checking for C compiler default output file name... a.out
> checking for suffix of executables...
> checking whether we are cross compiling... no
> checking for suffix of object files... o
> checking whether the compiler supports GNU C... yes
> checking whether x86_64-linux-gnu-gcc accepts -g... yes
> checking for x86_64-linux-gnu-gcc option to enable C11 features... none needed
> checking for stdio.h... yes
> checking for stdlib.h... yes
> checking for string.h... yes
> checking for inttypes.h... yes
> checking for stdint.h... yes
> checking for strings.h... yes
> checking for sys/stat.h... yes
> checking for sys/types.h... yes
> checking for unistd.h... yes
> checking for sql.h... yes
> checking for sqlext.h... yes
> checking for library containing SQLTables... -lodbc
> checking for SQLLEN... yes
> checking for SQLULEN... yes
> checking size of long... 8
> configure: creating ./config.status
> config.status: creating src/Makevars
> config.status: creating src/config.h
> ** libs
> using C compiler: ‘x86_64-linux-gnu-gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0’
> x86_64-linux-gnu-gcc -I"/usr/share/R/include" -DNDEBUG -I.      -fpic  -g -O2 -fno-omit-frame-pointer -mno-omit-leaf-frame-pointer -ffile-prefix-map=/build/r-base-yH7sYw/r-base-4.6.2=. -fstack-protector-strong -fstack-clash-protection -Wformat -Werror=format-security -fcf-protection -fdebug-prefix-map=/build/r-base-yH7sYw/r-base-4.6.2=/usr/src/r-base-4.6.2-1.2604.0 -Wdate-time -D_FORTIFY_SOURCE=3  -c RODBC.c -o RODBC.o
> x86_64-linux-gnu-gcc -shared -L/usr/lib/R/lib -Wl,-Bsymbolic-functions -Wl,--package-metadata=%7B%22type%22:%22deb%22%2C%22os%22:%22ubuntu%22%2C%22name%22:%22r-base%22%2C%22version%22:%224.6.2-1.2604.0%22%2C%22architecture%22:%22amd64%22%7D -flto=auto -ffat-lto-objects -Wl,-z,relro -o RODBC.so RODBC.o -lodbc -L/usr/lib/R/lib -lR
> installing to /home/chris/R/x86_64-pc-linux-gnu-library/4.6/00LOCK-RODBC/00new/RODBC/libs
> ** R
> ** inst
> ** byte-compile and prepare package for lazy loading
> ** help
> *** installing help indices
> ** building package indices
> ** installing vignettes
> ** testing if installed package can be loaded from temporary location
> ** checking absolute paths in shared objects and dynamic libraries
> ** testing if installed package can be loaded from final location
> ** testing if installed package keeps a record of temporary installation path
> * DONE (RODBC)
> 

> That looks fine to me. So that gives me RODBC version 1.3.26.3 running
> in 4.6.2 (2026-10-29) -- "Diversified Portfolio" on "26.04.1 LTS
> (Resolute Raccoon)" (technically Kubuntu) with linux packages unixodb
> version 2.3.14-1, mdbtools version 1.0.1-0.1build1 and odbc-mdtools
> version 1.0.1-0.1build1.  However, rerunning things gets exactly the
> same failure to read anything from the mdb files and does continue to
> give the bizarre warning:
> 

> > connEOS <- odbcConnect("EOS")
> Warning messages:
> 1: In grepl("PWD=", cs) :
>    unable to translate '(}<e7>q<93>\' to a wide string
> 2: In grepl("PWD=", cs) : input string 1 is invalid
> 

> * then I uploaded both mdb files to "my" web server which runs Debian
> 6.1.176-1 (2026-07-02) x86_64 GNU/Linux and "R version 4.5.3
> (2026-03-11)" and RODBC version 1.3.26.3, linux packages: unixodbc
> 2.3.11-2+deb12u1 mdbtools 1.0.0+dfsg-1.1
>   and odbc-mdbtools version 1.0.0+dfsg-1.1.
> Here I get
>   no warnings on the odbcConnect() but again, nothing is read in from
> either mdb file
> 

> * then I did the same on "my" shiny server (currently not serving shiny
> as per my other recent post here!). That runs Debian 6.1.176-1
> (2026-07-02) x86_64 GNU/Linux, "R version 4.2.2 Patched (2022-11-10
> r83330)" and again RODBC is version 1.3.26.3 and the linux packages are:
> unixodbc 2.3.11-2+deb12u1, mdbtools 1.0.0+dfsg-1.1 and odbc-mdbtools
> version 1.0.0+dfsg-1.1.
> Again no warning on odbcConnect() but equally nothing is read in from
> either mdb file.
> 

> Summary:
> 

> 1) I get the bizarre warnings only on my kubuntu 26.04 laptop
> 

> 2) I can't read either of these mdb files into R using the RODBC package
> on three significantly different machines, two different Debian versions
> and my Kubuntu laptop.  All three have RODBC version 1.3.26.3 but they
> have a variety of versions of the Linux packages.
> 

> Sadly, I can't share either mdb file but if someone has one that reads
> into R fine for them using RODBC, I would be happy to try that file in
> each of my three machines, or do I open this as a possible bug report
> for the RODBC: I see no other open bugs against it (assuming that I am
> searching correctly).
> 

> TIA,
> 

> 

> Chris
> 

> 

> On 02/11/2026 14:16, Chris Evans via R-help wrote:
> >
> > On 01/11/2026 20:10, Ivan Krylov wrote:
> >> On Mon, 26 Oct 2026 18:06:05 +0000
> >> Chris Evans via R-help<r-help using r-project.org> wrote:
> >>
> >>>> connEOS <- odbcConnect("EOS")
> >>> Warning messages:
> >>> 1: In grepl("PWD=", cs) :
> >>>     unable to translate '<c0><d3>6<ed><b1>{' to a wide string
> >>> 2: In grepl("PWD=", cs) : input string 1 is invalid
> >> This is quite weird.
> >>
> >> The 'cs' string is the connection string returned by the C code inside
> >> RODBC:
> >>
> >>     stat <- .Call(C_RODBCDriverConnect, as.character(connection), id,
> >>                   as.integer(believeNRows),
> >> as.logical(readOnlyOptimize))
> >>     # ...
> >>     cs <- attr(stat, "connection.string")
> >>     if(grepl("PWD=", cs)) {
> >>         # mask the password inside the returned object
> >>
> >> RODBC passes the connection string back to R like this:
> >>
> >>   #define buf1_len 8096
> >>   // ...
> >>   SQLCHAR buf1[buf1_len];
> >>   // ...
> >>   retval = SQLDriverConnect(
> >>    thisHandle->hDbc, NULL,
> >>    (SQLCHAR *)Rf_translateChar(STRING_ELT(connection, 0)),
> >>    SQL_NTS,
> >>    (SQLCHAR *)buf1, (SQLSMALLINT)buf1_len,
> >>    &tmp1,
> >>    SQL_DRIVER_NOPROMPT
> >>   );
> >>   if(retval == SQL_SUCCESS || retval == SQL_SUCCESS_WITH_INFO) {
> >>    // ...
> >>    PROTECT(constr = Rf_allocVector(STRSXP, 1));
> >>    SET_STRING_ELT(constr, 0, Rf_mkChar((char *)buf1));
> >>    // ...
> >>    Rf_setAttrib(ans, Rf_install("connection.string"), constr);
> >>
> >> Somehow, the 'buf1' string isn't filled at all and R receives
> >> uninitialised stack contents instead of the effective connection
> >> string, despite the return value indicates a successful connection. And
> >> yet 'idbc' works using the same connection string, suggesting that the
> >> problem isn't inside the MDBtools driver.
> > I think I follow all of that!
> >> How did you install the RODBC package?
> >
> > Sadly, I don't remember but almost certainly either with
> > install.packages("RODB") from the "console" in Rstudio or perhaps
> > using the Rstudio package install route.  It is just possible that I
> > installed it with apt-get install r-cran-rodbc but I think that's
> > unlikely.
> >
> > I see it at /home/chris/R/x86_64-pc-linux-gnu-library/4.6/RODBC but
> > not in /usr/lib/R/library/
> >
> >> If there's nothing obviously
> >> wrong with your R package installation, further investigation will
> >> probably require stepping through the C code in a debugger.
> >
> > I fear that is beyond my skills but I can clearly remove the package
> > with remove.packages() (and then checking that it's really gone from
> > the filesystem) and then reinstalling.  I thought I wouldn't do that
> > until you or someone might advise me if there is anything to be gained
> > diagnostically by keeping it.  I can't share the database in question
> > but it appears to be working fine using routes other than RODBC (see
> > my reply to Gregg Powell).
> >
> > I have dug around in my archives and found an old, small mdb file and
> > it works fine with the Hmisc::mdb.get() but gives the same weirdness
> > with RODBC so I think the issue is clearly with RODBC or my particular
> > installation of it.
> >
> > I have access to the data that I need now so this is really about
> > debugging the weirdness.  No rush from me but I will try to find time
> > to respond reasonably quickly if you or others have advice beyond just
> > removing and deleting the RODBC package and reinstalling it.  Is there
> > a preferred way for me to reinstall it if going that way?
> >
> > TIA,
> >
> >
> > Chris
> >
> >>
> >
> > ______________________________________________
> > R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide
> > https://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
> 	[[alternative HTML version deleted]]
> 

> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 603 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20261108/ac8e4be3/attachment.sig>