At 3:43 PM -0800 1/9/03, Mark Berryman wrote: >WARNING: Perl novice on board... > >I suddenly find myself in the need of being able to access a Microsoft >Access database from my VMS system. I figure Perl might be a good way >to do this. However, I seem to be lost in the morass of verbage that >talks about accessing a database via Perl so I'm turning here in the >hopes of obtaining some hints or pointers (or, at least some water >wings). > >What module(s) do I need to load into Perl?
The basic module for database access via Perl is DBI (DataBase Interface), which needs a specific database driver to go with it (DBD::Oracle, DBD::Sybase, DBD::Rdb, DBD::ODBC, etc.). There is an O'Reilly book called, IIRC, "Programming the Perl DBI" and I'm sure there are other resources on the web. >Do I need any additional software? The DBD module assumes either a local database or a local client that knows how to talk on the wire in whatever protocol the remote database understands. I believe that for Oracle, that protocol would be SQL*Net and the DBI driver would be DBD::Oracle. As another example, I use DBD::Sybase on VMS, which links against the Sybase Open Client libraries, and can access local or remote Sybase databases. Through a quirk of fate it can also access remote Microsoft SQL Server databases (at least up through 7.0) because the Borg assimilated/purchased rights to Sybase, and SQL Server thus uses Sybase's TDS (tabular data stream) protocol as its network language. (The TDS client at www.freetds.org should also work if you don't have the ancient, discontinued OpenVMS Sybase client.) In your case, I'm afraid I don't know what, if any, network protocol is supported by Access. In Access 2000 and later, there is the possibility that -- since it uses the SQL Server engine -- it supports TDS, but I really don't know. Older versions of Access may use the OLE DB provider, but I'm far from certain about that. >Has anyone done this? Not me. >Can it be done? Well, it's all just code. The interfaces and protocols are documented. Whether it's practical is a question of how much time you have for porting/integration and/or how much money you have to spend. >I had thought that the most likely way to accomplish this would be for >Perl to talk to the Access database via ODBC but, I can't tell if there >is a module that makes Perl speak ODBC natively; if I need an ODBC >driver or other 3rd-party package; etc. DBD::ODBC is the starting point, but you also need an ODBC driver specific to whatever remote data source you want to talk to. As I understand it, ODBC is a standard API, but you still need a driver specific to the (possibly remote) data source. Typically the remote data source would be an RDBMS over its native protocol, but with commercial products such as those from OpenRDA, EasySoft, and Attunity, the remote data source is a middleware server. I believe this is also true of CONNX. With these middleware solutions, the ODBC client driver is standardized; you only need one for whatever databases the middleware supports. The middleware server handles all the different databases. The disadvantage of the middleware is that you have to buy a server license for each server you want to access; hmm, I wonder why all the commercial products are implemented this way. Of course the advantage of the middleware is that someone has put the pieces together already one of the companies listed would probably have the quickest solution. >Any help, tips, pointers, etc. greatly appreciated, It's harder than it should be. Many of the potential pieces are available but there would almost certainly be some bumpy integration work. You really have to ask yourself whether the ability to send arbitrary SQL queries to the Access database is really what you need. If not, and if there are definable data sets that won't change frequently, there may be interfaces other than traditional database queries that come to mind, including COM, CORBA, and SOAP. -- ________________________________________ Craig A. Berry mailto:[EMAIL PROTECTED] "... getting out of a sonnet is much more difficult than getting in." Brad Leithauser
