in reply to Absolute simplest way to keep a database variable persistent?

The sticking point that blocks a direct implementation of the type of solution you would best like is that Perl blesses references to turn them into what amount to objects. WordNet::QueryData->new() returns a blessed object and that blessed state is what is difficult to persist in the manner you wish.

There may be another solution though. Perl is pretty good at driving OLE. In fact VB can generally be translated to Perl without too much trouble (after a little experience anyway). If your VBA script doesn't need much UI then it may be possible to turn things inside out a little and drive Excel from Perl to get the work done. Win32::OLE is the place to start exploring this option.

Another way you may be able to go about it is to have Perl manipulate your Excel files directly. Again, there is a fair amount of support for that in CPAN.

Perl reduces RSI - it saves typing

Replies are listed 'Best First'.
Re^2: Absolute simplest way to keep a database variable persistent?
by natestraight (Novice) on Oct 04, 2008 at 13:46 UTC
    Ah.

    Unfortunately, I'd really like the work to be done inside Excel as the whole point of the project is to be dynamic (i.e. call the Perl script as part of a volatile VBA function), with the parameters changing based on user input.

    The Excel sheet contains data that is automatically refreshed daily from a ton of blog feeds, and the WordNet modules are going to be used to perform some semantic analysis based on selected user-specified blog posts, phrase lengths, etc.

    Nevertheless, it's not entirely impossible that this be done outside of Excel. I could dump the entire worksheet into a MySQL database or the like, but then I really lose a lot of the dynamic, auto-refresh capabilities that I'd like.

    I'll look into this option, though, as it sounds like it would at least work. Thanks.
[reply]
Re^2: Absolute simplest way to keep a database variable persistent?
by dragonchild (Archbishop) on Oct 04, 2008 at 20:49 UTC
    DBM::Deep stores the blessed-ness of the object. That's been there since before I took it over. It would be somewhat useless if it didn't. :-)
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
      DBM::Deep seems to fail the first of your criteria for good software, though... unless something is screwy with my installation. *shrug*

      DBM::Deep sounds like it should do exactly what I want, but for the life of me I can't get it to work. I have copied line-for-line the script in the link in my original post that someone else was able to use for exactly the same task as my application, but it won't run. It gets killed during the cleanup stage with the error "Can't use an undefined value as a HASH reference in DBM/Deep/Hash.pm line 33."

      I have all the dependencies for DBM::Deep (and sub-dependencies) installed. I have made sure all of the versions are compatible. I don't have any other unnecessary Perl modules running. What's causing the "undefined value" error?

      Reading through some basic Perl tutorials online, I can see how the DBM::Deep scripts I copied should be working fine. I'm getting the sinking feeling, though, that I'm missing some simple change (like changing a *insert your local name here* type of reference in the example code to what it should actually be for my application; maybe the {foo} reference? I don't know) that should be obvious. It doesn't seem like the global destruction issue should be popping up and killing my scripts like it's doing.
[reply]
        Yeah, I need to fix that installation problem. I'll get around to it soon. If you force the installation, it works just fine.

        Can you pare down your script to the simplest case that demonstrates that problem and email to me via CPAN? That sounds like it might be a very subtle bug in DBM::Deep. The page you're getting the example off of was using a much older version of DBM::Deep and I might have introduced a bug in the past two years.

        My criteria for good software:
        1. Does it work?
        2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re^2: Absolute simplest way to keep a database variable persistent?
by natestraight (Novice) on Oct 08, 2008 at 02:00 UTC
    As of right now, I've turned things inside out the other way around.

    My VBA function now dynamically writes out the requisite perl script as one massive text file (that loads the lexicon once, then runs all of the individual queries in the same script so that the lexicon object persists obviously until the end of the script), and then calls that text file back into the shell inside VBA.

    So, right now I'm actually driving Perl from Excel. Very strange. It seems to me like a really ugly fix, but it works fairly well... except when it comes to recalculating anything, and the formula has to regenerate the text file and reload the lexicon (since it, as always, closed after the last dynamically-generated script finished).
[reply]

In Section Seekers of Perl Wisdom