Solved

Perl DBI bind parameter problem

Posted on 2008-10-15
5
1,043 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
OTRS Installation 1 276
use perl to insert into MySQL database 9 121
Perl output collect 7 64
collecting information 2 129
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now