Oracle DBD charset error when searching using text

Hi,

I'm trying to write a function which will search a table within Oracle for various columns when certain parameters are met. It works just fine when I use numeric search criteria but as soon as I try and search for rows where a particular column matches a text string I get the following:

me@myserver ~ $ ./poller3.pl
Searching for feeds with providerid=108 and feedcode=crystal palace
SMSAlerts::Feeds can't SELECT feedid, feedcode, serviceid
FROM   feeds
WHERE  enabled = ? AND feedcode = ? AND providerid = ?
: DBD::Oracle::st execute failed: ORA-12704: character set mismatch (DBD ERROR: error possibly near <*> indicator at char 88 in 'SELECT feedid, feedcode, serviceid
FROM   feeds
WHERE  enabled = :p1 AND feedcode = :p2 <*>AND providerid = :p3
') [for Statement "SELECT feedid, feedcode, serviceid
FROM   feeds
WHERE  enabled = ? AND feedcode = ? AND providerid = ?
" with ParamValues: :p3='108', :p1=1, :p2="crystal palace"] at /usr/me/environment/perl-5.8.7/lib/site_perl/5.8.7/DBIx/ContextualFetch.pm line 52.
 at /usr/me/environment/perl-5.8.7/lib/site_perl/5.8.7/Class/DBI/Search/Basic.pm line 169
me@myserver ~ $

In my environment I have the following variables set:

NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
NLS_LANG=american_america.we8iso8859p1

which matches how the database itself is configured.

This is the code which generates the above error:

print "Searching for feeds with providerid=$providerid and feedcode=$message->{feedCode}\n";
$feeds = $feedsclass->search(enabled => 1, providerid => $providerid, feedcode => $message->{feedCode});
print "Feeds=" . Dumper($feeds);

and for reference here is how I am connecting to the database:

#!/usr/me/environment/perl/bin/perl -w

use Class::DBI::Loader;
use Class::DBI::AbstractSearch;
use Data::Dumper;
use Digest::MD5;
use Log::Log4perl;
use LWP::UserAgent;
use POSIX qw(strftime);
use XML::Simple;

# Connect to Database and Initialise Tables

my $database = Class::DBI::Loader->new(
        dsn => 'dbi:Oracle:SID=mysid;host=1.2.3.4;port=1521;',
        user => 'myuser',
        password => 'mypassword',
        options => { LongTruncOk => 1, LongReadLen => 524288 },
        namespace => 'mynamespace',
        additional_classes => 'Class::DBI::AbstractSearch',
        relationships => '0');

unless ($database) {
        print "Error\n";
        exit;
}

my $providerclass = $database->find_class('providers');
$providerclass->columns(Primary => 'Providerid');
$providerclass->columns(Essential => qw/ providerid url frequency timeout lastid /);

my $feedsclass = $database->find_class('feeds');
$feedsclass->columns(Primary => 'Feedid');
$feedsclass->columns(Essential => qw/ feedid feedcode serviceid /);

(and yes I know that I need better error handling, I'm just debugging at the moment though).

Has anyone got any ideas where I'm going wrong and what I need to do to fix it?

Thanks

G
Start Free Trial