Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Do you know where your variables are?
 
PerlMonks  

Create a table in Mysql with perl

by padawan_linuxero (Scribe)
on Nov 07, 2007 at 20:14 UTC ( #649561=perlquestion: print w/ replies, xml ) Need Help??
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;
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 ();

Comment on Create a table in Mysql with perl
Select or Download Code
Re: Create a table in Mysql with perl
by ikegami (Pope) 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 ");

    (do calls prepare and execute for you.)

Re: Create a table in Mysql with perl
by mje (Deacon) 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;

    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.

Re: Create a table in Mysql with perl
by KurtSchwind (Hermit) 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 ();

    Breaking the statements into 2 distinct calls.
    --
    I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://649561]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (19)
As of 2014-04-16 13:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (427 votes), past polls