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

[Chris Evans]

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

>