asked on

Passing GUID from cookie to cfstoredproc as cfprocparam

I have a DB structure that uses SQL Server uniqueidentifier fields as Keys on all it's tables. There are times when these ID's are necessary to access user info from the Cold Fusion UI. These values are stored as session cookies.

I am getting the following error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to uniqueidentifier.

Whenever I try to use a cfprocparam to run a stored procedure from the cold fusion site.

For example:
<cfprocparam type="in" cfsqltype="cf_sql_char" dbvarname="@MemberID" value="#cookie.MEMID#" />

fails with cf_sql_char, and cf_sql_idstamp.

Any ideas?

_agx_

What is the value of the cookie you are trying to insert? Is it a valid SQL Server UUID, because they are not the same format as CF UUID's.

http://www.cflib.org/udf/CreateGUID

largeone

ASKER

They are generated server-side with NEWID(), so they are SQL Server GUIDs.

_agx_

It works fine for me with cf_sql_char on CF8/MS SQL 2005. Check the order of the parameters. "dbVarName" is deprecated. So you have to pass in the parameters in the same order as they are declared in the procedure.

CREATE PROCEDURE Test
@uuid uniqueidentifier
AS
BEGIN
	SELECT @uuid AS Result
END
GO
 
<cfstoredproc datasource="#dsn#" procedure="test">
	<cfprocparam type="in" cfsqltype="cf_sql_char" value="#cookie.MEMID#" />
	<cfprocresult name="x">
</cfstoredproc>
 
<cfdump var="#x#">

Open in new window

largeone

ASKER

Still getting the same.

See param defs and cf tags below.

ALTER PROCEDURE [dbo].[pMemberProgramChangeRequestInsUpd]
    @ChangeRequestID UNIQUEIDENTIFIER = NULL OUTPUT,
    @MemberID UNIQUEIDENTIFIER,
    @FromProgramTypeCD TINYINT,
    @ToProgramTypeCD TINYINT,
    @RequestDttm DATETIME,
    @UpgradeDttm DATETIME = NULL
AS
...
 
 
<cfstoredproc procedure="pMemberProgramChangeRequestInsUpd" datasource="#Application.db_dsn#" username="#Application.db_username#" password="#Application.db_password#">
        	<cfprocparam type="inout" cfsqltype="cf_sql_char" dbvarname="@ChangeRequestID" value="NULL"  variable="NewID" />  
            <cfprocparam type="in" cfsqltype="cf_sql_char" dbvarname="@MemberID" value="#cookie.MEMID#" />
            <cfprocparam type="in" cfsqltype="cf_sql_tinyint" dbvarname="@FromProgramTypeCD" value="#member.networkProgram#"/>
            <cfprocparam type="in" cfsqltype="cf_sql_tinyint" dbvarname="@ToProgramTypeCD" value="#form.newProgram#"/>
            <cfprocparam type="in" cfsqltype="cf_sql_DateTime" dbvarname="@RequestDttm" value="#FirstOfMonth#"/>
			<cfprocparam type="in" cfsqltype="cf_sql_DateTime" dbvarname="@UpgradeDttm" value="NULL"/>
        </cfstoredproc>

Open in new window

largeone

ASKER

It might be worth noting that I have another one of these procs being called as below without a problem. I haven't checked the return values yet, but it does not give me the same error. I have already tried the "cf_sql_uniqueidentifier" type on the above sp too.

<cfstoredproc procedure="pCheckCompleteProfile" datasource="#Application.db_dsn#" username="#Application.db_username#" password="#Application.db_password#">
<cfprocparam type="in" cfsqltype="cf_sql_uniqueidentifier" dbvarname="@MemberID" value="#cookie.MEMID#" />
<cfprocparam type="out" cfsqltype="cf_sql_bit" dbvarname="@IsComplete" variable="ProfileComplete" />
</cfstoredproc>

Open in new window

_agx_

Yes, I get the same error too now with the first proc signature. Maybe it has to do with it being an output param...

largeone

ASKER

I was afraid that might be the case. And, the second proc does not use it as output. Any other angles?

_agx_

Oh, no. It's a wrong data type (cf_sql_datetime). This works:

<cfstoredproc datasource="#dsn#" procedure="pMemberProgramChangeRequestInsUpd">
	<cfprocparam type="out" cfsqltype="cf_sql_char" null="true" variable="NewID" />
	<cfprocparam type="in" cfsqltype="CF_SQL_char" value="#cookie.MEMID#" />
	<cfprocparam type="in" cfsqltype="cf_sql_tinyint"  value="#member.networkProgram#"/>
    <cfprocparam type="in" cfsqltype="cf_sql_tinyint" value="#form.newProgram#"/>
    <cfprocparam type="in" cfsqltype="cf_sql_timestamp" value="#FirstOfMonth#"/>
    <cfprocparam type="in" cfsqltype="cf_sql_DateTime" null="true" />
	<cfprocresult name="x">
</cfstoredproc>

Open in new window

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

... Note:

1. Removed dbvarname (it doesn't work anyway)
2. Changed data type: cf_sql_timestamp instead of cf_sql_datetime
3. Used null attribute ( null="true" ) instead of value="null"

largeone

ASKER

I can't tell you how much easier my week just became.

Thanks!

_agx_

Glad I could help :)

largeone

ASKER

I was thinking value="NULL" was the problem, but I could have danced around the timestamp thing for days...actually it has been days now!

_agx_

Well, I just changed everything in one shot. So it may well have been a combination of both of them ;-)