Battling Getopt::Long

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

Hello oh great Monks

I am trying to get information out of a MS-SQL 2000 DB. I was able to connect to the database until I changed my script to accept inputs for server, user and password.

I am obviously not using it correctly, but I have gotten hopelessly confussed reading the help and looking at SoPW.

I tried using Dumper to see what the hash holding the input had in and all it shows is:

 $VAR1 = '1';<BR>
$VAR2 = undef;
[download]

I'm sure I messed up something. Any help would be great.

use strict;
use DBI;
use Data::Dumper;
use Getopt::Long qw (GetOptions);

my $help = "0";
my $server = "0";
my $user = "0";
my $password = "0";

#Set the options for GetOpt
Getopt::Long::Configure( "pass_through", "no_ignore_case", "prefix_pat
+tern=(-|\/)" );

#What to expect on the command line
my %opts = ();
my %opts=(GetOptions(
           'help|?'     => \$help,
           'server'     => \$server,
           'user'     => \$user,
           'password'     => \$password
          ));
print Dumper(%opts); #DEBUG: See what's there
          
if($opts{help}) {
    &Help;
}

my $mytime=(time);
my $database="TNGDB";

#Start Logging to log file..
open (LOGF, ">>%0../../$mytime.log") || die "Could not open the Log Fi
+le: $!\n";

#Connect to MSSQL Server
my $dsn="driver={SQL Server};Server=$server;database=$database;UID=$us
+er;PWD=$password;";
my $dbh=DBI->connect("dbi:ODBC:$dsn", $user, $password) || die &Logger
+("...Unable to connect. Reason: $DBI::errstr");
&Logger ("...Success: connected to database $server\\$database\n");

#Get Data
#Insert the select statements here....

$sth->finish();
$dbh->disconnect();




########################################
##            SUBS                    ##
########################################

# Message Logging 
sub Logger {
    my $text = shift;
    my $rc = 0;
    if (print LOGF "$text\n") {
        $rc=1;
    }
    return ($rc);
}

#CLI Help
sub Help {
    print "Invalid option.\n","The valid options are: \n";
    print "MakeCVM.exe -h (or ? or help) displays this.\n";
    print "MakeCVM.exe -server=<Server> -user=<SQL User> -password=<Pa
+ssword>";
}
[download]

The error I get running this is:

DBI connect('driver={SQL Server};Server=0;database=TNGDB;UID=0;PWD=0;'
+,'0',...)
failed:  at E:\MakeCVM\makeCVM-SQL.pl line 41
1 at E:\MakeCVM\makeCVM-SQL.pl line 41.
[download]

Thank you in advance oh Great and wonderous Monks!!

Comment on Battling Getopt::Long Select or Download Code

Replies are listed 'Best First'.
Re: Battling Getopt::Long by Tomte (Priest) on May 11, 2004 at 12:33 UTC
Change `my %opts = (); my %opts=(GetOptions( 'help\|?' => \$help, 'server' => \$server, 'user' => \$user, 'password' => \$password ));` [download] to `GetOptions( 'help:+' => \$help, 'server=s' => \$server, 'user=s' => \$user, 'password=s' => \$password, );` [download] and use the variables directly, not the hash. Edit: fixed the code according to the follow-up postings. should get you started regards, tomte An intellectual is someone whose mind watches itself. -- Albert Camus	[reply] [d/l] [select]
Re: Re: Battling Getopt::Long by Smaug (Pilgrim) on May 11, 2004 at 12:53 UTC
Thanks Tomte!! That's working great!! From your code could you explain the `'help:i'`. From what I have read to date, the `:` means it is optional and the `i` sets it as an integer. Does that mean that if it exists, it will set `$help` to `1` otherwise it will be `0`? Thanks again!	[reply] [d/l] [select]
Re: Re: Re: Battling Getopt::Long by Tomte (Priest) on May 11, 2004 at 12:57 UTC
ah no, my mistake... change the `i` to a `+` and test for `$help>0` regards, tomte An intellectual is someone whose mind watches itself. -- Albert Camus	[reply]
Re: Battling Getopt::Long by Abigail-II (Bishop) on May 11, 2004 at 12:32 UTC
GetOptions doesn't return a hash, it returns a status indicating whether parsing of the options went succesfully. The way you call GetOptions, values will be set in the variables you pass references - for instance, if a password is given, the value will appear in `$password`. If you want all the values in a hash, use a reference to a hash as first argument (and remove the other references). Abigail	[reply]
Re: Battling Getopt::Long by eserte (Deacon) on May 11, 2004 at 12:37 UTC
Getopt::Long::GetOptions does not return an option hash, it rather returns a boolean value showing if all entered options were valid. The actual option values are stored in the variables $help, $server etc. If you want the option values in a hash, then you have to use the alternate form of GetOptions: `GetOptions(\%opts, "help", "server=s", ...) or die "usage..."`	[reply] [d/l]
Re: Battling Getopt::Long by AcidHawk (Vicar) on May 11, 2004 at 15:25 UTC
A Few changed and your code will work.. Try changing `my %opts=(GetOptions( 'help\|?' => \$help, 'server' => \$server, 'user' => \$user, 'password' => \$password ));` [download] to `GetOptions (\%opts, qw( help\|h\|? server=s user=s password=s ));` [download] also put everything after `&Help; }` [download] into and elsif statement something like `&Help; } elsif( defined $opts{server} && defined $opts{user} && defined $opts{p +assword} ) { my $mytime=(time); ... } else { &Help; }` [download] Then for your database connection bit try using the following: `my $dbh; my $DSN = "driver={SQL Server};Server=$opts{server};database=$database +;"; if ($dbh = DBI->connect("dbi:ODBC:$DSN","$opts{user}","$opts{password} +")) { &Logger ("...Success: connected to database $opts{server}\\$databa +se\n"); } else { &Logger("ERROR - Cannot connect to DB: $dbh->errstr()\n"); }` [download] also you can't have `$sth->finish` if you havent prepared or ecexuted a $sth statement I.e. `my $query = "select * from sometable"; my $sth = $dbh->prepare($query); for my $rec (@records) { $sth->execute(@$rec); } $setsth->finish();` [download] Type Stuff - HTH Update:Sorry Typo... change `$setsth->execute(@$rec);` to `$sth->execute(@$rec);` ----- Of all the things I've lost in my life, its my mind I miss the most.	[reply] [d/l] [select]
Re: Re: Battling Getopt::Long by Smaug (Pilgrim) on May 12, 2004 at 08:48 UTC
Thanks AcidHawk. A quick question... From the code you so kindly provided, why is it `$setsth->finish();` and not `$sth->finish();` Thanks again. Dazed and confused, Smaug.	[reply] [d/l] [select]
Re: Battling Getopt::Long by Anonymous Monk on May 11, 2004 at 13:40 UTC
As already said Getopt does not return a hash. It also does not return whether the 'options were valid'. It 'parses the command line arguments that are present in @ARGV and sets the option variable to the value 1 if the option did occur on the command line. Otherwise, the option variable is not touched.' Good Luck!	[reply]
Re: Battling Getopt::Long by Abigail-II (Bishop) on May 11, 2004 at 13:44 UTC
2 paragraphs further is the relevant information: `GetOptions()` will return a true value if the command line could be processed successfully. Otherwise, it will write error messages to STDERR, and return a false result. Abigail	[reply]


Perl-Sensitive Sunglasses
	PerlMonks