Skip to main content
Participant
September 21, 2006
Question

Stored Procedure Call from CFMX7

  • September 21, 2006
  • 3 replies
  • 563 views
I have a stored procedure that returns properly when run from ColdFusion 5, but not ColdFusion MX. Under MX, it returns an empty string. The execution of the procedure by itself works as it should, but for some reason I can't get it to execute right under MX. The procedure is on SQL Server 2000.

Here is my call:

<CFSTOREDPROC procedure="x3ed" datasource="centersdb" debug="yes" returncode="yes">
<CFPROCPARAM type = "IN" cfsqltype="CF_SQL_VARCHAR" value="E">
<CFPROCPARAM type = "IN" cfsqltype="CF_SQL_VARCHAR" value=#form.password#>
<cfprocparam type = "OUT" cfsqltype="CF_SQL_VARCHAR" variable = "encpwd">
</cfstoredproc>

Any ideas? I wasn't able to find any deprecated attributes that I am using. Thanks in advance for your help.
This topic has been closed for replies.

3 replies

September 25, 2006

<CFPROCPARAM type = "IN" cfsqltype="CF_SQL_VARCHAR" value=#form.password#>

Have you tried it with quotes (around form.password) ?

<CFPROCPARAM type = "IN" cfsqltype="CF_SQL_VARCHAR" value="#form.password#">


Have you tried creating a much more simple proc to test the OUT feature?


Good luck!
Participant
September 25, 2006
That's the thing. I don't receive any error message. I just don't get a return value from the procedure.
Known Participant
September 25, 2006
Well then, can you post the code for your stored procedure? Thanks.

Chris
Participant
September 25, 2006
Yes-here it is. It's a password encrypt/decript script.
---------------
CREATE PROCEDURE X3ED
@tcAction varchar(1) , @tcString varchar(20), @tcStringOut varchar(20) output

AS

DECLARE @lnLen int
DECLARE @lcString varchar(20)
DECLARE @lnXX int
DECLARE @lnCharPos int
DECLARE @lnAscChar int
DECLARE @lcString2 varchar(20)
DECLARE @lcRetVal varchar(20)
DECLARE @lcAction varchar(1)
DECLARE @lnInt int
DECLARE @gcAscii varchar(255)
DECLARE @passascraw int
DECLARE @passlookraw int
DECLARE @lnminus int
DECLARE @lnmodint int


SET @lnLen = 20 --MUST BE SET TO THE SIZE OF THE PASSWORD

SET @lcAction=UPPER(@tcAction)

SET @gcAscii = ''
SET @lnXX=255

WHILE @lnXX >=0
BEGIN
SET @gcAscii = @gcAscii + char(@lnXX)
SET @lnXX=@lnXX-1
END

IF @lcAction <> 'E' and @lcAction <> 'D'
BEGIN
SET @tcStringOut =null
RETURN
END

IF @lcAction = 'E'
BEGIN
SET @lcString = ''
SET @lnXX = 1
WHILE @lnXX <= @lnLen
BEGIN
SET @lnCharPos = @lnLen - @lnXX+1
SET @lnAscChar =ASCII(SUBSTRING(@tcString,@lnCharPos,1))
SET @lnInt = @lnXX/2
IF (convert(numeric(5,3),@lnXX)/2 -@lnInt)*2 = 0
begin
SET @lnAscChar = @lnAscChar+1
end
ELSE
begin
SET @lnAscChar = @lnAscChar+2
end

SET @lnXX = @lnXX+1
SET @lcString=@lcString + char(@lnAscChar)
END

SET @lcString2 = ''
SET @lnXX=len(@lcString)
SET @lnInt = 1
WHILE @lnInt <= @lnXX
BEGIN
SET @lcString2 = @lcString2+SUBSTRING(@gcAscii,ASCII(SUBSTRING(@lcString,@lnInt,1)),1)
SET @lnInt = @lnInt+1
END

SET @tcStringOut = @lcString2

RETURN
END




IF @lcAction = 'D'
BEGIN
SET @tcStringOut=''
SET @lcString2= ''
SET @lnXX=1
SET @lnInt =1
set @passascraw = 1
set @passlookraw = 1
SET @lcString=''

WHILE @lnXX<=@lnLen
--eqivalant to the sys(15) in foxpro
BEGIN

--gets the ascii of the password
set @passascraw = ascii(SUBSTRING(@tcString, @lnXX,1))

--gets the ascii of the @passascraw position in the @gcascii variable.
--@gcascii is a reverse ascii table
set @passlookraw = ascii(substring(@gcAscii, @passascraw,1))

SET @lnmodint = @lnXX/2

IF convert(numeric(5,3),@lnInt)/2 -@lnmodint = 0
BEGIN
SET @lnminus =1
END
ELSE
BEGIN
IF (convert(numeric(5,3),@lnXX)/2 -@lnmodint)*2 =0
begin
SET @lnminus =1
end
ELSE
begin
SET @lnminus = 2
end
END

SET @lnAscChar = @passlookraw - @lnminus
SET @lcString=@lcString+ char(@lnAscChar)
SET @lnXX = @lnXX+1

END
SET @lnXX=@lnlen
SET @lnInt = 1
WHILE @lnXX>=@lnInt
begin
SET @tcStringOut =@tcStringOut+substring(@lcString, @lnXX,1)
SET @lnXX = @lnXX-1
end
RETURN
END
GO
---------------

I hope this helps. To reiterate, this returns fine in CF5.
Known Participant
September 21, 2006
Can you provide an error message to assist us in helping you? Thanks.

Chris