You should actually test your stored procedure as opposed to testing the
code in it. :P But I think you are right in suspecting that the problem
lies in CFML. Before you go hunt there though, try this in SQL Query
Analyzer (assuming that your stored proc is still called detail_story
and that the parameters have not changed):
DECLARE
@archive_id bigint,
@NB_ID char,
@COPYRIGHT varchar,
@PUB_DATE datetime,
@P_SECTION varchar,
@PAGE char OUTPUT,
@WORD_COUNT char,
@HEADLINE varchar,
@SUBHEAD varchar,
@LEDE varchar,
@STORY NTEXT
SET @archive_id = 2345
execute detail_story
@archive_id = @archive_id OUTPUT,
@NB_ID = @NB_ID OUTPUT,
@COPYRIGHT = @COPYRIGHT OUTPUT,
@PUB_DATE = @PUB_DATE OUTPUT,
@P_SECTION = @P_SECTION OUTPUT,
@PAGE = @PAGE OUTPUT,
@WORD_COUNT = @WORD_COUNT OUTPUT,
@HEADLINE = @HEADLINE OUTPUT,
@SUBHEAD = @SUBHEAD OUTPUT,
@LEDE = @LEDE OUTPUT,
@STORY = @STORY OUTPUT
PRINT 'Executed Query. Now lets see what happened to the variables'
PRINT '@archive_id = ' + @archive_id
PRINT '@NB_ID = ' + @NB_ID
PRINT '@COPYRIGHT = ' + @COPYRIGHT
PRINT '@PUB_DATE = ' + @PUB_DATE
PRINT '@P_SECTION = ' + @P_SECTION
PRINT '@PAGE = ' + @PAGE
PRINT '@WORD_COUNT = ' + @WORD_COUNT
PRINT '@HEADLINE = ' + @HEADLINE
PRINT '@SUBHEAD = ' + @SUBHEAD
PRINT '@LEDE = ' + @LEDE
PRINT '@STORY = ' + @STORY
Maybe this might shed some light? If not, the next debug thing to do is
to store the @ARCHIVE_ID that the stored proc received into a debug
table. Then query that table after you tested in CFML to see what values
were given to the stored proc by ColdFusion. Maybe it has to do with
VARCHAR to BIGINT conversion... :P
----------------------------
James Ang
Programmer
MedSeek, Inc.
[EMAIL PROTECTED]
-----Original Message-----
From: Owens, Howard [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 17, 2003 2:27 PM
To: CF-Talk
Subject: RE: StoredProc Error Num 3
Well, I'm getting no where fast ... and this is the last thing I need to
get
working, and then I have this application finished ... grrrr
As I said previously, cfdump turns up a big blank.
I ran this test in query analyzer and it worked:
DECLARE @archive_id bigint
select @archive_id = 2345
SELECT ARCHIVE_ID,
NB_ID,
COPYRIGHT,
PUB_DATE,
P_SECTION,
PAGE,
WORD_COUNT,
HEADLINE,
SUBHEAD,
LEDE,
STORY
FROM story_archive
WHERE ARCHIVE_ID = @ARCHIVE_ID
So, I remain stumped.
H.
> -----Original Message-----
> From: James Ang [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 12:06 PM
> To: CF-Talk
> Subject: RE: StoredProc Error Num 3
>
> When you said no rows, are you saying that:
>
> <cfdump var="#FULL_TEXT#">
>
> Returns an empty query dump? Does the query even have any columns?
>
> Judging from your stored procedure definition, those OUTPUT parameters
> are not needed since you never assign them any value. But if you do
need
> the output parameters, perhaps you meant to have:
>
> SELECT
> @NB_ID = NB_ID,
> @COPYRIGHT = COPYRIGHT,
> @PUB_DATE = PUB_DATE,
> @P_SECTION = P_SECTION,
> @PAGE = PAGE,
> @WORD_COUNT = WORD_COUNT,
> @HEADLINE = HEADLINE,
> @SUBHEAD = SUBHEAD,
> @LEDE = LEDE,
> @STORY = STORY
> FROM story_archive
> WHERE ARCHIVE_ID = @ARCHIVE_ID
>
> If you do the above, then: FULL_TEXT will definitely be empty, BUT all
> your <cfprocparam> variables will be populated after the stored proc
> call.
>
> And for future stored proc "bugs", you should test with SQL Query
> Analyzer first to make sure that your stored proc is working there. If
> it is, then the problem is in the CFML code. :) And nice thing about
> testing in SQL Query Analyzer is that you can use PRINT statements in
> your stored proc (which may or may not be deadly in a CFSTOREDPROC
call)
> to help you debug better. :)
>
> ----------------------------
> James Ang
> Programmer
> MedSeek, Inc.
> [EMAIL PROTECTED]
>
>
>
>
>
> -----Original Message-----
> From: Owens, Howard [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 17, 2003 11:13 AM
> To: CF-Talk
> Subject: RE: StoredProc Error Num 3
>
>
> I'm still pulling my hair out over this stored proc.
>
> To recap, the variable archive_id does not seem to be getting passed
to
> the
> query ... so no rows (and 1 row should be returned) are being
returned.
>
> Here's my cf tags:
>
> <cfstoredproc procedure="detail_story"
> datasource="#request.dsn#"
> returncode="yes">
>
>
>
> <cfprocparam type="INOUT"
> cfsqltype="CF_SQL_VARCHAR"
> variable="ARCHIVE_ID"
> value="#URL.SID#"
> null="yes">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_CHAR"
> variable="NB_ID"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_VARCHAR"
> variable="COPYRIGHT"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_TIMESTAMP"
> variable="PUB_DATE"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_VARCHAR"
> variable="P_SECTION"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_CHAR"
> variable="PAGE"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_CHAR"
> variable="WORD_COUNT"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_VARCHAR"
> variable="HEADLINE"
> null="NO">
>
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_VARCHAR"
> variable="SUBHEAD"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_VARCHAR"
> variable="LEDE"
> null="NO">
>
> <cfprocparam type="Out"
> cfsqltype="CF_SQL_longVARCHAR"
> variable="STORY"
> null="no">
>
> <cfprocresult name="FULL_TEXT"
> resultset="1">
>
>
> </cfstoredproc>
>
> Here's my stored proc:
>
>
>
>
> CREATE PROCEDURE detail_story
> @ARCHIVE_ID bigint,
> @NB_ID char OUTPUT,
> @COPYRIGHT varchar OUTPUT,
> @PUB_DATE datetime OUTPUT,
> @P_SECTION varchar OUTPUT,
> @PAGE char OUTPUT,
> @WORD_COUNT char OUTPUT,
> @HEADLINE varchar OUTPUT,
> @SUBHEAD varchar OUTPUT,
> @LEDE varchar OUTPUT,
> @STORY NTEXT OUTPUT
> AS SELECT ARCHIVE_ID,
> NB_ID,
> COPYRIGHT,
> PUB_DATE,
> P_SECTION,
> PAGE,
> WORD_COUNT,
> HEADLINE,
> SUBHEAD,
> LEDE,
> STORY
> FROM story_archive
> WHERE ARCHIVE_ID = @ARCHIVE_ID
> GO
>
> I even tried editing the stored proc to hardcode a variable
@archive_id,
> and
> I still got no results returned. But if I run the query in the SQL
> admin,
> runs fine ... returns results.
>
> It's probably some stupid simple little error, but I can't see it.
>
> H.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
