Troubleshooting Connections page about db.rstudio.com HOT 5 OPEN

@daattali / @DavisVaughan - If there's anything you guys think I should include as part of a Troubleshooting page can you provide feedback here please?

from db.rstudio.com.

I don't know how much burden of explanation about databases/odbc should fall on rstudio vs how much you should expect people who deal with databases to know.

I personally don't have much experience with connecting to databases myself (everywhere I've worked previously I didn't have to deal with that, everything was set up already), so every time I try to connect to a new type of database, I find myself having problems :)

I don't know if it's important for most people, but to me knowing how to connect to local databases is important because it's sometimes different (different params) than connecting to a production external database, and it's the first step - I like to develop locally instead of connecting externally when possible. So I always look for help in how to connect locally.

I think the parameters should be standardized in terms of uppercase or lowercase.

It would be helpful to list exactly what parameters are supported for each database or package, because I find myself never knowing if I need to use server or host or ...? username or UID or user or ...? It's always a few hours of scrambling.

I would also like a good explanation of the difference between using odbc vs the dedicated packages (RMySQL, RPostgreSQL, RSQLServer, ROracle, etc)

I'd also like to know what goes in the connection string, I couldn't find much info on that.

And perhaps tips on how to make sure I have the odbc drivers. I've connected to a few databases and I never had to do anything special to get the drivers, but with postgres it was the first time I actually had to download them which I wouldn't have thought of (thanks @DavisVaughan!)

As I said, some of this might be out of scope for rstudio and should be the responsibility of the developer. But those are some things that I always struggle with

from db.rstudio.com.

Thanks @daattali for the feedback!

I like the idea of maybe doing an article to discuss difference between DB packages and ODBC drivers

When you get a minute, would you mind letting me know if there's anything I should add to the PostreSQL page that would have helped you? http://db.rstudio.com/databases/postgresql/

from db.rstudio.com.

I think what may be helpful is a more complete explanation of setting up odbc with Windows here. Some things that might be helpful there:

• An explanation of how to check if you have a driver installed (he needed psqlODBC but how do I tell him how to check if he has it?)
• The general paths of where to find the 32 bit and 64 bit ODBC Data Source Admin apps. I think it is much easier to find these on Windows 8 and above, but on Windows 7 I always have a hard time. If I remember right, for one of 32/64 bit you have to launch the executable directly. File paths are listed here.
• How to set up a DSN? You mention it in Securing Credentials here, and I would say DSNs also reduce a lot of connection headache, but there is no explanation of how to set one up.
• The biggest issue I've run into is whether to connect using 32 bit or 64 bit. An anecdotal example is connecting to Access. It was a 32 bit version, which means I needed the 32 bit version of ODBC Data Source Admin AND the 32 bit version of R. I think saying something about that problem along with a link to better resources might be useful. This stack overflow answer is good here.

Lastly, I think people might be hitting these roadblocks without even knowing it. They get an error, but don't know how to interpret the error. I think there could be an Error Troubleshooting page that lists common odbc errors and potential solutions. For example, @daattali's error of [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified led me to realize that psqlODBC might not be installed. Maybe this message would have a generic "go look for that driver name in the ODBC Data Source Admin" solution.

from db.rstudio.com.

Thanks @DavisVaughan ! These are very helpful comments.

from db.rstudio.com.