|
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.
Blackadder
|
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 application.name FROM application, group, subgroup, app_sub
+group WHERE
application.id = app_subgroup.application AND
app_subgroup.subgroup = subgroup.id AND
subgroup.group = group.id AND group.name='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".
| [reply]
[d/l]
[select] |
|
|
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/selectall_a.al in @INC (@INC contains: C:
+/Perl/lib C:/Perl/site/lib .)
at U:\scripts\odbc1.pl line 14
Any thoughts? Thanks
Blackadder
| [reply]
[d/l]
[select] |
|
|
I used DBI style method calls for running the sql, you need to find an equivalent method call for the ODBC module you are using. (Maybe $dbh->Sql ? I don't know, I don't use windows)
| [reply] |
|
|
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\odbc1.pl line 15.
Hmmmm. Holy ones please HELP!
Blackadder
| [reply]
[d/l]
[select] |
|
|
|
|
|
|
|
|
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.
| [reply] |
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.
PJ
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
| [reply]
[d/l]
[select] |
|
|
