Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi! I am using OLE (don't ask me why (grin)) to connect and access information from my MS Access database, and needed to to get "hash reference" from the rows in the database. Being fairly new to Perl, I'm already lost .... any input would be appreciated. Thanks. 

my $dbh; 

$dbh = CreateObject OLE "ADODB.Connection" or die "Can't create connec
+tion to DataBase: $!" unless $dbh;

$dbh->Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=db/textbookhu
+b.mdb");

my $rs = CreateObject OLE "ADODB.Recordset" or die "can not create rec
+ordset"; 

my $sqlstatement = "SELECT username, sessionID FROM user WHERE id=$id"
+;  # Prepare and execute SQL statement

$rs = $dbh->Execute($sqlstatement);

[download]

Basically, I want to username and sessionID into a hash reference to pass elsewhere ...... ?! :-(

Thanks in adance.

Replies are listed 'Best First'.
Re: Database (OLE) Hashrefs ?
by poj (Abbot) on Jan 12, 2003 at 12:27 UTC
    This is very basic with minimal error handling to get you going but DBI would be better. 
    #!c:/perl/bin/perl -w
use strict;
use Win32::OLE;
# Set up a DSN using 
# Settings->Control Panel->ODBC Data Sources

# make a connection
# query the database
# close the connection
my $conn = Win32::OLE->new("ADODB.Connection"); 
$conn->open("myDSN1");
my $id = "103" ; # test value
my $hash_ref = getData($conn,$id);
$conn->close;

# check the result
print $$hash_ref{'username'}." : ".$$hash_ref{'sessionID'};


sub getData {

  my ($conn,$id) = @_; 
  my %data=(); # return values

  # prepare the SQL Statement
  my $sql = qq( SELECT username,sessionID FROM user WHERE id='$id' );
  my $rs = $conn->execute($sql);

  # catch any error
  if  (!$rs) {
    my $errors = $conn->errors();
    print "Errors:\n";
    foreach my $error (keys %$errors) {
        print $error->{Description}, "\n";
    }
    die;
  }

  # get the results
  # use until loop and movenext
  # if expecting >1 record
  # and add id into the %data structure
  #until ($rs->EOF){
    for ('username','sessionID'){
      $data{$_}  = $rs->fields($_)->value,
    }
  # $rs->movenext; 
  #}
  $rs->close;
  return \%data;
}

    [download]

    poj
[reply]
[d/l]
Re: Database (OLE) Hashrefs ?
by theorbtwo (Prior) on Jan 12, 2003 at 08:16 UTC

    Actualy, I think I will ask... why are you using OLE/ADO, instead of the much more perlish DBI? Your terminology ($dbh, prepare, execute) seems DBIish, and you'll be able to get help from the much larger population of perl monks who know DBI, rather then only those who know ADO. Also, your last sentance makes no sense whatsoever.

    (I was about to say that that's why you probably havn't gotten any replies yet, but then relized it's only been 10 minutes at 3 AM.)

    Warning: Unless otherwise stated, code is untested. Do not use without understanding. Code is posted in the hopes it is useful, but without warranty. All copyrights are relinquished into the public domain unless otherwise stated. I am not an angel. I am capable of error, and err on a fairly regular basis. If I made a mistake, please let me know (such as by replying to this node).

[reply]
Re: Database (OLE) Hashrefs ?
by CountZero (Bishop) on Jan 12, 2003 at 11:44 UTC

    Using DBI and DBD::ADO would seem a beter solution, but of course TIMTOWTDI.

    However, in order for us to help you, can you tell us what modules you are using (e.g. Win32::OLE or Win32::ODBC or ...)?

    Update: POJ said it all, so no need to respond to this note anymore.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

Back to Seekers of Perl Wisdom