SingleStore binary(32) datatype shows values A2A2A2A2A2A2... in SAS Enterprise Guide using ODBC

[DannyPannemans]

Hi,

We are connecting to SingleStore using Access to ODBC and noticed that SAS Enterprise Guide (v7.13) (SAS 9.4 9.04.01M7P080620) shows SingleStores binary(32) datatype as a column with:

Type: character

Length 32

Format $HEX64.

Informat $HEX64.

If we browse the table in SAS EG the content of this binary column (hub_burger_hkey) is shown as '2A2A2A2A...' for all records even if the underlying value is different.

Grouping on this column gives correct results but all values are shown as '2A2A2A2A2A...' .. which is of course not easy to work with when identifying records.

When trying to store it with a $32. format it is possible to show it correctly... but we don't find any other way to do so. Is there anyone who has any idea how we can let SAS EG visualise this type of binary column correctly without rewriting the data into SAS datasets (using just a hex format doesn't work neither)?

PROC SQL;
   CREATE TABLE WORK.TEST AS 
   SELECT t1.hub_burger_hkey AS hub_burger_hkey_hex64, 
          /* HKEY_ALS_CHAR32 */
            (put(t1.hub_burger_hkey,$32.)) AS HKEY_ALS_CHAR32, 
          t1.hub_burger_hkey FORMAT=$hex32. AS hub_burger_hkey_format_hex32, 
          t1.hub_burger_hkey FORMAT=$32. AS hub_burger_hkey_format_char32
      FROM _BINTEST.hub_burger_binary t1
      WHERE t1.burger_bk = '60027907';
QUIT;

Thanks in advance,

Danny

[SASKiwi]

How does this variable display in SingleStore - I assume you want it to display the same as it is at source. 

[DannyPannemans]

Thanks for your reply.

This value shows as an hexadecimal string:

Example:

FAD30861400C11964DCC20DE66052384

And yes... we want to DISPLAY it in this way correctly.

[Tom]

