Thanks for the help Neil and Bodo.
However, I do not want to store a Perl array in multiple rows, but in
a single SQL array.
At 7:49 pm +1000 4/4/01, Neil Lunn wrote:
>[...]
>You're right. You have misunderstood the concept
>[...]
>Maybe you want to have an array field? You would still have to insert each
>element of the array, not the whole array. Below are alterations for listed
>records. Otherwise ->
>
>$cursor = $dbh->prepare( qq{INSERT INTO $table (dwarves[1], dwaves[2] #etc)
> VALUES (?,?)}
> ) || die $dbh->errstr;
>
>for (my $i=0; $i <= @dwarves; $++) {
> $cursor->bind_param($i, $dwarves[$i]);
>}
>
># Or something similar.
I'm heading back to perldoc DBI to try and figure out what's going on here.
>### The listed concept
># Not this
>> $cursor->execute("{@dwarves}") || die $dbh->errstr;
>
># But instead
>foreach my $dwarf (@dwarves) {
> $cursor->execute("$dwarf") || die $dbh->errstr;
>}
># Actually Tolkien says the correct plural or dwarf is dwarfs! go figure!
(And many other strange things besides; but either form is
acceptable, I promise .... On the other hand, Damian Conway uses
'dwarfs', if I repent will it improve my Perl?)
>[...]
>#Not this
>> @dwarves = $cursor->fetchrow_array;
>
>while ( my @row = $cursor->fetchrow_array ) {
> push @new_dwarves, $row[0];
>}
># or something like that.
>[...]
Using psql, I can do the following:
test=> CREATE TABLE array_test1 (
test(> dwarves TEXT[4]
test(> );
CREATE
test=> INSERT INTO array_test1 VALUES (
test(> '{"Happy", "Grumpy", "Dopey", "Doc"}'
test(> );
INSERT 37962 1
test=> SELECT * FROM array_test1;
dwarves
----------------------------------
{"Happy","Grumpy","Dopey","Doc"}
(1 row)
To access any single member of the array, with psql:
test=> SELECT dwarves[3] FROM array_test1;
dwarves
---------
Dopey
(1 row)
And similarly using DBI:
$cursor = $dbh->prepare( "SELECT dwarves[3] FROM $table;" );
$cursor->execute;
my $dwarf = $cursor->fetchrow_array;
I can recreate the psql code in DBI with arrays by converting the
arrays into comma-delimited lists and inserting those:
$dbh->do( "CREATE TABLE $table( dwarves TEXT[4] )" ) || die $dbh->errstr;
$dbh->commit || die $dbh->errstr;
$cursor = $dbh->prepare( qq{INSERT INTO $table (dwarves)
VALUES (?)} ) || die $dbh->errstr;
my $dwarf_list = ary2list(@dwarves);
$cursor->execute("{$dwarf_list}") || die $dbh->errstr;
sub ary2list {
my ($item, $array_str);
foreach $item (@_) {
$array_str .= '"' . $item . '", ';
}
chop $array_str;
chop $array_str;
return $array_str;
}
Then reversing the procedure involves deleting the beginning and end
two characters and splitting the lists on '","', which restores my
array.
But this seems seriously error-prone. Surely there must be a better way?
--
My brain hurts!
SeanC
Mediatek Training Institute
26 Crart Ave., Berea, Durban, South Africa
<-- New Address
phone: +27 (0)31 202 1886 [EMAIL PROTECTED] <--
New Phone Number
fax: +27 (0)31 202 1767
<-- New Fax Number
<http://members.nbci.com/s_carte/>
