|Notify me of new responses|
Hi, I implemented a PL/SQL function to report the access status of a photo given the photo ID and an ACS user_id. I keep getting "invalid relational operator" errors on the SELECT. Here's the SELECT:
set selection [ns_db 1row $db \ "select count(*) from family_photo_relative_map where relative_id = $relative_id and family_photo_access_check(family_photo_id, $acs_user_id)"]
and here's the PL/SQL function, it returns TRUE if access is granted, FALSE otherwise.
create or replace function family_photo_access_check (v_photo_id family_photos.family_photo_id%TYPE, v_user_id users.user_id%TYPE) return BOOLEAN is v_photo_count integer; v_access_type varchar(20); BEGIN select access_control into v_access_type from family_photos where family_photo_id = v_photo_id; if v_photo_id = 'public' then return TRUE; elsif v_photo_id = 'family' then if v_user_id > 0 then return TRUE; else return FALSE; end if; else select count(*) into v_photo_count from family_photo_access_control where family_photo_id = v_photo_id and user_id = v_user_id; if v_photo_count != 0 then return TRUE; else return FALSE; end if; end if; END family_photo_access_check;
-- Matthew Lee, October 11, 1999
I implemented a nastier, less elegant method of doing access control using just plain old queries and subqueries. It works... I'm happy.
I would have liked for this PL/SQL thing to have worked though...
-- Matthew Lee, October 12, 1999
The prob here is that you need to use a relational operator for the where clause. What does this mean? Include a comparision against the return value of your PL/SQL function, like
... WHERE can_access_p(v_user_id) = 't'
-- Ben Walter, October 15, 1999