Site Navigation
|
|
PGTS Humble BlogThread: Tips/Tricks For Programming etc |
|
 |
Gerry Patterson. The world's most humble blogger |
| Edited and endorsed by PGTS, Home of the world's most humble blogger | |
| | |
Using ODBC with Windows Seven. |
|
Chronogical Blog Entries:
|
|
| | |
Date: Sat, 14 Jan 2012 00:16:35 +1100Recently, I needed to get data from an old MS Access database and insert it into a Linux apache database. The most difficult part was extracting the data. It seemed that the logical platform of choice was a windows workstation ... However the only windows workstation available was Windows Seven ... |
|
Of course despite all the hype (and the new name) Windows Seven is little more than Vista SP2 ... But at least Microsoft has fixed many of the more glaringly obvious flaws that shipped with Vile Vista version 1.
And so I got some hands-on experience with Windows Seven.
Remarkably Microsoft now have a switch user function that actually works! It is ironic that Ubuntu have had a perfectly functional switch user function for many years ... But it is now broken due to poor integration with the new Unity interface (this may be due to hardware limitations --- I have not yet tested Unity on modern hardware).
Of course Windows Seven is not without its foibles ... All of the menu items have been moved around so you can't find anything any more ... For example, the task scheduler is now hidden in "Administrative Tools".
And having found the task scheduler, it is difficult to actually schedule a task. My first attempt resulted in the following rather arcane message ...
An error has occurred for task weekly_upload. One or more of the specified arguments are not correct
It seems that when creating a scheduled task you must tick one of the boxes Run only when user is logged on or Do not store password. The task will only have access to local computer resources.
And Some further efforts have made been toward fixing the eternally flaky task scheduler ... And it certainly needs it! ... You might recall the original version which only ran as administrator ... The version that behaved in a very weird manner unless someone was "logged on" ... The strange and wacky problems with networked drives ... etc ... etc. With the new version, tasks seem to run more reliably in the background. But weird file locking errors do occasionally crop up ... And it seems that when it comes to running tasks without a terminal, the geniuses at Microsoft still just don't get it!
Cygwin seems to install ok in Windows 7. However the ftp command no longer works from the cygwin bash prompt ... This might be something to with the way that the ftp command handles termio on the password command (Net::FTP still works in perl ... And so does the .netrc file ... See below for user home paths) ... So any one foolish enough to write bash scripts that called windows FTP from cygwin are bound to be dissapointed ...
In order to get the (Version 7.9.1) cygwin paths working with Windows 7, I found it easiest to use the following commands in conjunction with /etc/fstab:
mkdir /pc_home mount C:/Users/SP01 /pc_home # this creates a version of fstab for your user ID mount -m > /etc/fstab.d/$USER
Alternatively, if you wanted to, you could probably mount C:/Users as /home.
It also seems that the brains trust at Microsoft have been pondering about security and how to handle "privileged mode" (without sudo). Files such as the hosts file (which is now stored in C:\Windows\System32\drivers\etc\hosts), can only be modified if the editor is Run as administrator (e.g. right-click on Gvim and choose Run as administrator).
Finally Microsoft has decided to opt for sensible default names for each user's home path (C:\Users\login_name -- see above for experiments with cygwin). So the user "foo" would by default have downloads stored in C:\Users\foo\Downloads.
Connecting To Access With Activestate ODBC.
Activestate perl installs ok with Windows 7. If you don't have proxy servers etc, you can get cpan working simply by starting it from the command line. By default cpan will try to install dmake and the MinGW gcc compiler. These will be setup in C:\Perl\site\bin.
Since I was trying to get data from an old MS Access system I had to find where the ODBC drivers were kept. You can get ODBC to work with perl by starting up a command prompt ... That is when you find the command prompt! I tried entering "cmd" into the search box in the Windows Start button (shades of Ubuntu Unity here?) ... And there it was. Whenever I open a command prompt, the first thing I do is right click the bar at the top and enable Properties->Options->QuickEdit Mode. (why would any experienced command line user NOT want this option enabled?) ...
To get the old ODBC drivers , enter this command:
odbcad32
A screen will pop-up as below. You should choose the Add option.
odbcad32 screen 1
After you choose the Add option. You will be presented with a list of options as below ... You should choose the Microsoft Access Driver.
odbcad32 screen 2
After you choose the Microsoft Access Driver, in the next screen (below) fill in the details for the new ODBC connection as below, and click on "Select".
odbcad32 screen 3
Finally, in the file dialogue which pops up, choose the folder, and you should see your .mdb file ... Select it and click "ok".
odbcad32 screen 4
Your 32 Bit MS-Access ODBC connection should now be ready to use ... (Why couldn't they have written a command line utility to do this?)
Getting your new ODBC connection to work in perl.
There are several utilities online which offer ODBC connectivity. However most of them don't seem to add much functionality to the basic dbi::ODBC package that ships with the standard Activestate distribution.
I found the easiest way to get data out of the old .mdb was to use code such as the following:
#!/usr/bin/perl use DBI; use strict; use warnings; use subs qw (massage); our $sth; my @data; =head1 subroutines massage - massages the data for loading into postgres =cut # ------------------------------------------------------------------------ =head1 massage Expand Tabs and look for text that needs to be marked up Note: Must expand tabs otherwise tab-delimited file format is broken =cut sub massage { my $str = $_[0]; return "" unless $str; return $str if ($str =~ /^\d+$/); $str =~ s/\t/ /g; $str =~ s/&/&/g; $str =~ s/\</</g; $str =~ s/\</>/g; $str =~ s/\r//g; $str =~ s/\n/\<br\>/g; my @chr = split('',$str); $str = ""; # my $dbug = 0; foreach my $c (@chr) { if ($c lt " ") { # $c = ord $c; # printf STDERR "Dropped control char: '\\x0%X' from data stream\n", $c; # drop any control characters next; } elsif ($c gt '~') { $c = ord $c; if ($c == 127) { # drop the delete character next; } elsif ($c == 8217 || $c == 146) { # get rid of annoying "smart" quotes $str .= "'"; next; } # print "DBUG: $c\n"; $str .= sprintf "&#%d;", $c; # $dbug++; } else { $str .= $c; } } # print STDERR "$str\n" if ($dbug); return $str; } # open connection to Old Access archive our $dbh = DBI->connect('dbi:ODBC:foo_bar'); # ------------------------------------------------------------------------ =head1 The code below is a snippet generated with a perl script. In fact in the actual script I used there were many such tables ... You'll have to write your own code to generate similar statements (maybe from the catalog?) =cut # === credits === $sth = $dbh->prepare( "select CR_num, type, date, amount, invoice_num, Memo1, Memo2, GST, Adjust_reason from credits"); $sth->{LongReadLen} = 4096; $sth->{LongTruncOk} = 1; $sth->execute || die "Cannot execute Statement"; open OUT,">>credits.dump" || die $!; while (@data=$sth->fetchrow_array) { foreach my $i(0 .. $#data) { $data[$i] = massage $data[$i]; } print OUT join("\t",@data) . "\n"; } |
Note: One of the first problems I encountered was the length of items in Memo fields, which is the reason for the LongReadLen and LongTruncOk modifications. Also there are some annoying non-ISO characters (e.g. 'smart quotes'), from various Microsoft products. These were handled by the massage() subroutine, which gets rid of carriage return and line-feeds and smart-quotes ... Other characters are marked up in a rather ham-fisted manner ... You might want to uncomment the #debug# statements while you attempt to tailor the conversion script to your data. (YMMV).
Of course then you have to write the scripts to create your postgres tables etc ... And say goodbye to Windows Access (hopefully forever) ...
BTW: This blog entry is now W3C compatible (an earlier version had problems with stylesheets and classes)