<jimi_xyz_at_hotmail.com> wrote in message
news:1125518620.045207.45070_at_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
>
I don't think it works. Why are you building up a string and expecting that
to work? Don't do that.
Jim
Received on Wed Aug 31 2005 - 20:38:01 CDT
