Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Extracting data from an Ms ACCESS query

by blackadder (Hermit)
on Jun 11, 2004 at 11:27 UTC ( #363370=perlquestion: print w/replies, xml ) Need Help??

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

Respected Monks,

This is very basic stuff to most of you so please accept my apologies.

I have a query in an MSAccess database like so;
group_id business_unit_name group_name sub_group id app +lication_name 282 Corporate Services CS Helpdesk 3658 Adobe Acrobat +Full 282 Corporate Services CS Helpdesk 4120 Internet Explo +rer 282 Corporate Services CS Helpdesk 4197 Lotus Notes 282 Corporate Services CS Helpdesk 4230 Microsoft Acce +ss 282 Corporate Services CS Helpdesk 4291 Netscape Suite 282 Corporate Services CS Helpdesk 5023 Microsoft Offi +ce 2003 282 Corporate Services CS Helpdesk 5026 Falcon 282 Corporate Services CS Helpdesk 5038 Microsoft Outl +ook 282 Corporate Services CS Helpdesk 5054 Placeware Plug +in 282 Corporate Services CS Helpdesk 5090 Volo View Expr +ess 282 Corporate Services CS Helpdesk 5091 Real Networks +Real Player 283 Corporate Services EMD 3658 Adobe Acro +bat Full 283 Corporate Services EMD 4120 Internet E +xplorer 283 Corporate Services EMD 4197 Lotus Note +s 283 Corporate Services EMD 4230 Microsoft +Access 283 Corporate Services EMD 4291 Netscape S +uite 283 Corporate Services EMD 4856 Hummingbir +d Exceed 8.0 IED 283 Corporate Services EMD 5023 Microsoft +Office 2003 283 Corporate Services EMD 5026 Falcon 283 Corporate Services EMD 5038 Microsoft +Outlook 283 Corporate Services EMD 5054 Placeware +Plugin 283 Corporate Services EMD 5090 Volo View +Express 283 Corporate Services EMD 5091 Real Netwo +rks Real Player 285 Corporate Services Procurement 3658 Adobe Acrobat +Full 285 Corporate Services Procurement 4120 Internet Explo +rer 285 Corporate Services Procurement 4191 Lofmap 285 Corporate Services Procurement 4230 Microsoft Acce +ss
Depending on which group, I need to select their asociated applications; example;

The application list for Procurement should be Adobe Acrobat Full,Internet Explorer,Lofmap and MS Access.

I have this code below, however not sure on how to proceed, or how to print out the OBBCE hash and select certain areas from that query.
use strict; use warnings 'all'; use Win32; use Win32::ODBC; my $dsn = "Hydra_FE"; my $qry = "dbo_vfunction_application"; my $Hydra_db = new Win32::ODBC($dsn) || die "\nError=>\t$^E : $!\n"; print "\nError: $^E\n" if (! $Hydra_db); if ( $Hydra_db->Sql("SELECT * FROM $qry")) { print "Cool\n"; print $Hydra_db; }
Thnaks and I am abit snowed on.


Replies are listed 'Best First'.
Re: Extracting data from an Ms ACCESS query
by Joost (Canon) on Jun 11, 2004 at 11:44 UTC
    Hmm... it looks to me that you should read up on database design and SQL. You can solve it like this in Perl, but I wouldn't. Oh and I would use DBD::ODBC instead, so your code would be a bit more portable.

    I'd make several tables for business_unit, group, subgroup and application, referering to each other where appropriate. and a cross table between subgroup and application.

    Then do something like:

    my $result = $dbh->selectall_arrayref( "SELECT FROM application, group, subgroup, app_sub +group WHERE = app_subgroup.application AND app_subgroup.subgroup = AND = AND'Procurement'" ); foreach (@$result) { print @$_; }
    With the current model it would look like:
    my $result = $dbh->selectall_arrayref( "SELECT DISTINCT(application) FROM table_name WHERE group_name='Procurement'" ); foreach (@$result) { print @$_; }
    Which is simpler, but will get you in trouble later on when your table starts getting bigger and bigger and people start spelling Internet Explorer as "Internt Explorer".

      Thanks, I see your point but to start off with I will carry on with ODBC.

      Anyway, this is what I tried;
      use Win32; use Win32::ODBC; my $dsn = "Hydra_FE"; my $qry = "dbo_vfunction_application"; my $dbh = new Win32::ODBC($dsn) || die "\nError=>\t$^E : $!\n"; print "\nError: $^E\n" if (! $dbh); my $result = $dbh->selectall_arrayref("SELECT DISTINCT(application) FR +OM dbo_vfunction_application WHERE group_name='Procurement'"); foreach (@$result) { print @$_; }
      and this was what I got;
      Can't locate auto/Win32/ODBC/ in @INC (@INC contains: C: +/Perl/lib C:/Perl/site/lib .) at U:\scripts\ line 14
      Any thoughts? Thanks

        Yes, I tried this;
        my $result = $dbh->sql("SELECT * FROM $qry WHERE group_name='Procureme +nt'"); foreach (@$result) { print @$_; }
        and I got this;
        Can't use string ("1") as an ARRAY ref while "strict refs" in use at U +:\scripts\ line 15.
        Hmmmm. Holy ones please HELP!

Re: Extracting data from an Ms ACCESS query
by McMahon (Chaplain) on Jun 11, 2004 at 19:51 UTC
    Win32::ODBC tanked early for me. When I sent email to Dave Roth, I got this back:

    "To be honest we have not dealt with Win32::ODBC for several years now.
    Most users are migrating to use DBI and the DBD::ODBC modules instead.
    Since our perl projects are unfunded we have very little time to devote to them.
    If you want to explore this further the source code for Win32::ODBC is on our FTP site. "

    I've been using DBI ever since.
Re: Extracting data from an Ms ACCESS query
by periapt (Hermit) on Jun 11, 2004 at 19:00 UTC
    You've made a good start. $Hydra_db, at this point, contains a reference to the ODBC connection structure and not the results of the query itself. One issue you have is that the Sql statement from WinODBC returns the SQL error number or false if there was not error. Thus, in your code, the if statement block will run only if there is an error. Try this code instead.
    if($Hydra_db->Sql("SELECT * FROM $qry")){ print "SQL Error: ".$Hydra_db.Error()."\n"; }
    Now, once you have a successful query, you need to retrieve the results. This is achieved in two steps. Step 1, fetch the row from the ODBC recordset. Step 2, move the contents of the row into a variable. I usually do it this way.
    if($Hydra_db->Sql("SELECT * FROM $qry")){ print "SQL Error: ".$Hydra_db.Error()."\n"; }else{ # keep retrieving rows until nothing is left while($Hydra_db->FetchRow()){ # pull one record into a variable for use @groupapps = $Hydra_db->Data(); print @groupapps,"\n"; } }
    You can also pull the data into a hash. I'm not aware of any pod or other freely available doc that covers Win32 ODBC-maybe another Monk has a suggestion-but Dave Roth's Win32 Perl Programming: The Standard Extentions, 2nd Ed and Martin Brown's Active Perl Developer's Guide are two books that provide a lot of useful information on using Win32ODBC.

    We are drowning in information and starving for knowledge - Rutherford D. Rogers
    What good is knowledge if you have to pull teeth to get it - anonymous

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://363370]
Approved by sunadmn
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (1)
As of 2023-09-21 20:09 GMT
Find Nodes?
    Voting Booth?

    No recent polls found