| Subject: | error: create database |
Using RHEL 6 (2.6.32-220) connecting to a pgsql 8.3 database. Using perl.5.10.1 dbd.2.15.1
(from rpm) and perl-dbi.1.609 (from rpm).
Trying to move admin functions in to a perl script. Errors are generated when attempting to
issue a "create database" sql command. The error indicates that the sql can not run inside a
transaction. I have autocommit set to 0 so it should not be a transaction block.
Code:
sub create_db {
my ($user) = @_;
# create user $user with password '$pass' connection limit 10
# create database $user with owner $user
# grant connect on database $user to $user
my $dbname = "admin";
my $host = "db";
my $port = "5433";
my $results = "";
my $results_err = "";
my $debug = 1;
print "create_db:1 start create db\n" if $debug;
# dbh - database handle
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port", 'gallantm',
'bigtree', {AutoCommit => 0});
if (!defined $dbh) {
print "create_db:can not connect to $host\n";
$results_err = $results_err."create_db:can not connect to $host\n";
}
#
use DBD::Pg qw(:pg_types);
#
my $sql = "create database $user with owner $user";
print "create_db:2:$sql\n" if $debug;
$dbh->do($sql);
$results_err = $results_err."create_db:2.1:".$dbh->errstr.":".$dbh-
Show quoted text
>err().":".$DBI::errstr."\n";
$sth->execute();
$rv = $sth->err;
if ($rv) {
print "create_db:4:".$sth->errstr.":".$DBI::errstr."\n";
$results_err = $results_err."create_db:4:".$sth->errstr.":".$DBI::errstr."\n";
}
else {
while ( @row = $sth->fetchrow()) {
print "create_db:row:$#row\n" if $debug;
$results = $row[0];
}
}
$rv = $dbh->disconnect;
$rv = $sth->err;
if ($rv) {
print "create_db:5:".$sth->errstr."\n";
$results_err = $results_err."create_db:5:".$sth->errstr."\n";
}
return $results_err;
}
Errors:
create_db:1 start create db
create_db:2:create database test with owner test
DBD::Pg::db do failed: ERROR: CREATE DATABASE cannot run inside a transaction block at
./mkdbacct line 105.
DBD::Pg::st execute failed: Cannot call execute on a disconnected database handle at
./mkdbacct line 114.
create_db:4:Cannot call execute on a disconnected database handle:Cannot call execute on a
disconnected database handle
create_db:5:Cannot call execute on a disconnected database handle
main:6:create_db:2.1:ERROR: CREATE DATABASE cannot run inside a transaction
block:7:ERROR: CREATE DATABASE cannot run inside a transaction block
create_db:4:Cannot call execute on a disconnected database handle:Cannot call execute on a
disconnected database handle