Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing GUID from cookie to cfstoredproc as cfprocparam

Posted on 2009-04-21
14
Medium Priority
?
742 Views
Last Modified: 2013-12-24
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?


   
0
Comment
Question by:largeone
  • 8
  • 6
14 Comments
 
LVL 53

Expert Comment

by:_agx_
ID: 24197605
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
0
 

Author Comment

by:largeone
ID: 24197623
They are generated server-side with NEWID(), so they are SQL Server GUIDs.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 24197697
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:largeone
ID: 24197766
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

0
 

Author Comment

by:largeone
ID: 24197893
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

0
 
LVL 53

Expert Comment

by:_agx_
ID: 24197964
Yes, I get the same error too now with the first proc signature.   Maybe it has to do with it being an output param...
0
 

Author Comment

by:largeone
ID: 24198002
I was afraid that might be the case.  And, the second proc does not use it as output.  Any other angles?
0
 
LVL 53

Expert Comment

by:_agx_
ID: 24198032
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

0
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 24198040
Correction:
<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_timestamp" null="true" />
	<cfprocresult name="x">
</cfstoredproc>

Open in new window

0
 
LVL 53

Expert Comment

by:_agx_
ID: 24198088
...  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"  
0
 

Author Closing Comment

by:largeone
ID: 31572949
I can't tell you how much easier my week just became.  

Thanks!
0
 
LVL 53

Expert Comment

by:_agx_
ID: 24198097
Glad I could help :)
0
 

Author Comment

by:largeone
ID: 24198107
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!
0
 
LVL 53

Expert Comment

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question