Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Perl DBI bind parameter problem

Posted on 2008-10-15
5
Medium Priority
?
1,097 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 2000 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:Tobias
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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…
Six Sigma Control Plans

972 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