Ramprasad wrote:
> Hello all,
>
> I have a mysql database and I want to create tables
> w1 w2 w3 w4 ... w30
>
> Can I use the prepare and execute methods of DBI
> I am getting an error because DBI is quoting the table name and
> Mysql is not accepting it
>
> This is my code
>
> #!/usr/bin/perl
> use DBI;
> use strict;
>
> my $dbh = DBI->connect( 'DBI:mysql:database=words;host=;port=3306',
> "", "" ) or die "Can't connect to Mysql database:
> $DBI::errstr\n";
>
> my $sql="CREATE TABLE ? (word VARCHAR( ? ) DEFAULT 'a' NOT NULL
> ,'wkey' VARCHAR( ? ) DEFAULT 'a' NOT NULL ,PRIMARY KEY ( word ))";
> my $sth = $dbh->prepare( $sql ) || die $dbh->errstr;
>
> my($i);
> foreach $i ( 1..30){
> $sth->execute("w$i",($i+1),($i+1)) || die $dbh->errstr;
> print "Created w$i\n";
> }
>
>
> Error
>
> DBD::mysql::st execute failed: You have an error in your SQL syntax.
> Check the manual that corresponds to your MySQL server version for
> the right syntax to use near ''w1' (word VARCHAR( 2 ) DEFAULT 'a'
> NOT NULL ,'wkey' VARCHAR( 2 at ./createsql.pl line 13.
>
>
>
> The problem is with the execute statement
> I know I can use the do statement and put the entire sql in the
> loop but
> Is there a way I can so it using prepare and execute
Hi Ram.
If all you're doing is to create a number of tables then I'd
get Perl to write the whole SQL statement for you and just
$dbh-do($sql)
Even so, what you've written is weird. You're creating a set of thirty
tables, each with two character columns with a maximum length equal
to the table number. Is this just an example?
To show you what I mean, here's a sample. It prints out
each SQL statement instead of executing 'do' on the string.
HTH,
Rob
use strict;
use warnings;
foreach my $n ( 1 .. 30 ) {
my $sql = <<END;
CREATE TABLE w$n (
word VARCHAR($n) DEFAULT 'a' NOT NULL,
wkey VARCHAR($n) DEFAULT 'a' NOT NULL,
PRIMARY KEY (word)
)
END
print $sql, "\n"; # $dbh->do($sql);
}
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
