Table creation

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

Dear Monks

I want to run two $sql assigned create table statements and run them one after the other, adding tables to the same database. My program creates the first table but not the second. Any suggestions why?


my $sql = qq{ CREATE TABLE Table_A (
#SQL goes in here

)};

$dbh->do($sql);
    
$dbh->disconnect();

    if ($table_name = $Table_4){
      print "hereb\n";
      
      my $sql = qq{ CREATE TABLE Second_table (                       
+                                  
#SQL statement goes in here

)};
      
      my $dbh->do($sql);
      
      
      
      
      $dbh->disconnect();
    }
[download]

Comment on Table creation Download Code

Replies are listed 'Best First'.
Re: Table creation by edan (Curate) on Nov 24, 2003 at 13:39 UTC
try changing: `my $dbh->do($sql);` to this: `$dbh->do($sql);` [download] I would get rid of the disconnect as well, since I assume you still want to be connected to the database when you do this. Also you probably want to change this: `if ($table_name = $Table_4){` [download] to something more like this: `if ($table_name eq $Table_4){` [download] -- 3dan	[reply] [d/l] [select]
Re: Table creation by moxliukas (Curate) on Nov 24, 2003 at 13:17 UTC
Well, you have `$dbh->disconnect();` before the second sql statement, so you are no longer connected to the database when you try to create the second table.	[reply] [d/l]
Re: Re: Table creation by Win (Novice) on Nov 24, 2003 at 13:33 UTC
I tried removing that before and it gave me the error message: `Can't call method "do" on an undefined value at ....` [download]	[reply] [d/l]
Re: Re: Re: Table creation by Roger (Parson) on Nov 25, 2003 at 01:00 UTC
`my $dbh->do(...)` Aha... You didn't open your database handle, did you? ;-) You have to make sure there is a database connection before issuing SQL commands. If you do not call DBI->connect, your database handle will be undefined. In your case, you have created a new lexical with `my $dbh` in your second part of the code, which will be undefined unless you call the connect, or use the existing handle by dropping that my in front of `$dbh->do(...)` (assuming that you have dropped that first `$dbh->disconnect` of course). Here's an example on how to connect to the database using DBI and DBD. `#!/usr/bin/perl -w use strict; use DBI; use DBD::Sybase; my $dbh = DBI->connect("dbi:Sybase:server=XXXX;database=XXXX", "USER", +"PASSWD") or die "Failed to connect to the database!"; my $sth = $dbh->prepare( qq{ SQL statement } ); $sth->execute(); my @column_headings = @{$sth->{NAME}}; while (my @row = $sth->fetchrow()) { # Data elements $row[0] ... $row[-1] ... } $sth->finish; $dbh->disconnect;` [download]	[reply] [d/l] [select]