DBI: alter table - 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_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 } );