1

I am trying to connect to a firebird DB via ODBC. I have an working ODBC Windows Connection (ODBC Data Sources 32 Bit) and also an established connection via Tableau. I tried using dbConnect but was not able to set the right parameters I guess. I tried

library(odbc)
con <- dbConnect(odbc::odbc(),
                 drv = "Firebird/InterBase(r) driver",
                 database = "VARIO",
                 uid = "xxx",
                 pwd = "xxx",
                 host = "192.xxx.xxx.xx",
                 port = "xxxxx")

in e.g. Excel I can access the database by using the established windows connection. Anyways, sorry for my beginner formulation!

To add here, it seems I am running on 64 bit R Version

> Sys.info()[["machine"]]
[1] "x86-64"

After your comments I tried

con <- dbConnect(odbc::odbc(),
                 dsn = "VARIO",
                 database = "192.168.XX.X/56300:VARIO8",
                 uid = "XXX",
                 pwd = "XXX",
                 host = "192.168.XX.X",
                 port = "56300")

>Error: nanodbc/nanodbc.cpp:983: 01S00: [ODBC Firebird Driver]Unable to connect to data source: library 'C:\Users\XXX\Desktop\fbclient.dll' failed to load  [ODBC Firebird Driver]Invalid connection string attribute  [ODBC Firebird Driver]Invalid connection string attribute

fbclient.dll is there where its supposed to be

What is more I did not put an driver. Whenever I ad

drv = "Firebird/InterBase(r) driver"

I get: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbConnect’ for signature ‘"character"’

Maybe this helps? This is from the Windows ODBC Data Sources and says 32/64 Bit. Don't know if this is imprtant

Any further Ideas?

Holmer
  • 11
  • 3
  • According to firebird's [connection parameters](https://firebirdsql.org/file/documentation/reference_manuals/driver_manuals/odbc/html/fbodbc205-conn-params.html) page, it looks like the host and port should be specified within the dbname, such as `database=192.xxx.xxx.xx/xxxxx:/usr/local/db/myDb.fdb`. I don't know enough about firebird to know what of the path versus database (`VARIO`) should be used, perhaps this is enough of a hint to get you something more informative. – r2evans Aug 26 '20 at 16:21
  • 1
    Is your R by any chance 64-bit? In that case you need to install the 64-bit ODBC driver (and a 64-bit fbclient) and configure it from the ODBC Data Source 64 bit screen. Have you also tried specifying the DSN? Do you get any errors, if so, what are those errors? And as suggested by r2evans, the value for `database` is wrong, see also [Connection Parameters](https://firebirdsql.org/file/documentation/reference_manuals/driver_manuals/odbc/html/fbodbc205-conn-params.html). – Mark Rotteveel Aug 26 '20 at 17:59
  • I added some further information in my question as I am still not there. Thanks for your support! This is of great help. – Holmer Aug 27 '20 at 20:33
  • use SysInternals Process Monitor to track the very moment when your application tries to make database connection. Which exactly registry keys and file i/o happens, where does it try to find and open fbclient.dll ? what are the errors ? Your R seems ot be x64, then is your Excel .x64 or x86 ? Are you sure Excel uses ODBC not OLE DB or some other interface? – Arioch 'The Aug 27 '20 at 20:45
  • can u use JDBC rather than ODBC ? Maybe https://rdrr.io/cran/DatabaseConnector/ – Arioch 'The Aug 27 '20 at 20:51

1 Answers1

1

Checking against the list of odbc-known DB drivers, Firebird doesn't seem to be a known driver.

library(odbc)
odbc::odbcListDrivers()

(src: https://rdrr.io/cran/odbc/man/odbcListDrivers.html)

I guess you would have to install that driver on your machine and then register the Data Source Name (DSN) to make it available to R.
Once that's done, please change the drv in your function call to dsn. drv refers to the odbc::odbc() argument which you've provided above, while dsn should then refer to the installed driver by its registered name (unless I'm mixing up things badly here. I've luckily never had to leave the warm comfort of RPostgreSQL...).

So it would look something like this:

library(odbc)
con <- dbConnect(drv = odbc::odbc(),
                 dsn = "Firebird", # this should be the DSN you have set
                 database = "VARIO",
                 uid = "xxx",
                 pwd = "xxx",
                 host = "192.xxx.xxx.xx",
                 port = "xxxxx")
alex_jwb90
  • 1,663
  • 1
  • 11
  • 20
  • Seems I am running on 64 Bit R Version: Sys.info()[["machine"]] retuns "x86-64" ``` – Holmer Aug 27 '20 at 20:20
  • I think you meant to comment on the other thread.. :) – alex_jwb90 Aug 27 '20 at 20:27
  • Does my answer get you anywhere or is it not solving your problem? I'm not 100% sure since I can't test it and I hadn't used a setup with a "custom" odbc driver before... – alex_jwb90 Aug 27 '20 at 20:29
  • can R connect OLE DB not ODBC which was declared obsolete and dead end some years ago ? I think Firebird ODBC driver lost steam that year and never got it back – Arioch 'The Aug 27 '20 at 20:47
  • or perhaps you better use JDBC, R seems to be capable of it (https://rdrr.io/cran/DatabaseConnector/) and Firebird/JDBC (Jaybird) maintainer is very active on Stack Overflow – Arioch 'The Aug 27 '20 at 20:49
  • I added some information on what I tried in my initial answer above! – Holmer Aug 27 '20 at 21:20
  • First of all, `drv` is not to be set to a string, it needs to point to the `odbc::odbc()` object. You can try the param `driver` instead. – alex_jwb90 Aug 27 '20 at 21:23
  • Secondly, have you checked the name of the Firebird DSN you're trying to connect to? The link I put above should tell you how to set this up. I'm pretty sure that it is not VARIO, unless you set it that way, since that's the name of your DB, not the name of the odbc Driver you want to refer to with the `dsn` parameter – alex_jwb90 Aug 27 '20 at 21:25