Re: Absolute simplest way to keep a database variable persistent?

[clwolfe]

How about IPC (inter-process communications) using STDIN and STDOUT?

Create a long-running perl script that starts up, loads your massive library, then waits for input on STDIN. Devise a simple "protocol" to communicate to the perl API from VBA, then outputs the results to STDOUT. All this in a while(1) loop.

From VBA, when the spreadsheet opens, start up the perl process and get handles on its input and output. Then feed it function calls and read the results.

I have no idea how easy/possible it is to do that in VBA, but it's not unreasonable. You'll also need to be aware of deadlocking issues, etc.

This approach would give you the benefits of a long-running, compile-once perl instance, without the learning curve of apache+modperl.

[natestraight]

This sounds like the best solution yet.

I hate to have to remind you that I'm a complete n00b, however. (Though I am learning quite a bit trying to figure out a good method on my own.)

Can you show me what a basic long-running script such as this would look like? Particularly the "waits for input on STDIN" section? Thanks.

[clwolfe]

Here's the basics:

use strict;

# This turns off output buffering, so output is immediate.
$| = 1; 

# Load Wordnet
use WordNet::QueryData;
# Adjust location as needed
my $wordnet = WordNet::QueryData->new("wordnet.db"); 

# This reads lines from STDIN.  If there is no input, 
# it waits.
while (my $input = <>) {
    # Remove the newline from the end
    chomp $input;

    # Here we have to think about a protocol.
    # I suggest something of the form command;arg1;arg2
    my ($command, $arg1, $arg2) = split ';', $input;

    my @results;

    # Dispatch based on command
    if ($command eq 'quit') {
       exit;
    } elsif ($command eq 'word') {
       # Do a WordNet queryWord
       @results = $wordnet->queryWord($arg1, $arg2);
    } elsif ($command eq 'sense') {
       @results = $wordnet->querySense($arg1, $arg2);
    } else {
       die "Unknown command $command";
    }

    # OK, send the results back out over STDOUT
    my $line = join ',', @results;
    print $line . "\n";
}

Now then, that's the Perl side. You can test it from the command line like this (if you saved it in wordnet-ipc.pl):

  perl.exe wordnet-ipc.pl

It should appear to "hang", but really it's just waiting on your input. Try typing sense;run#v. To exit, type exit. The part I'm still fuzzy on is how to start up a process from VBA and get stream objects on it's input and output. I'm on a linux box now, so I don't have access to my VBA docs. I'll try to have a shot at it when I get home. --Clinton

[natestraight]

Looks great.

Like you say, I'm not sure whether VBA can directly pass parameters to an already running perl program. This is definitely a start, though.

Can Perl take its STDIN from another text file / script? I can easily make VBA dynamically write a small text file, and if Perl is setup to dynamically (since the Perl script will keep running until I kill it) read that same text file for its parameters, it just might work. Perl would keep looping and reading the text file until VBA changed it, then Perl would spit out some different output and VBA would read it (I think reading dynamic output from an already running Perl program is easier than writing to it).

[GrandFather]

To apply a nomenclature that may help understand clwolfe's idea consider the Perl application as a server and your VBA code as a client. The overall process is a little like using OLE to allow one application (your VBA code) to connect to another application (the Perl code).

In fact it may be that using OLE is exactly the solution you are looking for. It is not a 'free' option however. ActiveState provide tools for doing the heavy lifting as part of their pro package. See OLE automation server and Creating a COM/OLE server for some discussion of this option.

[natestraight]

OLE / COM does sound like exactly what I need, but anything non-free (barring paltry sums... say less than $20) really is out of the picture for now. This is primarily a pet project, not a necessity, and I can't think of a time when I would need to use Perl again.