topic Re: Encoding on OLEDB connection in SAS Programming

Encoding on OLEDB connection

J_Muth — Wed, 10 Mar 2021 17:41:43 GMT

Hello,

Does somebody know, how I can set the encoding for reading a table on a MS SQL server via OLE DB?

The table in MSSQL has a wlatin1 encoding.

When SAS 9.4 is started with the English locale, it reads the table correctly. Unfortunately there are also other resources that need SAS in UNICODE mode. But that doesn't read the special characters correctly.

libname mydblib oledb
init_string="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=&dbase;Data Source=&server" schema=dbo;

proc sql;
create table test as
SELECT * FROM mydblib.test;
quit;

I tried outencoding = "wlatin1" on the libname statement and encoding = "wlatin1" on the SELECT, but got

WARNING 278-63: The option OUTENCODING is not implemented in the OLEDB engine.

and

WARNING: The option ENCODING is not implemented in the OLEDB engine.

Is there any possibility to read that wlatin1 table correctly in SAS 9.4 Unicode?

Thank you for hints!

Re: Encoding on OLEDB connection

ChrisNZ — Thu, 11 Mar 2021 01:28:17 GMT

Have you tried this

proc sql;
create table test as
SELECT * FROM mydblib.test(encoding='wlatin1');
quit;

Re: Encoding on OLEDB connection

J_Muth — Thu, 11 Mar 2021 07:08:54 GMT

Dear Chris!

As you can see in the description of my problem: Yes, I did.

And the error message was:

WARNING: The option ENCODING is not implemented in the OLEDB engine.

BR,

Juergen

Re: Encoding on OLEDB connection

ChrisNZ — Thu, 11 Mar 2021 07:16:35 GMT

Can you show the full log please?

Re: Encoding on OLEDB connection

J_Muth — Thu, 11 Mar 2021 11:46:22 GMT

Thanks for checking. Here is the log...

Re: Encoding on OLEDB connection

ChrisNZ — Thu, 11 Mar 2021 22:18:20 GMT

Just like your PDF, this file is off limits.

Do not attach files. Paste everything in line using the appropriate icons.

Re: Encoding on OLEDB connection

J_Muth — Fri, 12 Mar 2021 06:24:42 GMT

NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M6 MBCS3170)
      Licensed to XXXXX.
NOTE: This session is executing on the X64_10PRO  platform.



NOTE: Analytical products:

      SAS/STAT 15.1
      SAS/ETS 15.1
      SAS/IML 15.1

NOTE: Additional host information:

 X64_10PRO WIN 10.0.18362  Workstation

NOTE: SAS initialization used:
      real time           1.10 seconds
      cpu time            0.92 seconds

NOTE: AUTOEXEC processing beginning; file is XXXXX.sas.

NOTE: Libref XXXXX was successfully assigned as follows:
      Engine:        V9
      Physical Name: XXXXX
NOTE: Libref XXXXX was successfully assigned as follows:
      Engine:        V9
      Physical Name: XXXXX

NOTE: AUTOEXEC processing completed.

1    %let server=XXXXX;
2    %let dbase=XXXXX;
3
4    libname mydblib oledb
5    init_string="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;
5  ! Initial Catalog=&dbase;Data Source=&server" outencoding="wlatin1" schema=dbo;
                                                             ---------
                                                             278
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        OLEDB
      Physical Name:
WARNING 278-63: The option OUTENCODING is not implemented in the OLEDB engine.

6
7    libname mydblib oledb
8    init_string="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;
8  ! Initial Catalog=&dbase;Data Source=&server" schema=dbo;
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        OLEDB
      Physical Name:

9    proc sql;
10   create table test as
11   SELECT  *
12     FROM mydblib.test(encoding='wlatin1');
WARNING: The option ENCODING is not implemented in the OLEDB engine.
13   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.20 seconds
      cpu time            0.00 seconds

Re: Encoding on OLEDB connection

ChrisNZ — Sun, 14 Mar 2021 07:18:10 GMT

I see. This is less than ideal. Ideas:

- Look if there are parameters that can be configured with the OLEDB provider. This might involve getting a more modern version. MS has left many dead bodies in the wake of the OLEDB ship. See here. https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

- Use ODBC. Unsure if encoding options are better supported.

- Use the native SAS/Access product. See here https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p0378ek9nfsu5bn1cpsc8woi6qjf.htm&locale=en Again, unsure if encoding options are better supported.

- Ask the SQL Server admins to copy that table to a UTF-8 database. UTF-8 is the future anyway, so they might as well start the migration.

None of this is ideal, but then you seem to be stuck.

Re: Encoding on OLEDB connection

J_Muth — Sun, 14 Mar 2021 08:56:45 GMT

Thank you for the hints. I don't think that this is a problem on the Microsoft side. As I wrote above:

