coldfusion converts GUID to binary

[JJBBDD]

here's a simple stored procedure

CREATE PROCEDURE guidTEST
AS
SELECT newID() as guidTEST,
'8B710BAD-2F8C-42AA-BA67-8984CBA81BD6' as guidTEST2

when i run this sproc from the query analyzer (exec guidTEST)
it returns, as expected, a query with a new GUID and the 2nd GUID as a string

BUT, when I run this:

<cfstoredproc procedure="guidTEST" datasource="test">
<cfprocresult resultset="1" name="test">
</cfstoredproc>
<cfdump var="#test#">

the first column is converted to binary, (the second column is still a string).
how do i make sure the first col stay returns as a string?

i'm running CF7, SQL2000, apache

THANKS!!

[MikerRoo]

SELECT
CAST (newID() AS varchar(55)) as guidTEST

[JJBBDD]

thanks... i'm wondeirng if there's anything that would work as a global
solution since there are a ton of instances in which i grab GUIDs from the db.

also, actually, i was a little bit off here. apparently cf 6 and above convert
GUIDs to bytearrays. what's strange is that this problem isn't happening on the
production server. it's only occuring on my local dev machine. is there some
global setting that i'm missing that automatically converts bytearrays to
strings? or perhaps a setting on the database side thate ensures more friendly
encoding of GUIDs?

if not, can someone recommend a workaround that will let me convert the
bytearrays when i need to?

thanks

-j

[MikerRoo]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>JJBBDD</b></b></i>
... if not, can someone recommend a workaround that will let me convert the
bytearrays when i need to?<hr></blockquote>
See the post above!

Anyway, you are quite incorrect, "CF6 and above" does not "convert GUIDs to
bytearrays". If you examine the type of the returned column it is
"uniqueidentifier" but it normally behaves just like a string in CF. Output
and string functions work just as on any string.

Anyway, you're a fool if you use GUIDs. Google "COMB GUIDS".

COMB GUID's would be the global workaround you asked for.

[JJBBDD]

okay, i can cast the guid as a string it it works....

unfortunately, this won't work as a solution for me because I'm dealing with a
legacy app that passes a ton of GUIDs back and forth to the DB.

i'm running CF7, SQL2000, apache locally. the production site uses CF6.1. The
only thing I can think of is that 6.1 interprets GUIDs differently than 7. Can
anyone else even replicate this issue? I'm confused and have been serching for
a week with no luck.

[MikerRoo]

You can try running "SELECT @@OPTIONS" against both databases and see if there
are any differences.

But the fact is GUIDS become strings the moment you use them in CF6 and 7.

Attach the EXACT error mmessagge you get and the code that generated it.

[JJBBDD]

mike-- thanks for sticking with me on this. i've posted this issue to some
other forums with no luck.

i tired select@@ options...
my dev box returned 5496
the production box returned 1080
(note, the production box is running sql 7)

here are 2 screen shots of the error, the stack, and a dump of the relevant
query-- in the dump, it labels the 2 GUIDs as binary objects, but i think they
are byte arrays (at least, they certainly are not binary, and when I run
anything on them, coldfusion tells me what i'm doing will not work with byte
arrays)

<a target=_blank class=ftalternatingbarlinklarge
href="http://www.thepokerhound.com/bpoerror.gif
[url]http://www.thepokerhound.com/stackerr.gif[/url]

also,">[url]http://www.thepokerhound.com/bpoerror.gif[/url]
[url]http://www.thepokerhound.com/stackerr.gif[/url]

also,</a> it occurs to me that the problem may be because I'm using an ODBC
connection... i could not get the standard sql server connector to work
(presumably JDBC), and they are a number of posts that suggest the ODBC
workaround.

thanks again.

[MikerRoo]

Yes, the ODBC driver is the problem. Sorry I forgot to ask but that is a
non-standard configuration and you didn't state that SQL server 7 was involved.

Anyway, goto CF administrator and createnew data sources using the "Microsoft
SQL server" type. Then delete the ODBC sources both in CF and the control
panel -- they are a security risk.

Using CF's drivers, you will see superior performance and the GUID's will
behave normally (act as strings when you need them to).

BTW the @@option differences translate to CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER, and ARITHABORT settings (or at least they would if both
servers were MSSQL 2000). The dev box is set more correctly but none of
these settings have a bearing on your guid problem.