serializeJSON UUID returns LeastSignificantBits, MostSignificantBits

dev
1

I have record ID’s that are UUID’s in sql database. When I use serializeJSON(q, ‘struct’), it returns LeastSignificantBits":-5173477121847357440,“MostSignificantBits”:-8480164728915343360}.

How can I get to the original UUID?

Thanks.

2

@nalbee, Always mention a version. And if possible put your test case it will help to identify your problem. Also, send the output of serializeJSON.

3 
<cfquery name="q" datasource="log">
	SELECT	NewId() as GUID
</cfquery>
<cfscript>
	dump(server.lucee.version);
	dump(getMetadata(q));
	dump(q);
	dump(serializeJson(q));
</cfscript>

with sql server (I no idea which db you are using as you didn’t mention it), i get the following output

image
image1019×579 79.1 KB

4

What I want to achieve is to return the UUID’s in json (example: 71d8ecea-1652-48e0-a0cb-3afe267c351f).

Not like this: [{“ID”:{“LeastSignificantBits”:-5173477121847357440,“MostSignificantBits”:-8480164728915343360}}]

Postgres DB.

<cfquery name="getBuildings" datasource="iot" maxrows="1">
select ID FROM "businessEntityID"
WHERE "ID"=  <cfqueryparam value = '71d8ecea-1652-48e0-a0cb-3afe267c351f' CFSQLType = "cf_sql_other">
</cfquery>

<CFDUMP var="#getBuildings#">

<cfoutput>
    getBuildings.ID:<br>
    #getBuildings.ID# <br>
</cfoutput>

serializeQueryByColumns:<br>
<CFDUMP var="#serializeJSON(getBuildings, 'struct')#">

Screen Shot 2020-06-17 at 10.15.00 AM
Screen Shot 2020-06-17 at 10.15.00 AM842×722 69.4 KB

5

I can fix it in sql: by using cast in sql: CAST(ID AS VARCHAR(50)):

<cfquery name="getBuildings" datasource="iot" maxrows="1">

select CAST(ID AS VARCHAR(50)) FROM "buildings"
WHERE "businessEntityID"= <cfqueryparam value = '71d8ecea-1652-48e0-a0cb-3afe267c351f' CFSQLType = "cf_sql_other">

</cfquery>e

image
image869×274 26.6 KB

Curious why serializeJSON returns that, is that expected?

6

Looks like a casting issue, SQL server maps to a known datatype, postgres doesn’t. File a bug