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

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
Posted by Don Baccus on
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
Posted by Don Baccus on
Look elsewhere for how to do this correctly, for instance forums message insert should do so.
Collapse
Posted by Raúl Morales Hidalgo on
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
Posted by Jim Lynch on
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
Posted by Jim Lynch on
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
Posted by Emmanuelle Raffenne on
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.