API works fine, but returns no results to ASP front end

From: <jimi_xyz_at_hotmail.com>
Date: 31 Aug 2005 13:03:40 -0700
Message-ID: <1125518620.045207.45070@g43g2000cwa.googlegroups.com>

OK,
I have a pl/sql API which seems to work fine when i test it in sqlPLUS, but when I try to run it through the ASP front end, it returns no results.

code..
PL/SQL code--------

---

PROCEDURE drill(in_projIDarray	in
	Jiffy_IntArray_Def_API.int_array_typ,
			in_taxID	in		number,
	 		out_error_code 	 	out 	number,
		 	out_error_message 	out 	varchar2,
		 	out_ret_val 	 	out 	Jiffy_Cursor_Def_API.ref_cur_typ )

IS
BEGIN projIDs := in_projIDarray(1);
If in_projIDarray.count > 1 Then

	FOR i in 2..in_projIDarray.Count LOOP
		projIDs := projIDs || ', ' || in_projIDarray(i);
	END LOOP;

End If;

vgbl_sql := 'SELECT project.projectid' ||

			' FROM project, projectassociations' ||
			' WHERE taxid =' || in_taxID ||
			' AND project.projectid = projectassociations.projectid' ||
			' AND project.projectid in ('|| projIDs ||')';

OPEN out_ret_val FOR vgbl_sql;

stdout(vgbl_sql);
	 out_error_code := 0;
	 out_error_message := SQLERRM;

EXCEPTION
WHEN OTHERS THEN

		out_error_code := SQLCODE;
		out_error_message := '[' || CONST_PACKAGENAME || '.drill] ' ||
SQLERRM;
	ROLLBACK;

END drill;

---

------SQL PLUS test procedure....

declare
id Jiffy_IntArray_Def_API.int_array_typ; tax number;
code number;
mess varchar2(100);
begin
id(1) := 10;
id(2) := 9;
tax := 30;
taxonomy_api.drill(id, tax, code, mess, :cv); end;
/

--This will return the correct result..

---

------ASP class...

'--------------------------------------------------------------------
	' Procedure: drill
	'
	' Purpose:
	' Inputs:
	' Returns:
	'--------------------------------------------------------------------

Public Function drill(ByVal ProjectIDarray, ByVal TaxID) Dim sqlparams, sqlretvals, sqlproc

sqlproc = "Taxonomy_API.drill"
dim i

sqlparams = array( "in_projIDarray", ProjectIDarray,

ORAPARM_INPUT,     ORATYPE_ARRAY, _
		"in_taxID",  TaxID,        ORAPARM_INPUT,     ORATYPE_NUMBER, _
   		"OUT_ERROR_CODE",    	0,    		ORAPARM_OUTPUT,   ORATYPE_NUMBER, _
   		"OUT_ERROR_MESSAGE",    "",    		ORAPARM_OUTPUT,
ORATYPE_VARCHAR2, _
   		"OUT_RET_VAL",    	null,    	ORAPARM_OUTPUT,   ORATYPE_CURSOR )

sqlretvals = APIIntFace( sqlproc, gobjConn, sqlparams, true )

'//
'// sqlretvals is an array of n Output values
'//

response.write "<BR>test == " &sqlretvals(2).recordcount&"<br>" set drill = sqlretvals(2)

set sqlretvals = nothing : set sqlparams = nothing End Function

--I beleive this is where I lose information, when the API returns the
information.

------ASP front end, the user will see this section...

---

Sub Drill(ProjID, TaxID)
Dim ObjDrill, rsDrill, i

response.write ProjID
ProjID = Split(ProjID, " ")

for i = lbound(ProjID) to Ubound(ProjID)

        ProjID(i) = CInt(ProjID(i))
Next

Set ObjDrill = NEW taxonomyClass
Set rsDrill = ObjDrill.drill(ProjID, Taxid)

Do While NOT rsDrill.EOF

	Response.write "Projectid = " &rsDrill&"<BR>"
	rsDrill.MoveNext

Loop

End Sub

---

--I am not to sure what is wrong with my code; any information will
help..

Thank you,
Jimmie Received on Wed Aug 31 2005 - 15:03:40 CDT