RE: HP-UX - Perl DBI - mainframe issue

[Jesse, Rich]

To see if the problem's in the Oracle side or the DBI side, does the SQL
statement run directly from SQL*Plus on the client?  Have you tried using
the WE8ISO8859P1 NLS_LANG setting?  Don't use the EBCDIC one on HP -- it
won't work.

A few other questions/points.  Why are you using TO_CHAR?  Or, instead of
having Perl do your accumulating, you could be using the SQL SUM and COUNT
functions.  Like this:

        "select to_char(SUM(sea_temperature),'99999'),
                count(*) from midasvu.marine_ob ". 
                             "where OB_TIME between 
         to_date('01-JAN-1989 0000',". 
               "'dd-mon-yyyy hh24mi') and to_date('01-JAN-1989 2359',". 
               "'dd-mon-yyyy hh24mi')"; 

Also, when using 8-bit charsets, you'll need to make sure that your term on
HP is set up to handle it.  You'll need at least "stty cs8 -istrip".

Rich Jesse                           System/Database Administrator
[EMAIL PROTECTED]              Quad/Tech International, Sussex, WI USA


> -----Original Message-----
> From: Birchall, Austen [mailto:austen.birchall@;metoffice.com]
> Sent: Thursday, October 17, 2002 4:16 AM
> To: 'Birchall, Austen'; [EMAIL PROTECTED]
> Subject: RE: HP-UX - Perl DBI - mainframe issue
> 
> 
> Following Davids suggestion (thanks) this is the code that is 
> causing the
> problem - please note I am very much the 'middle man' here 
> but if anyone has
> any further suggestions/questions I can pass them on to the developer 
> 
> Thanks
> 
> Austen
> 
> 
> #!/usr/bin/perl -w 
> 
> # Connect to MIDAS 
> # 
> # Note that this script requires a range of Oracle 
> Environment variables to 
> #  be set before starting, and will only work on workstations 
> where the 
> #  oracle client software is installed. I.e. hc1900. 
> # 
> 
> use strict; 
> use DBI; 
> 
> # Choose production (orap) or test (orat) databases 
> $ENV{TWO_TASK}='orap'; 
> #$ENV{'NLS_LANG'}='american_america.we8iso8859p1'; 
> #$ENV{'NLS_LANG'}='american_america.we8ebcdic1047'; 
> 
> # Get user name and password from users .netrc 
> my ($UserName,$Password); 
> open(DIN,"$ENV{HOME}/.netrc") or die "Can't open $ENV{HOME}/.netrc"; 
> while(<DIN>) { 
>  my @fields=split; 
>  if($fields[1] eq 'ukmet') { 
>     $UserName = $fields[3]; 
>     $Password = $fields[5]; 
>  } 
> } 
> close(DIN); 
> unless( defined($UserName) && defined($Password)) { 
>    die "Failed to extract COSMOS username and password from
> $ENV{HOME}/.netrc"; 
> } 
> 
> # Connect to the database 
> my $dbh = DBI->connect("DBI:Oracle:", 
>                         $UserName,$Password, 
>    {PrintError => 1, RaiseError => 1}) or 
>    die $DBI::errstr; 
>   
> # Do some processing - let's get some marine data. 
> my $Sql_statement = "select to_char(sea_temperature,'99999') from
> midasvu.marine_ob ". 
>                     "where OB_TIME between 
> to_date('01-JAN-1989 0000',". 
>       "'dd-mon-yyyy hh24mi') and to_date('01-JAN-1989 2359',". 
>       "'dd-mon-yyyy hh24mi')"; 
>   
> my $Statement = $dbh->prepare($Sql_statement); 
> $Statement->execute(); 
> 
> # Process the results - let's calculate the mean. 
> my $count = 0; 
> my $Accumulate=0; 
> while(my @sstA = $Statement->fetchrow_array()) { 
>    unless(defined($sstA[0])) { next; } 
>    $Accumulate += $sstA[0]; 
>    $count++; 
> } 
> $Statement->finish(); 
> 
> printf "Mean SST was %5.2f, from %6d
> measurements\n",$Accumulate/$count,$count; 
> 
> # Close the database connection 
> $dbh->disconnect(); 
> exit(0);