Sean,
After trying this out a few times I do get what you are after.
Presently your script would result in
dwarf 0: {"Happy Grumpy Dopey Doc"}
This is actually sort of okay. If it were right you would be getting output
like this
dwarf 0: {"Happy","Grumpy","Dopey","Doc"}
This is still a scalar in one field, but this seems to be actuall how
postgres does it. The same query directly into psql would return this.
dwarves
----------------------------------
{"Happy","Grumpy","Dopey","Doc"}
(1 row)
However if the query were different:
test=# select dwarves[1], dwarves[2] from array_test1
test-# \g
dwarves | dwarves
---------+---------
Happy | Grumpy
(1 row)
Which is very interesting because the field name is returned as exactly the
same even though we explicitly selected the elements.
I tend to not like this method. Certainly the duplication of field names
tends to break code. If you are really looking at storing array values in a
field for some purpose, you would be better off breaking up the scalar in
perl.
If the idea is just day[1], day[2], etc then name fields day_1, day_2, etc.
This approach tends to be more portable between databases. Not everything
supports array fields and if it does it usually isn't like this.
Neil
> -----Original Message-----
> From: Sean Carte [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 04, 2001 10:44 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Storing and Retrieving Arrays
>
>
> 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/>
>
__________________________________________________________________________
Please Note :
Only the intended recipient is authorised to access or use this e-mail. If
you are not the intended recipient,
please delete this e-mail and notify the sender immediately. The contents
of this e-mail are the writer's
opinion and are not necessarily endorsed by the Gunz Companies unless
expressly stated.
We use virus scanning software but exclude all liability for viruses or
similar in any attachment.
