Perl - CGI::Application - Create session variables from database

Question

I have a bunch of configuration variables stored within a database, key value pairs accessable via the following query:

select * from conf_table;

I want to load these key/value pairs into a CGI::Applicaiton session. At the moment this is manually done (so not from database, but hardcoded) via

$self->session->param( NAME => VALUE );

For a bunch of key value pairs. Is there a more sensible way do to this with DBI and some form of loop?

Thanks

It's an Oracle database, I can connect to it via DBI, I'm essentially asking how to do this with DBI (the most sensible way to do so) and how to assign the results to CGI::App::Sessions, again the most sensible way. — Dr.Avalanche, Apr 12 '13 at 10:54

score 3 · Accepted Answer · answered Apr 12 '13 at 10:58

3

You mean something like this?

my $sth = $dbh->prepare("select key, value from mytable");
$sth->execute;
$sth->bind_columns(\(my ($key, $value)));
while ($sth->fetch) {
  $self->session->param($key => $value);
}

answered Apr 12 '13 at 10:58

Dave Sherohman

45,363
14
64
102

This seems to be exactly what I'm looking for. Thanks – Dr.Avalanche Apr 12 '13 at 11:03

score 1 · Answer 2 · answered Apr 13 '13 at 18:48

DBI has some convenience methods that make this sort of work simpler. Try selectall_arrayref:

my $configs = $dbh->selectall_arrayref(
    'SELECT * FROM conf_table',
    { Slice => {} }, # make each row a hash
);
$self->session->param($_->{key} => $_->{value}) for @$configs;

Perl - CGI::Application - Create session variables from database

2 Answers2