Create a table in Mysql with perl

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

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;
[download]

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 ();
[download]

Comment on Create a table in Mysql with perl Select or Download Code

Replies are listed 'Best First'.
Re: Create a table in Mysql with perl by ikegami (Patriarch) on Nov 07, 2007 at 20:39 UTC
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 ");` [download] (`do` calls `prepare` and `execute` for you.)	[reply] [d/l] [select]
Re: Create a table in Mysql with perl by mje (Curate) on Nov 07, 2007 at 22:14 UTC
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;` [download] 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.	[reply] [d/l]
Re: Create a table in Mysql with perl by KurtSchwind (Chaplain) on Nov 07, 2007 at 23:36 UTC
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 ();` [download] Breaking the statements into 2 distinct calls. -- I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.	[reply] [d/l]