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

Is it possible to do multiple or compound statements with DBI.
For example, 
$rv = $dbh->do("CREATE TABLE foo; CREATE TABLE bar")

[download]
Of course, you can always do it in two lines. I'm trying to write some code that makes up for mysql's lack of subselects.
The theory is I write the sql assuming that you can have subselects and I have a function that converts the statement into multiple ones that creates a temporary table as the subselect.

The problem is that I am also intending to use DBIx::Pretty for its way cool variable substitution stuff. Unfortunately (and sanely), this expects one statement at a time.

thanks in advance

tonyday

Replies are listed 'Best First'.
Re: Multiple SQL statements
by chromatic (Archbishop) on Jul 19, 2001 at 09:46 UTC
    I sent the following to MySQL through the DBI and got an error: 
    $sth = $dbh->prepare("describe node; describe user");

# result

DBD::mysql::st execute failed: You have an error in your SQL syntax 
near '; describe user' at line 1

    [download]
    Looks like you need to return multiple statements, or rewrite your queries to use a self join. (That may work for your purposes, and it may not.)
[reply]
[d/l]
      (Too lazy to try this out) It is possible that the prepare failed because it is trying to prepare a single SQL statement, but that a $dbh->do("sql 1; sql2") might work.

      This would probably work on the example given (create FOO; create BAR;), but not on the problem to be solved (lack of sub-select), since do isn't going to help on selects.

[reply]
Re: Multiple SQL statements
by MZSanford (Curate) on Jul 19, 2001 at 13:36 UTC
    I believe this will depend on the DBD driver and Database. If the database would not let you run "CREATE TABLE foo; CREATE TABLE bar", then DBI will not either. I tested with my rather old version od MySql, and even from the myswl client, this did not work. I could be wrong, but from reading the DBD::Sybase source, and assuming others are similarly setup, i would have to say ... my gut says "maybe". All depends on the database.
    OH, a sarcasm detector, that’s really useful
[reply]
Re: Multiple SQL statements
by Wookie (Beadle) on Jul 19, 2001 at 14:19 UTC
    On Sybase, just splitting such statements with a new line seemed to work fine. I'm not too familiar with mysql - but unless you would normally use the ';' command terminator when you are running one command - then it looks to me like it won't help with multiple commands.

    Here's some sample code for Sybase anyway - hope this might help a little:
    #!/usr/bin/perl -w

use DBI;
use strict;

my $foo=DBI->connect('DBI:Sybase:server=FOO', user, pass)||die "Connec
+tion Failed! $DBI::errstr\n";
my $sql="CREATE TABLE dbo.foo\n(bar int NOT NULL)\nCREATE TABLE dbo.fo
+otoo\n(bar int NOT NULL)";
my $fooexec=$foo->prepare($sql);
$fooexec->execute()||die "SQL failed: $!\n";
$fooexec->finish();
$foo->disconnect();

exit (0);

    [download]
    UPDATE: Thanks bobione - fixed that problem :)
    game(Wookie,opponent) eq 'Wookie' ? undef $problem : remove_limbs(arms,opponent);
[reply]
[d/l]
      Hi, just a rectification ?!

      Isn't it $foo instead of $daffy ?
      It seems to be a "cut/past" error.

      BobiOne KenoBi ;)

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom