Database Connectivity

[wbuchanan]

I was curious if any one has had any experience writing a plugin to connect to a database using either a C API or JDBC? I know there is a FAQ available on Stata's website showing how to write a simple plugin for MySQL, but typically need to connect to MS SQL Server or Oracle 11g. If anyone has written anything like this, are you able to share some resources/references on how to code something like this and/or is it possible to push it into the SSC archives?

[David Roodman]

I just use Stata's -odbc- command to connect with MS SQL, creating DSNs to point each database I'm working with. But probably you know more about databases than me, and this is not a helpful observation...

[Daniel Bela]

Hi,
I also use Stata's inbuilt -odbc- connectivity to connect to MS SQL databases. As there are a variety of ODBC connectors for many database applications (MySQL, Oracle, PostgreSQL), it's not clear to me why any other function is needed.

Do you want to write a plugin for performance reasons? I can imagine that a C API plugin may work quicker than an ODBC connection.

[wbuchanan]

ODBC is pretty bad in terms of performance in most of my experience connecting to different data systems. The other big issue is platform dependence and driver availability. If you ever want to experience true pain and frustration, try setting up ODBC capabilities on Unix/Linux boxes; it took me forever to get everything compiled correctly and built out and even then performance is still pretty bad. Because JDBC drivers are more accessible - and platform independent - it seems like it would be a huge win for Stata, since it could provide a method of working with multiple datasets simultaneously (via multiple tables in a database backend).

A solution written in C would probably be the fastest in most cases, but would require a solution written to the individual C APIs and written for each platform and architecture combination which would take a huge amount of effort. It would also be really nice to be able to pass a file with SQL that would return the query to Stata, but the batch version of ODBC only printed the results to the console when I had tried it.

From some of my more recent experience, there also seemed to be issues calling stored procedures passed through the ODBC connection, which could be an issue of ODBC and/or the data system itself. In either case, it isn't too much fun waiting 10-15 minutes for data to load from an ODBC connection if there are more efficient ways to load, store, and/or manipulate data within the Stata platform. If I'm somehow able to learn enough Java to cobble something together I'm more than happy to share, but I'm pretty sure the developers at StataCorp could build out something infinitely more robust with significantly less effort.

[wbuchanan]

Hi,
I also use Stata's inbuilt -http://www.stata.com/help.cgi?odbc"]odbc[/URL]- connectivity to connect to MS SQL databases. As there are a variety of ODBC connectors for many database applications (MySQL, Oracle, PostgreSQL), it's not clear to me why any other function is needed.

Do you want to write a plugin for performance reasons? I can imagine that a C API plugin may work quicker than an ODBC connection.

Not all data systems provide drivers for all platforms; connecting to an Oracle DB from a Mac involves third party drivers which aren't terribly expensive but are also not free. JDBC on the other hand is much more flexible and would also provide a better mechanism to support connection pooling and would be more extensible in general.