Thank you David for the hint! The db admin had this to say:
> "EXEC sp_helpsort"
>
> This stored proc's results shows that myhost uses the Default
> Character Set of Code Page (ISO) 1252.
I created a standalone Perl program to test, see below. I tried
the following character sets without success:
charset=1252
charset=windows-1252
charset=Code Page (ISO) 1252
How can I either disable the character conversion, or make the
conversion work?
Regards,
Peter
-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----
[r...@twiki-ctva32 lib]# perl test_dbd_sybase.pl
DBD::Sybase::st fetchrow_hashref failed: OpenClient message: LAYER =
(0) ORIGIN = (0) SEVERITY = (9) NUMBER = (99)
Server , database
Message String: WARNING! Some character(s) could not be converted into
client's character set. Unconverted bytes were changed to question
marks ('?').
Overview: Test Company, Inc. (?TEST?), applies advanced technologies
blah blah...
-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----
Note: The question marks in (?TEST?) are the Microsoft specific start
and end double quotes.
-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----
#!/usr/bin/perl -w
my $connect = 'database=MyData;host=myhost.example.com;port=1433;';
$connect .= 'charset=windows-1252;client charset=windows-1252;tds
version=8.0';
my $sql = 'SELECT Overview from CompanyInfo where CompanyID = 11678';
my $token = 'Overview';
use DBI;
my $dbh = DBI->connect( "DBI:Sybase:$connect", 'myuser', 'mypassword')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare( $sql )
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute();
while( my $hash_ref = $sth->fetchrow_hashref ) {
print "$token: " . $hash_ref->{$token} . "\n";
}
$dbh->disconnect;
-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----8><-----
On May 6, 2009, at 2:27 PM, David Goodman wrote:
Hello Peter:
From within SQL Server "exec sp_helpsort" should display the proper
spelling of your default character set.
regards,
David
--- On Wed, 5/6/09, Peter Thoeny <[email protected]>
wrote:
From: Peter Thoeny <[email protected]>
Subject: DBD::Sybase charset issue connecting to MS-SQL
To: [email protected]
Date: Wednesday, May 6, 2009, 4:06 PM
Hi,
I stumbled on a roadblock and need some help.
I am trying to connect to a MS-SQL database using
DBD::Sybase, http://search.cpan.org/~mewp/DBD-Sybase-1.09/dbd-sybase.pod
on Centos 5.2. The DBD::Sybase module is used from within
the DatabasePlugin of the TWiki Enterprise Collaboration
Platform.
Connection works fine unless there are special characters
such as single quotes in the query result. With special
characters, I get this message: "Some character(s) could not
be converted into client's character set. Unconverted bytes
were changed to question marks ('?')."
Reading the docs it looks like I can turn this off if I
specify the proper character set when connecting to the
database. I verified, the MS-SQL uses Code Page (ISO) 1252.
I tried "charset=1252" and "charset=windows-1252" and a
bunch of other variations, but I can't get rid of the
error.
Any hint? Any other detail you need? See below error
output.
Regards,
Peter
DBD::Sybase::st fetchrow_hashref failed: OpenClient?
message: LAYER = (0) ORIGIN = (0) SEVERITY = (9) NUMBER =
(99) Server , database Message String: WARNING! Some
character(s) could not be converted into client's character
set. Unconverted bytes were changed to question marks ('?').
at
/var/www/twiki/lib/TWiki/Plugins/DatabasePlugin/DATABASE_SQL.pm
line 19
TWiki
::Plugins
::DatabasePlugin
::DATABASE_SQL
::handle
('TWiki::Plugins::DatabasePlugin::Connection=HASH(0x8fda120)',
'description="MyData" sql="SELECT Overview from CompanyInfo?
...') called at
/var/www/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 81
eval {...} called at
/var/www/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 75
TWiki::Plugins::DatabasePlugin::_dispatch('DATABASE_SQL',
'description="MyData" sql="SELECT Overview from CompanyInfo?
...') called at
/var/www/twiki/lib/TWiki/Plugins/DatabasePlugin.pm line 98
TWiki::Plugins::DatabasePlugin::commonTagsHandler('\x{a}
%...', 'TestSqlComapnyDescription', 'Sandbox', 0,
'TWiki::Meta=HASH(0x901ae98)') called at
/var/www/twiki/lib/TWiki/Plugin.pm line 266
TWiki::Plugin::invoke('TWiki::Plugin=HASH(0x889a440)',
'commonTagsHandler', '\x{a}
%...', 'TestSqlComapnyDescription', 'Sandbox', 0,
'TWiki::Meta=HASH(0x901ae98)') called at
/var/www/twiki/lib/TWiki/Plugins.pm line 344
TWiki::Plugins::_dispatch('TWiki::Plugins=HASH(0x83d8348)',
'commonTagsHandler', '\x{a}
%...', 'TestSqlComapnyDescription', 'Sandbox', 0,
'TWiki::Meta=HASH(0x901ae98)') called at
/var/www/twiki/lib/TWiki/Plugins.pm line 480
TWiki::Plugins::commonTagsHandler('TWiki::Plugins=HASH(0x83d8348)',
'\x{a}
-- * Peter Thoeny, CTO - [email protected]
* http://twiki.net - TWIKI.NET - the Enterprise
Wiki
* http://twiki.org - is your team already TWiki
enabled?
* Knowledge cannot be managed, it can be discovered
and shared
* This e-mail is: (_) private
(x) ask first (_) public
--
* Peter Thoeny, CTO - [email protected]
* http://twiki.net - TWIKI.NET - the Enterprise Wiki
* http://twiki.org - is your team already TWiki enabled?
* Knowledge cannot be managed, it can be discovered and shared
* This e-mail is: (_) private (x) ask first (_) public
