Forum OpenACS Development: How do I get the creation_user from acs_objects?

How do I get the creation_user from acs_objects? Should I just do a join against the acs_objects table? I tried using acs_object__get_attribute but that returns a varchar and I need it as an integer. Is there a way to do a type conversion? I want to write some sql code like this:

select ...
person__name(acs_object__get_attribute
(r.response_id, 'creation_user')) as respondant_name
...

Is there a simple way to convert varchar to integer?

Thanks Dan!

I just fixed this in cvs so that acs_object__get_attribute will work correctly with timestamps. You just need to add a cast to varchar on the selected value:

create function acs_object__get_attribute (integer,varchar)
returns varchar as '
declare
  object_id_in           alias for $1;  
  attribute_name_in      alias for $2;  
  v_table_name           varchar(200);  
  v_column               varchar(200);  
  v_key_sql              text; 
  v_return               text; 
  v_storage              text;
  v_rec                  record;
begin

   v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);

   v_column     := acs_object__get_attr_storage_column(v_storage);
   v_table_name := acs_object__get_attr_storage_table(v_storage);
   v_key_sql    := acs_object__get_attr_storage_sql(v_storage);

   for v_rec in execute ''select '' || quote_ident(v_column) || ''::varchar as return from '' || quote_ident(v_table_name) || '' where '' || v_key_sql
      LOOP
        v_return := v_rec.return;
        exit;
   end loop;
   if not FOUND then 
       return null;
   end if;

   return v_return;

end;' language 'plpgsql';

Yeah, that didn't work for me either.  I ended up doing a join against the acs_objects table.

Dan, I tried using the cast and I get some really strange behavior. If I run one line of the query in psql there are no problems:

select person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'

The only things that show up are the following:

NOTICE:  identifier "acs_object__get_attribute_storage" will be truncated to "acs_object__get_attribute_stora"
NOTICE:  identifier "acs_object__get_attr_storage_column" will be truncated to "acs_object__get_attr_storage_co"
NOTICE:  identifier "acs_object__get_attr_storage_table" will be truncated to "acs_object__get_attr_storage_ta"
NOTICE:  identifier "acs_object__get_attr_storage_sql" will be truncated to "acs_object__get_attr_storage_sq"

Now if I do it again for creation_date right after doing the query for creation_user, it will bomb and say "ERROR: type of v_rec.return doesn't match that when preparing the plan"

select acs_object__get_attribute(r.response_id,'creation_date') as submission_date
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'

If I exit psql and restart it and do the same queries again, the same problem comes up (the second call of the function on a different object attribute fails). If I reverse the order of the queries, the second query still fails. If I do the full query (see code below) it also fails! It looks like I can run the acs_objects__get_attribute function once per query (maybe per session). The first call of the function always works, but the rest of the calls fail. However, I can repeat the first query indefinitely and it will always work.

After you updated the acs_object__get_attribute function, I dropped it and reloaded the new function. I even dropped the whole database and reloaded it. Do you have any idea what is going on? I hope my explaination was clear.

[04/Jul/2001:23:15:23][723.4101][-conn0-] Notice: Querying '

select
  varchar_answer as response,
  person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name,
  acs_object__get_attribute(r.response_id,'creation_date') as submission_date,
  acs_object__get_attribute(r.response_id,'creation_ip') as ip_address
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'
  order by submission_date;'
[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: Ns_PgExec: result status: 7 mes
sage: ERROR:  type of v_rec.return doesn't match that when preparing the plan

[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: dbinit: error(localhost::openacs4,ERROR:  type of v_rec.return doesn't match that when preparing the plan
): '
select
  varchar_answer as response, person__name(acs_object__get_attribute(r.response_id,'creation_user')::text::integer) as respondent_name,
  acs_object__get_attribute(r.response_id,'creation_date') as submission_date,
  acs_object__get_attribute(r.response_id,'creation_ip') as ip_address
from
  survsimp_responses r,
  survsimp_question_responses qr
where
  qr.response_id = r.response_id
  and qr.question_id = '2216'
  order by submission_date

      '
[04/Jul/2001:23:15:23][723.4101][-conn0-] Notice: RP (109.983 ms): error in rp_handler: serving GET /surveys/admin/view-text-responsesquestion_id=2216
        ad_url "/surveys/admin/view-text-responses" maps to file "/web/openacs-4/packages/simple-survey/www/admin/view-text-responses.tcl"
errmsg is Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: RestrictErrorsToAdminsP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: AutomaticErrorReportingP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Warning: APM: EnabledP does not exist
[04/Jul/2001:23:15:23][723.4101][-conn0-] Error: GET /surveys/admin/view-text-responses question_id=2216 Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind select nsdb0 {
...

Hi Dan,

Thanks!  That did the trick.  Did you update the function in the cvs repository?  I don't see the changes in packages/acs-kernel/sql/postgresql/acs-object-create.sql.