CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Windows 7, MS Access 2013, DBI question

 



PapaGeek
User

Feb 24, 2014, 7:43 AM

Post #1 of 4 (23078 views)
Windows 7, MS Access 2013, DBI question Can't Post

(There is an answer to this question further down this thread!)

This is my first attempt to access a database with Perl. I have MS Access 2013 on my Windows 7 PC and I am used to working with it. I created a database using access. The filename is testDB.accdb.

I found the following code on line and gave it a try.

Code
use Modern::Perl '2013'; 

my $dbLocation = 'C:\Users\Sandy\Documents\TestDB.accdb';
my $connectAccess = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$dbLocation ";
my $dbAccess = DBI->connect("dbi:ODBC:$connectAccess") or die $DBI::errstr;


and I got the following errors:


Code
Name "DBI::errstr" used only once: possible typo at 
C:/Users/Sandy/workspace/Quickie/QuickTest.pl line 5.
Can't locate object method "connect" via package "DBI" at
C:/Users/Sandy/workspace/Quickie/QuickTest.pl line 5.


Iím a relative newbie to Perl, what am I missing?


(This post was edited by PapaGeek on Feb 25, 2014, 4:26 AM)


FishMonger
Veteran / Moderator

Feb 24, 2014, 7:54 AM

Post #2 of 4 (23075 views)
Re: [PapaGeek] Windows 7, MS Access 2013, DBI question [In reply to] Can't Post

Looks like you forgot to load the DBI module.


Code
use Modern::Perl '2013'; 
use DBI;
use DBD::ODBC; # this line is optional



PapaGeek
User

Feb 24, 2014, 11:42 AM

Post #3 of 4 (23065 views)
Re: [FishMonger] Windows 7, MS Access 2013, DBI question [In reply to] Can't Post

I managed to get MS Access working with Perl on Window7. As a background:

Perl Ėver gives me:
This is perl 5, version 16, subversion 3 (v5.16.3) built for MSWin32-x86-multi-thread Ö etc.
Iím running Strawberry Perl.

C:\Program Files\Microsoft Office 15\root\office15>notepad msaccess.exe.manifest
Shows the line:
<assemblyIdentity processorArchitecture="X86" type="win32" name="msaccess" version="15.0.0.0">

And the ďcomputer iconĒ properties page shows:
Windows 7 professional
System type: 64-bit Operating System

So, it looks like Iím running 32 bit Perl and 32 bit Access on a 64 bit operating system!

I couldnít get ODBC to work in the control panel so I searched the web and found the trick of saving my database as mdb 2010 compatible and running the 32 bit version of the control panel ODBC to create an ODBC named ďFundsĒ. Still not sure how to work with the new native .accdb files.

c:\windows\sysWOW64\odbcad32.exe

And I managed to read my first database records into Perl with the following code:

Code
 use Modern::Perl '2013'; 
use DBI;

my $dbh = DBI->connect('dbi:ODBC:Funds', '', '', { RaiseError => 1, odbc_cursortype => 2});
my $sth = $dbh->prepare("SELECT * from Accounts");
$sth->execute;
my @row;
while (@row = $sth->fetchrow_array) {
print join(", ", @row);
print "\n";
}

And it returned

Code
 2, Sandy 401K, Finiti, 2004-01-05 00:00:00 
3, Shirley 401K, Regional, 2011-07-09 00:00:00


Iím probably not going to use * as a selection criteria, but is there a way to determine what fields were returned in what order?

It looks like all fields are returned as strings. What is the best way to cast each field into its proper data type? In my case: number, string, string, date


(This post was edited by PapaGeek on Feb 25, 2014, 4:29 AM)


PapaGeek
User

Feb 24, 2014, 12:48 PM

Post #4 of 4 (23058 views)
Re: [PapaGeek] Windows 7, MS Access 2013, DBI question [In reply to] Can't Post

Previous post changed from a question to an answer, . . . and of course a couple of extra follow up questions


(This post was edited by PapaGeek on Feb 25, 2014, 4:30 AM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives