Create a table in Mysql with perl

[padawan_linuxero]

Hello !!! For a project in the work I need to create a few tables as backups so I need to create them from perl, in mysql I can go and do this :

DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable
  SELECT T.time, T.name, T.last, P.company, P.wife 
  FROM T 
  JOIN P ON T.wife = P.wife and T.company = P.company;

but when I try to do it in perl did not work Why?
On perl I try to put every thing like this

$sql="DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable
  SELECT T.time, T.name, T.last, P.company, P.wife 
  FROM T 
  JOIN P ON T.wife = P.wife and T.company = P.company";
$sth = $dbh->prepare($sql);
$sth->execute ();
$sth->finish ();
$dbh->disconnect ();

[ikegami]

In your client, ; means execute. In DBI, execute is used to execute statements.

$dbh->do("DROP TABLE IF EXISTS newtable");
$dbh->do("
   CREATE TABLE newtable
   SELECT T.time, T.name, T.last, P.company, P.wife 
     FROM T
     JOIN P ON T.wife = P.wife AND T.company = P.company
");

(do calls prepare and execute for you.)

[mje]

I think you really want to split this up into a sequence of 2 do method calls:

eval {
  $dbh->do(q{drop table if exists newtable});
};
$dbh->do(q{create table newtable select t.time, t.name, t.last, t.comp
+any, t.wife from t join p on t.wife = p.wife and t.company = p.compan
+y'});
$dbh->disconnect;

Although it is possible with some DBI::DBDs to batch up statements separated with ';' it is not portable and not guaranteed to work.

Of course, this is just advice and not necessarily the solution to the problem since you did not say what error you were getting doing it your way.

[KurtSchwind]

I agree with the other comments. All you need to do is change your code to:

$dbh->do("DROP TABLE IF EXISTS newtable");
$sql="CREATE TABLE newtable
  SELECT T.time, T.name, T.last, P.company, P.wife 
  FROM T 
  JOIN P ON T.wife = P.wife and T.company = P.company";
$sth = $dbh->prepare($sql);
$sth->execute ();
$sth->finish ();
$dbh->disconnect ();


Breaking the statements into 2 distinct calls.