DBI:更改表-问题

Question

#!/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_status } );
}

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

$dbh->disconnect;

此脚本输出：

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

如何更改此脚本才能获得以下输出：

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

Answer 1

分两步完成此操作：

Create the 3 columns
insert data in them 

Answer 2

您准备一条SQL语句3次，然后对值1、2执行两次，这样就得到了6行。我不知道如何回答您的问题，即如何将其更改为2行，因为我们不知道您想要实现什么。在不知道您要实现的目标的情况下，我只能猜测您想要的输出结果如下：

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 } );