Re: How to Turn Off Character Conversion Failed Warnings

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" <[email protected]>
To: <[email protected]>
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
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>