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
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
