BookmarkSubscribeRSS Feed
Peter_L
Quartz | Level 8

I have a strange problem which I have been discussing for some time with SAS support. Now we are playing Support Ping-Pong. They say it is the driver but it works for other applications, including R. Has anyone else seen this?

 

I have SAS 9.4M5 64-bit on Windows 10 (64-bit Intel) connecting via ODBC (64-bit of course) to SAP SQL Anywhere. SAS is using utf-8 encoding. I can connect and read the table schemas (oddly with lengths 3x database column width). I can read tables with only numeric columns. I can read tables with some text columns of even width. Any tables which contain even one text column with an odd width: 1, 3, 5 characters etc. throw an ODBC error:

ERROR: CLI column bind error: [SAP][ODBC Driver]Invalid string or buffer length.

 

My investigation indicates that this is a well-known problem in ODBC caused by the buffer memory for the columns not being aligned according to the processor architecture restrictions, which would be on a 2 or 4 byte boundary. It is possible that the SAP SQL Anywhere driver implements (for portability reasons) a stricter alignment rule than other manufacturers' ODBC drivers.

 

Interestingly, this occurs only when SAS uses utf-8 encoding, not in Wlatin1. That would mean that the ODBC driver is in UNICODE wide mode. I want to join the rest of the world in using Unicode so I can display international characters from our database, including Polish and Cyrillic, which are not in Wlatin1.

 

Has anyone else seen this? Alternatively, has anyone proof that the scenario I describe above works for other ODBC drivers?

 

Thanks.

Peter Lancashire

4 REPLIES 4
JuanS_OCS
Amethyst | Level 16

Hello @Peter_L,

 

when you work with ODBC connections, you must know SAS has trouble with trans-coding. 

Meaning: trans-code is the operation to convert a certain encoding to another one. 

But SAS cannot, as far as I know, transcode directly when ODBC and SAS tables are involved.

 

The general recommendation is to create a specific SAS session on the same encoding (and language, preferably) as your ODBC data source, for reading and writing. 

 

With this session you will get the tables into SAS tables.

 

Then you can have your general SAS session in UTF-8, where you can transcode the SAS dataset, into another with your own encoding (UTF-8) and language.

 

Last bit, to write data sets back to origin, ou need to repeat the process but viceversa.

 

PS. Would like to recommend you some read reference:http://support.sas.com/resources/papers/Multilingual_Computing_with_SAS_94.pdf

 

Hope it can help you.

 

Best regards,

Juan

 

Peter_L
Quartz | Level 8
Thanks for the tips on multi-lingual computing. The database uses utf-8: that's why I want to use it in SAS. There's the problem: it doesn't work for tables with one or more text columns of an odd (not even) width.
JuanS_OCS
Amethyst | Level 16

Always a pleasure, hopefully we can get your environment ready so you can work.

First of all, If at end of today we cannot help you and if you are under pressure, I recommend you to contact SAS Technical Support or SAS Consulting services to help you faster and on-site.

 

Now coming back to the issue, I think that perhaps problem is in configuration/options. The question is where:

In one hand, first try to get another tool to query the database though the same ODBC connection and check the results if you query a table. if still wrong, the problem is in the configuration of your ODBC connector, most probably.

 

But if OK there, then you might need to fie tune some settings of your SAS library connection, in the SAS Management Console, there might be some options that can help you while reading/writing.

 

http://documentation.sas.com/api/docsets/odbcdref/9.4/content/odbcdref.pdf?locale=en

Peter_L
Quartz | Level 8
Thanks. I have been discussing this with Technical support for some time. They confirm that the issue is with byte alignment but they haven't seen it with other ODBC drivers. Alignment is a known problem: Microsoft describe it in some detail. There is a work-around: use the DBSASTYPE dataset option to specify the width for every column. This is extremely tedious. The problem is being discussed with SAS R&D.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 3023 views
  • 3 likes
  • 2 in conversation