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!!

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]
      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]

        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]
      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]
      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]

Back to Seekers of Perl Wisdom