Re: designer webserver library (wsgl?) problems

From: frank <fbortel_at_home.nl>
Date: Tue, 10 Oct 2000 19:03:57 GMT
Message-ID: <39E36876.82EC73A1@home.nl>

Well, I see the same over here now, and all I can say for now is that it is gone with V6 WSG.
It's been a while since I've been digging in the generated code, but i'll see what I can come up with - could take some time, as I can only do this at home in the evening, all else permitting. Rest of comments inline:

Verna Legaspi wrote:

> I've checked - all the tables being accessed are already owned by bob.
> Therefore, I cannot grant bob access to his own stuff.
>
> Also... what do you mean "invokers' stuff?" Invoker meaning schema/user who
> executed the procedure? (In my case, this would be bob)
>
> Just to make sure I explained correctly - the bob schema has all the tables.
> The wsgl_public schema just has the wsgl packages such as wsgl, wsglm, and
> wsgjsl. Where it seems to be failing is in this code:
> -----------------
> function QueryHits(
> P_GLOS_CATEGORY1 in varchar2,
> P_GLOS_CATEGORY2 in varchar2,
> P_GLOS_CATEGORY3 in varchar2,
> P_GLOS_OWNER in varchar2) return number is
> begin
> if not BuildSQL(P_GLOS_CATEGORY1,
> P_GLOS_CATEGORY2,
> P_GLOS_CATEGORY3,
> P_GLOS_OWNER) then
> return -1;

This is what returns the -1...

>
> end if;
> return WSGL.CountHits(ZONE_SQL);
> exception
> when others then
> WSGL.DisplayMessage(WSGL.MESS_EXCEPTION, SQLERRM, ''||' : '||'',
> BODY_ATTRIBUTES,
> 'GLOSMAIN2$GLOSTRM.QueryHits');
> return -1;
> end;
> -----------------
> We get that "-1" instead of an actual hit count.
>
> Skimming through the code - all the tables are owned by bob, so it can't be
> a no SELECT priv issue, can it?
>
> Sorry for throwing all that code at you. I'm just not good enough of a
> debugger to narrow it down further.

V6 generated code looks like:
function QueryHits(

            P_COMPANY_IDENTIFIER in varchar2,
            P_NAME2 in varchar2,
            P_CONTACT_NAME8 in varchar2,
            P_SUPPLIER_NUMBER10 in varchar2,
            P_APPROVED12 in varchar2) return number is
      I_QUERY     varchar2(32767) := '';
      I_CURSOR    integer;
      I_VOID      integer;
      I_FROM_POS  integer := 0;
      I_COUNT     number(10);

   begin

      if not BuildSQL(P_COMPANY_IDENTIFIER,
                      P_NAME2,
                      P_CONTACT_NAME8,
                      P_SUPPLIER_NUMBER10,
                      P_APPROVED12) then
         return -1;
      end if;

      if not PreQuery(P_COMPANY_IDENTIFIER,
                      P_NAME2,
                      P_CONTACT_NAME8,
                      P_SUPPLIER_NUMBER10,
                      P_APPROVED12) then
         WSGL.DisplayMessage(WSGL.MESS_ERROR, cg$errors.GetErrors,
                             'Record and Maintain Suppliers'||' :
'||'Suppliers', DEF_BODY_ATTRIBUTES);
         return -1;
      end if;

      I_FROM_POS := instr(upper(ZONE_SQL), ' FROM ');

      if I_FROM_POS = 0 then
         return -1;
      end if;

      I_QUERY := 'SELECT count(*)' ||
                 substr(ZONE_SQL, I_FROM_POS);

      I_CURSOR := dbms_sql.open_cursor;
      dbms_sql.parse(I_CURSOR, I_QUERY, dbms_sql.v7);
      dbms_sql.define_column(I_CURSOR, 1, I_COUNT);
      I_VOID := dbms_sql.execute(I_CURSOR);
      I_VOID := dbms_sql.fetch_rows(I_CURSOR);
      dbms_sql.column_value(I_CURSOR, 1, I_COUNT);
      dbms_sql.close_cursor(I_CURSOR);

      return I_COUNT;

   exception
      when others then
         WSGL.DisplayMessage(WSGL.MESS_EXCEPTION, SQLERRM, 'Record and Maintain
Suppliers'||' : '||'Suppliers',
                             DEF_BODY_ATTRIBUTES, 'supplier$co.QueryHits');
         return -1;

   end; Received on Tue Oct 10 2000 - 14:03:57 CDT