Forum OpenACS Development: Error in Oracle's acs_mail_lite queue insert

Collapse
1: Error in Oracle's acs_mail_lite queue insert
Posted by Raúl Morales Hidalgo on 08/13/10 06:24 PM
Hi there,

Since OpenACS 5.4 the acs_mail_lite_queue insertion within acs_mail_lite::send doesn't work properly for oracle. We have fixed it changing

db_dml create_queue_entry {}

to

db_dml create_queue_entry {} -clobs [list $body]

within acs-mail-lite-procs.tcl and

:body,
:extraheaders,
:object_id
)

to

empty_clob(),
:extraheaders,
:object_id
) returning body into :1

is this right in postgresql? If yes, Should I commit the fix?

cheers,
Raúl

Collapse
2: Re: Error in Oracle's acs_mail_lite queue insert (response to 1)
Posted by Don Baccus on 08/13/10 06:48 PM
No, it's not right for PG, leave the PG query alone, and put the PG query into a PG-specific XQL file, and the Oracle one into an Oracle-specific XQL file.
Collapse
3: Re: Error in Oracle's acs_mail_lite queue insert (response to 1)
Posted by Don Baccus on 08/13/10 06:50 PM
Look elsewhere for how to do this correctly, for instance forums message insert should do so.
Collapse
4: Re: Error in Oracle's acs_mail_lite queue insert (response to 3)
Posted by Raúl Morales Hidalgo on 08/13/10 07:17 PM
I had thought about inserting an "if oracle..." within the .tcl but seemed an ugly kludge, I'll look into doing it via pl/sql and pgsql like in forums as soon as I get back form holidays and will post back here.

Thanks!

Collapse
5: Re: Error in Oracle's acs_mail_lite queue insert (response to 4)
Posted by Jim Lynch on 08/14/10 09:05 PM
You don't want to be checking for which database in tcl, because there is already a structure for that which you should always use if you're committing anything...

You should find dirs .../$service/packages/$yourpackage/sql/postgresql and .../$service/packages/$yourpackage/sql/oracle, if you would be defining pl/sql procs, they would go in the oracle dir...

Then, you can have queries in places like .../$service/packages/$yourpackage/www/somepage-oracle.xql and .../$service/packages/$yourpackage/www/somepage-postgresql.xql as well as .../$service/packages/$yourpackage/tcl/some-procs-postgresql.xql and .../$service/packages/$yourpackage/tcl/some-procs-oracle.xql and possibly other places as well; check documentation and source of openacs for details.

Collapse
6: Re: Error in Oracle's acs_mail_lite queue insert (response to 1)
Posted by Jim Lynch on 08/14/10 09:07 PM
Look at several .xql files to see how to structure it; you'll need to get very familiar also with how to name queries so that they can be referred to.
Collapse
7: Re: Error in Oracle's acs_mail_lite queue insert (response to 1)
Posted by Emmanuelle Raffenne on 08/16/10 10:35 AM
Hi all,

There are already acs-mail-lite-procs-oracle.xql and acs-mail-lite-procs-postgresql.xql and the query create_queue_entry for each.

So if you need to fix the oracle query, go ahead, the postgresql one works well.

However, I don't know if "db_dml create_queue_entry {} -clobs [list $body]" will work with postgresql, so it's what you need to check.