Connect 4D database with R

Question

I think StackOverflow is the best QA for asking this question.

I would like to connect a 4D database (please don't ask why 4D, this is enough painful to have to use it) with mathematics tools such R (or Matlab or even a C++ or .NET handwritten client) but I can figure out the connection string!

I have installed 4D ODBC Driver, edited my ODBC sources and I am able to connect it through MS Access. I am working on W7 Pro. I am sure there is another possibilities and I want to explore them.

I checked various connection string databases but there is no connection string for 4D (I don't ask myself why). Simultaneously I will write to the 4D customer service, but I am sure some of you know the key or maybe a hint to solve this.

score 2 · Accepted Answer · answered Mar 11 '14 at 18:30

I'm a (somewhat reluctant) 4D developer by day, Python guy by night. When I need to connect via SQL to 4D I use the pyodbc module. I'm working on windows so my connection string is just my dsn name.

import pyodbc
conn = pyodbc.connect('DSN=4D_DSN_Name')

4D_DSN_Name is a 32-bit system DSN that I've created with the 4D ODBC driver. I haven't done anything special with the setup, just making sure I have the correct address, port, username, and password. The rest of the default settings seem to work.

Not a direct answer, but maybe a clue or alternative.

Diving deeper in RODBC user guide, it looks like it is as simple as you said. I'll check it tomorrow and accept your answer if relevant. Thank you for answering. — jlandercy, Mar 11 '14 at 18:55

score 1 · Answer 2 · answered Mar 12 '14 at 10:33

Marked Joshua post as answer and I will add some details here...

It was as simple as using DSN name standing for the whole connection string. It goes this way in R:

> library(RODBC)
> dbhandle <- odbcDriverConnect('DSN=SomeObscureDatabase')
> dbhandle
RODBC Connection 1
Details:
  case=nochange
  DSN=SomeObscureDatabase
  DRIVER=4D v12 ODBC Driver
  Description=4D v12 SQL datasource.
  Server=x
  Port=19812
  UID=x
  PWD=x
  SSL=true
  PhysicalConnectionTimeout=30
  LoginTimeout=30
  QueryTimeout=240
  DefaultPageSize=100
  FavouriteImageFormats=*
  NetworkCacheSize=128
  CharsEncoding=UTF-8
  OpenQuery=false

Connect 4D database with R

2 Answers2