When I start SAS with the English configuration script, it works correctly. But because of the connection to UTF8 encoded sources I want to start it with the u8 configuration.

ODBC is not a solution because of the SingleSignOn approach used in our MSSQL.

And I am not allowed to change the table in the DB.

So the only way to go is to start SAS (Englisch), get the table from the db. Start SAS (Unicode) and proceed...

Re: Encoding on OLEDB connection

Tom — Sun, 14 Mar 2021 13:55:58 GMT

@J_Muth wrote:

Hello,

Does somebody know, how I can set the encoding for reading a table on a MS SQL server via OLE DB?

The table in MSSQL has a wlatin1 encoding.

When SAS 9.4 is started with the English locale, it reads the table correctly. Unfortunately there are also other resources that need SAS in UNICODE mode. But that doesn't read the special characters correctly.

...

What does "doesn't read the special characters correctly" mean? Does it translate the non-7bit ascii codes in the database from their representation in the single byte WLATIN1 encoding into the multi-byte UTF-8 encoding? Did you remember to include extra length in the character variables to allow the possibility that the new UTF-8 strings will be longer?

Re: Encoding on OLEDB connection

ChrisNZ — Sun, 14 Mar 2021 21:56:35 GMT

> I don't think that this is a problem on the Microsoft side.

Not saying there is a problem on the MS side, just that there might be useful parameters that you could set.

Also, did you see on the MS page whose link I sent that your connection method seems to be unsupported?

If my ideas are not workable, then I see no option other than importing with SAS UTF-8 and reprocessing with SAS wlatin, as you suggest.

Re: Encoding on OLEDB connection

J_Muth — Mon, 15 Mar 2021 09:29:45 GMT

Dear Tom!

Thank you for your reply.

(Sorry, it is not possible to attach a screenshots. I hope, the characters will show correctly in this forum...)

There are 2 problems:

1) Reading data that is already in the DB

2) Writing updates into the DB

For Simulation:
data testline;
length text 20;
* E4 60 B4 F1;
text = "ä ` ´ ñ";
run;

data mydblib.test;
set testline;
run;

Ad 1) With
proc sql noprint;
select text into :check from mydblib.test;
quit;
%put &check;

executed in SAS (English) I get as wanted:

ä ` ´ ñ

The SQL run in SAS (U8) on the same data (not created with SAS (U8)) gives something that I can’t copy paste from the log.

But it looks like:

• ` • • in the log and

� ` � when displaying the table (Yes, there are only two ?, but length is 20!)

An export to a csv gives correct characters.

------------------

Ad 2)

On the other hand, when I append data with SAS (U8),
proc append data=testline base=mydblib.test;

run;

SAS (English) reads:

Ã¤ ` Â´ Ã± from the second line (in the log, when displaying the table and exporting to CSV)

----------------------

So as long as there is no encoding-Option on just that connection I will have to use the workaround of starting two SAS sessions, one for the communication with the MSSQL server (SAS(English)), another for the rest of the system (SAS(U8)). 😞

Re: Encoding on OLEDB connection

Tom — Mon, 15 Mar 2021 12:40:50 GMT

So does the database contain the WLATIN1 encoding of 'E460B4F1'x for those characters?

Or the UTF-8 encoding of 'C3A460C2B4C3B'?
Note that the back-tick uses the same '60'x encoding in both.

Are you literally placings those non-7bit ascii codes into the text of your SAS program? Or are you reading them from the files?

Re: Encoding on OLEDB connection

J_Muth — Mon, 15 Mar 2021 12:57:45 GMT

The database contains texts in wlatin1, as 'E460B4F1'x, placed there by another legacy application.

The literals in the snippet were only for demonstration purposes. Those texts are normally imported from other systems (file interface, e.g. csv, xlsx) and I can handle the encoding with options.

Re: Encoding on OLEDB connection

Tom — Mon, 15 Mar 2021 13:09:56 GMT

So if you read an 'E4'x byte from a WLATIN1 source (your OLEDB database) into a UTF-8 SAS session you would expect the result to be the bytes 'C3A4'x. Is that what is happening?

And what happens if you try the reverse? THat is push the hex code 'C3A4'x into the database? Does it end up as 'E4'x?

Re: Encoding on OLEDB connection

J_Muth — Mon, 15 Mar 2021 13:34:45 GMT

What I expect:

Some option similar to (in-/out-)encoding in a libname or filename statement, that enables me in a U8 session, to read the data from OLE DB like I can do in an English session.

It is important that that can be done in a central point and no corrections of the dataset are necessary since that would affect a lot of code that I can't/won't change.

>From the documentation, in-/outencoding should do exactly that.

The annoying thing is that it is not implemented in the OLE DB engine...

And about the reverse:

The text written with U8 gives Ã¤ ` Â´ Ã± in English (as expected)