Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re: Perl DBI can't display Chinese text?

by mje (Curate)
on Oct 14, 2013 at 08:21 UTC ( #1058145=note: print w/replies, xml ) Need Help??

in reply to Perl DBI can't display Chinese text?

What platform are you running this on? DBD::ODBC only builds by default using the ODBC Unicode API on Windows. On UNIX you need to add the -u switch to Makefile.PL. What ODBC driver are you using? (as not all of them support unicode). Whilst you are at it show us your DBI and DBD::ODBC versions - you can do that with:

perl -MDBI -le "print $DBI::VERSION" perl -MDBD::ODBC -le "print $DBD::ODBC::VERSION"

Are you sure the column containing chinese text is known to SQL Server as Chinese - normally data like this goes into nvarchar columns. Even if DBD::ODBC is built using the unicode API, the ODBC driver needs to tell DBD::ODBC the column is of type SQL_WCHAR - we will only see that if you provide a trace.

Assuming you have a recent DBI and DBD::ODBC you can produce a trace like this:

# on windows set DBI_TRACE=DBD=x.log run your perl program here # on unix export DBI_TRACE=DBD=x.log run your perl program here

The logging will end up in the file x.log. If that does not produce any logging then your DBI and/or DBD::ODBC are too old so replace the "DBD" above with "15" (you'll get a lot more logging, most of which is not required).

Replies are listed 'Best First'.
Re^2: Perl DBI can't display Chinese text?
by Thai Heng (Beadle) on Oct 14, 2013 at 22:50 UTC
    Thanks mje! According to your suggest, I trace my perl code. The x.log as follows:
    Unicode login6 dbname=SQLSERVER2008R2, uid='sa', pwd=xxxxx SQLDriverConnect 'SQLSERVER2008R2', ''sa'', 'xxxx' SQLDriverConnectW failed: SQLConnect ''SQLSERVER2008R2'', ''sa'' !!dbd_error2(err_rc=1, what=db_login6sv/SQLConnectW, handles=(26a8 +398,20caeb0,0) !SQLError(26a8398,20caeb0,0) = (01000, 5701, [Microsoft][SQL Serve +r Native Client 10.0][SQL Server]ӑ˽ݿ㊏&#102 +6;τ�'ECISAN_JX') !SQLError(26a8398,20caeb0,0) = (01000, 5703, [Microsoft][SQL Serve +r Native Client 10.0][SQL Server]ӑԯҔʨ׃ +�󍦖юġ) Turning autocommit on DRIVER_ODBC_VER = 03.52 DRIVER_NAME = sqlncli10.dll, type=2 DRIVER_VERSION = 10.50.4000 MAX_COLUMN_NAME_LEN = 128 DBD::ODBC is unicode built : YES SQLMoreResults supported: 1 SQLDescribeParam supported: 1 !!DBD::ODBC unsupported attribute passed (PrintError) setting AutoCommit !!DBD::ODBC unsupported attribute passed (Username) !!DBD::ODBC unsupported attribute passed (dbi_connect_closure) initializing sth query timeout to -1 ignore named placeholders = 0 SQLPrepare select top 1000 name,create_date from sys.objects where + type = 'U' and charindex('t_',name) > 0 order by create_date Processing non-utf8 sql in unicode mode SQLPrepare = 0 initializing sth query timeout to -1 ignore named placeholders = 0 SQLPrepare select name,deptcode from t_department Processing non-utf8 sql in unicode mode SQLPrepare = 0 +dbd_st_execute(2683be4) dbd_st_finish(2683be4) outparams = 0 SQLExecute/SQLExecDirect(20c6630)=0 SQLRowCount=0 (rows=-1) SQLNumResultCols=0 (flds=2) dbd_describe done_desc=0 dbd_describe SQLNumResultCols=0 (columns=2) DescribeCol column = 1, name = n, namelen = 4, type = VARCHAR(12), + precision/column size = 50, scale = 0, nullable = 0 SQL_COLUMN_DISPLAY_SIZE = 50 SQL_COLUMN_LENGTH = 50 now using col 1: type = UNICODE CHAR (-8), len = 102, display siz +e = 102, prec = 50, scale = 0 DescribeCol column = 2, name = d, namelen = 8, type = VARCHAR(12), + precision/column size = 10, scale = 0, nullable = 0 SQL_COLUMN_DISPLAY_SIZE = 10 SQL_COLUMN_LENGTH = 10 now using col 2: type = UNICODE CHAR (-8), len = 22, display size + = 22, prec = 10, scale = 0 -dbd_describe done_bind=0 have 2 fields -dbd_st_execute(2683be4)=-1 bind_columns fbh=2716b94 fields=2 Bind 1: type = UNICODE CHAR(-8), buf=2180494, buflen=102 Bind 2: type = UNICODE CHAR(-8), buf=21804fc, buflen=22 bind_columns=0 SQLFetch=0 fetch num_fields=2 fetch col#1 n datalen=8 displ=102 SQL_C_WCHAR data = "临床科室" fetch col#2 d datalen=2 displ=22 SQL_C_WCHAR data = '1' SQLFetch=0 fetch num_fields=2 ... ...
    In my compute, chinese display well in x.log. In print text(cmd), there is such info as follows:
    Wide character in print at E:\temp\DBI_HG_Study_20130912\ConnectSQL line 122.
    What's the question key?
      Regarding this message:
      Wide character in print at E:\...\ line 122.
      Figure out what file handle is being printed to at line 122 of that script. If it's STDOUT, then somewhere before you reach that statement, you should do:
      binmode STDOUT, ':utf8';
      If it's some file handle that you open, add ':utf8' to the mode portion of the open call - e.g.
      open( my $output_handle, '>:utf8', $output_name ) or die $!;

      So you are running on Windows, your DBD::ODBC is a unicode supprting build and you are using the MS SQL Server native client driver.

      We still don't know your DBI and DBD::ODBC versions. I slightly feel you are drip feeding us information.

      As far as I can see that log is showing chinese characters returned. Characters 临, 床, 科 and 室 all look like chinese unicode characters. When you fetch that column you can pass it to data_string_desc and DBI will show you more information. If you do what graff told you and your terminal is set up correctly you should be able to print that string.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1058145]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (4)
As of 2018-01-23 22:44 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (254 votes). Check out past polls.