http://www.perlmonks.org?node_id=491253


in reply to Accessing MS-Access memofield via DBI

You need to import the SQL type constants from DBI and specify that SQL_LONGVARCHAR as the type of the data to be added to the memo field.

The code below extracts the news from the Google RSS feed and inserts the stories into an Access database via DBI and ODBC. The database connection is made on the fly without needing configuration. The description item is a memo field in the Access database.

#! /usr/bin/perl # use strict; use warnings; use LWP; use XML::RSSLite; use DBI ':sql_types'; use Date::Parse; use Date::Format; use HTML::Entities; use Data::Dumper; my $rssUrl = 'http://news.google.com/news?M=&M=&M=&M=&%20Toubro=&outpu +t=rss'; unless (getRss()) { $ENV{HTTP_PROXY}='myproxy:80'; getRss(); } sub getRss { my $ua = LWP::UserAgent->new; $ua->timeout(10); $ua->env_proxy; $ua->agent('Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.11) + Gecko/20050728'); my $response = $ua->get($rssUrl); my $rssContent; if ($response->is_success) { $rssContent = $response->content; # or whatever } my $mdb = "C:\\perl\\rssnewsdb\\rss.mdb"; my $DSN = "driver=Microsoft Access Driver (*.mdb);dbq=$mdb"; my $dbh = DBI->connect("dbi:ODBC:$DSN", ",") or die "$DBI::errstr\n"; if ($rssContent) { $rssContent =~ s/ isPermaLink="false"//g; my %rssHash; parseRSS(\%rssHash, \$rssContent); foreach my $item (@{$rssHash{item}}) { my $sql = "INSERT INTO tFeed ( [guid], title, link, category , + publicationdate, description) VALUES(?,?,?,?,?,?)"; my $sth = $dbh->prepare($sql); my $guid = $item->{'guid'}; my $title = $item->{'title'}; my $link = $item->{'link'}; my $description = $item->{'description'}; my $category = $item->{'category'}; my $publicationdate = time2str ('%c', str2time($item->{'pubDat +e'})); $description =~ s/<.*?>/ /g; $description =~ s/&/&/g; decode_entities $description; $description =~ s/\s+/ /g; $description =~ s/^\s+//g; print "$description\n\n"; $sth->bind_param(1, $guid); $sth->bind_param(2, $title); $sth->bind_param(3, $link); $sth->bind_param(4, $category); $sth->bind_param(5, $publicationdate); $sth->bind_param(6, $description, SQL_LONGVARCHAR); eval { $sth->execute() or die $dbh->errstr; }; if ($@) { print "Failed to add - $title\n"; } } return 1; } else { print "error!\n"; } return undef; }