> -----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.
