开发者

DBI: alter table - question

开发者 https://www.devze.com 2023-02-16 20:58 出处:网络
#!/usr/bin/env perl use warnings; use 5.012; use DBI; my $dsn = \"DBI:Proxy:hostname=horst;port=2000;dsn=DBI:ODBC:db1.mdb\";
#!/usr/bin/env perl
use warnings;
use 5.012;
use DBI;

my $dsn = "DBI:Proxy:hostname=horst;port=2000;dsn=DBI:ODBC:db1.mdb";
my $dbh = DBI->connect( $dsn, undef, undef ) or die $DBI::errstr;
$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 0;

my $my_table = 'my_table';
eval{ $dbh->do( "DROP TABLE $my_table" ) };
$dbh->do( "CREATE TABLE $my_table" );

my $ref = [ qw( 1 2 ) ];

for my $col ( 'col_1', 'col_2', 'col_3' ) {
    my $add = "$col INT";
    $dbh->do( "ALTER TABLE $my_table ADD $add" );
    my $sql = "INSERT INTO $my_table ( $col ) VALUES( ? )";
    my $sth = $dbh->prepare( $sql );
    $sth->bind_param_array( 1, $ref );
    $sth->execute_array( { ArrayTupleStatus => \my @tuple_sta开发者_如何学Gotus } );
}

my $sth = $dbh->prepare( "SELECT * FROM $my_table" );
$sth->execute();
$sth->dump_results();

$dbh->disconnect;

This script outputs:

'1', undef, undef
'2', undef, undef
undef, '1', undef
undef, '2', undef
undef, undef, '1'
undef, undef, '2'
6 rows

How do I have to change this script to get this output:

'1', '1', '1'
'2', '2', '2'
2 rows


Do this in two steps:

Create the 3 columns
insert data in them 


You prepare a SQL statement 3 times and execute twice for values 1,2 so you get 6 rows. I don't know how to answer your question of how do you change it to get 2 rows since we've no idea what you are trying to achieve. Without knowing what you are trying to achieve I'd be guessing but the following results in the output you wanted:

my $ref = [ qw( 1 2 ) ];

for my $col ( 'col_1', 'col_2', 'col_3' ) {
    my $add = "$col INT";
    $dbh->do( "ALTER TABLE $my_table ADD $add" );
}
$sql = "INSERT INTO $my_table ( col_1, col_2, col_3 ) VALUES( ?,?,? )";
my $sth = $dbh->prepare( $sql );
$sth->bind_param_array( 1, $ref );
$sth->bind_param_array( 2, $ref );
$sth->bind_param_array( 3, $ref );
$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号