How would you catch this error?

LanceDeeply has asked for the wisdom of the Perl Monks concerning the following question:

I'm using open to have microsoft's osql.exe execute some sql for me and save it down to file. This works fine except when the file it saves down to is open. ( like in excel for instance. ) At that point, I get an error printed out to console:

Cannot open output file - c:\test\data\extract.csv
No such file or directory
[download]

Now I know it's the wrong error getting printed to console, I just want to catch the fact that something bad happenned. how would guys do it?

sub SqlScriptResultsToFile
{
    my %args = (
        db_srv                => '',
        db_name                => '',
        db_user                => '',
        db_pass                => '',
        sql                    => '',
        filepath            => '',
        separator            => ',',
        columnwidth            => 20000,
        @_
    );
    
    my @errors;

    #  
    #  arg validation ...
    #  

    my $osql = "|c:\\Progra~1\\MSSQL7\\Binn\\osql.exe -n -U$args{db_us
+er} -P$args{db_pass} -S$args{db_srv} -d$args{db_name} -o$args{filepat
+h}";
    $osql .= " -s$args{separator}"        if ( $args{separator}   ne '
+' );
    $osql .= " -w$args{columnwidth}"    if ( $args{columnwidth} ne '' 
+);

    if ( ! open(PROGRAM,$osql) )
    {
        push @errors, "invalid filepath [$args{$filepath}]";
        return ( success => 0, errors => \@errors );
    }
    
    print PROGRAM $args{sql};

    close PROGRAM;

    return ( success => 1, errors => \@errors );
}
[download]

Comment on How would you catch this error? Select or Download Code

Replies are listed 'Best First'.
Re: How would you catch this error? by hardburn (Abbot) on May 09, 2003 at 14:47 UTC
Why are you using an external program? Perl's DBI module has an ODBC driver, which should do what you want. You mentioned Excel, so I should also point you to Spreadsheet::WriteExcel, Spreadsheet::ParseExcel, and DBD::Excel. ---- I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident. -- Schemer Note: All code is untested, unless otherwise stated	[reply]
Re: Re: How would you catch this error? by LanceDeeply (Chaplain) on May 09, 2003 at 14:57 UTC
Usually, I do use DBI. I use this for when I have to run sql scripts that I have no control over. The scripts can get a little involved, for instance creating temp tables to build the result sets and declaring variables. Basically- stuff that should be done in a stored proc, but isn't.	[reply]
Re: How would you catch this error? by BrowserUk (Patriarch) on May 09, 2003 at 16:05 UTC
One way would be to redirect the output from the command line and capture it. Take a look at IPC::Open3 as a substitute for using open. It allows you to capture STDOUT and STDERR to different streams. Assuming that the exe in question does something as sensible as issuing the errors on a different handle to the normal output. Perhaps easier, although it does a leave a window of opportunity for error, would be to check the status of the file before issuing the command. You might need to use sysopen or Win32API::File::createFile with the appropriate set of share attributes to detect of the file is already in use before you issue the command. Examine what is said, not who speaks. "Efficiency is intelligent laziness." -David Dunham "When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong." -Richard Buckminster Fuller	[reply]
Re: Re: How would you catch this error? by LanceDeeply (Chaplain) on May 09, 2003 at 18:06 UTC
Perhaps easier, although it does a leave a window of opportunity for error, would be to check the status of the file before issuing the command. thanks, I like it- I like it alot!	[reply]