Solved

Perl DBI bind parameter problem

Posted on 2008-10-15
5
1,057 Views
Last Modified: 2012-05-05
I have a script that I'm using to change my database passwords for multiple databases.  Most of it works, but I can't seem to get the bind parameters on the $sth->execute statement to work.  Here is the code:

use DBI;

$oldpwd = $ARGV[0];
$newpwd = $ARGV[1];

$whichhost = `hostname -s`;
chop($whichhost);

if($whichhost =~ /^new-cleaner/) {
        open(P, "pdbases.txt") or die "Cannot open database hosts file\n";
}

while(<P>) {
        $dbase = $_;
        chop($dbase);

        $dbh = DBI->connect("dbi:Oracle:$dbase","gerdesk","$oldpwd")
                or die "Can't connect to Oracle database: $dbh->errstr\n";

        $sth = $dbh->prepare( 'alter user GERDESK identified by ? replace ?' );
        $sth->execute( $newpwd, $oldpwd ) or die "Can't execute SQL statement: $dbh->errstr\n";

        print "Successfully updated password on $dbase from $oldpwd to $newpwd\n";
}

if($whichhost =~ /^new-cleaner/) {
        close(P);
}

The $oldpwd goes in fine, and I connect to the database, but when I pass the $oldpwd as a bind parameter to the execute, I get this lovely error:

DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName) [for Statement "alter user GERDESK identified by ? replace ?" with ParamValues: :p1='A11tyd00z!', :p2=undef] at changedbpasswd.pl line 23, <P> line 1.

So $oldpwd is undef in the execute.  Why?  
0
Comment
Question by:texasreddog
  • 3
5 Comments
 
LVL 14

Expert Comment

by:tomaugerdotcom
ID: 22722917
Do you need the oldpwd at all?

dbh->do("alter user GERDESK identified by ?", $newpwd) or die $DBI::errstr;
0
 
LVL 14

Accepted Solution

by:
tomaugerdotcom earned 500 total points
ID: 22722949
Alternatively, just for debugging purposes, have you tried to see whether this works without the bind variables, by just putting the variables into the prepare string?

$dbh->prepare("alter user GERDESK identified by $newpwd replacing $oldpwd");
0
 
LVL 10

Expert Comment

by:MadShiva
ID: 22723072
HI !



$oldpwd = $ARGV[0];
$newpwd = $ARGV[1];

$whichhost = `hostname -s`;
chop($whichhost);

if($whichhost =~ /^new-cleaner/) {
        open(P, "pdbases.txt") or die "Cannot open database hosts file\n";
}

while(<P>) {
        $dbase = $_;
        chop($dbase);

        $dbh = DBI->connect("dbi:Oracle:$dbase","gerdesk","$oldpwd")
                or die "Can't connect to Oracle database: $dbh->errstr\n";

        $sth = $dbh->prepare( 'alter user GERDESK identified by ?' );
        $sth->execute($newpwd) or die "Can't execute SQL statement: $dbh->errstr\n";

        print "Successfully updated password on $dbase from $oldpwd to $newpwd\n";
}

if($whichhost =~ /^new-cleaner/) {
        close(P);
}


Try this, sorry but i can't test now.


Best Regards
0
 
LVL 14

Expert Comment

by:tomaugerdotcom
ID: 22723175
Yeah, what I said.
0
 

Author Comment

by:texasreddog
ID: 22724166
well, the bind parameters don't work.  when I inserted the variables back into the sql query and just ran $sth->execute(), I didn't have a problem.  I'll just forget about the bind parameters for this script.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Strange perl issue 6 128
pattern matching in perl 2 103
Exchange 2010 Transport Rule Regex 28 108
What does msixpodualn stand for and how do I read this "qr/STRING/msixpodualn"? 4 68
I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question