Forum OpenACS Q&A: ad_form and getting around postgres

I have a table defined with this new-data clause:

-------------
in add-edit.tcl:
-------------

-new_data {

    db_exec_plsql new_brand { }

    ad_returnredirect "."
    ad_script_abort

}

Unfortunately, because of Postgres' 16 argument limit, I only put the most important items in the creation of the new brand.

-------------
This is from add-edit-postgresql.xql:
-------------

<fullquery name="new_brand">
      <querytext>
      select brand__new(
          null,
          :name,
          :name,
          :notes,
          'f',
          [db_nextval brand_brand_lid_seq],
          'brand',
          now(),
          :user_id,
          :peeraddr,
          :package_id
      );
      </querytext>
</fullquery>

-------------
and here is the definition of brand__new:
-------------

create or replace function brand__new (integer,varchar,varchar,varchar,varchar,integer,varchar,timestamp,integer,varchar,integer)
returns integer as '
declare
  p_brand_id              alias for $1;      -- default null
  p_name                  alias for $2;
  p_display_name          alias for $3;
  p_notes                alias for $4;
  p_ibr_p                alias for $5;
  p_brand_lid            alias for $6;
  p_object_type          alias for $7;      -- default ''brand''
  p_creation_date        alias for $8;      -- default now()
  p_creation_user        alias for $9;      -- default null
  p_creation_ip          alias for $10;      -- default null
  p_context_id            alias for $11;      -- default null
  v_brand_id      brand.brand_id%TYPE;
begin
  v_brand_id := acs_object__new (
        p_brand_id,
                p_object_type,
                p_creation_date,
                p_creation_user,
                p_creation_ip,
                p_context_id
        );

  insert into
  brand
  (brand_id,
  brand_lid,
  name,
  display_name,
  notes,
  ibr_p)
  values
  (v_brand_id,
    p_brand_lid,
    p_name,
    p_display_name,
    p_notes,
    p_ibr_p);

PERFORM acs_permission__grant_permission(
    v_brand_id,
    p_creation_user,
    ''admin''
);

return v_brand_id;

end;' language 'plpgsql';

-------------
So I want to UPDATE the table after creating it.

I tried using a db_dml update inside the above -new_data clause, but it didn't seem to do anything (even give an error).

This doesn't seem to work:

} -new_data {

    db_exec_plsql new_brand { }
    db_dml brand_update {
      select name,
            notes,
            brand_lid,
            display_name,
            status,
            to_char(tm_first_used,'YYYY MM DD') as tm_first_used,
            to_char(tm_applied,'YYYY MM DD') as tm_applied,
            to_char(tm_issued,'YYYY MM DD') as tm_issued,
            to_char(tm_renewal,'YYYY MM DD') as tm_renewal,
            registration_type,
            bread_p,
            bread_mixes_p,
            cookies_p,
            crackers_p,
            other_p,
            application_type,
            registration_number,
            to_char(sect_8_15_renewal,'YYYY MM DD') as sect_8_15_renewal,
            ibr_p
        from brand
      where brand_id = :brand_id

    }

    ad_returnredirect "."
    ad_script_abort

}


It seems like this should be pretty easy, but I'm not sure where to go from here. Any suggestions?

Looking in the log, here is what it says:

[09/Apr/2003:15:48:31][21439.98311][-conn3-] Notice: Querying '
      select brand__new(
          null,
          'DDDDDDDDDD',
          'DDDDDDDDDD',
          'DDDDDDDDDDDDDDDd',
          'f',
          71,
          'brand',
          now(),
          '2284',
          '192.168.1.21',
          '2328'
      );'
[09/Apr/2003:15:48:33][21439.98311][-conn3-] Notice: dbinit: sql(localhost::ibr)
: '
      select brand__new(
          null,
          'DDDDDDDDDD',
          'DDDDDDDDDD',
          'DDDDDDDDDDDDDDDd',
          'f',
          71,
          'brand',
          now(),
          '2284',
          '192.168.1.21',
          '2328'
      );
      '
[09/Apr/2003:15:48:33][21439.98311][-conn3-] Notice: Querying '
      update brand
      set name                = 'DDDDDDDDDD',
          notes              = 'DDDDDDDDDDDDDDDd',
          display_name        = 'DDDDDDDDDDd',
          status              = 'a',
          tm_first_used      = to_timestamp('2003 4 9 {} {} {} {MONTH DD YYYY}'
,'YYYY MM DD HH24 MI SS'),
          tm_applied          = to_timestamp('2003 4 9 {} {} {} {MONTH DD YYYY}'
,'YYYY MM DD HH24 MI SS'),
          tm_issued          = to_timestamp('2003 4 9 {} {} {} {MONTH DD YYYY}'
,'YYYY MM DD HH24 MI SS'),
          tm_renewal          = to_timestamp('2003 4 9 {} {} {} {MONTH DD YYYY}'
,'YYYY MM DD HH24 MI SS'),
          registration_type  = 'w',
          bread_p            = 't',
          bread_mixes_p      = 't',
          cookies_p          = 't',
          crackers_p          = 't',
          other_p            = 't',
          application_type    = 'u',
          registration_number = NULL,
          sect_8_15_renewal  = to_timestamp('2003 4 9 {} {} {} {MONTH DD YYYY}'
,'YYYY MM DD HH24 MI SS'),
          ibr_p              = 't'
      where brand_id = '2836';'

It looks like the brand_id is different in the update statement. Why would that happen?

What would be the way to make both the insert and update transactional so I can't lose the update if some problem between happens?

Just for everyone else that might find this thread, here's my corrected ad_form declaration:
----------------

ad_form -name add_edit -form {

    brand_id:key

    ... declaration of form elements

} -select_query_name brand_select -on_submit {

    set user_id [ad_conn user_id]
    set peeraddr [ad_conn peeraddr]

} -new_data {

    db_transaction {
      db_exec_plsql new_brand { }
      db_dml brand_update { }
    }

    ad_returnredirect "."
    ad_script_abort

} -edit_data {

    db_dml brand_update { }

    ad_returnredirect "."
    ad_script_abort

}

-----------

The bug was in the add-edit-postgres.xql file. Here is how it was:

<fullquery name="new_brand">
      <querytext>
      select brand__new(
          null,
          :name,
          :name,
          :notes,
          'f',
          [db_nextval brand_brand_lid_seq],
          'brand',
          now(),
          :user_id,
          :peeraddr,
          :package_id
      );
      </querytext>
</fullquery>

-------------
If you look at the declaration of brand__new, the first parameter is brand_id. I was passing it null, so it wasn't getting the brand_id that was generated by ad_form.

Hopefully, this will help out someone else that makes the same mistake I did.

For the search engine: postgres 16 argument ad_form key changes