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
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
