RE: Storing and Retrieving Arrays [DBD::Pg]
At 7:57 pm +1000 5/4/01, Neil Lunn wrote: 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. And the array fields seem to be quite limited in usefulness: there doesn't seem to be much that you can do with one beyond SELECTing its individual elements by index -- I can't find a way to DELETE or UPDATE elements. Oh well, it's been educational. Thanks for all the help! -- 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/
RE: Storing and Retrieving Arrays
-Original Message-
From: Sean Carte [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 04, 2001 7:27 PM
To: [EMAIL PROTECTED]
Subject: Storing and Retrieving Arrays
You're right. You have misunderstood the concept
I've asked for help on this topic before, but received no response.
Is there a better way of doing this?
The following is a sample script:
#!/usr/bin/perl -w
# sql_arrays.pl
use strict;
use DBI;
use Fcntl;
my $uid = 'database';
my $db_name = 'test';
my $table = 'array_test1';
my $pass = '';
my $data_source = "dbi:Pg:dbname=$db_name";
my ($dbh, $rv, $str, $cursor);
my @dwarves = qw(Happy Grumpy Dopey Doc);
my $trace_level = 0;
DBI-trace($trace_level);
$dbh = DBI-connect( $data_source, $uid, $pass, {
PrintError = 0,
RaiseError = 0,
AutoCommit = 0
});
unless ( defined($dbh) ) { warn_and_exit("DBI-connect failed:
$DBI::errstr\n"); }
$dbh-do( "CREATE TABLE $table( dwarves TEXT[] )" ) || die
$dbh-errstr;
$dbh-commit || die $dbh-errstr;
$cursor = $dbh-prepare( qq{INSERT INTO $table (dwarves) VALUES (?)} )
|| die $dbh-errstr;
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.
### 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!
$dbh-commit || die $dbh-errstr;
$dbh-disconnect;
$dbh = DBI-connect( $data_source, $uid, $pass, {
PrintError = 0,
AutoCommit = 0
});
unless ( defined($dbh) ) {
warn_and_exit("DBI-connect failed: $DBI::errstr\n");
}
$cursor = $dbh-prepare( "SELECT dwarves FROM $table;" );
$rv = $cursor-execute;
unless ( defined($rv) ) {
$str = $cursor-errstr;
$dbh-disconnect;
warn_and_exit("\$cursor-execute failed: $str\n");
}
#Not this
@dwarves = $cursor-fetchrow_array;
while ( my @row = $cursor-fetchrow_array ) {
push @new_dwarves, $row[0];
}
# or something like that.
$dbh-disconnect;
my $i = 0;
foreach (@dwarves) {
print "dwarf $i: $_\n";
$i++;
}
exit 1;
__END__
What I end up with is the former @dwarves array now a scalar
at $dwarves[0].
Have I misunderstood the purpose of the array in SQL or am I
just misusing it?
--
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.
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/
