Coldfusion UUID issues?

[BusseGardens]

I am baffled by this problem and hope someone can help.  I have a perennial plant website that takes online orders.  For most customers, including me when I place test orders, things work as designed.  However some customers generate errors.

When a new customer order comes in I create a customer ID and an order ID:

<cfset variables.CustID = #CreateUUID()#>

<cfset variables.CustOrderID = #CreateUUID()#>

I have tried this without the # signs and the result seems to be the same.  In the usual case a Coldfusion UUID is created in the form 8-4-4-16.

I write the customer ID to the customer table into an nvarchar(max) column and write both the customer ID and order ID into the order table into nvarchar(max) columns.  I also have an order item detail table into which I write the custOrderID into an nvarchar(max) column.

As noted, this works for most customers.

However, for some customers - at least one that I am working with - two odd things occur that I can't figure out.

Here's the error reported to the customer:

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting the varchar value 'b9b8bc49fcad6e0f-9C03B2A0-B0A9-5D8C-6FE0A4C952F8BD4F' to data type int.

MY QUESTIONS:

The two mysteries for me are (1) where does this long string come from, that looks like a UUID, but is in the form 16-8-4-4-16, so it has an extra 16 characters at the beginning and (2) why is a conversion to INT being attempted?  My code simply writes the uuids into the columns that are set up as nvarchar(max).

A typical insert query that I use looks like this:

<cfquery name="qryCreateOrderRecord" datasource="bussesql">

Insert into tblCustOrders (CustOrderID,CustID)

<cfoutput>

values('#custorderid#','#custid#')

</cfoutput>

</cfquery>

FINAL QUESTION:

Lastly, why does my code work for some users without any problem and generates this error for at least one.  There are other customers who have had problems, but they were not specific o the error messages.

Any thoughts greatly appreciated.

Gary

[WolfShade]

bussegardens  wrote

When a new customer order comes in I create a customer ID and an order ID:

<cfset variables.CustID = #CreateUUID()#>

<cfset variables.CustOrderID = #CreateUUID()#>

Without the hashtags is the proper way.  Using hashtags, unless inside quotes as a string or for visual output to the browser, slows things down.

bussegardens  wrote

I write the customer ID to the customer table into an nvarchar(max) column and write both the customer ID and order ID into the order table into nvarchar(max) columns.  I also have an order item detail table into which I write the custOrderID into an nvarchar(max) column.

I am curious as to why varchar(max), when varchar(36) is sufficient.  I know that the length is dynamically determined upon insert and it doesn't bloat anything, I just don't see a need for max.  (I'm a bit pedantic.)

bussegardens  wrote

(1) where does this long string come from, that looks like a UUID, but is in the form 16-8-4-4-16, so it has an extra 16 characters at the beginning.

This I am at a loss to explain.  I can't figure that out, either.  It should not be happening, but I would have to see code before I could even think of anything.

bussegardens  wrote

(2) why is a conversion to INT being attempted?  My code simply writes the uuids into the columns that are set up as nvarchar(max).

Something, somewhere, is attempting to convert to int.  Are you using cfqueryparam?  Again, I'd have to see code.

bussegardens  wrote

A typical insert query that I use looks like this:

<cfquery name="qryCreateOrderRecord" datasource="bussesql">

Insert into tblCustOrders (CustOrderID,CustID)

<cfoutput>

values('#custorderid#','#custid#')

</cfoutput>

</cfquery>

You don't need <cfoutput> tags inside a cfquery; ColdFusion assumes anything between hashtags is a variable.  And you should be using cfqueryparam tags to contain the values, to prevent against SQL injection, even against CF generated UUIDs.

bussegardens  wrote

Lastly, why does my code work for some users without any problem and generates this error for at least one.  There are other customers who have had problems, but they were not specific o the error messages.

I frequently run into situations like this, where it works for 99% of the time, but occasionally blows up for an odd user or two, with no discernible triggers.  I just blame gremlins. 

V/r,

^ _ ^

[Dave Watts]

First, the hashes are unnecessary. They don't hurt anything, but in general you should only use them when you're generating a string to output.

Second, I would see if I could find the exact values of the UUIDs that are causing a problem. It's possible that something in the UUID value itself is causing the problem. UUIDs don't really have a universal format despite the name, and it's possible that your database is seeing something here that it really shouldn't.

Third, if you know the exact length of a value when it's expressed as a string, I'd create a character field of exactly that value. The CHAR and NCHAR datatypes are designed for this. VARCHAR is designed for strings where you don't know the length.

Fourth - and this is where your specific problem probably is - you should not use CFOUTPUT to output strings within a CFQUERY. You should use CFQUERYPARAM and tell the database explicitly what datatype the values are (CF_SQL_CHAR or CF_SQL_VARCHAR should work here).

I don't know where the additional value is coming from. Perhaps it's intended as a machine identifier. You might be able to solve this by using your database to generate your UUIDs instead of CF, if the fourth item doesn't fix your problem.

Dave Watts, Fig Leaf Software