Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

How can I connect/access an MS access database from solaris?

by Anonymous Monk
on Sep 10, 2002 at 16:50 UTC ( [id://196751] : perlquestion . print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Q: How can I connect/access an MS access database from solaris? More specifically. I'm running aome Perl CGI on a solaris box at one location (intranet app). But there is a customer-facing website running off IIS, where I need to have customers login to get some info pushed there from the intranet Apache web server.

I'm running mySQL on the Solaris side, and can setup an MS Access database on the WinNT/IIS side. But how to I access that database to push the info from the Solaris mySQL source to the WinNT MS Access source?

  • Comment on How can I connect/access an MS access database from solaris?

Replies are listed 'Best First'.
Re: How can I connect/access an MS access database from solaris?
by dws (Chancellor) on Sep 10, 2002 at 17:39 UTC
    How can I connect/access an MS access database from solaris?

    Access isn't a database server in the same way that Oracle, MySQL, et al. are. There are no client libraries that connect via socket to an Access server. Access to Access is typically via ODBC or OLE to the JET engine, which is packaged as a set of DLLs. JET manipulates the disk directly.

    If you want to get at Access from Solaris, you'll need to set up some sort of proxy server on Win32, and connect to the proxy server from Solaris. Look into DBI's proxy mechanism, DBD::Proxy and DBI::ProxyServer. Using these, you would set up a proxy server on Win32 that would talk to Access via DBD::ODBC, and access that proxy server from Solaris via DBD::Proxy. I've seen such a setup work for access other databases.

      In talking with a DBA friend of mine, we've both confirmed that I won't be able to connect to the DSN/ODBC for the MS Access database file.

      While your suggestion of a proxy is a good one, I don't have control over the server that file will be on - in the manner of installing a proxy.

      I may need to consider the option of using a SQL Server, or having some ASP code that I POST my record info, and the ASP page inserts it to the local Access database file.

        If you can connect to the access database using ASP, then you should be able to run a CGI script. And, if you can run a CGI script you may be able to run the CGI as a SOAP service.

        Your service could take your data as parameter(s) and store them in Access on the remote box. Take a look at Soap::Lite for Perl, there are some ASP examples there too.

        --
        hiseldl
        "Act better than you feel" --inner prophet

        FYI

        MSDE 2000 is a free Microsoft baby brother to SQL 2000. It runs on the desktop but it limits the number of connections. I don’t know if it will run on as a server for Internet connections. But for local database applications under Windows, it can be valuable since it is royalty free and you can include it with your application.

        Richard

        There are three types of people in this world, those that can count and those that cannot. Anon

Re: How can I connect/access an MS access database from solaris?
by ignatz (Vicar) on Sep 10, 2002 at 17:24 UTC
Re: How can I connect/access an MS access database from solaris?
by jplindstrom (Monsignor) on Sep 10, 2002 at 17:30 UTC
    I managed to connect to a MS SQL Server from a Linux box using the FreeTDS db library and DBD::Sybase. I guess Access would be pretty much the same thing.

    So install FreeTDS, configure a data source using the ports Access are using (use TDS 4.2) and connect using DBI with a connect string referencing your data source.

    Canīt give you more detailed instructions than that, I'm on the road at the moment.

Re: How can I connect/access an MS access database from solaris?
by jerrygarciuh (Curate) on Sep 10, 2002 at 18:08 UTC
    I have a workaround script for this sort of situation, the code may be seen here. It involves exporting CSV files from an inaccessable database and using the script to upload them and thwen use them to do INSERTs and UPDATEs to a mySQL db. The most recent version includs buffer flushing and a progress bar for large ( > 5000 lines ) csv's. This may require too much human intervention to be a solution for you.
    Peace,
    jg
    _____________________________________________________
    "The man who grasps principles can successfully select his own methods.
    The man who tries methods, ignoring principles, is sure to have trouble.
    - Ralph Waldo Emerson
      Creating insert and update statements for sending CSV data to mySQL is slow and unnecessary. Mysql lets you directly import delimited files via the LOAD DATA function. Just export to CSV and do that instead (you must create your table def first tho).

      --
      perl: code of the samurai

        I had thought LOAD was only available from the command line. I see that I was incorrect.
        Thank you for the tip.
        jg
        _____________________________________________________
        "The man who grasps principles can successfully select his own methods.
        The man who tries methods, ignoring principles, is sure to have trouble.
        ~Ralph Waldo Emerson
        However, doing this will break replication if you are using this. In that case, you have to create the insert commands before pushing them in to MySQL.
Re: How can I connect/access an MS access database from solaris?
by bear_phillips (Novice) on Sep 10, 2002 at 17:55 UTC
    I do something similiar but using FoxPro databases. We use a product from http://www.easysoft.com/ . It is an ODBC bridge. Basically you install the easysoft server on the windows box. Solaris/Perl will use the DBI to make requests to the easysoft server, which then makes the actual odbc call to the database, then passes the data back to Solaris/Perl.