From e5c6e6d9c56fbfc54508fa2c32b03a5a870b7553 Mon Sep 17 00:00:00 2001 From: Karl Berry Date: Wed, 23 Apr 2014 22:10:41 +0000 Subject: oops, remove 5.16 tlperl files git-svn-id: svn://tug.org/texlive/trunk@33649 c570f23f-e606-0410-a88d-b1316a301751 --- Master/tlpkg/tlperl/lib/Win32/OLE/NEWS.pod | 380 -------------- Master/tlpkg/tlperl/lib/Win32/OLE/TPJ.pod | 798 ----------------------------- 2 files changed, 1178 deletions(-) delete mode 100644 Master/tlpkg/tlperl/lib/Win32/OLE/NEWS.pod delete mode 100644 Master/tlpkg/tlperl/lib/Win32/OLE/TPJ.pod (limited to 'Master/tlpkg/tlperl/lib/Win32') diff --git a/Master/tlpkg/tlperl/lib/Win32/OLE/NEWS.pod b/Master/tlpkg/tlperl/lib/Win32/OLE/NEWS.pod deleted file mode 100644 index 1de6b7ce0a5..00000000000 --- a/Master/tlpkg/tlperl/lib/Win32/OLE/NEWS.pod +++ /dev/null @@ -1,380 +0,0 @@ -=pod - -=head1 NAME - -Win32::OLE::NEWS - What's new in Win32::OLE - -This file contains a history of user visible changes to the -Win32::OLE::* modules. Only new features and major bug fixes that -might affect backwards compatibility are included. - -=head1 Version 0.18 - -=head2 VT_CY and VT_DECIMAL return values handled differently - -The new C option enables values of VT_CY or VT_DECIMAL type -to be returned as Win32::OLE::Variant objects instead of being -converted into strings and numbers respectively. This is similar to -the change in Win32::OLE version 0.12 to VT_DATE and VT_ERROR values. -The Win32::OLE::Variant module must be included to make sure that -VT_CY and VT_DECIMAL values behave as before in numeric or string -contexts. - -Because the new behavior is potentially incompatible, it must be -explicitly enabled: - - Win32::OLE->Option(Variant => 1); - - -=head1 Version 0.17 - -=head2 New nullstring() function in Win32::OLE::Variant - -The nullstring() function returns a VT_BSTR variant containing a NULL -string pointer. Note that this is not the same as a VT_BSTR variant -containing the empty string "". - -The nullstring() return value is equivalent to the Visual Basic -C constant. - - -=head1 Version 0.16 - -=head2 Improved Unicode support - -Passing Unicode strings to methods and properties as well as returning -Unicode strings back to Perl works now with both Perl 5.6 and 5.8. -Note that the Unicode support in 5.8 is much more complete than in 5.6 -or 5.6.1. - -C objects can now be passed to methods or assigned to -properties. - -You must enable Unicode support by switching Win32::OLE to the UTF8 -codepage: - - Win32::OLE->Option(CP => Win32::OLE::CP_UTF8()); - - -=head1 Version 0.13 - -=head2 New nothing() function in Win32::OLE::Variant - -The nothing() function returns an empty VT_DISPATCH variant. It can be -used to clear an object reference stored in a property - - use Win32::OLE::Variant qw(:DEFAULT nothing); - # ... - $object->{Property} = nothing; - -This has the same effect as the Visual Basic statement - - Set object.Property = Nothing - -=head2 New _NewEnum and _Unique options - -There are two new options available for the Win32::OLE->Option class -method: C<_NewEnum> provides the elements of a collection object -directly as the value of a C<_NewEnum> property. The C<_Unique> -option guarantees that Win32::OLE will not create multiple proxy -objects for the same underlying COM/OLE object. - -Both options are only really useful to tree traversal programs or -during debugging. - - -=head1 Version 0.12 - -=head2 Additional error handling functionality - -The Warn option can now be set to a CODE reference too. For example, - - Win32::OLE->Option(Warn => 3); - -could now be written as - - Win32::OLE->Option(Warn => \&Carp::croak); - -This can even be used to emulate the VisualBasic C construct: - - Win32::OLE->Option(Warn => sub {goto CheckError}); - # ... your normal OLE code here ... - - CheckError: - # ... your error handling code here ... - -=head2 Builtin event loop - -Processing OLE events required a polling loop before, e.g. - - my $Quit; - #... - until ($Quit) { - Win32::OLE->SpinMessageLoop; - Win32::Sleep(100); - } - package BrowserEvents; - sub OnQuit { $Quit = 1 } - -This is inefficient and a bit odd. This version of Win32::OLE now -supports a standard messageloop: - - Win32::OLE->MessageLoop(); - - package BrowserEvents; - sub OnQuit { Win32::OLE->QuitMessageLoop } - -=head2 Free unused OLE libraries - -Previous versions of Win32::OLE would call the CoFreeUnusedLibraries() -API whenever an OLE object was destroyed. This made sure that OLE -libraries would be unloaded as soon as they were no longer needed. -Unfortunately, objects implemented in Visual Basic tend to crash -during this call, as they pretend to be ready for unloading, when in -fact, they aren't. - -The unloading of object libraries is really only important for long -running processes that might instantiate a huge number of B -objects over time. Therefore this API is no longer called -automatically. The functionality is now available explicitly to those -who want or need it by calling a Win32::OLE class method: - - Win32::OLE->FreeUnusedLibraries(); - -=head2 The "Win32::OLE" article from "The Perl Journal #10" - -The article is Copyright 1998 by I. http://www.tpj.com - -It originally appeared in I # 10 and appears here -courtesy of Jon Orwant and I. The sample code from -the article is in the F file. - -=head2 VARIANT->Put() bug fixes - -The Put() method didn't work correctly for arrays of type VT_BSTR, -VT_DISPATH or VT_UNKNOWN. This has been fixed. - -=head2 Error message fixes - -Previous versions of Win32::OLE gave a wrong argument index for some -OLE error messages (the number was too large by 1). This should be -fixed now. - -=head2 VT_DATE and VT_ERROR return values handled differently - -Method calls and property accesses returning a VT_DATE or VT_ERROR -value would previously translate the value to string or integer -format. This has been changed to return a Win32::OLE::Variant object. -The return values will behave as before if the Win32::OLE::Variant -module is being used. This module overloads the conversion of -the objects to strings and numbers. - - -=head1 Version 0.11 (changes since 0.1008) - -=head2 new DHTML typelib browser - -The Win32::OLE distribution now contains a type library browser. It -is written in PerlScript, generating dynamic HTML. It requires -Internet Explorer 4.0 or later. You'll find it in -F. It should be available in the ActivePerl -HTML help under Win32::OLE::Browser. - -After selecting a library, type or member you can press F1 to call up -the corresponding help file at the appropriate location. - -=head2 VT_DECIMAL support - -The Win32::OLE::Variant module now supports VT_DECIMAL variants too. -They are not "officially" allowed in OLE Automation calls, but even -Microsoft's "ActiveX Data Objects" sometimes returns VT_DECIMAL -values. - -VT_DECIMAL variables are stored as 96-bit integers scaled by a -variable power of 10. The power of 10 scaling factor specifies the -number of digits to the right of the decimal point, and ranges from 0 -to 28. With a scale of 0 (no decimal places), the largest possible -value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal -places, the largest value is +/-7.9228162514264337593543950335 and the -smallest, non-zero value is +/-0.0000000000000000000000000001. - -=head1 Version 0.1008 - -=head2 new LetProperty() object method - -In Win32::OLE property assignment using the hash syntax is equivalent -to the Visual Basic C syntax (I assignment): - - $Object->{Property} = $OtherObject; - -corresponds to this Visual Basic statement: - - Set Object.Property = OtherObject - -To get the I treatment of the Visual Basic C statement - - Object.Property = OtherObject - -you have to use the LetProperty() object method in Perl: - - $Object->LetProperty($Property, $OtherObject); - -=head2 new HRESULT() function - -The HRESULT() function converts an unsigned number into a signed HRESULT -error value as used by OLE internally. This is necessary because Perl -treats all hexadecimal constants as unsigned. To check if the last OLE -function returned "Member not found" (0x80020003) you can write: - - if (Win32::OLE->LastError == HRESULT(0x80020003)) { - # your error recovery here - } - -=head1 Version 0.1007 (changes since 0.1005) - -=head2 OLE Event support - -This version of Win32::OLE contains B level support for OLE events. The -userinterface is still subject to change. There are ActiveX objects / controls -that don't fire events under the current implementation. - -Events are enabled for a specific object with the Win32::OLE->WithEvents() -class method: - - Win32::OLE->WithEvents(OBJECT, HANDLER, INTERFACE) - -Please read further documentation in Win32::OLE. - -=head2 GetObject() and GetActiveObject() now support optional DESTRUCTOR argument - -It is now possible to specify a DESTRUCTOR argument to the GetObject() and -GetActiveObject() class methods. They work identical to the new() DESTRUCTOR -argument. - -=head2 Remote object instantiation via DCOM - -This has actually been in Win32::OLE since 0.0608, but somehow never got -documented. You can provide an array reference in place of the usual PROGID -parameter to Win32::OLE->new(): - - OBJ = Win32::OLE->new([MACHINE, PRODID]); - -The array must contain two elements: the name of the MACHINE and the PROGID. -This will try to create the object on the remote MACHINE. - -=head2 Enumerate all Win32::OLE objects - -This class method returns the number Win32::OLE objects currently in -existance. It will call the optional CALLBACK function for each of -these objects: - - $Count = Win32::OLE->EnumAllObjects(sub { - my $Object = shift; - my $Class = Win32::OLE->QueryObjectType($Object); - printf "# Object=%s Class=%s\n", $Object, $Class; - }); - -The EnumAllObjects() method is primarily a debugging tool. It can be -used e.g. in an END block to check if all external connections have -been properly destroyed. - -=head2 The VARIANT->Put() method now returns the VARIANT object itself - -This allows chaining of Put() method calls to set multiple values in an -array variant: - - $Array->Put(0,0,$First_value)->Put(0,1,$Another_value); - -=head2 The VARIANT->Put(ARRAYREF) form allows assignment to a complete SAFEARRAY - -This allows automatic conversion from a list of lists to a SAFEARRAY. -You can now write: - - my $Array = Variant(VT_ARRAY|VT_R8, [1,2], 2); - $Array->Put([[1,2], [3,4]]); - -instead of the tedious: - - $Array->Put(1,0,1); - $Array->Put(1,1,2); - $Array->Put(2,0,3); - $Array->Put(2,1,4); - -=head2 New Variant formatting methods - -There are four new methods for formatting variant values: Currency(), Date(), -Number() and Time(). For example: - - my $v = Variant(VT_DATE, "April 1 99"); - print $v->Date(DATE_LONGDATE), "\n"; - print $v->Date("ddd',' MMM dd yy"), "\n"; - -will print: - - Thursday, April 01, 1999 - Thu, Apr 01 99 - -=head2 new Win32::OLE::NLS methods: SendSettingChange() and SetLocaleInfo() - -SendSettingChange() sends a WM_SETTINGCHANGE message to all top level windows. - -SetLocaleInfo() allows changing elements in the user override section of the -locale database. Unfortunately these changes are not automatically available -to further Variant formatting; you have to call SendSettingChange() first. - -=head2 Win32::OLE::Const now correctly treats version numbers as hex - -The minor and major version numbers of type libraries have been treated as -decimal. This was wrong. They are now correctly decoded as hex. - -=head2 more robust global destruction of Win32::OLE objects - -The final destruction of Win32::OLE objects has always been somewhat fragile. -The reason for this is that Perl doesn't honour reference counts during global -destruction but destroys objects in seemingly random order. This can lead -to leaked database connections or unterminated external objects. The only -solution was to make all objects lexical and hope that no object would be -trapped in a closure. Alternatively all objects could be explicitly set to -C, which doesn't work very well with exception handling. - -With version 0.1007 of Win32::OLE this problem should be gone: The module -keeps a list of active Win32::OLE objects. It uses an END block to destroy -all objects at program termination I the Perl's global destruction -starts. Objects still existing at program termination are now destroyed in -reverse order of creation. The effect is similar to explicitly calling -Win32::OLE->Uninitialize() just prior to termination. - -=head1 Version 0.1005 (changes since 0.1003) - -Win32::OLE 0.1005 has been release with ActivePerl build 509. It is also -included in the I Update. - -=head2 optional DESTRUCTOR for GetActiveObject() GetObject() class methods - -The GetActiveObject() and GetObject() class method now also support an -optional DESTRUCTOR parameter just like Win32::OLE->new(). The DESTRUCTOR -is executed when the last reference to this object goes away. It is -generally considered C to stop applications that you did not -start yourself. - -=head2 new Variant object method: $object->Copy() - -See L. - -=head2 new Win32::OLE->Option() class method - -The Option() class method can be used to inspect and modify -L. The single argument form retrieves -the value of an option: - - my $CP = Win32::OLE->Option('CP'); - -A single call can be used to set multiple options simultaneously: - - Win32::OLE->Option(CP => CP_ACP, Warn => 3); - -Currently the following options exist: CP, LCID and C. - -=cut diff --git a/Master/tlpkg/tlperl/lib/Win32/OLE/TPJ.pod b/Master/tlpkg/tlperl/lib/Win32/OLE/TPJ.pod deleted file mode 100644 index e45770baa42..00000000000 --- a/Master/tlpkg/tlperl/lib/Win32/OLE/TPJ.pod +++ /dev/null @@ -1,798 +0,0 @@ -=pod - -=head1 NAME - -The Perl Journal #10 - Win32::OLE by Jan Dubois - -=head1 INTRODUCTION - -Suppose you're composing a document with Microsoft Word. You want to -include an Excel spreadsheet. You could save the spreadsheet in some -image format that Word can understand, and import it into your -document. But if the spreadsheet changes, your document will be out of -date. - -Microsoft's OLE (Object Linking and Embedding, pronounced "olay") lets -one program use objects from another. In the above scenario, the -spreadsheet is the object. As long as Excel makes that spreadsheet -available as an OLE object, and Word knows to treat it like one, your -document will always be current. - -You can control OLE objects from Perl with the Win32::OLE module, and -that's what this article is about. First, I'll show you how to "think -OLE," which mostly involves a lot of jargon. Next, I'll show you the -mechanics involved in using Win32::OLE. Then we'll go through a Perl -program that uses OLE to manipulate Microsoft Excel, Microsoft Access, -and Lotus Notes. Finally, I'll talk about Variants, an internal OLE -data type. - - -=head1 THE OLE MINDSET - -When an application makes an OLE object available for other -applications to use, that's called OLE I. The program -using the object is called the I, and the application -providing the object is called the I. OLE automation is guided -by the OLE Component Object Model (COM) which specifies how those -objects must behave if they are to be used by other processes and -machines. - -There are two different types of OLE automation servers. I -servers are implemented as dynamic link libraries (DLLs) and run in -the same process space as the controller. I servers -are more interesting; they are standalone executables that exist as -separate processes - possibly on a different computer. - -The Win32::OLE module lets your Perl program act as an OLE -controller. It allows Perl to be used in place of other languages like -Visual Basic or Java to control OLE objects. This makes all OLE -automation servers immediately available as Perl modules. - -Don't confuse ActiveState OLE with Win32::OLE. ActiveState OLE is -completely different, although future builds of ActiveState Perl (500 -and up) will work with Win32::OLE. - -Objects can expose OLE methods, properties, and events to the outside -world. Methods are functions that the controller can call to make the -object do something; properties describe the state of the object; and -events let the controller know about external events affecting the -object, such as the user clicking on a button. Since events involve -asynchronous communication with their objects, they require either -threads or an event loop. They are not yet supported by the Win32::OLE -module, and for the same reason ActiveX controls (OCXs) are currently -unsupported as well. - -=head1 WORKING WITH WIN32::OLE - -The Win32::OLE module doesn't let your Perl program create OLE -objects. What it does do is let your Perl program act like a remote -control for other applications-it lets your program be an OLE -controller. You can take an OLE object from another application -(Access, Notes, Excel, or anything else that speaks OLE) and invoke -its methods or manipulate its properties. - -=head2 THE FIRST STEP: CREATING AN OLE SERVER OBJECT - -First, we need to create a Perl object to represent the OLE -server. This is a weird idea; what it amounts to is that if we want to -control OLE objects produced by, say, Excel, we have to create a Perl -object that represents Excel. So even though our program is an OLE -controller, it'll contain objects that represent OLE servers. - -You can create a new OLE I with C<< Win32::OLE->new >>. -This takes a program ID (a human readable string like -C<'Speech.VoiceText'>) and returns a server object: - - my $server = Win32::OLE->new('Excel.Application', 'Quit'); - -Some server objects (particularly those for Microsoft Office -applications) don't automatically terminate when your program no -longer needs them. They need some kind of Quit method, and that's just -what our second argument is. It can be either a code reference or a -method name to be invoked when the object is destroyed. This lets you -ensure that objects will be properly cleaned up even when the Perl -program dies abnormally. - -To access a server object on a different computer, replace the first -argument with a reference to a list of the server name and program ID: - - my $server = Win32::OLE->new(['foo.bar.com', - 'Excel.Application']); - -(To get the requisite permissions, you'll need to configure your -security settings with F.) - -You can also directly attach your program to an already running OLE -server: - - my $server = Win32::OLE->GetActiveObject('Excel.Application'); - -This fails (returning C) if no server exists, or if the server -refuses the connection for some reason. It is also possible to use a -persistent object moniker (usually a filename) to start the associated -server and load the object into memory: - - my $doc = Win32::OLE->GetObject("MyDocument.Doc"); - -=head2 METHOD CALLS - -Once you've created one of these server objects, you need to call its -methods to make the OLE objects sing and dance. OLE methods are -invoked just like normal Perl object methods: - - $server->Foo(@Arguments); - -This is a Perl method call - but it also triggers an OLE method call -in the object. After your program executes this statement, the -C<$server> object will execute its Foo() method. The available methods -are typically documented in the application's I. - -B By default, all parameters are positional -(e.g. C) rather than named (e.g. -C<< foo(-name => "Yogi", -title => "Coach") >>). The required parameters -come first, followed by the optional parameters; if you need to -provide a dummy value for an optional parameter, use undef. - -Positional parameters get cumbersome if a method takes a lot of -them. You can use named arguments instead if you go to a little extra -trouble - when the last argument is a reference to a hash, the -key/value pairs of the hash are treated as named parameters: - - $server->Foo($Pos1, $Pos2, {Name1 => $Value1, - Name2 => $Value2}); - -B Sometimes OLE servers use -method and property names that are specific to a non-English -locale. That means they might have non-ASCII characters, which aren't -allowed in Perl variable names. In German, you might see C<Öffnen> used -instead of C. In these cases, you can use the Invoke() method: - - $server->Invoke('Öffnen', @Arguments); - -This is necessary because C<< $Server->Öffnen(@Arguments) >> is a syntax -error in current versions of Perl. - -=head2 PROPERTIES - -As I said earlier, objects can expose three things to the outside -world: methods, properties, and events. We've covered methods, and -Win32::OLE can't handle events. That leaves properties. But as it -turns out, properties and events are largely interchangeable. Most -methods have corresponding properties, and vice versa. - -An object's properties can be accessed with a hash reference: - - $server->{Bar} = $value; - $value = $server->{Bar}; - -This example sets and queries the value of the property named -C. You could also have called the object's Bar() method to -achieve the same effect: - - $value = $server->Bar; - -However, you can't write the first line as C<< $server->Bar = $value >>, -because you can't assign to the return value of a method call. In -Visual Basic, OLE automation distinguishes between assigning the name -of an object and assigning its value: - - Set Object = OtherObject - - Let Value = Object - -The C statement shown here makes C refer to the same object as -C. The C statement copies the value instead. (The value of -an OLE object is what you get when you call the object's default -method. - -In Perl, saying C<< $server1 = $server2 >> always creates another reference, -just like the C in Visual Basic. If you want to assign the value -instead, use the valof() function: - - my $value = valof $server; - -This is equivalent to - - my $value = $server->Invoke(''); - -=head2 SAMPLE APPLICATION - -Let's look at how all of this might be used. In Listing: 1 you'll see -F, a program that uses Win32::OLE for an almost-real world -application. - -The developer of this application, Mary Lynch, is a financial futures -broker. Every afternoon, she connects to the Chicago Board of Trade -(CBoT) web site at http://www.cbot.com and collects the time and sales -information for U.S. T-bond futures. She wants her program to create a -chart that depicts the data in 15-minute intervals, and then she wants -to record the data in a database for later analysis. Then she wants -her program to send mail to her clients. - -Mary's program will use Microsoft Access as a database, Microsoft -Excel to produce the chart, and Lotus Notes to send the mail. It will -all be controlled from a single Perl program using OLE automation. In -this section, we'll go through T-Bond. pl step by step so you can see -how Win32::OLE lets you control these applications. - -=head2 DOWNLOADING A WEB PAGE WITH LWP - -However, Mary first needs to amass the raw T-bond data by having her -Perl program automatically download and parse a web page. That's the -perfect job for LWP, the libwww-perl bundle available on the CPAN. LWP -has nothing to do with OLE. But this is a real-world application, and -it's just what Mary needs to download her data from the Chicago Board -of Trade. - - use LWP::Simple; - my $URL = 'http://www.cbot.com/mplex/quotes/tsfut'; - my $text = get("$URL/tsf$Contract.htm"); - -She could also have used the Win32::Internet module: - - use Win32::Internet; - my $URL = 'http://www.cbot.com/mplex/quotes/tsfut'; - my $text = $Win32::Internet->new->FetchURL("$URL/tsf$Contract.htm"); - -Mary wants to condense the ticker data into 15 minute bars. She's -interested only in lines that look like this: - - 03/12/1998 US 98Mar 12116 15:28:34 Open - -A regular expression can be used to determine whether a line looks -like this. If it does, the regex can split it up into individual -fields. The price quoted above, C<12116>, really means 121 16/32, and -needs to be converted to 121.5. The data is then condensed into 15 -minute intervals and only the first, last, highest, and lowest price -during each interval are kept. The time series is stored in the array -C<@Bars>. Each entry in C<@Bars> is a reference to a list of 5 elements: -Time, Open, High, Low, and Close. - - foreach (split "\n", $text) { - # 03/12/1998 US 98Mar 12116 15:28:34 Open - my ($Date,$Price,$Hour,$Min,$Sec,$Ind) = - m|^\s*(\d+/\d+/\d+) # " 03/12/1998" - \s+US\s+\S+\s+(\d+) # " US 98Mar 12116" - \s+(\d+):(\d+):(\d+) # " 12:42:40" - \s*(.*)$|x; # " Ask" - next unless defined $Date; - $Day = $Date; - - # Convert from fractional to decimal format - $Price = int($Price/100) + ($Price%100)/32; - - # Round up time to next multiple of 15 minutes - my $NewTime = int(($Sec+$Min*60+$Hour*3600)/900+1)*900; - unless (defined $Time && $NewTime == $Time) { - push @Bars, [$hhmm, $Open, $High, $Low, $Close] - if defined $Time; - $Open = $High = $Low = $Close = undef; - $Time = $NewTime; - my $Hour = int($Time/3600); - $hhmm = sprintf "%02d:%02d", $Hour, $Time/60-$Hour*60; - } - - # Update 15 minute bar values - $Close = $Price; - $Open = $Price unless defined $Open; - $High = $Price unless defined $High && $High > $Price; - $Low = $Price unless defined $Low && $Low > $Price; - } - - die "No data found" unless defined $Time; - push @Bars, [$hhmm, $Open, $High, $Low, $Close]; - -=head2 MICROSOFT ACCESS - -Now that Mary has her T-bond quotes, she's ready to use Win32::OLE to -store them into a Microsoft Access database. This has the advantage -that she can copy the database to her lap-top and work with it on her -long New York commute. She's able to create an Access database as -follows: - - use Win32::ODBC; - use Win32::OLE; - - # Include the constants for the Microsoft Access - # "Data Access Object". - - use Win32::OLE::Const 'Microsoft DAO'; - - my $DSN = 'T-Bonds'; - my $Driver = 'Microsoft Access Driver (*.mdb)'; - my $Desc = 'US T-Bond Quotes'; - my $Dir = 'i:\tmp\tpj'; - my $File = 'T-Bonds.mdb'; - my $Fullname = "$Dir\\$File"; - - # Remove old database and dataset name - unlink $Fullname if -f $Fullname; - Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $Driver, "DSN=$DSN") - if Win32::ODBC::DataSources($DSN); - - # Create new database - my $Access = Win32::OLE->new('Access.Application', 'Quit'); - my $Workspace = $Access->DBEngine->CreateWorkspace('', 'Admin', ''); - my $Database = $Workspace->CreateDatabase($Fullname, dbLangGeneral); - - # Add new database name - Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $Driver, - "DSN=$DSN", "Description=$Desc", "DBQ=$Fullname", - "DEFAULTDIR=$Dir", "UID=", "PWD="); - -This uses Win32::ODBC (described in TPJ #9) to remove and create -F. This lets Mary use the same script on her workstation -and on her laptop even when the database is stored in different -locations on each. The program also uses Win32::OLE to make Microsoft -Access create an empty database. - -Every OLE server has some constants that your Perl program will need -to use, made accessible by the Win32::OLE::Const module. For instance, -to grab the Excel constants, say C. - -In the above example, we imported the Data Access Object con-stants -just so we could use C. - -=head2 MICROSOFT EXCEL - -Now Mary uses Win32::OLE a second time, to have Microsoft Excel create -the chart shown below. - - Figure 1: T-Bond data generated by MicroSoft Excel via Win32::OLE - - # Start Excel and create new workbook with a single sheet - use Win32::OLE qw(in valof with); - use Win32::OLE::Const 'Microsoft Excel'; - use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG); - - my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT); - $Win32::OLE::LCID = MAKELCID($lgid); - - $Win32::OLE::Warn = 3; - -Here, Mary sets the locale to American English, which lets her do -things like use American date formats (e.g. C<"12-30-98"> rather than -C<"30-12-98">) in her program. It will continue to work even when she's -visiting one of her international customers and has to run this -program on their computers. - -The value of C<$Win32::OLE::Warn> determines what happens when an OLE -error occurs. If it's 0, the error is ignored. If it's 2, or if it's 1 -and the script is running under C<-w>, the Win32::OLE module invokes -C. If C<$Win32::OLE::Warn> is set to 3, C -is invoked and the program dies immediately. - -Now the data can be put into an Excel spreadsheet to produce the -chart. The following section of the program launches Excel and creates -a new workbook with a single worksheet. It puts the column titles -('Time', 'Open', 'High', 'Low', and 'Close') in a bold font on the -first row of the sheet. The first column displays the timestamp in -I format; the next four display prices. - - my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); - $Excel->{SheetsInNewWorkbook} = 1; - my $Book = $Excel->Workbooks->Add; - my $Sheet = $Book->Worksheets(1); - $Sheet->{Name} = 'Candle'; - - # Insert column titles - my $Range = $Sheet->Range("A1:E1"); - $Range->{Value} = [qw(Time Open High Low Close)]; - $Range->Font->{Bold} = 1; - - $Sheet->Columns("A:A")->{NumberFormat} = "h:mm"; - # Open/High/Low/Close to be displayed in 32nds - $Sheet->Columns("B:E")->{NumberFormat} = "# ?/32"; - - # Add 15 minute data to spreadsheet - print "Add data\n"; - $Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars); - $Range->{Value} = \@Bars; - -The last statement shows how to pass arrays to OLE objects. The -Win32::OLE module automatically translates each array reference to a -C, the internal OLE array data type. This translation first -determines the maximum nesting level used by the Perl array, and then -creates a C of the same dimension. The C<@Bars> array -already contains the data in the correct form for the spreadsheet: - - ([Time1, Open1, High1, Low1, Close1], - ... - [TimeN, OpenN, HighN, LowN, CloseN]) - -Now the table in the spreadsheet can be used to create a candle stick -chart from our bars. Excel automatically chooses the time axis labels -if they are selected before the chart is created: - - # Create candle stick chart as new object on worksheet - $Sheet->Range("A:E")->Select; - - my $Chart = $Book->Charts->Add; - $Chart->{ChartType} = xlStockOHLC; - $Chart->Location(xlLocationAsObject, $Sheet->{Name}); - # Excel bug: the old $Chart is now invalid! - $Chart = $Excel->ActiveChart; - -We can change the type of the chart from a separate sheet to a chart -object on the spreadsheet page with the C<< $Chart->Location >> -method. (This invalidates the chart object handle, which might be -considered a bug in Excel.) Fortunately, this new chart is still the -'active' chart, so an object handle to it can be reclaimed simply by -asking Excel. - -At this point, our chart still needs a title, the legend is -meaningless, and the axis has decimals instead of fractions. We can -fix those with the following code: - - # Add title, remove legend - with($Chart, HasLegend => 0, HasTitle => 1); - $Chart->ChartTitle->Characters->{Text} = "US T-Bond"; - - # Set up daily statistics - $Open = $Bars[0][1]; - $High = $Sheet->Evaluate("MAX(C:C)"); - $Low = $Sheet->Evaluate("MIN(D:D)"); - $Close = $Bars[$#Bars][4]; - -The with() function partially mimics the Visual Basic With statement, -but allows only property assignments. It's a convenient shortcut for -this: - - { # open new scope - my $Axis = $Chart->Axes(xlValue); - $Axis->{HasMajorGridlines} = 1; - $Axis->{HasMinorGridlines} = 1; - # etc ... - } - -The C<$High> and C<$Low> for the day are needed to determine the -minimum and maximum scaling levels. MIN and MAX are spreadsheet -functions, and aren't automatically available as methods. However, -Excel provides an Evaluate() method to calculate arbitrary spreadsheet -functions, so we can use that. - -We want the chart to show major gridlines at every fourth tick and -minor gridlines at every second tick. The minimum and maximum are -chosen to be whatever multiples of 1/16 we need to do that. - - # Change tickmark spacing from decimal to fractional - with($Chart->Axes(xlValue), - HasMajorGridlines => 1, - HasMinorGridlines => 1, - MajorUnit => 1/8, - MinorUnit => 1/16, - MinimumScale => int($Low*16)/16, - MaximumScale => int($High*16+1)/16 - ); - - # Fat candles with only 5% gaps - $Chart->ChartGroups(1)->{GapWidth} = 5; - - sub RGB { $_[0] | ($_[1] >> 8) | ($_[2] >> 16) } - - # White background with a solid border - - $Chart->PlotArea->Border->{LineStyle} = xlContinuous; - $Chart->PlotArea->Border->{Color} = RGB(0,0,0); - $Chart->PlotArea->Interior->{Color} = RGB(255,255,255); - - # Add 1 hour moving average of the Close series - my $MovAvg = $Chart->SeriesCollection(4)->Trendlines - ->Add({Type => xlMovingAvg, Period => 4}); - $MovAvg->Border->{Color} = RGB(255,0,0); - -Now the finished workbook can be saved to disk as -F. That file most likely still exists from when the -program ran yesterday, so we'll remove it. (Otherwise, Excel would pop -up a dialog with a warning, because the SaveAs() method doesn't like -to overwrite files.) - - - # Save workbook to file my $Filename = 'i:\tmp\tpj\data.xls'; - unlink $Filename if -f $Filename; - $Book->SaveAs($Filename); - $Book->Close; - -=head2 ACTIVEX DATA OBJECTS - -Mary stores the daily prices in her T-bonds database, keeping the data -for the different contracts in separate tables. After creating an ADO -(ActiveX Data Object) connection to the database, she tries to connect -a record set to the table for the current contract. If this fails, she -assumes that the table doesn't exists yet and tries to create it: - - use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; - - my $Connection = Win32::OLE->new('ADODB.Connection'); - my $Recordset = Win32::OLE->new('ADODB.Recordset'); - $Connection->Open('T-Bonds'); - - # Open a record set for the table of this contract - { - local $Win32::OLE::Warn = 0; - $Recordset->Open($Contract, $Connection, adOpenKeyset, - adLockOptimistic, adCmdTable); - } - - # Create table and index if it doesn't exist yet - if (Win32::OLE->LastError) { - $Connection->Execute(<<"SQL"); - CREATE TABLE $Contract - ( - Day DATETIME, - Open DOUBLE, High DOUBLE, Low DOUBLE, Close DOUBLE - ) - SQL - $Connection->Execute(<<"SQL"); - CREATE INDEX $Contract - ON $Contract (Day) WITH PRIMARY - SQL - $Recordset->Open($Contract, $Connection, adOpenKeyset, - adLockOptimistic, adCmdTable); - } - -C<$Win32::OLE::Warn> is temporarily set to zero, so that if -C<$Recordset->Open> fails, the failure will be recorded silently without -terminating the program. CLastError> shows whether the Open -failed or not. C returns the OLE error code in a numeric -context and the OLE error message in a string context, just like -Perl's C<$!> variable. - -Now Mary can add today's data: - - # Add new record to table - use Win32::OLE::Variant; - $Win32::OLE::Variant::LCID = $Win32::OLE::LCID; - - my $Fields = [qw(Day Open High Low Close)]; - my $Values = [Variant(VT_DATE, $Day), - $Open, $High, $Low, $Close]; - -Mary uses the Win32::OLE::Variant module to store C<$Day> as a date -instead of a mere string. She wants to make sure that it's stored as -an American-style date, so in the third line shown here she sets the -locale ID of the Win32::OLE::Variant module to match the Win32::OLE -module. (C<$Win32::OLE::LCID> had been set earlier to English, since -that's what the Chicago Board of Trade uses.) - - { - local $Win32::OLE::Warn = 0; - $Recordset->AddNew($Fields, $Values); - } - - # Replace existing record - if (Win32::OLE->LastError) { - $Recordset->CancelUpdate; - $Recordset->Close; - $Recordset->Open(<<"SQL", $Connection, adOpenDynamic); - SELECT * FROM $Contract - WHERE Day = #$Day# - SQL - $Recordset->Update($Fields, $Values); - } - - $Recordset->Close; - $Connection->Close; - -The program expects to be able to add a new record to the table. It -fails if a record for this date already exists, because the Day field -is the primary index and therefore must be unique. If an error occurs, -the update operation started by AddNew() must first be cancelled with -C<< $Recordset->CancelUpdate >>; otherwise the record set won't close. - -=head2 LOTUS NOTES - -Now Mary can use Lotus Notes to mail updates to all her customers -interested in the T-bond data. (Lotus Notes doesn't provide its -constants in the OLE type library, so Mary had to determine them by -playing around with LotusScript.) The actual task is quite simple: A -Notes session must be started, the mail database must be opened and -the mail message must be created. The body of the message is created -as a rich text field, which lets her mix formatted text with object -attachments. - -In her program, Mary extracts the email addresses from her customer -database and sends separate message to each. Here, we've simplified it -somewhat. - - sub EMBED_ATTACHMENT {1454;} # from LotusScript - - my $Notes = Win32::OLE->new('Notes.NotesSession'); - my $Database = $Notes->GetDatabase('', ''); - $Database->OpenMail; - my $Document = $Database->CreateDocument; - - $Document->{Form} = 'Memo'; - $Document->{SendTo} = ['Jon Orwant >orwant@tpj.com>', - 'Jan Dubois >jan.dubois@ibm.net>']; - $Document->{Subject} = "US T-Bonds Chart for $Day"; - - my $Body = $Document->CreateRichtextItem('Body'); - $Body->AppendText(<<"EOT"); - I\'ve attached the latest US T-Bond data and chart for $Day. - The daily statistics were: - - \tOpen\t$Open - \tHigh\t$High - \tLow\t$Low - \tClose\t$Close - - Kind regards, - - Mary - EOT - - $Body->EmbedObject(EMBED_ATTACHMENT, '', $Filename); - - $Document->Send(0); - -=head1 VARIANTS - -In this final section, I'll talk about Variants, which are the data -types that you use to talk to OLE objects. We talked about this line -earlier: - - my $Values = [Variant(VT_DATE, $Day), - $Open, $High, $Low, $Close]; - -Here, the Variant() function creates a Variant object, of type C -and with the value C<$Day>. Variants are similar in many ways to Perl -scalars. Arguments to OLE methods are transparently converted from -their internal Perl representation to Variants and back again by the -Win32::OLE module. - -OLE automation uses a generic C data type to pass -parameters. This data type contains type information in addition to -the actual data value. Only the following data types are valid for OLE -automation: - - B - VT_EMPTY Not specified - VT_NULL Null - VT_I2 2 byte signed integer - VT_I4 4 byte signed integer - VT_R4 4 byte real - VT_R8 8 byte real - VT_CY Currency - VT_DATE Date - VT_BSTR Unicode string - VT_DISPATCH OLE automation interface - VT_ERROR Error - VT_BOOL Boolean - VT_VARIANT (only valid with VT_BYREF) - VT_UNKNOWN Generic COM interface - VT_UI1 Unsigned character - -The following two flags can also be used: - - VT_ARRAY Array of values - VT_BYREF Pass by reference (instead of by value) - -B The following conversions are -performed automatically whenever a Perl value must be translated into -a Variant: - - Perl value Variant - Integer values VT_I4 - Real values VT_R8 - Strings VT_BSTR - undef VT_ERROR (DISP_E_PARAMNOTFOUND) - Array reference VT_VARIANT | VT_ARRAY - Win32::OLE object VT_DISPATCH - Win32::OLE::Variant object Type of the Variant object - -What if your Perl value is a list of lists? Those can be irregularly -shaped in Perl; that is, the subsidiary lists needn't have the same -number of elements. In this case, the structure will be converted to a -"rectangular" C of Variants, with unused slots set to -C. Consider this Perl 2-D array: - - [ ["Perl" ], # one element - [1, 3.1215, undef] # three elements - ] - -This will be translated to a 2 by 3 C that looks like this: - - VT_BSTR("Perl") VT_EMPTY VT_EMPTY - VT_I4(1) VT_R8(3.1415) VT_ERROR(DISP_E_PARAMNOTFOUND) - -B Automatic conversion from Variants -to Perl values happens as follows: - - Variant Perl value - VT_BOOL, VT_ERROR Integer - VT_UI1, VT_I2, VT_I4 Integer - VT_R4, VT_R8 Float value - VT_BSTR String - VT_DISPATCH Win32::OLE object - -B This module provides access to the -Variant data type, which gives you more control over how these -arguments to OLE methods are encoded. (This is rarely necessary if you -have a good grasp of the default conversion rules.) A Variant object -can be created with the C<< Win32::OLE::Variant->new >> method or the -equivalent Variant() function: - - use Win32::OLE::Variant; - my $var1 = Win32::OLE::Variant->new(VT_DATE, 'Jan 1,1970'); - my $var2 = Variant(VT_BSTR, 'This is an Unicode string'); - -Several methods let you inspect and manipulate Variant objects: The -Type() and Value() methods return the variant type and value; the As() -method returns the value after converting it to a different variant -type; ChangeType() coerces the Variant into a different type; and -Unicode() returns the value of a Variant object as an object of the -Unicode::String class. - -These conversions are more interesting if they can be applied directly -to the return value of an OLE method call without first mutilating the -value with default conversions. This is possible with the following -trick: - - my $RetVal = Variant(VT_EMPTY, undef); - $Object->Dispatch($Method, $RetVal, @Arguments); - -Normally, you wouldn't call Dispatch() directly; it's executed -implicitly by either AUTOLOAD() or Invoke(). If Dispatch() realizes -that the return value is already a Win32::OLE::Variant object, the -return value is not translated into a Perl representation but rather -copied verbatim into the Variant object. - -Whenever a Win32::OLE::Variant object is used in a numeric or string -context it is automatically converted into the corresponding format. - - printf "Number: %f and String: %s\n", - $Var, $Var; - -This is equivalent to: - - printf "Number: %f and String: %s\n", - $Var->As(VT_R8), $Var->As(VT_BSTR); - -For methods that modify their arguments, you need to use the C -flag. This lets you create number and string Variants that can be -modified by OLE methods. Here, Corel's GetSize() method takes two -integers and stores the C and C dimensions in them: - - my $x = Variant( VT_I4 | VT_BYREF, 0); - my $y = Variant( VT_I4 | VT_BYREF, 0); - $Corel->GetSize($x, $y); - -C support for other Variant types might appear in future -releases of Win32::OLE. - -=head1 FURTHER INFORMATION - -=head2 DOCUMENTATION AND EXAMPLE CODE - -More information about the OLE modules can be found in the -documentation bundled with Win32::OLE. The distribution also contains -other code samples. - -The object model for Microsoft Office applications can be found in the -Visual Basic Reference for Microsoft Access, Excel, Word, or -PowerPoint. These help files are not installed by default, but they -can be added later by rerunning F and choosing I. The object model for Microsoft Outlook can be found on the -Microsoft Office Developer Forum at: -http://www.microsoft.com/OutlookDev/. - -Information about the LotusScript object model can be found at: -http://www.lotus.com/products/lotusscript.nsf. - -=head2 OLE AUTOMATION ON OTHER PLATFORMS - -Microsoft also makes OLE technology available for the Mac. DCOM is -already included in Windows NT 4.0 and can be downloaded for Windows -95. MVS and some Unix systems can use EntireX to get OLE -functionality; see -http://www.softwareag.com/corporat/solutions/entirex/entirex.htm. - -=head1 COPYRIGHT - -Copyright 1998 I. http://www.tpj.com - -This article originally appeared in I #10. It -appears courtesy of Jon Orwant and I. This document -may be distributed under the same terms as Perl itself. -- cgit v1.2.3