vsudha has asked for the wisdom of the Perl Monks concerning the following question:

hi all,

This is the code I am trying to execute.

my $sth=$dbh->prepare("create table test(id integer)");
$sth->execute();

And I am getting the error as "create table command is not allowed with in a multi-statement transaction".
But I am not issuing multiple statements and I didn't keep it in a transaction. Then why is is that I am getting this error. When I searched about this error I came to know that if "ddl in tran" is set to true then this error can be handled. Is there no other solution to this one, like executing as a single statement and not keeping it in a transaction. Can someone help me out in this. Thanks in advance. --Sudha
  • Comment on create table is not allowed with in a multi statement transaction

Replies are listed 'Best First'.
Re: create table is not allowed with in a multi statement transaction
by moritz (Cardinal) on Jan 23, 2008 at 10:38 UTC
    Afaict the prepare creates a stored procedure (or something similar) (at least with some database systems), and execute executes that.

    So it seems that your database doesn't allow table creation in stored procedures.

    A workaround is to try $dbh->do("create table test(id integer)");

[reply]
[d/l]
[select]
      hi,

      But with $dbh->do("create table #test(id integer)" also I am getting the same error, until and unless I keep AutoCommit attribute to 1. Is it mandatory to set AutoCommit value of dbh to be one to resolve this error.

      Thanks,
      Sudha
[reply]
        create is an DDL statement which is enabled with autocommit so u need to set the value before executing the create.
[reply]

Back to Seekers of Perl Wisdom