The $ format is for display text.  If your value is binary then you probably want to display it using the $HEX format so that you can READ it.  Otherwise it will print gibberish to the screen some of which might ring the bell (don't you have bell on your teletype machine?) and do even worse things.

How many of these different values do you actually have in the data you are using? If it just just a few then perhaps you can craft your own human readable way of displaying them.  For example just get the set of distinct values and number them.

So assuming your dataset is named HAVE and this 32byte "binary" string is named HKEY then you could make a format named $HKEY that would print each as a digit string (with leading zeros) from 1 to total number of distinct keys. (As long as the number of distinct keys is less than the maximum integer that SAS can store).

proc sql ;
create table keys as
   select distinct HKEY 
   from HAVE 
;
%let nobs=&sqlobs;
quit;
data format ;
  set keys;
  fmtname='$HKEY';
  rename HKEY = start ;
  row+1;
  value=put(row,Z%length(&nobs).);
run;
proc format cntlin=format;
run;

You could then use this $HKEY format with your data to be able to look at it.

So if you have less than 10,000 distinct HKEY values then they will display as 0001, 0002 ..... 9999.  If you have less than 100,000 then they will use 5 digits instead.

[DannyPannemans]

Thanks for your reply.
Your solutioning unfortunately would be a 'workaround' to be able to use binary data from SingleStore in SAS EG needing to create formats first and applying them on all columns... this would be a massive overload and not feasible.
We don't want to pull the data to SAS first to be able to work with the data in SingleStore.

[CaseySmith]

This is a known issue, due to a limitation of the underlying data access technology EG uses to read values and the way EG reads values by default. Specifically, that access layer is unable to return binary/hex raw values in certain scenarios -- it quietly (without error or exception) returns a bunch of asterisks ("******...") as the raw value for binary/hex values instead.

EG reads values (by default) by requesting raw values from the data access layer and then explicitly formats values for a certain number of columns to improve performance viewing wide data. Therefore, when EG gets back "******..." as the raw value (and no exception/error), it has no reason to assume this is not the correct raw value. EG then explicitly formats that value with the $hex format. The hex value for asterisk character is 2A, so "******..." formatted as #hex becomes the formatted value "2A2A2A..."

When we became aware of this issue with the data access layer, we did add a config option (named "FormatValuesAsNeeded") that will allow users to workaround. Setting that config option to false will allow users to explicitly turn off the explicit formatting of raw values and revert to getting the formatted values directly from the data access layer, which is able to provide the proper #hex formatted value. So, if you make this change in your SEGuide.exe.config file, I would expect the values to display as you desire/expect...

In SEGuide.exe.config file in your EG installation directory, set "FormatValuesAsNeeded" to false, similar to:

<appSettings>
  <add key="FormatValuesAsNeeded" value="false" />
</appSettings>

Casey

[DannyPannemans]

Thanks! Without having tested it yet... wouldn't changing this setting have no other 'undesired' results for datatypes where the explicit formatting does work properly?

[CaseySmith]

The only possible side effect I can think of is that very wide data (tables with lots of columns) might take a little longer to display in the data grid, since all of the columns will be formatted rather than a smaller subset just-in-time. Unless you are using very wide data, I doubt you'll notice a difference in performance.

Casey

[Tom]

How would an Enterprise Guide setting change how variables are pulled from an ODBC connection?

[FrederikEngelen]

I've just tried it out on my machine. The result coming back from a binary column are now formatted as a set of asterisks, instead of the '2A2A...' representation from before. That didn't do the trick.

When using PROC PRINT on this data set, the results are displayed correctly, so I assume it's an issue with EG, more than the data layer.

[Tom]

---

@FrederikEngelen wrote:

I've just tried it out on my machine. The result coming back from a binary column are now formatted as a set of asterisks, instead of the '2A2A...' representation from before. That didn't do the trick.

When using PROC PRINT on this data set, the results are displayed correctly, so I assume it's an issue with EG, more than the data layer.

---

So your original issue is not that the data is wrong but that when you browsed the dataset using Enterprise Guide it looks like ***... and/or A2A2A2... instead of the actual values that are in the dataset?  

I would say don't browse the dataset with Enterprise Guide.  You could make a version of the dataset that has the 32 byte binary variable converted to the corresponding 64 hex characters and browse that.  Enterprise Guide shouldn't mess up displaying digits 0 to 9 and letters A to F.

data for_browsing;
  length hkey $64 ;
  set have(rename=(hkey=hkey_binary));
  hkey=put(hkey_binary,$hex64.);
run;

[DannyPannemans]

Thanks Tom for your reply. I hope you can agree with me that copying the data and converting the data using a put function isn't a 'real' solution to a 'visualisation' issue in Enterprise Guide. The hex format should be able to visualise the data correctly in SAS EG but it doesn't. The idea is to use SAS EG to transform the data as much as possible using the power of SingleStore of course so keeping the data in SingleStore... and as SAS developer you just want to see the data correctly and now and then filter the dataset based on this binary 'hash' key. Thanks for thinking along :=)

[Tom]

I don't use EG myself, the interface does not really add anything that I find of value so I cannot offer any technical answers.  But it looks to me like there is an encoding issue going on.  EG seems to think it needs to transcode those binary bytes and since they aren't actually intended to be characters they don't transcode.

But if you have the setup to run EG then you should also have everything you need to use SAS/Studio.  Perhaps SAS/Studio does a better job of showing that type of field.  Or perhaps you can avoid whatever transcoding issue is confusing EG.

[CaseySmith]

Sorry to hear that did not resolve the issue. The fact that you are getting asterisks now ("************") instead of "2A2A2A2A..." confirms that the config option is doing what was asked (to request formatted values from the data layer, rather than raw values), however in this scenario, the data layer is also returning asterisks for the formatted value. This issue is lower-level than EG, the client. EG is just rendering the values that come back from the data access layer. Will see if I can get some simple steps to reproduce and take a closer look to confirm, but I'm fairly confident I could reproduce with a test program outside of EG.

[CaseySmith]

I finally got back around to looking at this one again. I'm pretty sure this will do the trick, but it is still a workaround for the fact there is not true binary data support in the data access layer (RIO).

If you set the following SAS system option (submit the following code) prior to opening the data, then the actual values should come back and appear in the EG data grid, instead of being masked with asterisks:

options TRANSCODEACTION=MODIFY;

However, you will then likely run into this secondary issue... binary values that contain x00 will be truncated at the first occurrence of x00, since it gets treated as a null string terminator. To workaround this, you can instruct EG to read the formatted values directly by setting the FormatValuesAsNeeded config option to false (add the line "<add key="FormatValuesAsNeeded" value="false" />" to the appSettings section of the SEGuide.exe.config file in the EG installation directory, as I noted earlier in this thread).

Those two things together should get you pretty close to what you want, even though (again) it is a workaround.