My stored procedure selects many records, but my <cfstoredproc> only returns 1 row (and dataset.recordcount = 1).
Here's the CF
Code:
<cfstoredproc datasource="myDataSource" procedure="P_AllActive" returnCode="No">
<cfprocresult name = "P_All" maxrows="-1">
</cfstoredproc>
<html>
<head>
<title>SP Test</title>
</head>
<cfoutput>
<p>Record Count: #P_All.recordCount# <p>Columns: #P_All.columnList#
<hr>
</cfoutput>
<table border=1 width=80% aligh=center>
<cfoutput query="P_All">
<tr>
<td width=30%>#P_All.PRSN_Full_Name#</td>
<td width=30%>#P_All.RANK_ID#</td>
<td width=15%>#P_All.PERS_ID#</td>
<td width=15%>#P_All.BSUN_ID#</td>
<td width=10%>#P_All.SHFT_ID#</td>
</tr>
</cfoutput> (Only 1 result row gets displayed)
</table>
</body>
</html>
===================================================
Here's the stored proc (Firebird 1.0.x, / Interbase)
Code:
CREATE PROCEDURE P_ALLACTIVE returns (
PRSN_FULL_NAME VarChar(35),
RANK_ID VarChar(12),
SHFT_ID VarChar(1),
BSUN_ID VarChar(15),
PERS_ID Integer)
AS
begin
FOR
SELECT
R.COMMON_NAME as PRSN_Full_Name,
R.RANK as RANK_ID,
R.SHIFT as SHFT_ID,
R.COMPANY as BSUN_ID,
R.TXFIR_ID as PERS_ID
FROM PERSONNEL R
JOIN COMPANIES R1 on (R1.COMPANY = R.COMPANY)
JOIN RANKS R2 on (R2.RANK = R.RANK)
WHERE (R.STATUS = 'A')
ORDER BY R.COMMON_NAME
INTO RSN_FULL_NAME,:RANK_ID,:SHFT_ID,:BSUN_ID,ERS_ID
DO
BEGIN
SUSPEND;
END
end
======================================================
Version Info:
CFMX 6.1 (Same results with CFMX 7)
Firebird 1.0.x SuperServer (working solid for 2 yr)
JDBC driver: Jaybird (v1.5.5.Release, Class 4 JCA-JDBC)
JDK 1.4
Win XP Pro
The stored proc returns about 1000 records when executed in database admin tool (DBworkbench)
Could it be a problem with the JDBC driver? I have stored procs that return 1 value (such as a count) that work fine, I'm just having problems with returning a dataset. Any help is greatly appreciated.
Dev Shed Forums Sponsor:
July 13th, 2005, 03:50 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
