Generated: Tue Feb 2 17:54:21 2010 from autoax01.pl 2006/06/12 6.7 KB.
#!/Perl # ### from : http://www.foo.be/docs/tpj/issues/vol3_2/tpj0302-0008.html # 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); # } # # $Win32::OLE::Warn is temporarily set to zero, so that if $Recordset->Open fails, the # failure will be recorded silently without terminating the program. # Win32::OLE->LastError shows whether the Open failed or not. LastError returns # the OLE error code in a numeric context and the OLE error message in a # string context, just like Perl's $! 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 $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. ($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 $Recordset->CancelUpdate; otherwise # the record set won't close. # ######################################################################### my $FileName = 'c:\tmp\tpj\T-Bonds.mdb'; my $Contract = "C1234567"; my @Bars = (); my $text = "03/12/1998 US 98Mar 12116 15:28:34 Open\n"; $text .= "03/12/1998 US 98Mar 12117 15:43:34 Open\n"; $text .= "03/12/1998 US 98Mar 12118 15:58:34 Open\n"; $text .= "03/12/1998 US 98Mar 12120 16:03:34 Open\n"; $text .= "03/12/1998 US 98Mar 12118 16:18:34 Open\n"; $text .= "03/12/1998 US 98Mar 12110 16:23:34 Open\n"; $text .= "03/12/1998 US 98Mar 12110 16:38:34 Open\n"; 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) { if (defined $Time) { print "Pushing $hhmm $Open $High $Low $Close ...\n"; } 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; print "Pushing $hhmm $Open $High $Low $Close ...\n"; push @Bars, [$hhmm, $Open, $High, $Low, $Close]; 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); } # 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]; { 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;