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

Chris Evans chr|@ho|d @end|ng |rom p@yctc@org
Sun Nov 8 15:49:14 CET 2026 

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]]

More information about the R-help mailing list