Loading ...
Sorry, an error occurred while loading the content.

Re: [soaplite] OLE (Win32::OLE) and SOAP::Lite [solution and question]

Expand Messages
  • Jörg Ziefle
    ... The following email contains a solution to my problem with a little problem at the very bottom (see PROBLEM). SOLUTION ======== Thanks to your hint, I have
    Message 1 of 4 , Jul 5, 2001
    • 0 Attachment
      On Wed, Jul 04, 2001 at 09:15:14PM -0700, Paul Kulchenko wrote:

      > > I want to use MS Excel (on the server side) (via Win32::OLE) over
      > > SOAP::Lite. Unfortunately, I encountered some problems. Does
      > > anybody have an idea what could be the wrong (please see below)?
      > Nothing is wrong there, but Win32::OLE is one of the objects that
      > can't be properly serialized. If you create an object of this type
      > and try to make 'x' in debugger on this object, you'll see why. It
      > has some internal structure that will create new references on demand
      > when you try to get a value of this property. I don't know many
      > details about it, but it doesn't seem to be serializible with
      > Data::Dumper also and SOAP::Lite's serializer goes into endless loop
      > trying to serialize it. I would appreciate any information on how to
      > handle it properly.
      >
      > What you can do is use object-by-reference interface and don't return
      > Win32::OLE object back on client side. As slightly better option (you
      > have more control in this case), you can implement simple wrapper
      > around Win32::OLE object that keeps it on server side and return some
      > information on client side that helps you identify object stored on
      > server side. Let me know if you find any other options or way to
      > serialize Win32::OLE object.

      The following email contains a solution to my problem with a little
      problem at the very bottom (see PROBLEM).

      SOLUTION
      ========

      Thanks to your hint, I have come up with a little wrapper class
      (WrapOLE) around the Win32::OLE module. The class generates a global
      (to the class) Win32::OLE object with the name $OLE of the kind
      specified in the constructor call (see example) The class then provides
      a method OLE_eval_sub that uses string eval to evaluate the string
      passed form the client side and pass back the result. The string passed
      from the client can use (of course, otherwise the whole thing would be
      pointless) the global $OLE object stored on the server side. This
      solution works because the method stays on the server side and doesn't
      have to be serialized (what obviously doesn't work). I think the
      solution is simple, has only little overhead and is powerful. Of
      course, fancier things could be thought of like AUTOLOAD()ing and
      dispatching of WrapOLE method calls to the corresponding Win32::OLE $OLE
      object. Anyway, it works.

      Here's the WrapOLE class. Simple, isn't it? :)


      package WrapOLE;

      use strict;
      use Win32::OLE;

      our $OLE;

      sub new {

      my ($proto, $arg) = @_;

      my $class = ref $proto || $proto;
      $OLE = Win32::OLE->new($arg) or die Win32::OLE->LastError;
      my $self = {};
      bless $self, $class;
      return $self;

      };

      sub OLEeval_sub {

      return eval $_[1];

      };

      1;


      Of course, error checking of the eval() would be a good thing, but
      that's left as an exercise to the reader. :/

      Here's a sample client program that uses the WrapOLE module to access
      and perform a calculation in Excel (with Excel's builtin correl()
      function).


      use strict;
      use SOAP::Lite
      +trace => ['fault'],
      +autodispatch =>
      uri => 'http://128.61.33.168/',
      proxy => 'http://128.61.33.168:5000/',
      on_fault => sub { print $_[1]->faultstring },
      ;

      my $ole = WrapOLE->new('Excel.Application') or die "$!\n";

      my $sub = <<'EOSUB';
      $OLE->{Visible} = 0;
      $OLE->{DisplayAlerts} = 0;
      my $book = $OLE->Workbooks->Add or die Win32::OLE->LastError;
      my $sheet = $book->Worksheets(1) or die Win32::OLE->LastError;
      my @valueary = (
      [1, 34],
      [2, 45],
      [32, 34],
      [5, 7],
      [34, 45],
      [5, 7],
      [32, 32],
      [46, 8],
      [57, 34],
      [34, 84],
      [5, 12],
      [45, 34],
      [65, 72],
      [34, 82],
      [74, 98],
      [23, 2],
      [6, 43],
      [35, 65],
      [98, 74],
      [4, 45],
      [57, 34],
      [23, 3],
      [87, 74],
      [23, 25],
      [6, 37],
      [77, 2],
      );
      $sheet->Range('A1:B'.scalar @valueary)->{Value} = \@valueary;
      my $range1 = $sheet->Range('A1:A'.scalar @valueary);
      my $range2 = $sheet->Range('B1:B'.scalar @valueary);
      my $correl = $OLE->Correl($range1, $range2) or die Win32::OLE->LastError;
      $OLE->Quit;
      return $correl;
      EOSUB

      print $ole->OLEeval_sub($sub);


      If you set

      $OLE->{Visible} = 1;

      then you can even see the magic happening on the desktop of the Windows
      machine.

      PROBLEM
      =======

      Only one little problem is staying: Even though callling $OLE->Quit, of
      Excel doesn't seem to shut down. Alternatively, I provided a WrapOLE
      method quit that just called

      $OLE->Quit;

      but that didn't make a difference. However, Excel shut down fine when
      testing the program locally (without the wrapper module and without
      SOAP,- just the commands inside the eval).

      Does anybody have an idea why Excel keeps on running and doesn't
      shutdown properly?

      Thanks,

      Jörg

      P.s.: To complete the set of programs, here is the daemon I used:

      use SOAP::Transport::HTTP;

      $daemon = SOAP::Transport::HTTP::Daemon
      -> new(
      LocalPort => 5000,
      Reuse => 1,
      )
      -> dispatch_to('C:\\perl\\site\\lib', '[\w:]+');

      print "Contact to SOAP server at ", $daemon->url, "\n";

      $daemon->handle;
    • Paul Kulchenko
      Hi, Jörg! ... Difficult to say. You may try to do something like use Win32::OLE; # use existing instance if Excel is already running eval {$ex =
      Message 2 of 4 , Jul 6, 2001
      • 0 Attachment
        Hi, J�rg!

        > Does anybody have an idea why Excel keeps on running and doesn't
        > shutdown properly?
        Difficult to say. You may try to do something like

        use Win32::OLE;

        # use existing instance if Excel is already running
        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;
        unless (defined $ex) {
        $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
        or die "Oops, cannot start Excel";
        }

        instead of just

        $OLE = Win32::OLE->new($arg) or die Win32::OLE->LastError;

        > Here's the WrapOLE class. Simple, isn't it? :)
        yes, it is. But you need to be **VERY** careful, because it opens
        access to your system and allows to execute (almost) any code. It's
        very powerful conception, but ideally you need to execute it in a
        Safe environment or not execute at all (unless you secure connection
        and absolutely trust your client, which is not the case with most
        webservices). I worked on Safe environment to allow to execute any
        code on server side, but I faced some fundamental problems re call
        dispatch and didn't finish this part, mainly because there was no
        demand for it :). Yet I still have it on my TODO and hope to finish
        it some day.

        Best wishes, Paul.

        --- J�rg_Ziefle <joerg.ziefle@...> wrote:
        > On Wed, Jul 04, 2001 at 09:15:14PM -0700, Paul Kulchenko wrote:
        >
        > > > I want to use MS Excel (on the server side) (via Win32::OLE)
        > over
        > > > SOAP::Lite. Unfortunately, I encountered some problems. Does
        > > > anybody have an idea what could be the wrong (please see
        > below)?
        > > Nothing is wrong there, but Win32::OLE is one of the objects that
        > > can't be properly serialized. If you create an object of this
        > type
        > > and try to make 'x' in debugger on this object, you'll see why.
        > It
        > > has some internal structure that will create new references on
        > demand
        > > when you try to get a value of this property. I don't know many
        > > details about it, but it doesn't seem to be serializible with
        > > Data::Dumper also and SOAP::Lite's serializer goes into endless
        > loop
        > > trying to serialize it. I would appreciate any information on how
        > to
        > > handle it properly.
        > >
        > > What you can do is use object-by-reference interface and don't
        > return
        > > Win32::OLE object back on client side. As slightly better option
        > (you
        > > have more control in this case), you can implement simple wrapper
        > > around Win32::OLE object that keeps it on server side and return
        > some
        > > information on client side that helps you identify object stored
        > on
        > > server side. Let me know if you find any other options or way to
        > > serialize Win32::OLE object.
        >
        > The following email contains a solution to my problem with a little
        > problem at the very bottom (see PROBLEM).
        >
        > SOLUTION
        > ========
        >
        > Thanks to your hint, I have come up with a little wrapper class
        > (WrapOLE) around the Win32::OLE module. The class generates a
        > global
        > (to the class) Win32::OLE object with the name $OLE of the kind
        > specified in the constructor call (see example) The class then
        > provides
        > a method OLE_eval_sub that uses string eval to evaluate the string
        > passed form the client side and pass back the result. The string
        > passed
        > from the client can use (of course, otherwise the whole thing would
        > be
        > pointless) the global $OLE object stored on the server side. This
        > solution works because the method stays on the server side and
        > doesn't
        > have to be serialized (what obviously doesn't work). I think the
        > solution is simple, has only little overhead and is powerful. Of
        > course, fancier things could be thought of like AUTOLOAD()ing and
        > dispatching of WrapOLE method calls to the corresponding Win32::OLE
        > $OLE
        > object. Anyway, it works.
        >
        > Here's the WrapOLE class. Simple, isn't it? :)
        >
        >
        > package WrapOLE;
        >
        > use strict;
        > use Win32::OLE;
        >
        > our $OLE;
        >
        > sub new {
        >
        > my ($proto, $arg) = @_;
        >
        > my $class = ref $proto || $proto;
        > $OLE = Win32::OLE->new($arg) or die Win32::OLE->LastError;
        > my $self = {};
        > bless $self, $class;
        > return $self;
        >
        > };
        >
        > sub OLEeval_sub {
        >
        > return eval $_[1];
        >
        > };
        >
        > 1;
        >
        >
        > Of course, error checking of the eval() would be a good thing, but
        > that's left as an exercise to the reader. :/
        >
        > Here's a sample client program that uses the WrapOLE module to
        > access
        > and perform a calculation in Excel (with Excel's builtin correl()
        > function).
        >
        >
        > use strict;
        > use SOAP::Lite
        > +trace => ['fault'],
        > +autodispatch =>
        > uri => 'http://128.61.33.168/',
        > proxy => 'http://128.61.33.168:5000/',
        > on_fault => sub { print $_[1]->faultstring },
        > ;
        >
        > my $ole = WrapOLE->new('Excel.Application') or die "$!\n";
        >
        > my $sub = <<'EOSUB';
        > $OLE->{Visible} = 0;
        > $OLE->{DisplayAlerts} = 0;
        > my $book = $OLE->Workbooks->Add or die Win32::OLE->LastError;
        > my $sheet = $book->Worksheets(1) or die Win32::OLE->LastError;
        > my @valueary = (
        > [1, 34],
        > [2, 45],
        > [32, 34],
        > [5, 7],
        > [34, 45],
        > [5, 7],
        > [32, 32],
        > [46, 8],
        > [57, 34],
        > [34, 84],
        > [5, 12],
        > [45, 34],
        > [65, 72],
        > [34, 82],
        > [74, 98],
        > [23, 2],
        > [6, 43],
        > [35, 65],
        > [98, 74],
        > [4, 45],
        > [57, 34],
        > [23, 3],
        > [87, 74],
        > [23, 25],
        > [6, 37],
        > [77, 2],
        > );
        > $sheet->Range('A1:B'.scalar @valueary)->{Value} = \@valueary;
        > my $range1 = $sheet->Range('A1:A'.scalar @valueary);
        > my $range2 = $sheet->Range('B1:B'.scalar @valueary);
        > my $correl = $OLE->Correl($range1, $range2) or die
        > Win32::OLE->LastError;
        > $OLE->Quit;
        > return $correl;
        > EOSUB
        >
        > print $ole->OLEeval_sub($sub);
        >
        >
        > If you set
        >
        > $OLE->{Visible} = 1;
        >
        > then you can even see the magic happening on the desktop of the
        > Windows
        > machine.
        >
        > PROBLEM
        > =======
        >
        > Only one little problem is staying: Even though callling
        > $OLE->Quit, of
        > Excel doesn't seem to shut down. Alternatively, I provided a
        > WrapOLE
        > method quit that just called
        >
        > $OLE->Quit;
        >
        > but that didn't make a difference. However, Excel shut down fine
        > when
        > testing the program locally (without the wrapper module and without
        > SOAP,- just the commands inside the eval).
        >
        > Does anybody have an idea why Excel keeps on running and doesn't
        > shutdown properly?
        >
        > Thanks,
        >
        > J�rg
        >
        > P.s.: To complete the set of programs, here is the daemon I used:
        >
        > use SOAP::Transport::HTTP;
        >
        > $daemon = SOAP::Transport::HTTP::Daemon
        > -> new(
        > LocalPort => 5000,
        > Reuse => 1,
        > )
        > -> dispatch_to('C:\\perl\\site\\lib', '[\w:]+');
        >
        > print "Contact to SOAP server at ", $daemon->url, "\n";
        >
        >
        === message truncated ===


        __________________________________________________
        Do You Yahoo!?
        Get personalized email addresses from Yahoo! Mail
        http://personal.mail.yahoo.com/
      Your message has been successfully submitted and would be delivered to recipients shortly.