Re: Invalid character value for cast specification

It looks to me like you have one dot too many in OptionsDB..MyOpt .

Anyway, FWIW, here's a slightly shortened version of your script.

use strict;
use warnings;
use HTML::TableExtract;
use DBI;

my $dbh1 = DBI->connect('DBI:ODBC:OptionsDB', 'sa', '')
  or die "Couldn't connect to database: " . DBI->errstr;
my $sth1 = $dbh1->prepare(<<'EOSQL')
INSERT INTO OptionsDB.MyOpt
  (Security, Volume, Previous, TodayHigh, TodayLow, TodayClose, Change
+)
VALUES (?,?,?,?,?,?,?)
EOSQL
  or die "Couldn't prepare statement: " . $dbh1->errstr;

open CSEFILE, 'SCE.htm' or die "Failed to read SCE.htm\n";
my $response = do { local $/; <CSEFILE> };
close CSEFILE;

# Do you need the following s///'s transformations if you are going to
# have HTML::TableExtract parse $response ???
$response =~ s/.*Statistics//s;
$response =~ s/Volume Weighted Average.*//s;
$response =~ s/[\r\n]//gs;
$response =~ s/[ \t]+/ /gs;

my $te = HTML::TableExtract->new( headers =>
                                  qw( Security Volume Previous High
                                      Low Today's Change ) );
$te->parse( $response );
for my $ts ( $te->table_states ) {
  for my $row ( $ts->rows ) {
    print join( "\t", @$row ), "\n";
    $sth1->execute( @$row )
      or die "Couldn't execute statement: " . $sth1->errstr;
  }
}
[download]

the lowliest monk

Comment on Re: Invalid character value for cast specification Download Code

Replies are listed 'Best First'.
Re^2: Invalid character value for cast specification by eibwen (Friar) on Apr 21, 2005 at 05:14 UTC
Slightly shorter still... `for my $ts (HTML::TableExtract->new(headers=>qw(Security Volume Previo +us High Low Today's Change)) ->parse($response)->table_states ) { for my $row ( $ts->rows ) { print join("\t", @$row), "\n"; $sth1->execute( @$row ) or die "Couldn't execute statement: " . $sth1->errstr; } }` [download] Presuming that the `s///` transformations may be unnecessary as tlm pointed out: `open CSEFILE, 'SCE.htm' or die "Failed to read SCE.htm\n"; for my $ts (HTML::TableExtract->new(headers => qw(Security Volume Prev +ious High Low Today's Change)) ->parse(do { local $/; <CSEFILE> })->table_states) { for my $row ( $ts->rows ) { print join("\t", @$row), "\n"; $sth1->execute( @$row ) or die "Couldn't execute statement: " . $sth1->errstr; } } close CSEFILE;` [download] As to whether there are more efficent ways to do this, given the context you may be interested in the Finance modules.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: Invalid character value for cast specification
by eibwen (Friar) on Apr 21, 2005 at 05:14 UTC

for my $ts (HTML::TableExtract->new(headers=>qw(Security Volume Previo
+us High Low Today's Change))
  ->parse($response)->table_states ) {

  for my $row ( $ts->rows ) {
    print join("\t", @$row), "\n";
    $sth1->execute( @$row )
      or die "Couldn't execute statement: " . $sth1->errstr;
  }
}
[download]

Presuming that the s/// transformations may be unnecessary as tlm pointed out:

open CSEFILE, 'SCE.htm' or die "Failed to read SCE.htm\n";

for my $ts (HTML::TableExtract->new(headers => qw(Security Volume Prev
+ious High Low Today's Change))
  ->parse(do { local $/; <CSEFILE> })->table_states) {

  for my $row ( $ts->rows ) {
    print join("\t", @$row), "\n";
    $sth1->execute( @$row )
      or die "Couldn't execute statement: " . $sth1->errstr;
  }
}

close CSEFILE;
[download]

As to whether there are more efficent ways to do this, given the context you may be interested in the Finance modules.

[reply]
[d/l]
[select]