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

[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).

[clwolfe]

That's a good idea. It would have the downside that you'd have to flag the other process when you're done writing, and I think you'd have some headaches there. You'd probably have to introduce a waiting period - like, sleep for a second, check for file, read it, write out results, etc. Those seconds can add up.

Fortunately, I finally sat down in front of Excel and here's what I whipped up. It actually works, which stunned me.

First, in your VBA project, go to Tools->References, find 'Windows Script Host Model', and check the box.

Next add this VBA code, or something like it:

Option Explicit

Dim perlProc As WshExec
Dim perlIn As TextStream
Dim perlOut As TextStream

Private Sub Workbook_Open()
  Dim wsh As New WshShell
  Set perlProc = wsh.Exec("C:\perl\bin\perl C:\perl\play\wordnet-ipc.p
+l ")
  Set perlIn = perlProc.StdIn
  Set perlOut = perlProc.StdOut
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  perlProc.Terminate
End Sub

This handles starting and stopping the wordnet-ipc script, which can basically be as above. You should see a DOS box appear when you open the worksheet, if it's working right.

You could add a sub to restart the perl process if it dies.

Next step is to hook in your functions in VBA....

Function doQueryWord(aWord As String, aRole as String) As String
  ' Construct a command in the simple protocol wordnet-ipc expects
  Dim cmd as String
  cmd = "word" & ";" & aWord & ";" & aRole

  ' Send the command to the waiting perl script
  perlIn.writeLine cmd

  ' Read the output from the perl script.  This will block until outpu
+t appears.
  Dim results As String
  results = perlOut.readLine

  ' Return results.  You could split on commas - not sure what format 
+you need.
  doQueryWord = results
End Function

' doQuerySense would be nearly identical

That should do the trick. I haven't tried it with WordNet, as I don't have it installed on my windows box, but I did write a simple Perl print-execute-wait loop, and it worked fine.

best of luck! --Clinton

[natestraight]

This sounds incredible, and looks a helluva lot simpler and more elegant than my ridiculous current solution. Thanks a lot.