Hello,
I have a field in MS SQL databe, that is UUID type. I am getting wrong order in characters when using .GuidEncode() method.
Server Management Studio displays that the value is
8CE489C4-03EE-4F86-A5E4-612F3DEB233F
StringTheory encodes value as
C489E48C-EE03-864F-A5E4-612F3DEB233F
For the last two parts, orders of the characters are OK. But, first three parts are not. I have looked at the documentation, and I can not find anything. Is there some option that needs to be set?
Out of curiosity, I asked ChatGPT for its opinion:
Summary
-------
What you’re seeing is not a StringTheory option or bug. It’s a byte-order (endianness) mismatch between:
1) how SQL Server stores/returns a unique identifier as 16 raw bytes, and
2) how the “standard” GUID text format is displayed (like SSMS shows).
What GuidEncode() does
----------------------
StringTheory’s GuidEncode() expects the current string value to be EXACTLY 16 bytes (binary GUID).
It does NOT reorder any bytes. It simply:
- converts the 16 bytes to 32 hex digits (ToHex)
- formats them into 8-4-4-4-12 groups with hyphens (and optional braces)
So GuidEncode() will output a “correct” textual representation of the raw byte sequence you give it.
Why SSMS shows one value but GuidEncode shows a different “scrambled” value
---------------------------------------------------------------------------
SQL Server’s unique identifier has a well-known quirk:
When represented as raw bytes, the first 3 fields are stored/returned in little-endian order:
- first 4 bytes (Data1) are reversed
- next 2 bytes (Data2) are reversed
- next 2 bytes (Data3) are reversed
The remaining 8 bytes are in the expected order.
So if you fetch a GUID from SQL Server as 16 raw bytes and then simply hex+format it,
the first three groups appear “reversed”, while the last two groups look correct.
Example:
SSMS displays:
8CE489C4-03EE-4F86-A5E4-612F3DEB233F
GuidEncode() outputs (from raw bytes):
C489E48C-EE03-864F-A5E4-612F3DEB233F
Notice:
- 8CE489C4 -> C489E48C (bytes reversed in first 4-byte field)
- 03EE -> EE03 (bytes reversed in next 2-byte field)
- 4F86 -> 864F (bytes reversed in next 2-byte field)
- A5E4-612F3DEB233F remains the same (last 8 bytes unchanged)
Answer to “Is there an option that needs to be set?”
----------------------------------------------------
No. GuidEncode() has no “SQL GUID byte swap” option. It assumes your 16 bytes are already in the
standard display order.
Fix option A (recommended when you already have 16 bytes): swap the first 3 fields
---------------------------------------------------------------------------------
Reorder the 16 bytes before calling GuidEncode():
- reverse bytes 1–4
- reverse bytes 5–6
- reverse bytes 7–8
- leave bytes 9–16 unchanged
Clarion example:
GuidFixForDisplay PROCEDURE(STRING pGuidBin16),STRING
fixed STRING(16)
CODE
! Reverse first 4 bytes
fixed[1] = pGuidBin16[4]
fixed[2] = pGuidBin16[3]
fixed[3] = pGuidBin16[2]
fixed[4] = pGuidBin16[1]
! Reverse next 2
fixed[5] = pGuidBin16[6]
fixed[6] = pGuidBin16[5]
! Reverse next 2
fixed[7] = pGuidBin16[8]
fixed[8] = pGuidBin16[7]
! Last 8 unchanged
fixed[9 : 16] = pGuidBin16[9 : 16]
RETURN fixed
Then:
st.SetValue(GuidFixForDisplay(MyGuid16))
st.GuidEncode()
Fix option B: don’t fetch as raw bytes
-------------------------------------
If you can, return the GUID already as text from SQL Server and skip GuidEncode():
SELECT CONVERT(char(36), MyGuidCol) ...
or
SELECT CAST(MyGuidCol AS varchar(36)) ...
Then treat it as a normal 36-character string.
Practical note
--------------
Exactly where to apply the fix depends on how Clarion/your SQL driver is returning the column:
- If it returns a 16-byte buffer (binary), use Fix A.
- If it returns a 36-char string (already formatted), do NOT use GuidEncode() at all.
A GUID is defined as Long + 2 UShorts + 8 Bytes
typedef struct _GUID {
unsigned long Data1;
unsigned short Data2;
unsigned short Data3;
unsigned char Data4[8];
} GUID;
I would guess ST is just 16 bytes of Hex so Little Endian for Long and Short.
The other reflects that it’s
LONG(8CE489C4h) +USHORT(03EEh) +USHORT(4F86h)
showing Big Endian. That’s the hex you would need in code to specify numbers, unless you specify it as a String. You will see common values like IUnknown in code.
Which is correct? Seems like it would matter how the field is treated when it is a Key. I.e. is it sorted as a STRING(16) so LE, or as LONG+SHORTs so BE. But does sort order really matter with what is a random number.
This may be relevant: GUID (Microsoft COM UUID Mixed-Endian) constants in Clarion
So, this is Microsoft’s implementation of the GUID. And solution is to rearrange string.
uuidSorted = uuidStr.GetValue()
uuidSorted = uuidSorted[7:8] & uuidSorted[5:6] & uuidSorted[3:4] & uuidSorted[1:2] & |
uuidSorted[9] & |
uuidSorted[12:13] & uuidSorted[10:11] & |
uuidSorted[14] & |
uuidSorted[17:18] & uuidSorted[15:16] & |
uuidSorted[19:36]
Thank you all for help, and explanation.
I do not know should I start new topic, but it is related to this.
I have a problem that sometimes .GuidEncode() does not work. Value stays 16 character binary string. Last time it happened UUID was:
DD2084B0-0F25-E000-197C-DA0085122420
Any ideas why is it happening?
Hi Blue,
The issue isn’t a bug in GuidEncode — it’s the byte ordering of SQL Server’s uniqueidentifier type. StringTheory’s GuidEncode just hex-encodes the 16 bytes in the order it receives them and inserts the hyphens. But SQL Server (like the Windows GUID struct) stores the first three fields little-endian: Data1 (4 bytes), Data2 (2 bytes) and Data3 (2 bytes) are byte-reversed, while the last 8 bytes (Data4) are stored as-is.
So when you feed the raw 16 bytes straight into GuidEncode, the first three groups come out reversed and it doesn’t match what SSMS shows. Reverse those three fields first and you get an exact match.
One small gotcha worth knowing: GuidEncode returns the hex in lowercase, whereas SSMS displays uppercase. They’re the same GUID (GUIDs are case-insensitive), but if you want a character-for-character match with SSMS just call st.Upper() afterwards — that’s what I’ve done below.
Here’s a complete little test program. The first message shows the raw/unfixed result; the second applies the byte-order fix and matches your SSMS value 8CE489C4-03EE-4F86-A5E4-612F3DEB233F:
PROGRAM
INCLUDE('StringTheory.inc'),ONCE
MAP
FixSqlGuidByteOrder PROCEDURE(STRING pGuid),STRING
END
st StringTheory
SqlGuid STRING(16)
EncodedGuid STRING(36)
FixedGuid STRING(36)
CODE
! Raw 16 bytes as returned for Blue's SQL Server GUID:
!
! SQL Server / SSMS displays:
! 8CE489C4-03EE-4F86-A5E4-612F3DEB233F
!
! But the binary representation contains:
! C4 89 E4 8C EE 03 86 4F A5 E4 61 2F 3D EB 23 3F
SqlGuid = '<C4h><89h><E4h><8Ch>' & |
'<EEh><03h>' & |
'<86h><4Fh>' & |
'<A5h><E4h><61h><2Fh><3Dh><EBh><23h><3Fh>'
! Show what GuidEncode does with the raw SQL/Windows bytes
st.SetValue(SqlGuid)
st.GuidEncode()
st.Upper() ! GuidEncode returns lowercase; SSMS shows uppercase
EncodedGuid = st.GetValue()
MESSAGE('GuidEncode directly:' & |
'<13,10>' & EncodedGuid & |
'<13,10><13,10>' & |
'Expected from Blue''s example:' & |
'<13,10>C489E48C-EE03-864F-A5E4-612F3DEB233F')
! Correct the SQL/Windows GUID byte ordering first
st.SetValue(FixSqlGuidByteOrder(SqlGuid))
st.GuidEncode()
st.Upper() ! GuidEncode returns lowercase; SSMS shows uppercase
FixedGuid = st.GetValue()
MESSAGE('After FixSqlGuidByteOrder:' & |
'<13,10>' & FixedGuid & |
'<13,10><13,10>' & |
'SQL Server / SSMS value:' & |
'<13,10>8CE489C4-03EE-4F86-A5E4-612F3DEB233F')
FixSqlGuidByteOrder PROCEDURE(STRING pGuid)
Guid STRING(16)
CODE
Guid = pGuid
! A Windows / SQL Server GUID represents the first three
! fields as little-endian values:
!
! Data1 4 bytes
! Data2 2 bytes
! Data3 2 bytes
! Data4 8 bytes
!
! GuidEncode treats all 16 bytes sequentially, so reverse
! the first three fields before passing it to GuidEncode.
Guid[1] = pGuid[4]
Guid[2] = pGuid[3]
Guid[3] = pGuid[2]
Guid[4] = pGuid[1]
Guid[5] = pGuid[6]
Guid[6] = pGuid[5]
Guid[7] = pGuid[8]
Guid[8] = pGuid[7]
! Bytes 9-16 remain unchanged.
RETURN Guid
@Mark_Sarson thanks for reply. I changed code, so that in StringTheory object is written changed order. I hope that this will help and that .GuidEncode() will be success always.