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

OLE (Win32::OLE) and SOAP::Lite

Expand Messages
  • Jörg Ziefle
    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
    Message 1 of 4 , Jul 4, 2001
    • 0 Attachment
      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)?

      This is the server on the Windows machine where Excel is sitting. It
      uses autodispatching to the directory where the Win32::OLE module is (in
      a subdirectory).


      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;


      This is the client on the Unix machine. The program is working locally
      on the server (Windows) machine without SOAP::Lite.


      use strict;
      use Data::Dumper;

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

      my $excel = Win32::OLE->new('Excel.Application') or die Win32::OLE->LastError;

      $excel->{Visible} = 1;
      $excel->{DisplayAlerts} = 0;
      my $book = $excel->Workbooks->Add or die Win32::OLE->LastError;
      my $sheet = $book->Worksheets(1) or die Win32::OLE->LastError;

      my @valueary = map { [ split ] } <DATA>;
      $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 = $excel->Correl($range1, $range2) or die Win32::OLE->LastError;
      $excel->Quit;

      print $correl;

      __DATA__
      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


      When I run this program, I see on the server side that Excel is launched
      (but it doesn't become visible). After quite a long time, I see the
      following message from the server daemon:

      Deep recursion on subroutine "SOAP::Serializer::encode_object" at
      C:/Perl/site/lib/SOAP/Lite.pm line 489.
      Deep recursion on subroutine "SOAP::Serializer::encode_hash" at
      C:/Perl/site/lib/SOAP/Lite.pm line 425.

      If I turn on logging on the client side, I see that the request from the
      client to the server is encoded (seems to be correct) and sent, but then
      nothing happens any more on the client side.

      On the other hand, I can run certain server side modules. For example,
      I can obtain all exported OLE constants via Win32::OLE::Const->Load();

      I have a normal ActiveState installation in c:\perl (Perl 5.6.1, Binary
      Build 626).

      Could it be that something fancy is happening during the

      use Win32::OLE;

      (which I can do in the non-SOAP version but not when using SOAP::Lite)?

      Is there any possibility to require/import a remote module when using
      SOAP, that is, to import the semantics of the module?

      Any hint and help on how to use SOAP::Lite with OLE is greatly
      appreciated,

      Jörg
    • Paul Kulchenko
      Hi, Jörg! ... 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
      Message 2 of 4 , Jul 4, 2001
      • 0 Attachment
        Hi, J�rg!

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

        Best wishes, Paul.

        --- J�rg_Ziefle <joerg.ziefle@...> 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)?
        >
        > This is the server on the Windows machine where Excel is sitting.
        > It
        > uses autodispatching to the directory where the Win32::OLE module
        > is (in
        > a subdirectory).
        >
        >
        > 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;
        >
        >
        > This is the client on the Unix machine. The program is working
        > locally
        > on the server (Windows) machine without SOAP::Lite.
        >
        >
        > use strict;
        > use Data::Dumper;
        >
        > use SOAP::Lite
        > +trace => ['fault'],
        > +autodispatch =>
        > uri => 'http://128.61.33.168/',
        > proxy => 'http://128.61.33.168:5000/',
        > on_debug => sub { print @_ },
        > on_fault => sub { print $_[1]->faultstring, "\n" },
        > ;
        >
        > my $excel = Win32::OLE->new('Excel.Application') or die
        > Win32::OLE->LastError;
        >
        > $excel->{Visible} = 1;
        > $excel->{DisplayAlerts} = 0;
        > my $book = $excel->Workbooks->Add or die Win32::OLE->LastError;
        > my $sheet = $book->Worksheets(1) or die Win32::OLE->LastError;
        >
        > my @valueary = map { [ split ] } <DATA>;
        > $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 = $excel->Correl($range1, $range2) or die
        > Win32::OLE->LastError;
        > $excel->Quit;
        >
        > print $correl;
        >
        > __DATA__
        > 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
        >
        >
        > When I run this program, I see on the server side that Excel is
        > launched
        > (but it doesn't become visible). After quite a long time, I see
        > the
        > following message from the server daemon:
        >
        > Deep recursion on subroutine "SOAP::Serializer::encode_object" at
        > C:/Perl/site/lib/SOAP/Lite.pm line 489.
        > Deep recursion on subroutine "SOAP::Serializer::encode_hash" at
        > C:/Perl/site/lib/SOAP/Lite.pm line 425.
        >
        > If I turn on logging on the client side, I see that the request
        > from the
        > client to the server is encoded (seems to be correct) and sent, but
        > then
        > nothing happens any more on the client side.
        >
        > On the other hand, I can run certain server side modules. For
        > example,
        > I can obtain all exported OLE constants via
        > Win32::OLE::Const->Load();
        >
        > I have a normal ActiveState installation in c:\perl (Perl 5.6.1,
        > Binary
        > Build 626).
        >
        > Could it be that something fancy is happening during the
        >
        > use Win32::OLE;
        >
        > (which I can do in the non-SOAP version but not when using
        > SOAP::Lite)?
        >
        > Is there any possibility to require/import a remote module when
        > using
        > SOAP, that is, to import the semantics of the module?
        >
        > Any hint and help on how to use SOAP::Lite with OLE is greatly
        > appreciated,
        >
        > J�rg
        >
        > To unsubscribe from this group, send an email to:
        > soaplite-unsubscribe@yahoogroups.com
        >
        >
        >
        > Your use of Yahoo! Groups is subject to
        > http://docs.yahoo.com/info/terms/
        >
        >


        __________________________________________________
        Do You Yahoo!?
        Get personalized email addresses from Yahoo! Mail
        http://personal.mail.yahoo.com/
      • 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 3 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 4 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.