Skip to Content.
Sympa Menu

freetds - Re: [freetds] How to Turn Off Character Conversion Failed Warnings

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "David Chang" <dchang AT fsautomation.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] How to Turn Off Character Conversion Failed Warnings
  • Date: Mon, 15 Nov 2010 12:57:32 -0800

Eli,

I'm not a Perl programmer, but Sybase / FreeTDS has an error handler that can mask out regular errors.

I found this on CPAN, http://search.cpan.org/~mewp/DBD-Sybase-1.11/Sybase.pm

syb_err_handler (subroutine ref)

This attribute is used to set an ad-hoc error handler callback (ie a perl subroutine) that gets called before the normal error handler does it's job. If this subroutine returns 0 then the error is ignored. This is useful for handling PRINT statements in Transact-SQL, for handling messages from the Backup Server, showplan output, dbcc output, etc.

The subroutine is called with nine parameters:

o the Sybase error number
o the severity
o the state
o the line number in the SQL batch
o the server name (if available)
o the stored procedure name (if available)
o the message text
o the current SQL command buffer
o either of the strings "client" (for Client Library errors) or
"server" (for server errors, such as SQL syntax errors, etc),
allowing you to identify the error type.As a contrived example, here is a port of the distinct error and message handlers from the Sybase documentation:

Example:

sub err_handler {
my($err, $sev, $state, $line, $server,
$proc, $msg, $sql, $err_type) = @_;

my @msg = ();
if($err_type eq 'server') {
push @msg,
('',
'Server message',
sprintf('Message number: %ld, Severity %ld, State %ld, Line %ld',
$err,$sev,$state,$line),
(defined($server) ? "Server '$server' " : '') .
(defined($proc) ? "Procedure '$proc'" : ''),
"Message String:$msg");
} else {
push @msg,
('',
'Open Client Message:',
sprintf('Message number: SEVERITY = (%ld) NUMBER = (%ld)',
$sev, $err),
"Message String: $msg");
}
print STDERR join("\n",@msg);
return 0; ## CS_SUCCEED
}In a simpler and more focused example, this error handler traps showplan messages:

%showplan_msgs = map { $_ => 1} (3612 .. 3615, 6201 .. 6299, 10201 .. 10299);
sub err_handler {
my($err, $sev, $state, $line, $server,
$proc, $msg, $sql, $err_type) = @_;

if($showplan_msgs{$err}) { # it's a showplan message
print SHOWPLAN "$err - $msg\n";
return 0; # This is not an error
}
return 1;
}and this is how you would use it:

$dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', '');
$dbh->{syb_err_handler} = \&err_handler;
$dbh->do("set showplan on");
open(SHOWPLAN, ">>/var/tmp/showplan.log") || die "Can't open showplan log: $!";
$dbh->do("exec someproc"); # get the showplan trace for this proc.
$dbh->disconnect;NOTE - if you set the error handler in the DBI->connect() call like this

$dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', '',
{ syb_err_handler => \&err_handler });then the err_handler() routine will get called if there is an error during the connect itself. This is new behavior in DBD::Sybase 0.95.



I think your error number is 2402 or 2403 (Severity 16, but you should look it up). If you don't handle these errors, the default behavior may be to stop your program. If you handle these errors (i.e. return with a 0 status), I think you can ignore them.

DC

----- Original Message -----
From: "Eli Finkelshteyn" <vengeanceofalf AT gmail.com>
To: <freetds AT lists.ibiblio.org>
Sent: Monday, November 15, 2010 11:09 AM
Subject: Re: [freetds] How to Turn Off Character Conversion Failed Warnings


Hi David,
Unless I'm missing something, this seems like the same advice I've seen in
other places. It's not helpful to me because I'm already using "Client
Charset = UTF-8." This does not, and should not solve my problem as this can
only convert characters into UTF-8 that are correctly encoded in the
database. In my case-- and this is my main problem-- I have a char field
(note: chars are supposed to hold ASCII characters and nothing else) that
has cp1252 inserted into it. Because of this, when FreeTDS grabs data from
that field, assumes it's ASCII, and tries to convert it to UTF-8, it blows
up since some of those cp1252 code points don't exist in UTF-8 (i.e. code
point 253-- a y with an accent sign-- among others).

To reiterate from my last email, I'm completely fine with losing those
characters. Whoever threw them into the db messed up, and if I can get
FreeTDS to replace them with question marks, I can just filter those values
out and be done with the whole thing. It even sounds like FreeTDS wants to
do this as it gives me the warning "Some character could not be converted
into client's character set. Unconverted bytes were changed to question
marks ('?')." But then, instead of actually leaving unconverted bytes as
question marks, FreeTDS instead kills my whole program. Does anyone at all
know how to change this behavior? I know it should be possible based on the
"set char_convert on with no error" option in Sybase. Is there nothing
similar I can throw into FreeTDS.conf?

Help, please?

Regards,
Eli

On 11/12/2010 3:03 AM, David Dick wrote:

On 12/11/10 07:52, Eli Finkelshteyn wrote:

Hi Folks,
I'm using FreeTDS with Perl and DBD::Sybase to connect to a MSSQL 2008
server. Normally, this is well and good, but today I noticed that someone
thought it would be a good idea to write cp1252 data to a char column I'm
selecting from. This promptly made FreeTDS and Perl fail with the error:
"*Some
character(s) could not be converted into client's character set.
Unconverted bytes were changed to question marks ('?')*"

Now, that's completely fine, and I'm willing to live with the question
marks. The problem is that this warning is read as an error by Perl and
breaks my code!

Googling around, I found that I can use " * set * * char_convert on with no
error*" on a normal Sybase server to ignore this and go on with the code.
Is there some equivalent I can send to FreeTDS, or DBD::Sybase (perhaps a
setting in freetds.conf?) to do the same? I would find that immensely
helpful!


I wrote a <a href="http://use.perl.org/~ddick/journal/39102";<http://use.perl.org/%7Eddick/journal/39102>
use.perl.org journal</a> which might be useful
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds






Archive powered by MHonArc 2.6.24.

Top of Page